SQLitening Support Forum

General => General Board => Topic started by: cj on October 14, 2014, 07:09:30 am

Title: Bind RowId
Post by: cj on October 14, 2014, 07:09:30 am
'IF slExeBind uses 'NULL' as parameter for rowid as in (null,?,?) the rowid cannot be manually added.
'To get around this the function BindRowID is below.

MACRO JustBind(sData)        = slBuildBindDat(sData)       'no compression or encryption
#INCLUDE "sqlitening.inc"                                  'sqlitening routines
FUNCTION PBMAIN&                                           '
  DropTable& = 0                                           'non-zero to drop table
  sID$      = "null"                                       'pass number or nothing/null for next highest
  sColumn2$ = "Jane"                                       'data to insert into column one
  sColumn3$ = "Dough"                                      'data to insert into column two
  slOpen "sample.db3","C"                                  'open sample database
  IF DropTable& THEN slexe "drop table if exists t1"       'drop table if set
  slexe "create table if not exists t1(ID INTEGER PRIMARY KEY AUTOINCREMENT, FNAME,LNAME)" 'table with 2 columns
  sBindData$ = BindRowId(sID$) + JustBind(sColumn2$) + JustBind(sColumn3$)
  slExeBind "Insert into t1 values(?,?,?)",sBindData$        'insert bind data
  slselAry "select * from t1",sRecordSet$(),"Q9"     'select into sRecordSet() array
  MSGBOX JOIN$(sRecordSet$(),$CRLF),,"Join all rows"       'display recordset array
END FUNCTION

FUNCTION BindRowID(sNumber AS STRING) AS STRING
  IF LEN(sNumber) = 0 OR UCASE$(sNumber) = "NULL" THEN
    FUNCTION = slBuildBindDat("","Z")      'bind as null if nothing passed
  ELSE
    FUNCTION = slBuildBindDat(sNumber,"T") 'bind as text if a number is passed
  END IF
END FUNCTION
                                            '
'If using both encryption and compression
'MACRO CompressEncrypt(sData) = slBuildBindDat(sData,"CN")  'compress/encrypt
'MACRO Compress(sData)        = slBuildBindDat(sData,"C")   'compress
'MACRO Encrypt(sData)         = slBuildBindDat(sData,"N")   'encrypt
'slSetProcessMods "K" + SPACE$(32)                         'required encrption key
'Q one-dimension 9=tab delimited  U1,2 uncompress columns 1,2    D1,2 Decrypt columns 1,2
'slselAry "select rowid, * from t1",sRecordSet$(),"Q9  U1,2  D1,2" 'include c for no column names
Title: Bind RowId
Post by: cj on October 14, 2014, 07:24:06 am
Example using both encryption and compression
MACRO CompressEncrypt(sData) = slBuildBindDat(sData,"CN")  'compress/encrypt
#INCLUDE "sqlitening.inc"                                  'sqlitening routines
FUNCTION PBMAIN&                                           '
  slSetProcessMods "K" + SPACE$(32)                        'required encrption key
  DropTable& = 1                                           'non-zero to drop table
  sID$      = ""                                           'pass number or nothing/null for next highest
  sColumn2$ = "Jane"                                       'data to insert into column one
  sColumn3$ = "Dough"                                      'data to insert into column two
  slOpen "sample.db3","C"                                  'open sample database
  IF DropTable& THEN slexe "drop table if exists t1"       'drop table if set
  slexe "create table if not exists t1(ID INTEGER PRIMARY KEY AUTOINCREMENT, FNAME,LNAME)" 'table with 2 columns
  sBindData$ = BindRowId(sID$) + CompressEncrypt(sColumn2$) + CompressEncrypt(sColumn3$)
  slExeBind "Insert into t1 values(?,?,?)",sBindData$      'insert bind data
  slselAry "select id,fname,lname from t1",sRecordSet$(),"Q9  U2,3  D2,3" 'include c for no column names
  MSGBOX JOIN$(sRecordSet$(),$CRLF),,"Join all rows"       'display recordset array
END FUNCTION

FUNCTION BindRowID(sNumber AS STRING) AS STRING
  IF LEN(sNumber) = 0 OR UCASE$(sNumber) = "NULL" THEN
    FUNCTION = slBuildBindDat("","Z")                      'bind as null if nothing passed
  ELSE
    FUNCTION = slBuildBindDat(sNumber,"T") 'bind as text if a number is passed
  END IF
END FUNCTION
                                          '
'If using both encryption and compression
'MACRO CompressEncrypt(sData) = slBuildBindDat(sData,"CN")  'compress/encrypt
'MACRO Compress(sData)        = slBuildBindDat(sData,"C")   'compress
'MACRO Encrypt(sData)         = slBuildBindDat(sData,"N")   'encrypt
'slSetProcessMods "K" + SPACE$(32)                         'required encrption key
'Q one-dimension 9=tab delimited  U1,2 uncompress columns 2,3    D1,2 Decrypt columns 2,3
'slselAry "select rowid, * from t1",sRecordSet$(),"Q9  U2,3  D2,3" 'include c for no column names