- 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
