Another SQLite example

[Mistrel]

This is an example of my modifications to one of pdwyer's excellent examples. I modified it so that it reads better to me. Maybe it will make better sense to some of you than some of the other examples.

His original source can be found here.

Code: Select all

#SQLITE3_ISO8859         = 001   ;//
#SQLITE3_OK              = 000   ;// Successful result
#SQLITE3_ERROR           = 001   ;// SQL error or missing database
#SQLITE3_INTERNAL        = 002   ;// An internal logic error in SQLite
#SQLITE3_PERM            = 003   ;// Access permission denied
#SQLITE3_ABORT           = 004   ;// Callback routine requested an abort
#SQLITE3_BUSY            = 005   ;// The database file is locked
#SQLITE3_LOCKED          = 006   ;// A table in the database is locked
#SQLITE3_NOMEM           = 007   ;// A malloc() failed
#SQLITE3_READONLY        = 008   ;// Attempt to write a readonly database
#SQLITE3_INTERRUPT       = 009   ;// Operation terminated by sqlite3_interrupt()
#SQLITE3_IOERR           = 010   ;// Some kind of disk I/O error occurred
#SQLITE3_CORRUPT         = 011   ;// The database disk image is malformed
#SQLITE3_NOTFOUND        = 012   ;// (Internal Only) Table or record not found
#SQLITE3_FULL            = 013   ;// Insertion failed because database is full
#SQLITE3_CANTOPEN        = 014   ;// Unable to open the database file
#SQLITE3_PROTOCOL        = 015   ;// Database lock protocol error
#SQLITE3_EMPTY           = 016   ;// (Internal Only) Database table is empty
#SQLITE3_SCHEMA          = 017   ;// The database schema changed
#SQLITE3_TOOBIG          = 018   ;// Too much data for one row of a table
#SQLITE3_CONSTRAINT      = 019   ;// Abort due to contraint violation
#SQLITE3_MISMATCH        = 020   ;// Data type mismatch
#SQLITE3_MISUSE          = 021   ;// Library used incorrectly
#SQLITE3_NOLFS           = 022   ;// Uses OS features Not supported on host
#SQLITE3_AUTH            = 023   ;// Authorization denied
#SQLITE3_ROW             = 100   ;// sqlite3_step() has another row ready
#SQLITE3_DONE            = 101   ;// sqlite3_step() has finished executing

Enumeration
	#sqlite3dll
EndEnumeration

Global LastMessage.s
Global LibIsInitializedWithIncludedDLL.l

If Not OpenLibrary(#sqlite3dll,"sqlite3.dll")
		Debug "Could not load sqlite3 dll"
		End
EndIf

Global sqlite3_close.l=GetFunction(#sqlite3dll,"sqlite3_close")
Global sqlite3_exec.l=GetFunction(#sqlite3dll,"sqlite3_exec")
Global sqlite3_open.l=GetFunction(#sqlite3dll,"sqlite3_open")
Global sqlite3_errmsg.l=GetFunction(#sqlite3dll,"sqlite3_errmsg")

Macro sqlite3_close(hDB)
	CallCFunctionFast(sqlite3_close,hDB)
	;// hdb.l - The handle of an open database
EndMacro

Macro sqlite3_exec(hdb,sql,sqlite_callback,voidptr,errmsg)
	CallCFunctionFast(sqlite3_exec,hdb,sql,sqlite_callback,voidptr,errmsg)
	;// hdb.l - The handle of an open database
	;// sql.s	- SQL To be executed
	;// @sqlite_callback - Callback function
	;// @voidptr - First argument to callback function or null
	;// @errmsg - Error msg
EndMacro

Macro sqlite3_open(databasefile,hdb)
	CallCFunctionFast(sqlite3_open,databasefile,hdb)
	;// databasefile.s - Database filename (UTF-8)
	;// @hdb - OUT: SQLite db handle
EndMacro

Macro sqlite3_errmsg(hdb)
	CallCFunctionFast(sqlite3_errmsg,hdb)
	;// hdb.l - The handle of an open database
EndMacro

Declare ProcMain()
Declare CallBack_ReturnRows(NullPtr.l,ArgCount.l,*Values.l, *ColNames.l)

;// Program Entry Point
ProcMain()

Procedure.l ProcMain()
		Define hDB.l
    DataBaseFile.s="test0001.dat" ;
   
    ;Prep SQL statements
    CreateDB.s="CREATE TABLE 'MyTable' ("+#CRLF$
    CreateDB.s+"'ID' INTEGER  Not NULL PRIMARY KEY AUTOINCREMENT,"+#CRLF$
    CreateDB.s+"'StringCol1' TEXT  NULL,"+#CRLF$
    CreateDB.s+"'StringCol2' TEXT  NULL,"+#CRLF$
    CreateDB.s+"'IntCol1' INTEGER  NULL,"+#CRLF$
    CreateDB.s+"'IntCol2' INTEGER  NULL"+#CRLF$
    CreateDB.s+")"   
    CreateDB.s=ReplaceString(CreateDB.s,"'",#DOUBLEQUOTE$)

    SQLAddRow.s="INSERT INTO MyTable(StringCol1, StringCol2, IntCol1, IntCol2) VALUES('Hello', 'NextColumn', 100, 12345678) "

    CurrentTime.f=ElapsedMilliseconds()

    ;Open Database
    If sqlite3_open(DataBaseFile.s, @hDB)
        sqlite3_errmsg(@ReturnValue)
        Debug PeekS(ReturnValue)
    Else ; If successful send queries

        ; Send Created DB SQL, table may exist
        If sqlite3_exec(hDB,CreateDB.s,#Null,#Null,@ReturnValue)
            sqlite3_errmsg(@ReturnValue)
            Debug PeekS(ReturnValue)
        EndIf

        ; Send Insert Row SQL, table may exist
        sqlite3_exec(hDB,"BEGIN",#Null,#Null,@ReturnValue)
        For i.l = 1 To 100
            If sqlite3_exec(hDB,SQLAddRow,#Null,#Null,@ReturnValue)
                sqlite3_errmsg(@ReturnValue)
                Debug PeekS(ReturnValue)
            EndIf       
        Next
        sqlite3_exec(hDB,"COMMIT",#Null,#Null,@ReturnValue)
        
        ;Send arbitrary Query
        If sqlite3_exec(hDB,"SELECT * FROM MyTable",@CallBack_ReturnRows(),#Null,@ReturnValue)
            sqlite3_errmsg(@ReturnValue)
            Debug PeekS(ReturnValue)
        EndIf

        ;Close Database
        sqlite3_close(hDB)
    EndIf
    Debug (ElapsedMilliseconds()-CurrentTime)/1000
    ;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