Sqllite library

[dz32]

Sqllite wrapper DLL.

Cant seem to upload files right now.. you can grab a copy here: https://github.com/dzzie/sqllite4vb

This is another Claude creation. Uses the standard Sqllite C library with a small vb compatible shim layer so it will be easy to keep up to date.
C Lib is a standalone dll, and also handles unicode support.

ADO-flavored API: db.Query, db.Scalar, db.ExecInsert, db.ExecUpdate — parameterized, no string concatenation, no escaping nightmares

Schema introspection and migration tools — ColumnExists, AddColumn, UserVersion, IntegrityCheck, CompactDB, the works

Proper int64 handling via Currency marshalling, blob support, transactions, prepared statements with type-dispatched binding

Both an OO layer for sane code and a low-level sqlite3_* API surface (via a GlobalMultiUse class) for when you need raw control

Example:

Dim db As New cSQLite
    db.OpenDB sDbPath
    Log "Opened: version=" & db.Version & "  path=" & db.Path
    Log ""

    db.Execute _
        "CREATE TABLE users (" & _
        "  id INTEGER PRIMARY KEY AUTOINCREMENT," & _
        "  name TEXT NOT NULL," & _
        "  age INTEGER," & _
        "  joined TEXT," & _
        "  notes TEXT" & _
        ")"

    ' --- ExecInsert with mixed types ----------------------------------
    Log "[1] ExecInsert (parameterized, safe vs apostrophes)"
    db.ExecInsert "users", "name,age,joined,notes", "Alice", 30, Now, "first user"
    db.ExecInsert "users", "name,age,joined,notes", "O'Brien", 45, Now, "tests apostrophe"
    db.ExecInsert "users", "name,age,joined,notes", "Charlie", 27, Now, Null
    db.ExecInsert "users", "name,age,joined,notes", "Diana", 52, Now, _
        "name with " & ChrW$(&HE9) & " accent and ; semicolons; etc."
    Log "    " & db.RowsAffected & " row(s) affected by last insert"
    Log "    last rowid = " & CStr(Int64FromCurrency(db.LastInsertRowID))
    Log ""

    ' --- Query with rs("name") access ---------------------------------
    Log "[2] Query with by-name field access"
    Dim rs As cSQLiteResults
    Set rs = db.Query("SELECT id, name, age, notes FROM users ORDER BY id")
    Do While rs.MoveNext()
        Log "    " & rs("id") & " | " & rs("name") & " | age=" & rs("age") & _
            " | " & IIf(IsNull(rs("notes")), "(null)", rs("notes"))
    Loop
    rs.CloseRS
    Log ""

    ' --- Parameterized query ------------------------------------------
    Log "[3] Parameterized query"
    Set rs = db.Query("SELECT name, age FROM users WHERE age >= ? ORDER BY age DESC", 30)
    Do While rs.MoveNext()
        Log "    " & rs.FieldText("name") & "  (age " & rs.FieldInt("age") & ")"
    Loop
    Log ""

    ' --- Scalar -------------------------------------------------------
    Log "[4] Scalar query"
    Log "    avg age = " & Format$(db.Scalar("SELECT AVG(age) FROM users"), "0.00")
    Log "    Bob exists? = " & (Not IsEmpty(db.Scalar("SELECT 1 FROM users WHERE name=?", "Bob")))
    Log "    Alice age = " & db.Scalar("SELECT age FROM users WHERE name=?", "Alice")
    Log ""

    ' --- ExecUpdate ---------------------------------------------------
    Log "[5] ExecUpdate (criteria can be literal OR parameterized)"
    Dim affected As Long
    ' Literal criteria:
    affected = db.ExecUpdate("users", "WHERE name='Alice'", "age,notes", 31, "updated")
    Log "    literal-criteria update: " & affected & " row(s)"
    ' Parameterized criteria (safe with untrusted values):
    affected = db.ExecUpdate("users", "WHERE id=?", "age", 99, 2)
    '                                          ^placeholder       ^SET val ^criteria val
    Log "    parameterized-criteria update: " & affected & " row(s)"
    Log "    Alice's age now = " & db.Scalar("SELECT age FROM users WHERE name='Alice'")
    Log "    user id=2 age now = " & db.Scalar("SELECT age FROM users WHERE id=?", 2)
    Log ""

    ' --- Transaction with prepared statement reuse --------------------
    Log "[6] 1000 inserts in a transaction with statement reuse"
    db.Execute "CREATE TABLE big (n INTEGER, label TEXT)"
    Dim t0 As Single, t1 As Single
    t0 = Timer
    db.BeginTrans
    Dim stmt As cSQLiteStatement
    Set stmt = db.Prepare("INSERT INTO big (n, label) VALUES (?, ?)")
    Dim i As Long
    For i = 1 To 1000
        stmt.Bind 1, i
        stmt.Bind 2, "row " & i
        stmt.Execute
    Next i
    Set stmt = Nothing  ' triggers Class_Terminate -> finalize
    db.CommitTrans
    t1 = Timer
    Log "    inserted 1000 rows in " & Format$(t1 - t0, "0.000") & " sec"
    Log "    count = " & db.Scalar("SELECT COUNT(*) FROM big")
    Log ""

    ' --- LoadAll for small result sets --------------------------------
    Log "[7] LoadAll() — read first 5 big rows into 2D array"
    Set rs = db.Query("SELECT n, label FROM big WHERE n <= 5 ORDER BY n")
    Dim data As Variant
    data = rs.LoadAll
    If Not IsEmpty(data) Then
        Dim r As Long
        For r = LBound(data, 1) To UBound(data, 1)
            Log "    " & data(r, 0) & " | " & data(r, 1)
        Next r
    End If
    Log ""

