PureBasic Forums - English

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