• Welcome, Guest. Please login.
 
August 26, 2019, 12:40:11 am

News:

Welcome to the SQLitening support forums!


Get Encrypted Recordsets

Started by cj, September 24, 2015, 09:47:28 pm

Previous topic - Next topic

cj


#DIM ALL
#INCLUDE "sqlitening.inc"
$IntegerColumnIndicator = "_NUM"
$BindInteger   = "i"  'integer column
$BindCN        = "CN" 'compressed encrypt compliment of "DU"
$UnBindCN      = "DU" 'decrypt uncompress compliment of "CN"
'------------------------------------------------------------------------
FUNCTION PBMAIN () AS LONG
  LOCAL ColNumber,RowNumber,cols,rows AS LONG
  LOCAL sBind,sFillQuestionMarks, sResult AS STRING

  slSetProcessMods "K" + CHR$(1 TO 32) 'set encryption key
'------------------------------------------------------------------------
  slOpen "junk.db3","C"                'open/optionally create database
  slexe "drop table if exists table1"  'drop any previous junk table
  slexe "create table if not exists table1(Id_num Integer Primary Key, C2,C3_num Integer)
' Insert records
  FOR RowNumber = 1 TO 30
    sBind = slBuildBindDat(STR$(RowNumber),$BindInteger)          'integer
    sBind+= slBuildBindDat("Col two",$BindCN)                     'compress encrypt
    sBind+= slBuildBindDat(STR$(RowNumber*1000),$BindInteger)     'integer
'------------------------------------------------------------------------
    slExeBind "insert into table1 values(?,?,?)",sBind            'execute insert
  NEXT
'------------------------------------------------------------------------
  'Get recordset for records between 3 and 7
  sFillQuestionMarks = slBuildBindDat("3","i") + slBuildBindDat("7","i")
  slSelBind  "select rowid as row_num,* from table1 where id_num >? and id_num < ?",sFillQuestionMarks
'------------------------------------------------------------------------
  DIM sArray() AS STRING  'result results as a string and optional array
  sResult = GetResults(sArray(),",") 'results in both a string and array
  ? "String:" + $CR + sResult + $CR + $CR+ _
    "Array:" + $CR + JOIN$(sArray(),$CR),,"String and array results"
END FUNCTION
'------------------------------------------------------------------------
FUNCTION GetResults(sRecordSet() AS STRING,sColDelimiter AS STRING) AS STRING
  'put column names into sColumnNamesArray()
  LOCAL ColNumber,RowNumber,cols,rows AS LONG
  LOCAL sColumnNames AS STRING
  sColumnNames = slGetcolumnName 'string of column names
  cols = PARSECOUNT(sColumnNames,$NUL)  'number of column names
  IF cols = 0 THEN EXIT FUNCTION
'------------------------------------------------------------------------
  'If any column name ends with "_NUM" $UnBindInteger with $UnbindInteger
  'All other column names will unbind with $UnBindCN slfx(colnum,"DU")

  DIM sColumnNames(1 TO cols) AS STRING
  DIM sUnBind(1 TO cols) AS STRING
  PARSE sColumnNames, sColumnNames(),$NUL 'names into array
  FOR ColNumber = 1 TO Cols               'see if any numeric columns
    IF UCASE$(RIGHT$(sColumnNames(ColNumber),4)) = $IntegerColumnIndicator THEN
      sUnBind(colNumber) = $BindInteger
    ELSE
      sUnBind(colNumber) = $UnBindCN 'compressed encrypted
    END IF
  NEXT
'------------------------------------------------------------------------
  REDIM sCol(1 TO cols)    AS STRING   'build row from column(col,slfx)
  REDIM sRecordSet(1 TO 10000) AS STRING   'recordset
  DO WHILE slGetRow
    INCR rows
    IF Rows > 10 THEN
      REDIM PRESERVE sRecordSet(1 TO UBOUND(sRecordSet)+10000)
    END IF
    FOR ColNumber = 1 TO cols
      IF UCASE$(RIGHT$(sColumnNames(ColNumber),4)) = $IntegerColumnIndicator THEN
        sCol(ColNumber)=slfx(ColNumber) 'no modchar needed with integer
      ELSE
        sCol(ColNumber) = slfx(colnumber,$UnBindCN) 'unbindCN = "DU"
      END IF
    NEXT
    sRecordSet(rows) = JOIN$(sCol(),sColDelimiter)
  LOOP
  IF rows THEN
    REDIM PRESERVE sRecordSet(1 TO Rows)
    FUNCTION = JOIN$(sRecordSet(),$CR)
  ELSE
    ERASE sRecordSet() 'return nothing
  END IF
END FUNCTION

cj

September 24, 2015, 09:51:06 pm #1 Last Edit: September 24, 2015, 10:32:30 pm by cj
'Create table with numeric column names ending with _NUM
Create T1(id_num INTEGER, c2 text, c3 text, Amt_num INTEGER)

'Get recordset for records between 3 and 7
sFillQuestionMarks =   slBuildBindDat("3","i") +   slBuildBindDat("7","i")
slSelBind  "select rowid as row_num,* from table1 where id_num >? and id_num < ?",sFillQuestionMarks
GetResults(sArray(),$TAB)

I will work on a helper function to make filling in the ? marks more natural.
The columns can be returned in any order without having to program how to handle bound columns.
If a column was created with _NUM on the end it is returned as INTEGER all other compressed/encrypted

Placing _NUM after numeric column names also makes debugging a little easier.