Encrypted column recordsets in any order

[cj]

Recordsets  with compressed, encrypted or normal text fields in the demos use a bunch of IF statements
which depend upon the order of the data in the columns to match the SELECT statement.
Column order can be changed so this logic may fail if the SELECT changes.
An example would be adding ROWID as the first column wanted.
for column = 1 to slGetColumnCount
    if column  = 1 then sdata = slfx(column,"DU") 'this is now incorrect
next

This demonstrates creating tables with _C,  _N ,  or _CN appended to a column name.
_C compressed column   _N encrypted column   _CN compressed and encrypt column.
slexe "Create Table if not exists T1(F1_C,  F2_N,  F3,  F4_CN)
This keep you constantly remembering which columns are encrypted and compressed.
It eliminates IF logic for the columns.
A  single recordset building routine will handle any column from any table.

Anyone have a better way?

Thinking of a way without changing the column names.
Logic would include the hard-coded column names
for col = 1 to slGetColumnCount
  if slGeColumnname(col) = "ColumnName" then sFlag(col) = "D" or "U" or "DU"
next
Another way would be to have a startup table with the column names which are compressed/encrypted.
This startup table would prevent having to recompile the program if column names change.
Many programs store information needied on startup in an .INI file.  This could eliminate the .INI.

The current SELECT requests:
F3 in first column (normal data)
F2 in second column (encrypted data)
F4 in third column (compress and encrypted data)
F1 in fourth column (compressed data)
ROWID in fifth column (normal data)

Modify the order of the columns in $SelectStatement below to test.
'-------------------------------------------------------------------------------------------------------------
$SelectStatement = "SELECT F3, F2_N, F4_CN, F1_C,  ROWID from T1"
#INCLUDE "sqlitening.inc"  'SetColumnFlag.bas

SUB SetColumnFlag(sFlagArray() AS STRING)
'Read column names and place "DU", "D" or "U" into an array
'to Decrypt and or Uncompress each related column.
'If a column name ends _CN  decrypt and uncompress. "DU"
'if a column name ends _N   decrypt "D"
'if column name ends _C   uncompress "U"
  LOCAL COL,Cols AS LONG, sColumnName AS STRING
  cols = slGetcolumnCount
  IF Cols THEN
    DIM sFlagArray(cols) AS STRING
    FOR COL = 1 TO cols
      sColumnName = slGetColumnName(COL)
      IF INSTR(-1, sColumnName,"_CN") THEN    'if compressed encypt
         sFlagArray(COL) = "DU"                    '   decrypt uncompress
      ELSEIF INSTR(-1,sColumnName,"_N") THEN  'if encrypt
        sFlagArray(COL) = "D"                      '   decrypt
      ELSEIF INSTR(-1,sColumnName,"_C") THEN  'if compressed
        sFlagArray(COL) = "U"                 '   uncompress
      END IF
    NEXT
  END IF
END SUB
'
FUNCTION PBMAIN() AS LONG
  LOCAL x,rows,COL,cols AS LONG
  LOCAL sData,sColumnName AS STRING
  LOCAL sArray() AS STRING, sFlag() AS STRING
  slOpen "sample.db3","C"
  slexe "drop table if exists T1"   'delete previous data in table
'--------------------------------------------------------------------
  slexe "Create Table if not exists T1(F1_C,F2_N,F3,F4_CN)
'--------------------------------------------------------------------
  slSetProcessMods "K" + STRING$(16,"A") 'encryption key 16,24 or 32 bytes
  FOR x = 1 TO 3  'insert some records
    slExeBind "insert into T1 values(?,?,?,?)", _
      slBuildBindDat("(col1 compressed)","C")    +_
      slBuildBindDat("(col2 encrypted)","N")     +_
      slBuildBindDat("(col3 just text)")         +_
      slBuildBindDat("(col4 comp&encrypt)","CN")
  NEXT
'--------------------------------------------------------------------
  slsel $SelectStatement
  cols = slGetColumnCount
  IF cols THEN
    SetColumnFlag sFlag()  'so logic below knows if column is encrypted/compressed
    rows = 0
    sData = ""
    DO WHILE slgetRow
      INCR rows
      FOR COL = 1 TO slGetColumnCount
        IF LEN(sFlag(COL)) THEN 'Flag is set if data is encrypted and or compressed
          sData +=  slfx(COL,sFlag(COL)) + ","
        ELSE
          Sdata +=  slf(COL) + ","
        END IF
      NEXT
      ASC(sData,LEN(sData)) = 13 'last , to carriage return
    LOOP
   END IF
   ? sData,,"Rows" + STR$(rows)
END FUNCTION