Compress/encrypt made easier

[cj]

Please post any comments/suggestions.

Eliminate calling slBuildInsertOrUpdate, slBuildBindDat and slExeBind.
Those functions are called automatically within InsertRecord and UpdateRecord.

Calling syntax:
InsertRecord sTableName$, sInsertData$(),sInsertMod$()
UpdateRecord sTableName$,sColumnsToUpdate$,sUpdateData$(),sUpdateMod$(), sWhere$
The data for each column goes into either sInsertData$() or sUpdateData$().

If performing an update:
Columns names are separated by commas in sColumnsToUpdate$.
Example: sColumnsToUpdate$ =  "F1,F2,F3,Client,Invoice"

2 arrays should be REDIMed before each call (this may be improved upon.)
NumberOfColumnsToUpdate = 2  'change this
REDIM sUpdateData(NumberOfColumnsToUpdate)AS STRING
REDIM sUpdateMod(NumberOfColumnsToUpdate) AS STRING
Flags are set for each column by the elements in the arrays sInsertMod$() and sUpdateMod$().
sUpdateMod$(1) =  "TCN"  column 1 is  text/compress/encrypt.
sUpdateMod$(2) = " "T"     column 2 is text.

To decrypt/uncompress recordsets use SQLitening routines slFX, slFNX or slSelAry.
SlSelAry requires all rows for all columns used the same compression/encryption.

Code Select Expand


FUNCTION InsertRecord(sTableName AS STRING, sInsertData() AS STRING,sInsertMod() AS STRING) AS LONG
  LOCAL COL, NumberOfColumns AS LONG
  LOCAL sPlaceHolders, sBindData AS STRING
  NumberOfColumns = UBOUND(sInsertData)
  DIM sArray(1 TO NumberOfColumns) AS STRING
  FOR COL = 1 TO NumberOfColumns
    sArray(COL) = slBuildBindDat(sInsertData(COL),sInsertMod(COL))
  NEXT
  sBindData = JOIN$(sArray(),"")
  sPlaceHolders=REPEAT$(NumberOfColumns-1,"?"&$NUL) + "?"
  slExeBind slBuildInsertOrUpdate(sTableName,sPlaceHolders),sBindData
END FUNCTION


Code Select Expand


FUNCTION UpdateRecord(sTableName       AS STRING, _
                      sColumnsToUpdate AS STRING, _
                      sUpdateData()    AS STRING,    _
                      sUpdateMod()     AS STRING, _
                      sWhere           AS STRING) AS LONG
  LOCAL COL, NumberOfColumns AS LONG
  LOCAL sPlaceHolders, sBindData AS STRING
  NumberOfColumns = UBOUND(sUpdateData)
  DIM sArray(1 TO NumberOfColumns) AS STRING
  FOR COL = 1 TO NumberOfColumns
    sArray(COL) = slBuildBindDat(sUpdateData(COL),sUpdateMod(COL))
  NEXT
  sBindData = JOIN$(sArray(),"")
  sPlaceHolders=REPEAT$(NumberOfColumns-1,"?"&$NUL) + "?"
  slExeBind(slBuildInsertOrUpdate(sTableName,sPlaceHolders,sColumnsToUpdate,sWhere),sBindData)
END FUNCTION 


Here is a demo with help after the code on modchars, compression and encryption.
[code]
#DIM ALL
#INCLUDE "sqlitening.inc"  'binding.bas
%DropTable = 0
FUNCTION PBMAIN () AS LONG

  LOCAL sDatabaseName, sTableName,sData,sColumnsToUpdate,sResult,sWhere,sIp AS STRING
  LOCAL TotalColumns,NumberOfColumnsToUpdate,x,PortNumber AS LONG

  '-- Define encryption key  ------------------------------------
  LOCAL sEncryptionKey AS STRING * 16 '16,24, or 32
  sEncryptionKey = "VALID"
  slSetProcessMods "K" + sEncryptionKey 'do not combine Mods

  '-- Set Database name, table name, option IP address ----------
  sDatabaseName = "Test.db3"
  sTableName     = "T1"
  sIp            = "192.168.1.2"   'optional IP address or computer name
  sIp            = "174.74.114.227" 'optional IP address or computer name
  PortNumber     = 0       '0 = default (normally 51234)

  '-- Connect to server if sIP defined --------------------------
  IF LEN(sIp) THEN  slConnect sIp,PortNumber 'internet

  '--  Open database ---------------------------------------------
  slOpen sDatabaseName,"C"
