CSQLite Class

[José Roca]

My Windows API Headers III 1.04 incorporate 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 José 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 José 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", %SQLITE_TRANSIENT)
      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 José 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 José 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 José 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
' ========================================================================================


[Paul Squires]

Hi Jose,

Your SQLite classes are very impressive. I used them today to generate some speed benchmarks for inserting and navigating the database/index. The results are extremely good with speeds that I found to be incredible!

I did notice that when adding more than one record you need to add the "-1" parameter to your bind statements in order to ensure that the correct string data is bound to the prepared statement. One of your examples in this thread does not use the -1 (granted, that example only inserts one record).

pStmt.BindText(1, "fruit")

pStmt.BindText(1, "fruit", -1)

Here is the test code that I wrote (modifying your example). Thanks!

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 José 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"

'JPRO_COMPILER = PB/CC

' ========================================================================================
' Main
' ========================================================================================
Function PBMain

   ' // Create an instance of the class
   Local nStart  As Single
   Local nEnd    As Single
   Local nElapsed As Single
   Local nCount  As Long
   Local NumRecs As Long
   Local i       As Long
   Local pSQL  As ISQLite
   Local pStmt As ISQLiteStatement
   
   NumRecs = 100000
   
   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 (custid text, firstname text, lastname text)"
      pDbc.Exec(sql)

      Sql = "CREATE INDEX i ON t (custid)"
      pDbc.Exec(sql)
     
      Randomize 1
     
      nStart = Timer

      pDbc.Exec("BEGIN IMMEDIATE;")
      ' // Prepare the statement
      sql = "INSERT INTO t (custid,firstname,lastname) VALUES (?,?,?)"
      pStmt = pDbc.Prepare(sql)

      For i = 1 To NumRecs
         ' // Bind the text
         pStmt.BindText 1, Format$(Rnd(10000, 99999)), -1
         pStmt.BindText 2, "Paul", -1
         pStmt.BindText 3, "Squires", -1
         pStmt.Step
         '? "Row id was" & Str$(pDbc.LastInsertRowId)
         pStmt.Reset
      Next
      pDbc.Exec("END TRANSACTION;")

      nEnd = Timer
      nElapsed = nEnd - nStart
     
      Print "Adding"; Str$(NumRecs); " records. Time: " & _
            Format$(nElapsed, "#.00");" seconds. " & _
            Format$(numRecs/nElapsed, "0"); " recs per second."



      ' // Prepare a query
      nStart = Timer
      pStmt = pDbc.Prepare("SELECT * FROM t ORDER BY CUSTID;")
      ' // 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
         
         Incr nCount
         
         ' // Read the columns and values
         'For i = 0 To pStmt.ColumnCount- 1
         '   ? pStmt.ColumnName(i)
         '   ? pStmt.ColumnText(i)
         'Next
      Loop
      nEnd = Timer
      nElapsed = nEnd - nStart
     
      Print "Moving through"; Str$(nCount); " records. Time: " & _
            Format$(nElapsed, "#.00");" seconds. " & _
            Format$(numRecs/nElapsed, "0"); " recs per second."


   Catch
     ' // Display error information
      ? pSql.OleErrorInfo
   End Try

   ' // Cleanup
   pStmt = Nothing   ' // Deletes the prepared statement
   pDbc = Nothing    ' // Closes the database
   pSQL = Nothing

   ? "Complete."
   
   #If %Def(%Pb_Cc32)
      WAITKEY$
   #ENDIF

End Function
' ========================================================================================



[José Roca]

Thanks for pointing it. I have modified the example.

The purpose of this light wrapper class was to ease the use of SQLite with PowerBASIC (things like functions returning pointers to asciiz strings instead of strings, etc.), and also to add structured error handling (having to check the result code after each call to a function is a pain; structured error handling, amazingly little used by PBers, is a blessing).

[Paul Squires]

Hi Jose,

Hope you don't mind this suggestion. A great addition to your classes would be able to retrieve data based on the column name in addition to the existing column index. Here are the changes I made:

Changes to the Prepare method (cSQLiteDbc.inc):

