VB SQLite Library (COM-Wrapper)

[Krool]

This Ax-DLL project is intended to get a VB-friendly COM-Wrapper for the SQLite library, which is based on the sqlite3win32 __stdcall compilation.

Ax-DLL version: 1.2.41
SQLite version: 3.51.3

A Std-EXE demo project is also included to show how to use the Ax-DLL.

The interaction of the various components are very simplistic:


There is only one public creatable component, the SQLiteConnection object.

The SQLiteDataSet object is read-only and meaningful for SELECT statements only.
INSERT, UPDATE and DELETE statements shall be done via the 'Execute' methods of the SQLiteConnection or SQLiteCommand object.

The SQLiteCommand object is just a helper. All can be done without it.
However, for recurring tasks it might perform better and can also be convient for parametrized SQL statements.

The source code of the project can also be viewed on GitHub.

Helper functions to convert from/to JulianDay and UnixEpoch outside of SQL which can be useful for .SetParameterValue to convert from OADate into JulianDay or UnixEpoch and for a query result to convert from JulianDay or UnixEpoch back to an OADate.

List of overloaded built-in SQL functions:

- NOCASE collating sequence to support unicode.
- LIKE(), UPPER(), and LOWER() to support unicode.

List of new SQL functions:

- OADate() that can alter date and/or time and returns an OLE automation date.
  It returns the current UTC when there are no args.
  Arg 1: 'now' (current UTC), a number or a VB6 date string format which translates to a number using IsDate() and CDate()
  Arg 2 to N are modifiers that alter date and/or time to its left.
  Following modifiers are supported from the standard:
  - 'NNN days', 'NNN hours', 'NNN minutes', 'NNN seconds', 'NNN months' and 'NNN years'. (s at the end is optional)
  - 'start of month', 'start of year' using DateSerial().
  - 'start of day' to remove the time using Fix().
  - 'weekday N' using DateAdd() in a loop.
  - 'oadate' which is a no-op but for completeness.
  - 'unixepoch', 'julianday' and 'auto'.
  - 'localtime' and 'utc' to convert from/to UTC.
  - 'subsec' and 'subsecond' are no-ops.
- JulianDayFromOADate() and JulianDayToOADate() to convert between OADate and JulianDay.
- UnixEpochFromOADate() and UnixEpochToOADate() to convert between OADate and UnixEpoch.

List of revisions:

17-Apr-2026
- Upgrade sqlite3 c source to version 3.51.3 (2026-03-13).
15-Dec-2025
- Upgrade sqlite3 c source to version 3.50.4 (2025-07-30).
09-Jun-2025
- Upgrade sqlite3 c source to version 3.50.1 (2025-06-06).
21-Jan-2025
- Upgrade sqlite3 c source to version 3.47.2 (2024-12-07).
07-Nov-2024
- Included SQL function OADate() that can alter date and/or time and returns an OLE automation date.
05-Nov-2024
- Currency data type allowed on .SetParameterValue method and .LastInsertRowID property. (SQLITE_INTEGER)
04-Nov-2024
- Included SQL function JulianDayFromOADate() and JulianDayToOADate().
- Included SQL function UnixEpochFromOADate() and UnixEpochToOADate().
22-Oct-2024
- Passing vbNullString to .SetParameterValue no longer binds a SQL NULL in a SQLiteCommand or SQLiteCursor class.
08-Oct-2024
- Upgrade sqlite3 c source to version 3.46.1 (2024-08-13).
22-Apr-2024
- Upgrade sqlite3 c source to version 3.45.3 (2024-04-15).
24-Jan-2024
- Upgrade sqlite3 c source to version 3.44.2 (2023-11-24).
08-Nov-2023
- Removed the stmt_readonly check for .OpenDataSet and .CreateCursor.
  This allows for example to use a PRAGMA statement and read the result row.
11-Oct-2023
- Upgrade sqlite3 c source to version 3.43.2 (2023-10-10).
08-Jul-2023
- SQLiteCursor class is not auto-initialized anymore with sqlite3_step.
  Use .MoveFirst to ensure it is initialized, otherwise an error will occur.
