Sqlite Function problem

[wfchun]

I've been using code posted by Chris Iverson in this thread to play around with sqlite.

I built a mock table "employees" and filled it with 1000000 names, ids, salaries etc. Everything looks good, super fast.

I used the following sql statement in the main program:

SQL$ = "SELECT * FROM employees ORDER BY salary DESC LIMIT 10;"

It then flows into:

   hStatement = 0
   doit = 1

   ret = SQL3Prepare(hDB, SQL$, hStatement)
   if ret <> SQLLITE.OK then
       Print "SQL3Prepare() failed - ";ret;" - ";SQL3ErrStr$(ret)
       doit = 0
   end if

   if doit = 1 then

     do
         ret = SQL3Step(hStatement)
         if ret <> SQLITE.DONE and ret <> SQLITE.ROW then
             print "SQL3Step() error - ";ret;" - ";SQL3ErrStr$(ret)
             exit do
         end if

         numCols = SQL3DataCount(hStatement)

         'print "numCols - "; str$(numCols)
         data$ = ""

         if numCols <> 0 then
             for x = 0 to numCols - 1
                data$ = data$ + SQL3ColumnText$(hStatement, x) + ","
                'print SQL3ColumnText$(hStatement, x),
             next x

             print "data$ - "; data$
         end if
     loop while ret <> SQLITE.DONE

   end if

   print

   print "data$ - "; data$
   print "SQL3Finalize() - ";SQL3Finalize(hStatement)

This produces:

data$ - 681197,1358574,Cooper,Mary,1,50000.0,
data$ - 667757,1331703,O'Neil,Tracy,1,49999.92,
data$ - 288971,576540,Welsh,Gerald,1,49999.91,
data$ - 107144,213523,Newton,Colleen,1,49999.91,
data$ - 531406,1060027,Dawson,Norman,1,49999.85,
data$ - 388319,774724,O'Brien,Gerald,1,49999.81,
data$ - 350120,698443,Jameson,Norma,1,49999.79,
data$ - 658343,1312901,Carlson,Tracy,1,49999.77,
data$ - 647444,1291165,Jones,Bernard,1,49999.75,
data$ - 593062,1182994,James,Freda,1,49999.75,

data$ -
SQL3Finalize() - 0
SQL3Close() - 0
SQL3Shutdown() - 0
nsert code here

My problem arises when I create a function with the same code above and pass the sql$ to it:

foo = doSelectA(hDB, SQL$)
This produces

SQL3Step() error - 100 - another row available




While I have a fairly simple workaround to solve this, it doesn't get rid of the exception that's thrown.

Can anyone out there advise?

[Chris Iverson]

That's not an exception or an error message. You're reading the data wrong.

From this page on SQLite's return codes: sqlite.org/rescode.html#row

For the entry on return value 100(SQLITE_ROW):

The SQLITE_ROW result code returned by sqlite3_step()(LB equivalent: SQL3Step()) indicates that another row of output is available.

Note that in my code that you posted, when I'm calling SQL3Step(), I'm checking for any return values other than SQLITE_DONE(SQLITE.DONE in LB) or SQLITE_ROW(SQLITE.ROW in LB).



If you have copied that code directly, and it's still failing the same way(seeing as that error message is formatted the way I format them), then it's possible that you haven't defined the value of SQLITE.ROW properly. Check the values of SQLITE.DONE and SQLITE.ROW there; if they're not 101 and 100 respectively, then it's not checking for the return values properly.

[wfchun]

Hi Chris, Thanks for the response.

I have the SQL3PrepGlobals as originally posted:

[SQL3PrepGlobals]
Global SQLITE.OK : SQLITE.OK = 0
Global SQLITE.ERROR : SQLITE.ERROR = 1
Global SQLITE.BUSY : SQLITE.BUSY = 5
Global SQLITE.LOCKED : SQLITE.LOCKED = 6
Global SQLITE.MISMATCH : SQLITE.MISMATCH = 20
Global SQLITE.MISUSE : SQLITE.MISUSE = 21
Global SQLITE.ROW : SQLITE.ROW = 100
Global SQLITE.DONE : SQLITE.DONE = 101
Global SQLITE.INTEGER : SQLITE.INTEGER = 1
Global SQLITE.FLOAT : SQLITE.FLOAT = 2
Global SQLITE.BLOB : SQLITE.BLOB = 3
Global SQLITE.NULL : SQLITE.NULL = 4
Global SQLITE.TEXT : SQLITE.TEXT = 5
Global SQLITE.STATIC : SQLITE.STATIC = 0
Global SQLITE.TRANSIENT : SQLITE.TRANSIENT = -1
return



