PureBasic Forums - English

Posted: **Wed Oct 17, 2007 2:55 pm**

I think some people might be doing it like this already, but anyway, I wanted to work it out for myself. This uses the Prep-step-finalise method so it should be easy for beginners to see how the SQLite3 API works without really hiding anything. Features:

- There is no callback (I suspect some people don't like using that)
- There is no UTF-8 requirement (lock in) as it doesn't used SQLite3_exec()
- It doesn't use Get_Table() (I can't rave enough about how much I hate that function, I'd rather use MSAccess!

) This "steps" one record to memory at a time, jsut without the callback.

It still needs wrappers for blobs and floats but they would be easy to add. Just add your own DB file and SQL query to test. The Main() proc is where it all is, the wrappers are just for readability, I suppose macros may have worked as easily but I've never used PB macros

A UTF16 port shouldn't be too hard with this method.

Code: Select all

EnableExplicit

;{ SQL Constants
#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 */

;Types
#SQLITE_INTEGER          = 1
#SQLITE_FLOAT            = 2
#SQLITE_TEXT             = 3
#SQLITE_BLOB             = 4
#SQLITE_NULL             = 5

#SQL_LIB = 1  ; dll ID

;}

; Simple wrappers to shorten code lines
Procedure.s SQLite3_Col_Txt(hStatement.l, Col.l)
    ProcedureReturn PeekS(CallCFunction(#SQL_LIB, "sqlite3_column_text",hStatement,Col), CallCFunction(#SQL_LIB, "sqlite3_column_bytes",hStatement,Col)) 
EndProcedure

Procedure.l SQLite3_Col_Int(hStatement.l, Col.l)
    ProcedureReturn CallCFunction(#SQL_LIB, "sqlite3_column_int",hStatement,Col) 
EndProcedure

Procedure.l SQLite3_ColumnCount(hStatement.l)
    ProcedureReturn CallCFunction(#SQL_LIB, "sqlite3_column_count",hStatement) 
EndProcedure

Procedure.l SQLite3_Step(hStatement.l)
    ProcedureReturn CallCFunction(#SQL_LIB, "sqlite3_step",hStatement)
EndProcedure

Procedure.l SQLite3_Col_Type(hStatement.l, Col.l)
    ProcedureReturn CallCFunction(#SQL_LIB, "sqlite3_column_type",hStatement,Col) 
EndProcedure

Procedure.l SQLite3_Finalize(hStatement.l)
    ProcedureReturn CallCFunction(#SQL_LIB, "sqlite3_finalize", hStatement)
EndProcedure

Procedure.s SQLite3_ErrMsg(ErrNo.l)

    Define ErrMsg.s
    
    Select ErrNo
        Case #SQLITE3_OK 
            ErrMsg = "Success"
        Case #SQLITE3_ERROR 
            ErrMsg = "SQL error or missing database"
        Case #SQLITE3_INTERNAL 
            ErrMsg = "An internal logic error in SQLite"
        Case #SQLITE3_PERM 
            ErrMsg = "Access permission denied"
        Case #SQLITE3_ABORT 
            ErrMsg = "Callback routine requested an abort"
        Case #SQLITE3_BUSY 
            ErrMsg = "The database file is locked"
        Case #SQLITE3_LOCKED 
            ErrMsg = "A table in the database is locked"
        Case #SQLITE3_NOMEM 
            ErrMsg = "A malloc() failed"
        Case #SQLITE3_READONLY 
            ErrMsg = "Attempt to write a readonly database "
        Case #SQLITE3_INTERRUPT 
            ErrMsg = "Operation terminated by sqlite3_interrupt()"
        Case #SQLITE3_IOERR 
            ErrMsg = "Some kind of disk I/O error occurred"
        Case #SQLITE3_CORRUPT 
            ErrMsg = "The database disk image is malformed"
        Case #SQLITE3_NOTFOUND 
            ErrMsg = "(Internal Only) Table or record not found"
        Case #SQLITE3_FULL 
            ErrMsg = "Insertion failed because database is full"
        Case #SQLITE3_CANTOPEN 
            ErrMsg = "Unable to open the database file"
        Case #SQLITE3_PROTOCOL 
            ErrMsg = "Database lock protocol error"
        Case #SQLITE3_EMPTY 
            ErrMsg = "(Internal Only) Database table is empty"
        Case #SQLITE3_SCHEMA 
            ErrMsg = "The database schema changed"
        Case #SQLITE3_TOOBIG 
            ErrMsg = "Too much data for one row of a table"
        Case #SQLITE3_CONSTRAINT 
            ErrMsg = "Abort due to contraint violation"
        Case #SQLITE3_MISMATCH 
            ErrMsg = "Data type mismatch"
        Case #SQLITE3_MISUSE 
            ErrMsg = "Library used incorrectly"
        Case #SQLITE3_NOLFS 
            ErrMsg = "Uses OS features not supported on host"
        Case #SQLITE3_AUTH 
            ErrMsg = "Authorization denied"
        Case #SQLITE3_ROW 
            ErrMsg = "sqlite3_step() has another row ready"
        Case #SQLITE3_DONE 
            ErrMsg = "sqlite3_step() has finished executing"    
    
        Default 
            ErrMsg = "Unknown Error: " + Str(ErrNo)
            
    EndSelect
    
    ProcedureReturn ErrMsg

EndProcedure


Declare.l Main()

Main()

Procedure main()

    Define sDBFileName.s, sSQL.s, pzTail.s
    Define RetResult.l, i.l, hDB.l, Stmt.l, pStmt.l, ColCount.l
    
    pStmt = @Stmt 

    sDBFileName = "TestDB.db"
    sSQL = "select strcol,intcol from testtable"
    pzTail= ""

    ;Open SQLite3 DLL
    If OpenLibrary(#SQL_LIB, "sqlite3.dll")

        ;Open DB
        RetResult = CallCFunction(#SQL_LIB, "sqlite3_open" ,sDBFileName, @hDB)
        Debug SQLite3_ErrMsg(RetResult)
        
        ;Prepare SQL Statement
        RetResult = CallCFunction(#SQL_LIB, "sqlite3_prepare", hDB, sSQL, -1,pStmt, @pzTail)
        
        If RetResult = #SQLITE3_OK
    
            ColCount = SQLite3_ColumnCount(Stmt)
            
            ;Loop Rows
            While SQLite3_Step(Stmt) = #SQLITE3_ROW
                
                ;Loop Columns, display by type (int, txt at this stage)
                For i = 0 To ColCount -1
                
                    Select SQLite3_Col_Type(Stmt, i)
                        Case #SQLITE_TEXT
                            Debug SQLite3_Col_Txt(Stmt,i)    
                        Case #SQLITE_INTEGER
                            Debug SQLite3_Col_Int(Stmt,i)    
                    EndSelect
                Next
                
            Wend    
            
            ;Clean up Afterwards
            RetResult = SQLite3_Finalize(Stmt)
            Debug SQLite3_ErrMsg(RetResult)
            
        Else
            Debug SQLite3_ErrMsg(RetResult)
        EndIf
        
        CloseLibrary(#SQL_LIB)
    
    Else
        Debug "DLL Failed to Load"
    EndIf
    

EndProcedure

Below is the code updated for Unicode, tested with some japanese characters (both insert and select). compile in unicode

Code: Select all


EnableExplicit

;{ SQL Constants
#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 */

;Types
#SQLITE_INTEGER          = 1
#SQLITE_FLOAT            = 2
#SQLITE_TEXT             = 3
#SQLITE_BLOB             = 4
#SQLITE_NULL             = 5

#SQL_LIB = 1  ; dll ID

;}

; Simple wrappers to shorten code lines
Procedure.s SQLite3_Col_Txt(hStatement.l, Col.l)
    ProcedureReturn PeekS(CallCFunction(#SQL_LIB, "sqlite3_column_text16",hStatement,Col), CallCFunction(#SQL_LIB, "sqlite3_column_bytes16",hStatement,Col)) 
EndProcedure

Procedure.l SQLite3_Col_Int(hStatement.l, Col.l)
    ProcedureReturn CallCFunction(#SQL_LIB, "sqlite3_column_int",hStatement,Col) 
EndProcedure

Procedure.l SQLite3_ColumnCount(hStatement.l)
    ProcedureReturn CallCFunction(#SQL_LIB, "sqlite3_column_count",hStatement) 
EndProcedure

Procedure.l SQLite3_Step(hStatement.l)
    ProcedureReturn CallCFunction(#SQL_LIB, "sqlite3_step",hStatement)
EndProcedure

Procedure.l SQLite3_Col_Type(hStatement.l, Col.l)
    ProcedureReturn CallCFunction(#SQL_LIB, "sqlite3_column_type",hStatement,Col) 
EndProcedure

Procedure.l SQLite3_Finalize(hStatement.l)
    ProcedureReturn CallCFunction(#SQL_LIB, "sqlite3_finalize", hStatement)
EndProcedure

Procedure.s SQLite3_ErrMsg(ErrNo.l)

    Define ErrMsg.s
    
    Select ErrNo
        Case #SQLITE3_OK 
            ErrMsg = "Success"
        Case #SQLITE3_ERROR 
            ErrMsg = "SQL error or missing database"
        Case #SQLITE3_INTERNAL 
            ErrMsg = "An internal logic error in SQLite"
        Case #SQLITE3_PERM 
            ErrMsg = "Access permission denied"
        Case #SQLITE3_ABORT 
            ErrMsg = "Callback routine requested an abort"
        Case #SQLITE3_BUSY 
            ErrMsg = "The database file is locked"
        Case #SQLITE3_LOCKED 
            ErrMsg = "A table in the database is locked"
        Case #SQLITE3_NOMEM 
            ErrMsg = "A malloc() failed"
        Case #SQLITE3_READONLY 
            ErrMsg = "Attempt to write a readonly database "
        Case #SQLITE3_INTERRUPT 
            ErrMsg = "Operation terminated by sqlite3_interrupt()"
        Case #SQLITE3_IOERR 
            ErrMsg = "Some kind of disk I/O error occurred"
        Case #SQLITE3_CORRUPT 
            ErrMsg = "The database disk image is malformed"
        Case #SQLITE3_NOTFOUND 
            ErrMsg = "(Internal Only) Table or record not found"
        Case #SQLITE3_FULL 
            ErrMsg = "Insertion failed because database is full"
        Case #SQLITE3_CANTOPEN 
            ErrMsg = "Unable to open the database file"
        Case #SQLITE3_PROTOCOL 
            ErrMsg = "Database lock protocol error"
        Case #SQLITE3_EMPTY 
            ErrMsg = "(Internal Only) Database table is empty"
        Case #SQLITE3_SCHEMA 
            ErrMsg = "The database schema changed"
        Case #SQLITE3_TOOBIG 
            ErrMsg = "Too much data for one row of a table"
        Case #SQLITE3_CONSTRAINT 
            ErrMsg = "Abort due to contraint violation"
        Case #SQLITE3_MISMATCH 
            ErrMsg = "Data type mismatch"
        Case #SQLITE3_MISUSE 
            ErrMsg = "Library used incorrectly"
        Case #SQLITE3_NOLFS 
            ErrMsg = "Uses OS features not supported on host"
        Case #SQLITE3_AUTH 
            ErrMsg = "Authorization denied"
        Case #SQLITE3_ROW 
            ErrMsg = "sqlite3_step() has another row ready"
        Case #SQLITE3_DONE 
            ErrMsg = "sqlite3_step() has finished executing"    
    
        Default 
            ErrMsg = "Unknown Error: " + Str(ErrNo)
            
    EndSelect
    
    ProcedureReturn ErrMsg

EndProcedure


Declare.l Main()

Main()

Procedure main()

    Define sDBFileName.s, sSQL.s, pzTail.s
    Define RetResult.l, i.l, hDB.l, Stmt.l, pStmt.l, ColCount.l
    
    pStmt = @Stmt 

    ;sDBFileName = "TestDB.db"
    sSQL = "select strcol,intcol from testtable"
    sDBFileName = "UniTestDB.db"
    pzTail= ""

    ;Open SQLite3 DLL
    If OpenLibrary(#SQL_LIB, "sqlite3.dll")

        ;Open DB
        RetResult = CallCFunction(#SQL_LIB, "sqlite3_open16" ,sDBFileName, @hDB)
        Debug SQLite3_ErrMsg(RetResult)
        
        ;Prepare SQL Statement
        RetResult = CallCFunction(#SQL_LIB, "sqlite3_prepare16", hDB, sSQL, -1,pStmt, 0) ;@pzTailv
        
        If RetResult = #SQLITE3_OK
    
            ColCount = SQLite3_ColumnCount(Stmt)
            
            ;Loop Rows
            While SQLite3_Step(Stmt) = #SQLITE3_ROW
                
                ;Loop Columns, display by type (int, txt at this stage)
                For i = 0 To ColCount -1
                
                    Select SQLite3_Col_Type(Stmt, i)
                        Case #SQLITE_TEXT
                            MessageRequester("Unicode Text", SQLite3_Col_Txt(Stmt,i))
                        Case #SQLITE_INTEGER
                            Debug SQLite3_Col_Int(Stmt,i)    
                    EndSelect
                Next
                
            Wend    
            
            ;Clean up Afterwards
            RetResult = SQLite3_Finalize(Stmt)
            Debug SQLite3_ErrMsg(RetResult)
            
        Else
            Debug SQLite3_ErrMsg(RetResult)
        EndIf
        
        CloseLibrary(#SQL_LIB)
    
    Else
        Debug "DLL Failed to Load"
    EndIf
    

EndProcedure

Posted: **Thu Oct 18, 2007 12:40 am**

Thanks a lot Paul!

PureBasic Forums - English

SQLite Again! :) ... (unicode added)

SQLite Again! :) ... (unicode added)