CSQLite Class

[José Roca]

My Windows API Headers III 1.04 incorporated CSQLite, a wrapper class on top of SQLite.

Attached is the help file.

[José Roca]

Code Select Expand


' ########################################################################################
' 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
' ========================================================================================


[José Roca]

Code Select Expand


' ########################################################################################
' 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
' ========================================================================================


[José Roca]

Code Select Expand


' ########################################################################################
' 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
' ========================================================================================


[José Roca]

Code Select Expand


' ########################################################################################
' 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
' ========================================================================================


[José Roca]

Code Select Expand


' ########################################################################################
' 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
' ========================================================================================