I had a bit of a look at what was going on, and commented out this

'exit do

and it goes into an infinite loop, so I tried a counter


         ctrSql = ctrSql + 1
         if ctrSql = 24 then exit do


This produced this:

numCols - 6
data$ - 681197,1358574,Cooper,Mary,1,50000.0,
numCols - 6
data$ - 667757,1331703,O'Neil,Tracy,1,49999.92,
numCols - 6
data$ - 288971,576540,Welsh,Gerald,1,49999.91,
numCols - 6
data$ - 107144,213523,Newton,Colleen,1,49999.91,
numCols - 6
data$ - 531406,1060027,Dawson,Norman,1,49999.85,
numCols - 6
data$ - 388319,774724,O'Brien,Gerald,1,49999.81,
numCols - 6
data$ - 350120,698443,Jameson,Norma,1,49999.79,
numCols - 6
data$ - 658343,1312901,Carlson,Tracy,1,49999.77,
numCols - 6
data$ - 647444,1291165,Jones,Bernard,1,49999.75,
numCols - 6
data$ - 593062,1182994,James,Freda,1,49999.75,
numCols - 0
numCols - 6
data$ - 681197,1358574,Cooper,Mary,1,50000.0,
numCols - 6
data$ - 667757,1331703,O'Neil,Tracy,1,49999.92,
numCols - 6
data$ - 288971,576540,Welsh,Gerald,1,49999.91,
numCols - 6
data$ - 107144,213523,Newton,Colleen,1,49999.91,
numCols - 6
data$ - 531406,1060027,Dawson,Norman,1,49999.85,
numCols - 6
data$ - 388319,774724,O'Brien,Gerald,1,49999.81,
numCols - 6
data$ - 350120,698443,Jameson,Norma,1,49999.79,
numCols - 6
data$ - 658343,1312901,Carlson,Tracy,1,49999.77,
numCols - 6
data$ - 647444,1291165,Jones,Bernard,1,49999.75,
numCols - 6
data$ - 593062,1182994,James,Freda,1,49999.75,
numCols - 0
numCols - 6
data$ - 681197,1358574,Cooper,Mary,1,50000.0,



Which gives me a dirty work around:

         numCols = SQL3DataCount(hStatement)

         if numCols = 0 then exit do


[Chris Iverson]

If your code is identical to what I've previously written, then you should not be falling in to an infinite loop.

As written in what you've quoted, if the database returns an error message, it will stop looping.

If the database returns that you've reached the end of data, it will stop looping. (SQLITE_DONE).

If the database returns that more data is available(SQLITE_ROW), it will loop.

Something in that logic got mixed up when transcribing it, that's the only thing I can think of.

[wfchun]

Thanks Chris.

The first code snippet I posted I ran in main program, and is identical in the function. I only made minor changes to what you originally posted (removing the goto, eg).

The main code runs as expected, the second, not.

Mind. Boggled.

[Chris Iverson]

Hmm.

Just out of curiosity, would you be willing to post the whole function doSelectA()? I'm just curious if there's something I'm missing.

[wfchun]

Sure

function doSelectA(hDB, SQL$)

   doit = 1

   hStatement = 0
   ret = SQL3Prepare(hDB, SQL$, hStatement)

   if ret <> SQLLITE.OK then
       Print "SQL3Prepare() failed - ";ret;" - ";SQL3ErrStr$(ret)
       doit = 0
   end if


   if doit = 1 then

     ctrSql = 0

     do
         ret = SQL3Step(hStatement)

         if ret <> SQLITE.DONE and ret <> SQLITE.ROW then
             'print "SQL3Step() error - ";ret;" - ";SQL3ErrStr$(ret)
             'exit do
         end if

         numCols = SQL3DataCount(hStatement)

         'if numCols = 0 then exit do  'dirty fix


         ctrSql = ctrSql + 1
         if ctrSql = 24 then exit do


         print "numCols - "; str$(numCols)
         data$ = ""

         if numCols <> 0 then
             for x = 0 to numCols - 1
                data$ = data$ + SQL3ColumnText$(hStatement, x) + ","
                'print SQL3ColumnText$(hStatement, x),
             next x

             print "data$ - "; data$
         end if
     loop while ret <> SQLITE.DONE

   end if

   print : print

   print "data$ - "; data$

   print : print

   print "SQL3Finalize() - ";SQL3Finalize(hStatement)