Code Select Expand


   METHOD Prepare (BYVAL strSQL AS STRING) AS ISQLiteStatement
      Local lRes As Long, ppStmt As Dword, pStmt As ISQLiteStatement
      Local i As Long, sColumnLookup As String
      lRes = sqlite3_prepare_v2(m_hDbc, ByCopy strSQL, -1, ppStmt, ByVal 0)
      IF lRes = %SQLITE_OK THEN
         pStmt = CLASS "CSQLiteStatement"
         If IsObject(pStmt) Then
            pStmt.hStmt = ppStmt
         
            For i = 0 To pStmt.ColumnCount - 1
               sColumnLookup = sColumnLookup & $Bs & UCase$(pStmt.ColumnName(i))
            Next
            pStmt.ColumnNameLookup = sColumnLookup & $Bs

            Method = pStmt
         END IF
      END IF
      IF lRes THEN
         METHOD OBJRESULT = &H80004005&
         OleSetErrorInfo $IID_ISQLiteConnection, "ISQLITECONNECTION." & FUNCNAME$, "SQLite Error: " & FORMAT$(lRes) & ": " & ME.ErrMsgW
      END IF
   END METHOD


Changes to the cSQLiteStmt.inc:

Code Select Expand


   Instance m_sColumnLookup As String

   ' =====================================================================================
   ' Sets the column name lookup string
   ' =====================================================================================
   Property Set ColumnNameLookup (ByVal sColumnLookup As String)
      m_sColumnLookup = sColumnLookup
   End Property
   ' =====================================================================================

   ' =====================================================================================
   Method ColumnNameToIndex( ByVal sColumnName As String ) As Long
      Local i As Long
      i = InStr( m_sColumnLookup, $Bs & UCase$(sColumnName) & $Bs )
      If i Then i = Tally(Left$(m_sColumnLookup, i), $Bs)
      Method = i - 1
   End Method

   ' =====================================================================================
   Method ColumnTextByName (ByVal sColName As String) As String
      Local pszText As Asciiz Ptr
      Local nCol    As Long
      nCol = Me.ColumnNameToIndex(sColName)
      pszText = sqlite3_column_text(m_hStmt, nCol)
      If pszText Then Method = @pszText
   End Method
   ' =====================================================================================


Similar "ByName" methods could be made for ColumnLong, ColumnQuad, etc...

[Paul Squires]

Instead of using the Instr/Tally for the lookup it probably would have been cooler if I used a PowerCollection. 

[José Roca]

All suggestions are welcome, specially since I'm not by any means expert in SQLite (in fact, I have written the class to learn how to use it).

For the changes, I have used the same technique that I did use in the ODBC classes: a PowerCollection for the names of the columns and a variant for the column (this way, there is no need to duplicate methods: the same method accepts a column number or a column name).

For example:

Code Select Expand


   ' =====================================================================================
   ' Returns the column value as a UTF-8 string.
   ' The leftmost column of the result set has the index 0.
   ' =====================================================================================
   METHOD ColumnText (BYVAL vCol AS VARIANT) AS STRING
      LOCAL nCol AS LONG
      IF VARIANTVT(vCol) = %VT_BSTR THEN
         nCol = ME.ColNameToIdx(vCol)
         IF nCol = -1 THEN EXIT METHOD
      ELSE
         nCol = VARIANT#(vCol)
      END IF
      LOCAL pszText AS ASCIIZ PTR
      pszText = sqlite3_column_text(m_hStmt, nCol)
      IF pszText THEN METHOD = @pszText
   END METHOD
   ' =====================================================================================


Attached are the two modified includes.

[Paul Squires]

Cool Jose - thanks a lot. I will try the modified code in my application tomorrow when I get to work. I did a quick look at your ColNameToIdx  method and in two case it is doing METHOD = 1 when the object is invalid or the column name does not exist. Should that be -1 rather than 1 ?

I decided to use your class in my app rather than SQLitening this time because it is a standalone app and I wanted to test your code. Hopefully I can find a couple of other areas where we can build upon the great code that you have already made.

Thanks!

[José Roca]

Yes, it must be -1. Thanks for spotting it.

[Rick McNeely]

José,

