order by wrong (with compression)

cj · 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

SQLitening Support Forum

News:

order by wrong (with compression)

cj