My Windows API Headers III 1.04 incorporate 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 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
' ========================================================================================
' ########################################################################################
' 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
' ========================================================================================
' ########################################################################################
' 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
' ========================================================================================
' ########################################################################################
' 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
' ========================================================================================
' ########################################################################################
' 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
' ========================================================================================
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!
' #############################################################################' ########################################################################################
' 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
' ========================================================================================
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).
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):
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:
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...
Instead of using the Instr/Tally for the lookup it probably would have been cooler if I used a PowerCollection. :)
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:
' =====================================================================================
' 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.
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!
Yes, it must be -1. Thanks for spotting it.
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!"
You must be using an outdated version. There was a discussion in the PB forum and I modified that function to:
' =====================================================================================
' 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.
Perfect. Runs like a top now!
Thanks
BTW, do you actually sleep?
Where does one get the "CSQLite.Inc" file, it doesnt exist in any of the RAR/ZIP files?
As stated in the first post of this thread, it is part of my Windows API Headers:
http://www.jose.it-berater.org/smfforum/index.php?topic=4558.0
Thomas ,
Just a note: The headers referenced by José don't seem to have the latest 'METHOD Exec' in CSQLITEDbc.inc. I just pasted in the replacement that was shown in his post. Worked great after that.