SQLite3 raw & Simple from the DLL using the callback

pdwyer · Post by **pdwyer** » 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

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                               

;======================================================================================

srod · Post by **srod** » Sun May 13, 2007 1:57 pm

Interesting.

So the callback is called once for each row retrieved?

Nice, thanks man.

pdwyer · Post by **pdwyer** » Sun May 13, 2007 2:28 pm

Yes,

And if you check here: http://www.sqlite.org/capi3ref.html under sqlite3_get_table which is the other way of doing it it turns out to be just a wrapper on this anyway:

This next routine is really just a wrapper around sqlite3_exec(). Instead of invoking a user-supplied callback for each row of the result, this routine remembers each row of the result in memory obtained from malloc(), then returns all of the result after the query has finished.

So a lot of people seem to use the sqlite3_get_table for queries that return result sets (Selects) and sqlite3_exec for "sending" other types of statements like inserts and updates but in the end sqlite3_exec is used for everthing but sqlite3_get_table was created as a wrapper to assign memory for the entire output first. Others have since created further wrappers to make it a little more resultset (poor mans ADO) kind of thing but if you go back to the basics it's actually quite clean and gives you more control with less code. (admittedly much of the error checking has been dropped from my code except for the basics)

srod · Post by **srod** » Sun May 13, 2007 2:55 pm

SQLite is always my first choice and I admit to being a fan of SQLite3_get_table() since it is so convenient.

pdwyer · Post by **pdwyer** » Sun May 13, 2007 3:07 pm

It's not "Wrong" or "worse" to use that, it depends on what you are doing.

If you were making a tool to dump a million rows from an sqlite DB to some other format or for reporting it could become a problem though for both system memory and percieved responsiveness. Other than that it's pretty much the same, if not simpler (depending on your approach)

I'm just a minimalist

Which is why I haven't used VB in 7 years

Dare · Post by **Dare** » Sun May 13, 2007 3:16 pm

pdwyer wrote:I'm just a minimalist Which is why I haven't used VB in 7 years

Some very informative stuff there (not just the code). Thanks!

pdwyer · Post by **pdwyer** » Sun May 13, 2007 3:30 pm

On the insert transaction performance the code below should give you an idea of the differences.

In a previous life with a previous Basic Compiler I found a good balance at about 500 inserts in a single transaction so that if I had to write a tool to import an arbitrary number of rows (in my occupation an IIS or ISA log file) then I'd have a sub loop throwing in about 500 rows at a time, If the number of rows was too high then string concatenation and sheer size of data to the SQLite dll would start to have a detrimental effect. With PB that seems a little higher, maybe about 1000 or so.

Try for yourself, it could be system or I/O (IDE vs SATA vs SCSI vs SAS) or even NTFS vs FAT32 depenadant.

Change the "#RowsToTest = 2000" to something else to test

Using the same DB adding more and more rows doesn't seem to cause a problem which implies the SQLite DB is good enough to scale and not degrade with size

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)

#RowsToTest = 2000
; ==============================================================================

; Program Entry Point
ProcMain()

; ==============================================================================

Procedure.l ProcMain()

    SQLiteDLLPath.s = "sqlite3.dll" 
    DataBaseFile.s = "TestDB02.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) "

    CurrentTime.f = ElapsedMilliseconds()

    ;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 Rows SQL one at a time
        For InsertLoop.l = 1 To #RowsToTest
            QueryRet.l = CallCFunctionFast(sqlite3_exec, DBHandle, SQLAddRow, @CallBack_ReturnRows() , #Null, @ReturnValue) 
        Next
        
        Debug  "Single Rows: " + Str(ElapsedMilliseconds() - CurrentTime) + "ms"
        CurrentTime = ElapsedMilliseconds()

        ; Send Insert Rows SQL as a transaction
        TransSQLAddRow.s = "BEGIN; "
        For InsertLoop.l = 1 To #RowsToTest
            TransSQLAddRow = TransSQLAddRow + SQLAddRow + "; "     
        Next      
        TransSQLAddRow = TransSQLAddRow + "Commit; "
        QueryRet.l = CallCFunctionFast(sqlite3_exec, DBHandle, SQLAddRow, @CallBack_ReturnRows() , #Null, @ReturnValue)
        
        Debug  "One Transaction Rows: " + Str(ElapsedMilliseconds() - CurrentTime) + "ms"       

        ;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                               

;======================================================================================

byo · Post by **byo** » Thu May 17, 2007 7:35 pm

Nice tiny code. Interesting way of doing it.
I might try your way when I'm off work.

Just a remark about the comments.

I didn't understand this part:

Code: Select all

If CallCFunctionFast(sqlite3_open, DataBaseFile, @dbHandle) 
        CallCFunctionFast(sqlite3_errmsg, @RetVal) 
        Debug = PeekS(RetVal) 
    Else 
        ; If successful send queries

Shouldn't CallCFuntionFast return a non-zero value if it was successful?

ts-soft · Post by **ts-soft** » Thu May 17, 2007 8:19 pm

Code: Select all

#SQLITE3_OK              =   0

byo · Post by **byo** » Thu May 17, 2007 8:37 pm

ts-soft wrote:
Code: Select all
#SQLITE3_OK              =   0

pdwyer · Post by **pdwyer** » Fri May 18, 2007 2:15 am

admittedly it's not that intuitive, I was just trying to reduce the error catching lines of code since it was a 'snippit' for learning.

possibly had the opposite effect though