02-Jul-2023
- Upgrade sqlite3 c source to version 3.41.2 (2023-03-22).
26-Sep-2022
- Upgrade sqlite3 c source to version 3.39.3 (2022-09-05).
10-Aug-2022
- Upgrade sqlite3 c source to version 3.39.2 (2022-07-21).
- Upgrade of the regexp c extension.
- Added compile-time option SQLITE_OMIT_AUTOINIT 1.
03-Aug-2022
- Usage of sqlite3_prepare16_v2/v3 instead of sqlite3_prepare_v2/v3, if applicable.
01-Aug-2022
- Better performance due to sqlite3 c source compiled with SQLITE_DEFAULT_CACHE_SIZE -8000. (instead of default -2000)
- Usage of sqlite3_bind_text16 instead of sqlite3_bind_text.
28-Jul-2022
- Included the binding methods/properties in the SQLiteCursor class.
  This is meaningful only when created from a SQLiteConnection class, because the query will be expanded when created from an SQLiteCommand class.
  Using SetParameterValue or ClearParameters will move to the first row automatically.
27-Jul-2022
- Included the SQLiteCursor class which can be created from a SQLiteConnection or SQLiteCommand class.
  This is useful for very large tables in which data can be retrieved without memory problems. The cursor is treated here as an iterator.
02-Mar-2022
- Upgrade sqlite3 c source to version 3.37.2 (2022-01-06).
19-Apr-2021
- Included the SetProgressHandler method which registers/unregisters a progress handler callback.
  For this the new ISQLiteProgressHandler class needs to be implemented on the receiver.
- Included the BackupDB method which backups (copies) a SQLite database between two SQLite database connections.
- Included the SharedCache parameter in the OpenDB method.
- Included the LastInsertRowID property in the SQLiteConnection class.
- Included the AutoCommit (read-only) property in the SQLiteConnection class.
27-Mar-2021
- OpenDB method now uses the flags SQLITE_OPEN_FULLMUTEX to enable serialized threading mode.
  Also changed the compile-time option SQLITE_THREADSAFE from 2 to 1. (recommended default setting)
  This means multiple threads can safely use the same database connection.
  When each thread uses an own connection then the threading mode is as multi-thread. (equivalent as if SQLITE_OPEN_NOMUTEX or SQLITE_THREADSAFE=2)
26-Mar-2021
- REGEXP operator included by statically linking to the regexp c extension.
19-Mar-2021
- Upgrade sqlite3 c source to version 3.34.1 (2021-01-20).
- Optimized SQLiteDataSet as ReDim Preserve is called less times (array bump of 10 is defined) to improve performance.
29-Apr-2020
- Bugfix in SQLiteCommand for the SetParameterValue method.
  The VarType was not correctly when passing VT_DISPATCH that has a DISPID_VALUE.
04-Apr-2020
- Upgrade sqlite3 c source to version 3.31.1 (2020-01-27).
05-Feb-2020
- Unicode support for NOCASE collating sequence.
- Unicode support for LIKE(), UPPER(), and LOWER() functions.
24-Jan-2020
- First release.

The source code of "VBSQLite12SideBySide.res" for Registration-Free (Side-by-side) is:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<assembly xmlns="urn:schemas-microsoft-com:asm.v1" manifestVersion="1.0">
   <file name="VBSQLite12.DLL">
      <typelib tlbid="{7CC1A5F1-A0FF-4546-A0F1-FBFE744A4522}" version="1.1" flags="hasdiskimage" helpdir="" />
      <comClass clsid="{1DE49E39-B9D4-4CDF-95AA-098B89F11318}" tlbid="{7CC1A5F1-A0FF-4546-A0F1-FBFE744A4522}" threadingModel="Apartment" progid="VBSQLite12.SQLiteConnection" />
   </file>
</assembly>

The attached file VBSQLite12.zip (Src) and sqlite3win32 (Bin) needs to be extracted in a same root folder.
sqlite3win32 is only needed for compiling the Ax-DLL. The compiled Ax-DLL doesn't have any dependency, because sqlite3win32 was then compiled into it.