• Welcome, Guest. Please login.
 
September 17, 2019, 01:52:13 pm

News:

Welcome to the SQLitening support forums!


order by wrong (with compression)

Started by cj, May 25, 2015, 06:28:21 am

Previous topic - Next topic

cj

May 25, 2015, 06:28:21 am Last Edit: May 26, 2015, 02:49:16 am by cj
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