end function


[Chris Iverson]

Well, that's weird. They're not being recognized as globals. I'd say this is definitely a bug in LB.

Here's an example of my own SQlite3 sample code, with your function dropped in to it:

gosub [SQL3PrepGlobals]

print "SQL3Init() - ";SQL3Init()

hDB = 0
print "SQL3Open() - ";SQL3Open("test.db", hDB)



ret = SQL3ExecSingle(hDB, "CREATE TABLE test(a INT, b BLOB);")
print "SQL3ExecSingle() - ";ret

print "SQL3ExecSingle() - ";ret

if ret = SQLITE.DONE then
   print "SQL3ExecSingle() - ";SQL3ExecSingle(hDB, "INSERT INTO test(a, b) VALUES(12,'blah');")
else
   print SQL3ErrStr$(ret)
end if

print
print
SQL$ = "SELECT * FROM test;"

goto [skipInlinePrepare]
hStatement = 0

ret = SQL3Prepare(hDB, SQL$, hStatement)
if ret <> SQLLITE.OK then
   Print "SQL3Prepare() failed - ";ret;" - ";SQL3ErrStr$(ret)
   goto [skipSelect]
end if

numCols = SQL3ColumnCount(hStatement)
for i = 0 to numCols - 1
print SQL3ColumnName$(hStatement, i),
next i
print

do
   ret = SQL3Step(hStatement)
   if ret <> SQLITE.DONE and ret <> SQLITE.ROW then
       print "SQL3Step() error - ";ret;" - ";SQL3ErrStr$(ret)
       exit do
   end if

   numCols = SQL3DataCount(hStatement)

   if numCols <> 0 then
       for x = 0 to numCols - 1
           print SQL3ColumnText$(hStatement, x),
       next x

       print
   end if
loop while ret <> SQLITE.DONE

print
print

print "SQL3Finalize() - ";SQL3Finalize(hStatement)

[skipInlinePrepare]
print doSelectA(hDB, SQL$)

[skipSelect]
print "SQL3ExecSingle() - ";SQL3ExecSingle(hDB, "DROP TABLE test;")

print "SQL3Close() - ";SQL3Close(hDB)

print "SQL3Shutdown() - ";SQL3Shutdown()

end

function doSelectA(hDB, SQL$)

   doit = 1

   hStatement = 0
   ret = SQL3Prepare(hDB, SQL$, hStatement)

   if ret <> SQLLITE.OK then
       Print "SQL3Prepare() failed - ";ret;" - ";SQL3ErrStr$(ret)
       exit function
   end if


   if doit = 1 then

     do
         ret = SQL3Step(hStatement)
         print "SQLITE_DONE: ";SQLITE.DONE
         print "SQLITE_ROW: ";SQLITE.ROW
         if ret <> SQLITE.DONE and ret <> SQLITE.ROW then
             print "SQL3Step() error - ";ret;" - ";SQL3ErrStr$(ret)
             exit do
         end if

         numCols = SQL3DataCount(hStatement)

         print "numCols - "; str$(numCols)
         data$ = ""

         if numCols <> 0 then
             for x = 0 to numCols - 1
                data$ = data$ + SQL3ColumnText$(hStatement, x) + ","
                'print SQL3ColumnText$(hStatement, x),
             next x

             print "data$ - "; data$
         end if
     loop while ret <> SQLITE.DONE

   end if

   print : print

   print "data$ - "; data$

   print : print

   print "SQL3Finalize() - ";SQL3Finalize(hStatement)


end function


