• Welcome, Guest. Please login.
 
July 17, 2019, 04:23:08 pm

News:

Welcome to the SQLitening support forums!


Creating recordsets with or without binding

Started by cj, May 01, 2015, 02:18:46 pm

Previous topic - Next topic

cj

Each of these 4 examples produce the same result.

Example 1.  Create recordset to string looping through each row and column.
Example 2.  Create recordset to array

Example 3. Create recordset to string using compression and encryption.
Example 4. Create recordset to array using compression and encryption.


#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG

  LOCAL sData AS STRING, sArray() AS STRING
  LOCAL COL,rows AS LONG
'--------------------------------------------------------------------------
  'slConnect "",0  'ip address and port for client/server
  slOpen "sample.db3","C"  'create if it does not exist
  slexe "drop table if exists simple"
  slexe "create table if not exists simple(c1,c2)"
  slexe "insert into simple values('hello','world!')"
'--------------------------------------------------------------------------
'Example 1.  Build recordset to string
  slsel "select * from simple"
  rows = 0
  sData = ""
  DO WHILE slgetRow
    INCR rows
    FOR COL = 1 TO slGetColumnCount
      sData +=  slf(COL) + ","
    NEXT
    ASC(sData,LEN(sData)) = 13 'last , to carriage return
  LOOP
  MSGBOX sData,,"Rows" + STR$(rows)
'--------------------------------------------------------------------------
'Example 2.  Build recordset to array
  slSelAry("select * from simple",sArray(),"Q44 c")
  MSGBOX (JOIN$(sArray(),$CR)),,"Rows" + STR$(UBOUND(sArray))

'--------------------------------------------------------------------------
'BOUND DATA EXAMPLES

'Insert text Compressed and Encrypted  TCN parameters
  slexe "delete from simple"   'delete previous data in table
  slSetProcessMods "K" + STRING$(16,"A") 'encryption key 16,24 or 32 bytes
  slExeBind "insert into simple values(?,?)", _
    slBuildBindDat("hello","TCN") + _
    slBuildBindDat("world!","TCN")

'Example 3.  Build recordset to string Decrypted and Uncompressed
  slsel "select * from simple"
  rows = 0
  sData = ""
  DO WHILE slgetRow
    INCR rows
    FOR COL = 1 TO slGetColumnCount
      sData +=  slfx(COL,"DU") + ","  'decrypt uncompress
    NEXT
    ASC(sData,LEN(sData)) = 13 'last , to carriage return
  LOOP
  MSGBOX sData,,"Rows" + STR$(rows)
'--------------------------------------------------------------------------
'Example 4.  Build recordset array Decrypted and Uncompressed
  slSelAry("select * from simple",sArray(),"D1,2 U1,2 Q44 c")
  MSGBOX (JOIN$(sArray(),$CR)),,"Rows" + STR$(UBOUND(sArray))
END FUNCTION
'Notes:
'D1,2  = Decrypt columns 1,2
'U1,2  = Uncompress columns 1 and 2
'Q44   = delimit columns with a comma ASCII 44
'c     = do not include column headings in recordset

cj

The examples above returned records without a WHERE clause.
If there is a WHERE clause with BINDING:

   
    sBind = slBuildBindDat(sInput,"TCN")
    slExeBind "insert into simple values(?)",sBind
    slSelBind "Select c1 from simple where c1 =?",sBind
    'slSel "select c1 from simple"  'if no WHERE
    IF slGetErrorNumber = 0 THEN
      DO WHILE slGetRow
        ? slfx(1,"DU"),,"slSelBind Results" 'D=decrypt, U=uncompress
      LOOP
    END IF
  LOOP