Search code examples
sqlvbasqliteadodbanki

SQLite 'long' integers are returned truncated/incorrect by SQL select query — how can the correct values be retrieved? (SQLite ODBC driver, VBA/ADODB)

In Excel VBA with the SQLite ODBC Driver, my simple SELECT query run against a single table retrieves 'long' integers (10 or more decimal places) incorrectly. How can these values be retrieved correctly, without truncation or whatever garbling is going on?

(PLEASE NOTE: the database structure/field definitions can't be modified — the database belongs to an open source application, Anki, and changing the structure would break the software.)

The particular table I'm querying contains (at least) several fields that can contain longer integer values (10 or more decimal places). The primary key ("id") contains Unix timestamp (datetime) values, with milliseconds, so the integer in the primary key field always occupies 13 decimal places.

Here is the table definition:

CREATE TABLE "notes" (
    "id"    integer,
    "guid"  text NOT NULL,
    "mid"   integer NOT NULL,
    "mod"   integer NOT NULL,
    "usn"   integer NOT NULL,
    "tags"  text NOT NULL,
    "flds"  text NOT NULL,
    "sfld"  integer NOT NULL,
    "csum"  integer NOT NULL,
    "flags" integer NOT NULL,
    "data"  text NOT NULL,
    PRIMARY KEY("id")
);

One of my records has the value 1640497387755 in the id field. If I run this query:

SELECT id AS primarykey, flds FROM notes WHERE id = 1640497387755

the results contain 2147483647 in the primarykey field (a 1-record ADO recordset)—not 1640497387755. The flds field in the recordset has the correct (textual) data, however.

I have verified that the field contains the correct/expected Unix timestamp values by browsing the database with a separate tool (DB Browser for SQLite). Note further that I'm able to select the correct record(s) using WHERE clauses based on the actual id field value(s) in the database, as in the SELECT statement above.

Casting the field to BigInt and BigInteger hasn't affected the results—for example, I get exactly the same results with:

SELECT CAST(id as biginteger) AS primarykey, flds FROM notes

Neither has setting the BigInt parameter for the ADODB connection, as shown in my code below (see documentation for the SQL ODBC Driver, under the heading "Connect string parameters for DSN-less connects").

Here is a minimal example of working VBA code demonstrating the problem in all its frustrating fullness:

Sub ADOQueryAnki()
        
    Dim conn As Object, rst As Object
    Dim varRecords() As Variant
    Dim strDBasePath As String
    Dim strSQLSelect As String
    
    Set conn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")
    
    strDBasePath = "I:\Anki\Test\collection.anki2;" 
    ' collection.anki2 is a standard SQLite database
    ' --though the filename has a non-standard extension
    strSQLSelect = "SELECT id as primarykey, flds FROM notes"
    
    ' OPEN CONNECTION
    conn.Open "DRIVER=SQLite3 ODBC Driver;Database=" & strDBasePath & ";BigInt = true"
    conn.CursorLocation = adUseClient
    
    rst.Open strSQLSelect, conn, adOpenDynamic, adLockOptimistic ' have tried multiple options
    
    Debug.Print rst!primarykey
    
End Sub

In the run I just did, the Debug.Print statement outputs 2147483647, where the actual value of the corresponding field in the database is 1324302169120.

Note that the ActualSize and DefinedSize properties for the field in the recordset are both shown in the VBA debugger as "4," meaning (I think) 4 bytes, whereas SQLite uses 8 bytes to store these values in the database.

enter image description here

Solution

  • As commented, essentially the issue derives from how to include the BigInt parameter in connection string. While MSDN documentation appears to differ from implementation, key/value pairs should avoid whitespaces:

    DRIVER=SQLite3 ODBC Driver;Database=" & strDBasePath & ";BigInt=true"

    Lessons learned in debugging special attributes to ODBC connection strings:

    • Avoid preceding and trailing whitespaces and use single quotes for special string attributes;
    • For boolean attributes, check for case sensitivity (true vs True), integer (0, 1) synonyms, string (yes/no) synonyms;
    • For ending attributes, check for punctuation such as ending semicolon;
    • Run vanilla versions of connections and recordsets using default params without special configurations such as for CursorLocation, CursorType, and LockType;
    • Debug in other languages (i.e., Python, PowerShell) that support ODBC as control check to isolate environment or interface issues;
    • Carefully read documentation of specific ODBC driver or library/module and API like ADO.