'This function is provided as a shorthand for statements that can be executed
'in a single step, and don't return anything(CREATE, DROP, INSERT, etc)
Function SQL3ExecSingle(hDB, SQL$)
   'Prepare/compile the statement
   hStatement = 0
   ret = SQL3Prepare(hDB, SQL$, hStatement)
   if ret <> SQLITE.OK then
       'Statement preparation failed.  Error code may have more info.
       goto [endFunc]
   end if

   'Execute the statement.
   ret = SQL3Step(hStatement)

   [endFunc]
   dummy = SQL3Finalize(hStatement)
   SQL3ExecSingle = ret
End Function

Function SQL3Init()
   open "sqlite3" for DLL as #sqlite3

   CallDLL #sqlite3, "sqlite3_initialize",_
   SQL3Init as long
End Function

Function SQL3Shutdown()
   CallDLL #sqlite3, "sqlite3_shutdown",_
   SQL3Shutdown as long

   close #sqlite3
End Function

Function SQL3Open(fileName$, byref hDB)
   struct a, b as ulong

   CallDLL #sqlite3, "sqlite3_open",_
   fileName$ as ptr,_
   a as struct,_
   SQL3Open as long

   hDB = a.b.struct
End Function

Function SQL3Close(hDB)
   CallDLL #sqlite3, "sqlite3_close",_
   hDB as ulong,_
   SQL3Close as long
End Function

Function SQL3Prepare(hDB, SQL$, byref hStatement)
   sqlLen = len(SQL$)

   struct a, b as ulong

   CallDLL #sqlite3, "sqlite3_prepare_v2",_
   hDB as ulong,_
   SQL$ as ptr,_
   sqlLen as long,_
   a as struct,_
   _NULL as long,_
   SQL3Prepare as long

   hStatement = a.b.struct
End Function

Function SQL3Finalize(hStatement)
   CallDLL #sqlite3, "sqlite3_finalize",_
   hStatement as ulong,_
   SQL3Finalize as long
End Function

Function SQL3Step(hStatement)
   CallDLL #sqlite3, "sqlite3_step",_
   hStatement as ulong,_
   SQL3Step as long
End Function

Function SQL3Reset(hStatement)
   CallDLL #sqlite3, "sqlite3_reset",_
   hStatement as ulong,_
   SQL3Reset as long
End Function

Function SQL3ClearBindings(hStatement)
   CallDLL #sqlite3, "sqlite3_clear_bindings",_
   hStatement as ulong,_
   SQL3ClearBindings as long
End Function

Function SQL3ErrStr$(errorCode)
   CallDLL #sqlite3, "sqlite3_errstr",_
   errorCode as long,_
   ret as ulong

   if ret <> 0 then SQL3ErrStr$ = winstring(ret)
End Function

Function SQL3ColumnCount(hStatement)
   CallDLL #sqlite3, "sqlite3_column_count",_
   hStatement as ulong,_
   SQL3ColumnCount as long
End Function

Function SQL3DataCount(hStatement)
   CallDLL #sqlite3, "sqlite3_data_count",_
   hStatement as ulong,_
   SQL3DataCount as long
End Function

Function SQL3ColumnText$(hStatement, column)
   CallDLL #sqlite3, "sqlite3_column_text",_
   hStatement as ulong,_
   column as long,_
   ret as ulong

   if ret <> 0 then
       numBytes = SQL3ColumnBytes(hStatement, column)

       SQL3ColumnText$ = space$(numBytes)

       CallDLL #kernel32, "RtlMoveMemory",_
       SQL3ColumnText$ as ptr,_
       ret as ulong,_
       numBytes as long,_
       dummy as void
   end if
End Function

Function SQL3ColumnBytes(hStatement, column)
   CallDLL #sqlite3, "sqlite3_column_bytes",_
   hStatement as ulong,_
   column as long,_
   SQL3ColumnBytes as long
End Function

Function SQL3BindInt(hStatement, param, value)
   CallDLL #sqlite3, "sqlite3_bind_int",_
   hStatement as ulong,_
   param as long,_
   value as long,_
   SQL3BindInt as long
End Function

