SQLite3 raw & Simple from the DLL using the callback
Posted: Sun May 13, 2007 1:53 pm
Short and simple really! (Death to unnecessary wrappers! 
)
I had to borrow pretty heavily off the PB Open Source at first and it was a great learning tool but for a learning code snippet there was too much error checking etc (which is of course expected) so once trimming it down etc it looks like the code below.
The call back is implemented here unlike the OpenSource code which the main two advantages are:
1. Memory usage: To give an extreme but quite possible example if you wanted to dump the contents of a select statement to do something with it like write to CSV or something then the whole dump would have to fit in memory or it would page hideously (consider hundres of thousands of 1k rows) . Using the callback would mean that the entire contents of the select statement output would not go into memory but only pass through one row at a time which is some situations could be a large benefit
2. App Responsiveness: if the query was going to spend a long time preparing the recordset because it was large, or the dat file over the network or processor intensive joins then using the callback would mean that rows could come through as collected rather than waiting for the entire RS and you could update the client with the current row count
One word of warning with SQLite3 in windows is that if you need to do bulk inserts do NOT do them one at a time! build them into transactions of about (from experience) 500 inserts at a time and throw them in that way and you will get hundreds of time performance increases, please read about using transaction statements for large inserts and compare performance here in test 1 and 2 http://www.sqlite.org/speed.html
in this snippet data is sent to the debugger so just run from the IDE so see how it works, run it a few times to see the row count build and the effect of opening a new verses existing DB file
)I had to borrow pretty heavily off the PB Open Source at first and it was a great learning tool but for a learning code snippet there was too much error checking etc (which is of course expected) so once trimming it down etc it looks like the code below.
The call back is implemented here unlike the OpenSource code which the main two advantages are:
1. Memory usage: To give an extreme but quite possible example if you wanted to dump the contents of a select statement to do something with it like write to CSV or something then the whole dump would have to fit in memory or it would page hideously (consider hundres of thousands of 1k rows) . Using the callback would mean that the entire contents of the select statement output would not go into memory but only pass through one row at a time which is some situations could be a large benefit
2. App Responsiveness: if the query was going to spend a long time preparing the recordset because it was large, or the dat file over the network or processor intensive joins then using the callback would mean that rows could come through as collected rather than waiting for the entire RS and you could update the client with the current row count
One word of warning with SQLite3 in windows is that if you need to do bulk inserts do NOT do them one at a time! build them into transactions of about (from experience) 500 inserts at a time and throw them in that way and you will get hundreds of time performance increases, please read about using transaction statements for large inserts and compare performance here in test 1 and 2 http://www.sqlite.org/speed.html
in this snippet data is sent to the debugger so just run from the IDE so see how it works, run it a few times to see the row count build and the effect of opening a new verses existing DB file
Code: Select all
#SQLITE3_ISO8859 = 1 ;
#SQLITE3_OK = 0 ;'*/ Successful result */
#SQLITE3_ERROR = 1 ;'*/ SQL error or missing database */
#SQLITE3_INTERNAL = 2 ;'*/ An internal logic error in SQLite */
#SQLITE3_PERM = 3 ;'*/ Access permission denied */
#SQLITE3_ABORT = 4 ;'*/ Callback routine requested an abort */
#SQLITE3_BUSY = 5 ;'*/ The database file is locked */
#SQLITE3_LOCKED = 6 ;'*/ A table in the database is locked */
#SQLITE3_NOMEM = 7 ;'*/ A malloc() failed */
#SQLITE3_READONLY = 8 ;'*/ Attempt to write a readonly database */
#SQLITE3_INTERRUPT = 9 ;'*/ Operation terminated by sqlite3_interrupt() */
#SQLITE3_IOERR = 10 ;'*/ Some kind of disk I/O error occurred */
#SQLITE3_CORRUPT = 11 ;'*/ The database disk image is malformed */
#SQLITE3_NOTFOUND = 12 ;'*/ (Internal Only) Table or record not found */
#SQLITE3_FULL = 13 ;'*/ Insertion failed because database is full */
#SQLITE3_CANTOPEN = 14 ;'*/ Unable to open the database file */
#SQLITE3_PROTOCOL = 15 ;'*/ Database lock protocol error */
#SQLITE3_EMPTY = 16 ;'*/ (Internal Only) Database table is empty */
#SQLITE3_SCHEMA = 17 ;'*/ The database schema changed */
#SQLITE3_TOOBIG = 18 ;'*/ Too much data for one row of a table */
#SQLITE3_CONSTRAINT = 19 ;'*/ Abort due to contraint violation */
#SQLITE3_MISMATCH = 20 ;'*/ Data type mismatch */
#SQLITE3_MISUSE = 21 ;'*/ Library used incorrectly */
#SQLITE3_NOLFS = 22 ;'*/ Uses OS features not supported on host */
#SQLITE3_AUTH = 23 ;'*/ Authorization denied */
#SQLITE3_ROW = 100 ;'*/ sqlite3_step() has another row ready */
#SQLITE3_DONE = 101 ;'*/ sqlite3_step() has finished executing */
Global LastMessage.s
Global LibIsInitializedWithIncludedDLL.l
Global sqlite3_close.l
Global sqlite3_exec.l
Global sqlite3_open.l
Global sqlite3_errmsg.l
Global sqlite3DLL.l
Declare ProcMain()
Declare CallBack_ReturnRows(NullPtr.l,ArgCount.l,*Values.l, *ColNames.l)
; ==============================================================================
; Program Entry Point
ProcMain()
; ==============================================================================
Procedure.l ProcMain()
SQLiteDLLPath.s = "sqlite3.dll"
DataBaseFile.s = "TestDB01.dat"
;Load SQLite DLL
sqlite3DLL = LoadLibrary_(@SQLiteDLLPath)
sqlite3_close = GetProcAddress_(sqlite3DLL, "sqlite3_close")
sqlite3_exec = GetProcAddress_(sqlite3DLL, "sqlite3_exec")
sqlite3_open = GetProcAddress_(sqlite3DLL, "sqlite3_open")
sqlite3_errmsg = GetProcAddress_(sqlite3DLL, "sqlite3_errmsg")
;Prep SQL statements
CreateDB.s = "CREATE TABLE 'MyTable' (" + #CRLF$
CreateDB = CreateDB + "'ID' INTEGER Not NULL PRIMARY KEY AUTOINCREMENT," + #CRLF$
CreateDB = CreateDB + "'StringCol1' TEXT NULL," + #CRLF$
CreateDB = CreateDB + "'StringCol2' TEXT NULL," + #CRLF$
CreateDB = CreateDB + "'IntCol1' INTEGER NULL," + #CRLF$
CreateDB = CreateDB + "'IntCol2' INTEGER NULL" + #CRLF$
CreateDB = CreateDB + ")"
CreateDB = ReplaceString(CreateDB, "'", #DOUBLEQUOTE$)
SQLAddRow.s = "Insert into MyTable(StringCol1, StringCol2, IntCol1, IntCol2) values('Hello', 'NextColumn', 100, 12345678) "
;Open Database
If CallCFunctionFast(sqlite3_open, DataBaseFile, @dbHandle)
CallCFunctionFast(sqlite3_errmsg, @RetVal)
Debug = PeekS(RetVal)
Else
; If successful send queries
; Send Created DB SQL, table may exist
QueryRet.l = CallCFunctionFast(sqlite3_exec, DBHandle, CreateDB, @CallBack_ReturnRows() , #Null, @ReturnValue)
If QueryRet
CallCFunctionFast(sqlite3_errmsg, @ReturnValue)
Debug = PeekS(ReturnValue)
EndIf
; Send Insert Row SQL, table may exist
QueryRet.l = CallCFunctionFast(sqlite3_exec, DBHandle, SQLAddRow, @CallBack_ReturnRows() , #Null, @ReturnValue)
If QueryRet
CallCFunctionFast(sqlite3_errmsg, @ReturnValue)
Debug = PeekS(ReturnValue)
EndIf
;Send arbitrary Query
QueryRet.l = CallCFunctionFast(sqlite3_exec, DBHandle, "Select * from MyTable", @CallBack_ReturnRows() , #Null, @ReturnValue)
If QueryRet
CallCFunctionFast(sqlite3_errmsg, @ReturnValue)
Debug = PeekS(ReturnValue)
EndIf
;Close Database
CallCFunctionFast(sqlite3_close, DBHandle)
EndIf
;Unload DLL on program end
FreeLibrary_(sqlite3DLL)
EndProcedure
;==============================================================================
ProcedureC.l CallBack_ReturnRows(NullPtr.l,ArgCount.l,*Values.l, *ColNames.l) ;argcount = column count
Dim ValuePts.l(ArgCount)
Row.s = ""
For ValLoop.l = 0 To ArgCount -1
ValuePts(ValLoop) = PeekL(*Values + (ValLoop * 4))
Row = Row + PeekS(ValuePts(ValLoop) ) + ","
Next
Debug Row
ProcedureReturn = 0 ;Proceed with next row
EndProcedure
;======================================================================================
Which is why I haven't used VB in 7 years 
