SQLitening Support Forum

Support Forums => You've got Questions? We've got Answers! => Topic started by: cj on May 25, 2015, 06:28:21 AM

Title: order by wrong (with compression)
Post by: cj on May 25, 2015, 06:28:21 AM
I  see some SQL statements will not work correctly  because they are working on compressed or encrypted data.
ORDER BY is one of them.

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
  slOpen "sample.db3","C"
  slexe "drop table if exists t1"
  slexe "create table if not exists t1(c1)"
  slexebind "insert into t1 values(?)",slBuildBindDat("A","TC")
  slexebind "insert into t1 values(?)",slBuildBindDat("B","TC")
  slexebind "insert into t1 values(?)",slBuildBindDat("C","TC")
  slsel "select c1 from t1 order by c1"
  DO WHILE slGetRow
    s$ = s$ + slfx(1,"U")
  LOOP
  ? s$,,"Should be ABC"  'returns BAC
END FUNCTION       

Here is a probably bad solution re-writing the uncompressed/encrypted data into another table.
#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
  LOCAL s() AS STRING, sql,sResult AS STRING, x AS LONG
  sql = "select c1 from t1 order by c1"
  slSetProcessMods "K" + SPACE$(32) 'encrypt key
  slOpen "sample.db3","C"
  slexe "drop table if exists t1"
  slexe "create table if not exists t1(c1)"
  DIM s(3) AS STRING
  s(1) = slBuildBindDat("A","TCN")
  s(2) = slBuildBindDat("B","TCN")
  s(3) = slBuildBindDat("C","TCN")
  slexebind "insert into t1 values(?)",JOIN$(s(),""),"V1"
  slSelAry sql,s(),"(Qc)(D1)(U1)"
  'Remove "CN" binding
  FOR x=1 TO UBOUND(s):s(x) =slBuildBindDat(s(x),""):NEXT
  slopen ":memory:"
  slexe "create table t1(c1)"
  slexebind "insert into t1 values(?)",JOIN$(s(),""),"V1"
  slSelAry sql,s(),"Qc"
  ? JOIN$(s(),""),,sql  'returns ABC
END FUNCTION