Function SQL3BindText(hStatement, param, text$)
   textLen = len(text$)

   CallDLL #sqlite3, "sqlite3_bind_text",_
   hStatement as ulong,_
   param as long,_
   text$ as ptr,_
   textLen as long,_
   SQLITE.STATIC as long,_
   SQL3BindText as long
End Function

Function SQL3BindBlob(hStatement, param, blob$)
   blobLen = len(blob$)

   CallDLL #sqlite3, "sqlite3_bind_blob",_
   hStatement as ulong,_
   param as long,_
   blob$ as ptr,_
   blobLen as long,_
   SQLITE.STATIC as long,_
   SQL3BindBlob as long
End Function

Function SQL3ColumnName$(hStatement, column)
   CallDLL #sqlite3, "sqlite3_column_name",_
   hStatement as ulong,_
   column as long,_
   ret as ulong

   if ret <> 0 then SQL3ColumnName$ = winstring(ret)
End Function

Function SQL3ColumnType(hStatement, column)
   CallDLL #sqlite3, "sqlite3_column_type",_
   hStatement as ulong,_
   column as long,_
   SQL3ColumnType as long
End Function

Function SQL3EnableLoadExtension(hDB, onoff)
   SQLITE.DBCONFIG.ENABLE.LOAD.EXTENSION = 1005

   CallDLL #sqlite3, "sqlite3_db_config",_
   hDB as ulong,_
   SQLITE.DBCONFIG.ENABLE.LOAD.EXTENSION as long,_
   onoff as long,_
   SQL3EnableLoadExtension as long
End Function

Function SQL3LoadExtension(hDB, extFileName$)
   CallDLL #sqlite3, "sqlite3_load_extension",_
   hDB as ulong,_
   extFileName$ as ptr,_
   _NULL as long,_
   _NULL as long,_
   SQL3LoadExtension as long
End Function

Function fputs(stuff, hFile)
   if stuff = 0 then exit function
   stuff$ = winstring(stuff)
   print #dbdumpfile, stuff$;
End Function

Function SQL3DBDump(file$, hDB, schema$, table$)
   callback FPUTSCB, fputs(ulong, long), void

   open file$ for output as #dbdumpfile
   open "dbdump" for dll as #dbdump

   calldll #dbdump, "sqlite3_db_dump",_
   hDB as ulong,_
   schema$ as ptr,_
   table$ as ptr,_
   FPUTSCB as ulong,_
   0 as long,_
   SQL3DBDump as long

   close #dbdump
   close #dbdumpfile
End Function

[SQL3PrepGlobals]
Global SQLITE.OK : SQLITE.OK = 0
Global SQLITE.ERROR : SQLITE.ERROR = 1
Global SQLITE.BUSY : SQLITE.BUSY = 5
Global SQLITE.LOCKED : SQLITE.LOCKED = 6
Global SQLITE.MISMATCH : SQLITE.MISMATCH = 20
Global SQLITE.MISUSE : SQLITE.MISUSE = 21
Global SQLITE.ROW : SQLITE.ROW = 100
Global SQLITE.DONE : SQLITE.DONE = 101
Global SQLITE.INTEGER : SQLITE.INTEGER = 1
Global SQLITE.FLOAT : SQLITE.FLOAT = 2
Global SQLITE.BLOB : SQLITE.BLOB = 3
Global SQLITE.NULL : SQLITE.NULL = 4
Global SQLITE.TEXT : SQLITE.TEXT = 5
Global SQLITE.STATIC : SQLITE.STATIC = 0
Global SQLITE.TRANSIENT : SQLITE.TRANSIENT = -1
return

Note that, even though they're being declared as globals with values set at the beginning of the program, the value isn't retained inside the function.

If you move all the global declarations to the beginning of the program, instead of in a branch, or if you move the [SQL3PrepGlobals] branch code to just before the first function defined, then it all seems to work.

I would expect that, as long as the "GLOBAL" statement executed before any function used those globals, they would be available, but in LB, that's apparently not the case? GLOBAL seems to be more of a compiler directive instead of a program code statement, and anything that's not at the top level scope will not see any globals defined in later code when going line-by-line, even if that code is executed first.

[wfchun]

I saw your post, and moved all the global statements to the top of the code, and yes

You are a star Chris, *rapturousapplause.gif*