In case it is of use to any VB or VBA coders, I think this is all the code
needed to work with this dll. Also added a bit of code to do timings.
Option Explicit
Private Declare Sub sqlite3_open Lib "SQLite3VB.dll" _
(ByVal FileName As String, _
ByRef handle As Long)
Private Declare Sub sqlite3_close Lib "SQLite3VB.dll" _
(ByVal DB_Handle As Long)
Private Declare Function sqlite3_last_insert_rowid _
Lib "SQLite3VB.dll" _
(ByVal DB_Handle As Long) As Long
Private Declare Function sqlite3_changes _
Lib "SQLite3VB.dll" _
(ByVal DB_Handle As Long) As Long
Private Declare Function sqlite_get_table _
Lib "SQLite3VB.dll" _
(ByVal DB_Handle As Long, _
ByVal SQLString As String, _
ByRef ErrStr As String) As Variant()
'Now returns a BSTR
Private Declare Function sqlite_libversion _
Lib "SQLite3VB.dll" () As String
'// This function returns the number of rows from the last sql statement.
'// Use this to ensure you have a valid array
Private Declare Function number_of_rows_from_last_call _
Lib "SQLite3VB.dll" () As Long
'holding all the DB handles and paths
Public collSQLiteDB As Collection
Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Private lStartTime As Long
Sub StartSW()
lStartTime = timeGetTime()
End Sub
Sub StopSW(Optional ByRef strMessage As Variant = "")
MsgBox "Done in " & timeGetTime() - lStartTime & _
" msecs", , strMessage
End Sub
Function OpenDBConnection(strDB As String, _
Optional bClose As Boolean) As Long
Dim i As Long
Dim lDBHandle As Long
Dim strTempCurDrive As String
Dim strTempCurDir As String
Dim strErrors As String
Dim bFile As Boolean
Dim strLocalDrive As String
Dim bNewDBCollection As Boolean
Dim arr(1 To 2) As Variant
On Error GoTo ERROROUT
If collSQLiteDB Is Nothing Then
Set collSQLiteDB = New Collection
bNewDBCollection = True
End If
strTempCurDir = CurDir
strTempCurDrive = Left$(strTempCurDir, 1)
strLocalDrive = Left$(Application.Path, 1)
'this is to point to SQLite3VB.dll
'---------------------------------
ChDrive strLocalDrive
ChDir strLocalDrive & ":\SQLite3VB_Test\"
If InStr(1, strDB, ":memory:", vbTextCompare) = 0 Then
bFile = True
End If
'for in case there was a duplicate key in the DB collection
On Error Resume Next
If bClose Then
For i = 1 To collSQLiteDB.Count
If collSQLiteDB(i)(2) = strDB Then
sqlite3_close collSQLiteDB(i)(1)
collSQLiteDB.Remove i
Exit For
End If
Next
Else
If collSQLiteDB.Count = 0 Then
sqlite3_open strDB, lDBHandle
arr(1) = lDBHandle
arr(2) = strDB
'so a db path can only be in the collection once
collSQLiteDB.Add arr, strDB
OpenDBConnection = lDBHandle
Else
'see if there is a valid connection already for this DB
'------------------------------------------------------
For i = 1 To collSQLiteDB.Count
If collSQLiteDB(i)(2) = strDB Then
If collSQLiteDB(i)(1) > 0 Then
OpenDBConnection = collSQLiteDB(i)(1)
ChDrive strTempCurDrive
ChDir strTempCurDir
Exit Function
End If
sqlite3_close collSQLiteDB(i)(1)
collSQLiteDB.Remove i
End If
Next
sqlite3_open strDB, lDBHandle
arr(1) = lDBHandle
arr(2) = strDB
'so a db path can only be in the collection once
collSQLiteDB.Add arr, strDB
OpenDBConnection = lDBHandle
End If
'these speed up inserts enormously, particulary the first one
'------------------------------------------------------------
sqlite_get_table lDBHandle, "PRAGMA synchronous=off;", strErrors
sqlite_get_table lDBHandle, "PRAGMA encoding='UTF-8';", strErrors
'not sure default_cache_size applies to a memory database, probably not
If bFile Then
sqlite_get_table lDBHandle, _
"PRAGMA default_cache_size = 32768;", strErrors
End If
sqlite_get_table lDBHandle, "PRAGMA vdbe_trace = OFF;", strErrors
sqlite_get_table lDBHandle, "PRAGMA page_size=4096;", strErrors
End If
'to return to the CurDir at the start of the procedure
'-----------------------------------------------------
ChDrive strTempCurDrive
ChDir strTempCurDir
Exit Function
ERROROUT:
ChDrive strTempCurDrive
ChDir strTempCurDir
MsgBox Err.Description, , "error number: " & Err.Number
End Function
Function AlterDB(strCommand As String, _
Optional lDBHandle, _
Optional strDB As String) As Variant
Dim strErrors As String
Dim lChanges As Long
Dim lLastInsertRowID As Long
Dim arr(1 To 3) As Variant
On Error GoTo ERROROUT
If lDBHandle = 0 Then
lDBHandle = OpenDBConnection(strDB)
End If
sqlite_get_table lDBHandle, strCommand, strErrors
lChanges = sqlite3_changes(lDBHandle)
lLastInsertRowID = sqlite3_last_insert_rowid(lDBHandle)
arr(1) = lChanges
arr(2) = lLastInsertRowID
arr(3) = strErrors
AlterDB = arr
Exit Function
ERROROUT:
arr(1) = lChanges
arr(2) = lLastInsertRowID
arr(3) = strErrors
AlterDB = arr
End Function
Function GetFromDB(strSQL As String, _
Optional lReturnedRows As Long, _
Optional strError As String, _
Optional lDBHandle As Long, _
Optional strDB As String) As Variant
Dim arr
On Error GoTo ERROROUT
If lDBHandle = 0 Then
lDBHandle = OpenDBConnection(strDB)
End If
arr = sqlite_get_table(lDBHandle, strSQL, strError)
lReturnedRows = number_of_rows_from_last_call()
If lReturnedRows = 0 Then
GetFromDB = strError
Else
GetFromDB = arr
End If
Exit Function
ERROROUT:
GetFromDB = strError
End Function
Sub Test()
Dim i As Long
Dim r As Long
Dim c As Long
Dim strDB As String
Dim strSQL As String
Dim strError As String
Dim arr
Dim strResult As String
Dim lRows As Long
Dim lDBHandle As Long
strDB = "C:\test.db3"
'strDB = ":memory:"
lDBHandle = OpenDBConnection(strDB)
On Error Resume Next
strSQL = "DROP TABLE TEST"
AlterDB strSQL, lDBHandle
strSQL = "CREATE TABLE 'TEST' ([col1] INTEGER, [col2] TEXT)"
AlterDB strSQL, lDBHandle
StartSW
strSQL = "BEGIN TRANSACTION"
AlterDB strSQL, lDBHandle
For i = 1 To 6
strSQL = "INSERT INTO TEST (col1, col2) " & _
"VALUES(" & i & ", 'test value" & i & "')"
AlterDB strSQL, lDBHandle
Next
strSQL = "CREATE INDEX IDX_TEST_col1 ON TEST(col1)"
AlterDB strSQL, lDBHandle
strSQL = "COMMIT TRANSACTION"
AlterDB strSQL, lDBHandle
StopSW "time for inserting"
StartSW
strSQL = "SELECT * FROM TEST WHERE col1 > -1"
arr = GetFromDB(strSQL, lRows, strError, lDBHandle)
StopSW "time for retrieving"
If lRows = 0 Then
If Len(strError) > 0 Then
MsgBox strError, , "no rows returned"
Else
MsgBox "no rows returned", , ""
End If
OpenDBConnection strDB, True
Exit Sub
End If
'close the DB connection, don't have to do this
'----------------------------------------------
OpenDBConnection strDB, True
For r = 0 To UBound(arr)
For c = 0 To UBound(arr, 2)
If r = 0 And c = 0 Then
strResult = arr(r, c)
Else
If r = 1 And c = 0 Then
strResult = strResult & vbCrLf & _
"-------------------------"
End If
If c = LBound(arr, 2) Then
strResult = strResult & vbCrLf & arr(r, c)
Else
strResult = strResult & vbTab & arr(r, c)
End If
End If
Next
Next
MsgBox strResult, , strDB
End Sub
Sub ShowDBCollection()
Dim i As Long
Dim strResult As String
If collSQLiteDB Is Nothing Then
Exit Sub
End If
If collSQLiteDB.Count = 0 Then
Exit Sub
End If
For i = 1 To collSQLiteDB.Count
If i = 1 Then
strResult = collSQLiteDB(i)(1) & vbTab & collSQLiteDB(i)(2)
Else
strResult = strResult & vbCrLf & _
collSQLiteDB(i)(1) & vbTab & collSQLiteDB(i)(2)
End If
Next
MsgBox strResult, , "connected DB's"
End Sub
RBS
-----Original Message-----
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: 24 January 2007 21:41
To: [email protected]
Subject: RE: [sqlite] SQLite3VB.dll
Thanks, that makes it clear.
> SQLite implements the command "DELETE FROM table" without a WHERE clause
> by dropping and recreating the table.
Interesting, didn't know that.
Maybe I should re-create when the majority or a lot of rows are deleted.
RBS
-----Original Message-----
From: Trey Mack [mailto:[EMAIL PROTECTED]
Sent: 24 January 2007 21:20
To: [email protected]
Subject: Re: [sqlite] SQLite3VB.dll
> I have just one question.
> What exactly does this function do:
>
> Private Declare Function sqlite3_changes _
> Lib "SQLite3VB.dll" (ByVal DB_Handle As Long) As Long
>
> It looks it will always give one, unless you have just created a database
> and done nil with it. Is this how it is?
It returns the number of rows affected by a delete, update, or insert (not
updated by selects). It's not the number _changed_, but the number of rows
addressed by the WHERE clause, whether an update actually changes a value or
not. One special case is when you DELETE FROM table with no where clause..
explained below.
>From sqlite3.h:
/*
** This function returns the number of database rows that were changed
** (or inserted or deleted) by the most recent called sqlite3_exec().
**
** All changes are counted, even if they were later undone by a
** ROLLBACK or ABORT. Except, changes associated with creating and
** dropping tables are not counted.
**
** If a callback invokes sqlite3_exec() recursively, then the changes
** in the inner, recursive call are counted together with the changes
** in the outer call.
**
** SQLite implements the command "DELETE FROM table" without a WHERE clause
** by dropping and recreating the table. (This is much faster than going
** through and deleting individual elements form the table.) Because of
** this optimization, the change count for "DELETE FROM table" will be
** zero regardless of the number of elements that were originally in the
** table. To get an accurate count of the number of rows deleted, use
** "DELETE FROM table WHERE 1" instead.
*/
- Trey
----------------------------------------------------------------------------
-
To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------------
-
----------------------------------------------------------------------------
-
To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------------
-
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
