• Welcome, Guest. Please login.
 
May 26, 2019, 01:38:36 am

News:

Welcome to the SQLitening support forums!


Bind RowId

Started by cj, October 14, 2014, 07:09:30 am

Previous topic - Next topic

cj

'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

cj

October 14, 2014, 07:24:06 am #1 Last Edit: October 14, 2014, 07:26:26 am by cj
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