[Tech99]

Hi dzzie, looked your sqlite wrapper, i think one useful feature is missing. How to open database in readonly mode?

[dz32]

good call, I just added the following along with a backup db method. There was also a bug in error messages returned from exec

Public Sub OpenDB(ByVal sFilename As String, _
                  Optional ByVal readOnly As Boolean = False, _
                  Optional ByVal createMissing As Boolean = True, _
                  Optional ByVal timeout As Long = 5000)
    Dim flags As Long
    Dim rc As Long
    Dim msg As String

    If m_hDb <> 0 Then CloseDB

    If readOnly Then
        ' READONLY is mutually exclusive with READWRITE/CREATE — SQLite
        ' will reject the combination. createMissing is ignored when
        ' readOnly is True (you can't create a file you can't write to).
        flags = SQLITE_OPEN_READONLY
    Else
        flags = SQLITE_OPEN_READWRITE
        If createMissing Then flags = flags Or SQLITE_OPEN_CREATE
    End If

    rc = api.sqlite3_open_v2(sFilename, m_hDb, flags, vbNullString)
    If rc <> SQLITE_OK Then
        msg = api.sqlite3_errmsg(m_hDb)
        api.sqlite3_close m_hDb
        m_hDb = 0
        Err.Raise vbObjectError + 30901, "cSQLite.OpenDB", _
            "Open failed (" & rc & "): " & msg & " — " & sFilename
    End If

    m_path = sFilename
    api.sqlite3_busy_timeout m_hDb, timeout
End Sub

[Zvoni]

On a sidenote:
I'd rather offer an argument "Flags" as a ParamArray of Long (Last Argument obviously).
You give no provision for e.g. SQLITE_OPEN_MEMORY (for InMemory DB) or SQLITE_OPEN_NOMUTEX (for multiple Connections accessing the DB)
That's just on first look

[dz32]

Thats a good idea too thanks

Public Sub OpenDB(ByVal sFilename As String, _
                  Optional ByVal readOnly As Boolean = False, _
                  Optional ByVal createMissing As Boolean = True, _
                  Optional ByVal timeout As Long = 5000, _
                  Optional ByVal extraFlags As sqlLiteOpenConst = 0 _
                  )
    ...

    If extraFlags <> 0 Then flags = flags Or extraFlags

this will give the caller intellisense on the const as they or them together

[yereverluvinuncleber]

That's a bit good. I will try it.

[dz32]

I think i am going to add these aux classes to the library as well. they function fine external.

They give you the ability to get a detached recordset that can be passed around or serialized
load from json, csv, as well as generate sql statements for the encapsulated data.

playing with this because I am toying with the idea of utilizing this with teh ai4vb project
just posted where this could be a convenient way to let ai query databases through your app
and pass it results.