top:
  '-- Drop previous table (optional) ----------------------------
  IF %DropTable THEN slExe "Drop Table if Exists " + sTableName

  '-- Create table if it does not exist -------------------------
  slExe "Create Table If Not Exists " + sTableName + "(F1,F2)"

  '-- Insert record ---------------------------------------------
  TotalColumns = 2
  REDIM sArray(1 TO TotalColumns) AS STRING
  REDIM sInsertMod(1 TO TotalColumns) AS STRING
  FOR x = 1 TO TotalColumns
    sArray(x) = "(New column "+FORMAT$(x) + ")"
    sInsertMod(x) = "TCN"  'Text,compress,encrypt
  NEXT
  InsertRecord sTableName, sArray(),sInsertMod()
  sResult = "After insert:" + $CR + SQL("Select * from " + sTableName)

  '-- Update all columns ----------------------------------------
  NumberOfColumnsToUpdate = 2 'REDIM update arrays
  REDIM sUpdateData(1 TO NumberOfColumnsToUpdate)    AS STRING
  REDIM sUpdateMod(1 TO NumberOfColumnsToUpdate) AS STRING
  sUpdateData(1) = "(1)"
  sUpdateData(2) = "(2)"
  sUpdateMod(1) = "TCN"
  sUpdateMod(2) = "TCN"
  sColumnsToUpdate = "F1,F2"
  sWhere = "*"
  UpdateRecord sTableName,sColumnsToUpdate,sUpdateData(),sUpdateMod(), sWhere
  sResult = sResult + $CR + "After two column update:" + $CR + SQL("Select * from " + sTableName)

  '-- Update only one column ------------------------------------
  NumberOfColumnsToUpdate = 1 'REDIM update arrays
  REDIM sUpdateData(1 TO NumberOfColumnsToUpdate)    AS STRING
  REDIM sUpdateMod(1 TO NumberOfColumnsToUpdate) AS STRING
  sUpdateData(1) = "(Column 2 only updated)"
  sUpdateMod(1) = "TCN"
  sColumnsToUpdate = "F2"
  sWhere = "*"
  UpdateRecord sTableName,sColumnsToUpdate,sUpdateData(),sUpdateMod(), sWhere
  sResult = sResult + $CR + "After one column update:" + $CR + SQL("Select * from " + sTableName)
  x = MSGBOX(sResult,%MB_YESNOCANCEL,"[Y]es=Rerun   [N]o=Exit   Cancel=Drop table/rerun")
  IF x = %IDYES THEN
     GOTO TOP  ' ALT/Y rerun ALT/N exit (if options go off the screen)
  ELSEIF x = %IDNO THEN
    EXIT FUNCTION
  ELSEIF x = %IDCANCEL THEN
    slExe "Drop Table if Exists " + sTableName
    GOTO TOP
  END IF

  IF LEN(sIp) THEN slDisconnect
END FUNCTION
  '--------------------------------------------------------------
FUNCTION InsertRecord(sTableName AS STRING, sInsertData() AS STRING,sInsertMod() AS STRING) AS LONG

  LOCAL COL, NumberOfColumns AS LONG
  LOCAL sPlaceHolders, sBindData AS STRING

  NumberOfColumns = UBOUND(sInsertData)
  DIM sArray(1 TO NumberOfColumns) AS STRING
  FOR COL = 1 TO NumberOfColumns
    sArray(COL) = slBuildBindDat(sInsertData(COL),sInsertMod(COL))
  NEXT
  sBindData = JOIN$(sArray(),"")
  sPlaceHolders=REPEAT$(NumberOfColumns-1,"?"&$NUL) + "?"
  slExeBind slBuildInsertOrUpdate(sTableName,sPlaceHolders),sBindData
END FUNCTION
   '--------------------------------------------------------------
FUNCTION UpdateRecord(sTableName       AS STRING, _
                      sColumnsToUpdate AS STRING, _
                      sUpdateData()          AS STRING,    _
                      sUpdateMod()         AS STRING, _
                      sWhere           AS STRING) AS LONG

  LOCAL COL, NumberOfColumns AS LONG
  LOCAL sPlaceHolders, sBindData AS STRING

  NumberOfColumns = UBOUND(sUpdateData)
  DIM sArray(1 TO NumberOfColumns) AS STRING
  FOR COL = 1 TO NumberOfColumns
    sArray(COL) = slBuildBindDat(sUpdateData(COL),sUpdateMod(COL))
  NEXT
  sBindData = JOIN$(sArray(),"")
  sPlaceHolders=REPEAT$(NumberOfColumns-1,"?"&$NUL) + "?"
  slExeBind(slBuildInsertOrUpdate(sTableName,sPlaceHolders,sColumnsToUpdate,sWhere),sBindData)
END FUNCTION
  '--------------------------------------------------------------
FUNCTION SQL(SqlStatement AS STRING) AS STRING
LOCAL columns,x AS LONG, s AS STRING
slSel SqlStatement: columns = slGetColumnCount
DO WHILE slGetRow
  FOR x = 1 TO Columns
    s = s + slfx(x,"DU") + ","  'slfx decrypt/uncompress
   NEXT
  ASC(s,LEN(s)) = 13            '$CR after each row
LOOP
FUNCTION = s
END FUNCTION
'Calling syntax:
'InsertRecord sTableName$, sInsertData$(),sInsertMod$()
'UpdateRecord sTableName$,sColumnsToUpdate$,sUpdateData$(),sUpdateMod$(), sWhere$

'See remarks after code on compression and encryption.
'Flags are set for each column by the elements in the arrays sInsertMod() and sUpdateMod$().
'sUpdateMod(1) =  "TCN"  column 1 is  text/compress/encrypt.
'sUpdateMod(2) = " "T"     column 2 is text.

'To decrypt/uncompress data see SQLitening routines slFX and slFNX.

'
'FUNCTION InsertRecord/UpdateRecord call slBuildBindDat to produce the specially
'formatted string for each column of the record. Supply the letters in the element
'InsertMod() or UpdateMod() for each column in the record.
'--
'Calling Insert/Record/UpdateRecord does the following automatically:

'Returns aBindDat entry specially formatted string required by slExeBind.
'Data contains the value you want converted into a BindDat. A BindDat(s) is required
'to be passed to slExeBind. The returned data may also be compressed and/or encrypted.
'If an error occurs then the return value will be an empty string.
'Use slGetError or slGetErrorNumber to determine the error.

'ModChars:
'