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