My Windows API Headers III 1.04 incorporated CSQLite, a wrapper class on top of SQLite.
Attached is the help file.
' ########################################################################################
' Microsoft Windows
' File: CSQLITE_Step.bas
' Contents: CSQLite class example
' Connects to a database and reads records.
' Copyright (c) 2012 Jose Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "CSQLite.INC"
' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN
' // Create an instance of the class
LOCAL pSQL AS ISQLite
pSQL = CLASS "CSQLite"
IF ISNOTHING(pSQL) THEN EXIT FUNCTION
' // Create a connection object
LOCAL pDbc AS ISQLiteConnection
pDbc = pSQL.Connection
IF ISNOTHING(pDbc) THEN EXIT FUNCTION
TRY
' // Delete our test database if it exists
IF ISFILE(EXE.PATH$ & "Test.sdb") THEN KILL EXE.PATH$ & "Test.sdb"
' // Create a new database
pDbc.OpenDatabase(EXE.PATH$ & "Test.sdb")
' // Create a table
pDbc.Exec("CREATE TABLE t (xyz text)")
' // Insert rows
pDbc.Exec("INSERT INTO t (xyz) VALUES ('fruit')")
pDbc.Exec("INSERT INTO t (xyz) VALUES ('fish')")
' ' // Prepare a query
LOCAL pStmt AS ISQLiteStatement
pStmt = pDbc.Prepare("SELECT * FROM t")
? "Column count:" & STR$(pStmt.ColumnCount)
' // Read the column names and values
LOCAL i AS LONG
DO
' // Fetch rows of the result set
IF pStmt.Step = %SQLITE_DONE THEN EXIT DO
' // Read the columns and values
FOR i = 0 TO pStmt.ColumnCount- 1
? pStmt.ColumnName(i)
? pStmt.ColumnText(i)
NEXT
LOOP
CATCH
' // Display error information
? pSql.OleErrorInfo
END TRY
' // Cleanup
pStmt = NOTHING ' // Deletes the prepared statement
pDbc = NOTHING ' // Closes the database
pSQL = NOTHING
#IF %DEF(%PB_CC32)
WAITKEY$
#ENDIF
END FUNCTION
' ========================================================================================
' ########################################################################################
' Microsoft Windows
' File: CSQLITE_Insert.bas
' Contents: CSQLite class example
' Demonstrates the basic steps to use the CSQLite class to connect to a database and prepare
' an statement.
' Copyright (c) 2012 Jose Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "CSQLite.INC"
' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN
' // Create an instance of the class
LOCAL pSQL AS ISQLite
pSQL = CLASS "CSQLite"
IF ISNOTHING(pSQL) THEN EXIT FUNCTION
' // Create a connection object
LOCAL pDbc AS ISQLiteConnection
pDbc = pSQL.Connection
IF ISNOTHING(pDbc) THEN EXIT FUNCTION
TRY
' // Delete our test database if it exists
IF ISFILE(EXE.PATH$ & "Test.sdb") THEN KILL EXE.PATH$ & "Test.sdb"
' // Create a new database
pDbc.OpenDatabase(EXE.PATH$ & "Test.sdb")
' // Create a table
LOCAL sql AS STRING
Sql = "CREATE TABLE t (xyz text)"
pDbc.Exec(sql)
' // Prepare the statement
sql = "INSERT INTO t (xyz) VALUES (?)"
LOCAL pStmt AS ISQLiteStatement
pStmt = pDbc.Prepare(sql)
' // Bind the text
pStmt.BindText(1, "fruit")
pStmt.Step
? "Row id was" & STR$(pDbc.LastInsertRowId)
CATCH
' // Display error information
? pSql.OleErrorInfo
END TRY
' // Cleanup
pStmt = NOTHING ' // Deletes the prepared statement
pDbc = NOTHING ' // Closes the database
pSQL = NOTHING
#IF %DEF(%PB_CC32)
WAITKEY$
#ENDIF
END FUNCTION
' ========================================================================================
' ########################################################################################
' Microsoft Windows
' File: CSQLITE_Exec.bas
' Contents: CSQLite class example
' Connects to a database and reads records.
' Copyright (c) 2012 Jose Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "CSQLite.INC"
' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN
' // Create an instance of the class
LOCAL pSQL AS ISQLite
pSQL = CLASS "CSQLite"
IF ISNOTHING(pSQL) THEN EXIT FUNCTION
' // Create a connection object
LOCAL pDbc AS ISQLiteConnection
pDbc = pSQL.Connection
IF ISNOTHING(pDbc) THEN EXIT FUNCTION
TRY
' // Delete our test database if it exists
IF ISFILE(EXE.PATH$ & "Test.sdb") THEN KILL EXE.PATH$ & "Test.sdb"
' // Create a new database
pDbc.OpenDatabase(EXE.PATH$ & "Test.sdb")
' // Create a table
LOCAL sql AS STRING
Sql = "CREATE TABLE t (xyz text)"
pDbc.Exec(sql)
' // Prepare the statement
sql = "INSERT INTO t (xyz) VALUES (?)"
LOCAL pStmt AS ISQLiteStatement
pStmt = pDbc.Prepare(sql)
' // Bind the text
pStmt.BindText(1, "fruit")
pStmt.Step
? "Row id was" & STR$(pDbc.LastInsertRowId)
' // Delete the prepared statement
pStmt = NOTHING
' // Close the database
pDbc.CloseDatabase
' // Open existing database for reading
pDbc.OpenDatabase2(EXE.PATH$ & "Test.sdb", %SQLITE_OPEN_READONLY)
' // Create a table
Sql = "SELECT * FROM t"
pDbc.Exec(sql, CODEPTR(SQLite_Exec_Callback))
CATCH
' // Display error information
? pSql.OleErrorInfo
END TRY
' // Cleanup
pDbc = NOTHING ' // Closes the database
pSQL = NOTHING
#IF %DEF(%PB_CC32)
WAITKEY$
#ENDIF
END FUNCTION
' ========================================================================================
' ========================================================================================
' Callback function for the Exec method.
' Return value:
' If returns non-zero, the Exec method returns SQLITE_ABORT without invoking the callback
' again and without running any subsequent SQL statements.
' ========================================================================================
FUNCTION SQLite_Exec_Callback CDECL(BYVAL pData AS DWORD, BYVAL numCols AS LONG, BYVAL pszColValues AS ASCIIZ PTR, BYVAL pszColNames AS ASCIIZ PTR) AS LONG
LOCAL i AS LONG
REDIM ColValues (0 TO numCols) AS ASCIIZ PTR AT pszColValues
REDIM ColNames (0 TO numCols) AS ASCIIZ PTR AT pszColNames
FOR i = 0 TO numCols - 1
? "Column name: " & @ColNames(i)
? "Column value: " & @ColValues(i)
NEXT
END FUNCTION
' ========================================================================================
' ########################################################################################
' Microsoft Windows
' File: CSQLITE_Insert.bas
' Contents: CSQLite class example
' Demonstrates the basic steps to use the CSQLite class to connect to a database and prepare
' an statement.
' Copyright (c) 2012 Jose Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "CSQLite.INC"
' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN
' // Create an instance of the class
LOCAL pSQL AS ISQLite
pSQL = CLASS "CSQLite"
IF ISNOTHING(pSQL) THEN EXIT FUNCTION
' // Create a connection object
LOCAL pDbc AS ISQLiteConnection
pDbc = pSQL.Connection
IF ISNOTHING(pDbc) THEN EXIT FUNCTION
TRY
' // Create a new database
pDbc.OpenDatabase(":memory:")
' // Create a table
LOCAL sql AS STRING
Sql = "CREATE TABLE t (xyz text)"
pDbc.Exec(sql)
' // Prepare the statement
sql = "INSERT INTO t (xyz) VALUES (?)"
LOCAL pStmt AS ISQLiteStatement
pStmt = pDbc.Prepare(sql)
' // Bind the text
pStmt.BindText(1, "fruit")
pStmt.Step
? "Row id was" & STR$(pDbc.LastInsertRowId)
' // Delete the prepared statement
pStmt = NOTHING
' // Query the database
Sql = "SELECT * FROM t"
pDbc.Exec(sql, CODEPTR(SQLite_Exec_Callback))
CATCH
' // Display error information
? pSql.OleErrorInfo
END TRY
' // Cleanup
pStmt = NOTHING ' // Deletes the prepared statement
pDbc = NOTHING ' // Closes the database
pSQL = NOTHING
#IF %DEF(%PB_CC32)
WAITKEY$
#ENDIF
END FUNCTION
' ========================================================================================
' ========================================================================================
' Callback function for the Exec method.
' Return value:
' If returns non-zero, the Exec method returns SQLITE_ABORT without invoking the callback
' again and without running any subsequent SQL statements.
' ========================================================================================
FUNCTION SQLite_Exec_Callback CDECL(BYVAL pData AS DWORD, BYVAL numCols AS LONG, BYVAL pszColValues AS ASCIIZ PTR, BYVAL pszColNames AS ASCIIZ PTR) AS LONG
LOCAL i AS LONG
REDIM ColValues (0 TO numCols) AS ASCIIZ PTR AT pszColValues
REDIM ColNames (0 TO numCols) AS ASCIIZ PTR AT pszColNames
FOR i = 0 TO numCols - 1
? "Column name: " & @ColNames(i)
? "Column value: " & @ColValues(i)
NEXT
END FUNCTION
' ========================================================================================
' ########################################################################################
' Microsoft Windows
' File: CSQLITE_Blob.bas
' Contents: CSQLite class example
' Copyright (c) 2012 Jose Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "CSQLite.INC"
' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN
' // Create an instance of the class
LOCAL pSQL AS ISQLite
pSQL = CLASS "CSQLite"
IF ISNOTHING(pSQL) THEN EXIT FUNCTION
' // Create a connection object
LOCAL pDbc AS ISQLiteConnection
pDbc = pSQL.Connection
IF ISNOTHING(pDbc) THEN EXIT FUNCTION
TRY
' // Delete our test database if it exists
IF ISFILE(EXE.PATH$ & "TestBlob.sdb") THEN KILL EXE.PATH$ & "TestBlob.sdb"
' // Create a new database
pDbc.OpenDatabase(EXE.PATH$ & "TestBlob.sdb")
' // Create a table
LOCAL sql AS STRING
Sql = "CREATE TABLE t (xyz blob)"
pDbc.Exec(sql)
' // Prepare the statement
sql = "INSERT INTO t (xyz) VALUES (?)"
LOCAL pStmt AS ISQLiteStatement
pStmt = pDbc.Prepare(sql)
' // Bind the blob
LOCAL fakeBlob AS STRING
fakeBlob = STRING$(500, "A")
pStmt.BindBlob(1, STRPTR(fakeBlob), 500, %SQLITE_TRANSIENT)
' // Fetch the row.
pStmt.Step
? "Row id was" & STR$(pDbc.LastInsertRowId)
' // Delete the prepared statement
pStmt = NOTHING
' // Read the blob
LOCAL pBlob AS ISQLiteBlob, nBlobBytes AS LONG
pBlob = pDbc.BlobOpen("main", "t", "xyz", 1)
nBlobBytes = pBlob.BlobBytes
? "Blob bytes: " & STR$(nBlobBytes)
LOCAL strBlob AS STRING
strBlob = NUL$(nBlobBytes)
pBlob.BlobRead(pDbc, BYVAL STRPTR(strBlob), nBlobBytes, 0)
? strBlob
pBlob = NOTHING
CATCH
' // Display error information
? pSql.OleErrorInfo
END TRY
' // Cleanup
pDbc = NOTHING ' // Closes the database
pSQL = NOTHING
#IF %DEF(%PB_CC32)
WAITKEY$
#ENDIF
END FUNCTION
' ========================================================================================