I'm trying out SQLite in a simple application.  When the app starts I want it to create the db, create a couple of tables, and add a couple of entries.  That part works fine.  If this stuff already exists I want the statements to fail gracefully via try/catch but something is happening that PB is not catching.  For example, if the create table statement fails because a table already exists, the msgbox in catch never occurs, and the code never falls through to the following statements.  It appears to just exit the function (WM_CREATE).

I'm getting my info from your post: http://www.powerbasic.com/support/pbforums/showthread.php?t=51108&highlight=sqlite

What am I missing here?

Thanks


    ' // 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
    Else
   
    End If
   
    Try
      pDbc.OpenDatabase(Exe.PATH$ & "USM_AMX.sqlite")
      pDbc.Exec("PRAGMA foreign_keys = true;")
    Catch
        MsgBox "Couldn't open"
    End Try
   
    Try
        pDbc.Exec("CREATE TABLE projects (ndx INTEGER PRIMARY KEY, campus TEXT, building TEXT, room TEXT);")
    Catch
        MsgBox "couldn't create"
    End Try
   
    Try ' if table doesn't exist, create it
        pDbc.Exec("CREATE TABLE properties (ndx INTEGER PRIMARY KEY, " & _
                  "campus TEXT, building TEXT, room TEXT, " & _
                  "fndx INTEGER, " & _
                  "FOREIGN KEY(fndx) REFERENCES projects(ndx) ON DELETE CASCADE ON UPDATE CASCADE);")
    Catch
      MsgBox "couldn't create tables"
    End Try
   
    Try
      pDbc.Exec("INSERT OR REPLACE INTO projects (campus, building, room) VALUES ('UIUC', 'Bevier', 'Ricks Test AMX');")
      pDbc.Exec("INSERT OR REPLACE INTO projects (campus, building, room) VALUES ('UIUC', 'Bevier', 'Rons Test AMX');")
   Catch
      '// Display Error information
      MsgBox pSql.OleErrorInfo
   End Try

   ' // Cleanup
   'pStmt = Nothing   ' // Deletes the prepared statement
   pDbc = Nothing    ' // Closes the database
   pSQL = Nothing
   MsgBox "Made it!"

[José Roca]

You must be using an outdated version. There was a discussion in the PB forum and I modified that function to:

Code Select Expand


   ' =====================================================================================
   ' Runs zero or more semicolon separated SQL statements.
   ' Callback function prototype:
   ' FUNCTION SQLite_Exec_Callback CDECL(BYVAL pUserData AS DWORD, BYVAL numCols AS LONG, _
   '   BYVAL pszColValues AS ASCIIZ PTR, BYVAL pszColNames AS ASCIIZ PTR) AS LONG
   ' Usage example to retrieve the contents of the pColValues and pColNames arrays:
   ' FOR i = 0 TO numCols - 1
   '    ? "Column name: " & @@pszColNames[i]
   '    ? "Column value: " & @@pszColValues[i]
   ' NEXT
   ' If an sqlite3_exec() callback returns non-zero, the sqlite3_exec() routine returns
   ' SQLITE_ABORT without invoking the callback again and without running any subsequent
   ' SQL statements.
   ' =====================================================================================
   METHOD Exec (BYVAL sql AS STRING, OPTIONAL BYVAL pCallback AS DWORD, BYVAL pData AS DWORD, BYREF ppErrMsg AS DWORD) AS LONG
      LOCAL lRes AS LONG, pErrMsg AS ASCIIZ PTR
      lRes = sqlite3_exec(m_hDbc, BYCOPY sql, pCallback, pData, pErrMsg)
      METHOD = lRes
      IF lRes THEN
         METHOD OBJRESULT = &H80004005&
         IF pErrMsg THEN
            OleSetErrorInfo $IID_ISQLiteConnection, "ISQLITECONNECTION." & FUNCNAME$, "SQLite Error: " & FORMAT$(lRes) & ": " & @pErrMsg
         ELSE
            OleSetErrorInfo $IID_ISQLiteConnection, "ISQLITECONNECTION." & FUNCNAME$, "SQLite Error: " & FORMAT$(lRes)
         END IF
         IF VARPTR(ppErrMsg) THEN
            ppErrMsg = pErrMsg
         ELSE
            sqlite3_free pErrMsg
         END IF
      END IF
   END METHOD
   ' =====================================================================================


It is in CSQliteDbc.inc.