Search notes:

Connect to SQLite from VBA using winsqlite3.dll

Windows 10 comes with a pre installed DLL that can be used for accessing SQLite databases (C:\Windows\System32\winsqlite3.dll).
Because Visual Basic for Applications (VBA) allows to use functions that are defined in a DLL, it is possible to use SQLite in VBA directly, that is, without OLE DB or ADOX or any other of these fancy technologies.
It was only after I started to develop these sources that I found out that Govert van Drimmelen already did the same thing, but way more complete.
The following two code snippets try to demonstrate how that might be achieved.

winsqlite3_4_VBA.bas

These declare statements allow to use winsqlite3.dll from Visual Basic for Applications 
option explicit

public const SQLITE_OK      =    0  ' Successful result
public const SQLITE_ERROR   =    1  ' Generic Error
public const SQLITE_BUSY    =    5  ' The database file is locked
public const SQLITE_TOOBIG  =   18  ' String or BLOB exceeds size limit
public const SQLITE_MISUSE  =   21  ' Library used incorrectly
public const SQLITE_ROW     =  100  ' sqlite3_step() has another row ready
public const SQLITE_DONE    =  101  ' sqlite3_step() has finished executing

' Constants that identify SQLite data types ' {
'
' Returned by sqlite3_column_type()
'
public const SQLITE_INTEGER =  1
public const SQLITE_FLOAT   =  2
public const SQLITE_TEXT    =  3  ' TODO: Should it be named SQLITE3_TEXT?
public const SQLITE_BLOB    =  4
public const SQLITE_NULL    =  5
' }

' { WinAPI constants
private const CP_UTF8        = 65001
' }

' sqlite3_open {
declare ptrSafe function sqlite3_open        lib "winsqlite3.dll" (  _
     byVal    zFilename        as string , _
     byRef    ppDB             as longPtr  _
) as longPtr ' }

' sqlite3_close {
declare ptrSafe function sqlite3_close       lib "winsqlite3.dll" ( _
     byVal    db               as longPtr  _
) as longPtr ' }

' sqlite3_exec ' {
'
'   TODO: how can/should errmsg be handled in case of an error
'
declare ptrSafe function sqlite3_exec        lib "winsqlite3.dll" ( _
     byVal    db               as any    , _
     byVal    sql              as string , _
     byVal    callback         as longPtr, _
     byVal    argument_1       as longPtr, _
     byRef    errmsg           as string   _
) as longPtr ' }

' sqlite3_prepare_v2 {
'
'   TODO pzTail is actually a char**
'
declare ptrSafe function sqlite3_prepare_v2  lib "winsqlite3.dll" ( _
     byVal    db               as any    , _
     byVal    zSql             as string , _
     byVal    nByte            as longPtr, _
     byRef    ppStatement      as longPtr, _
     byRef    pzTail           as any      _
) as longPtr ' }

' sqlite3_finalize {
'
'
declare ptrSafe function sqlite3_finalize    lib "winsqlite3.dll" ( _
     byVal    stmt             as longPtr  _
) as longPtr ' }

' sqlite3_bind_* ' {

' sqlite3_bind_int {
declare ptrSafe function sqlite3_bind_int    lib "winsqlite3.dll" ( _
     byVal    stmt            as longPtr , _
     byVal    pos             as long    , _
     byVal    val             as long      _
) as long ' }

' sqlite3_bind_text {
'
'     TODO: what is the «whatIsThis» parameter used for?
'
declare ptrSafe function sqlite3_bind_text_  lib "winsqlite3.dll" alias "sqlite3_bind_text" ( _
     byVal    stmt            as longPtr , _
     byVal    pos             as long    , _
     byVal    val             as longPtr , _
     byVal    len_            as integer , _
     byVal    whatIsThis      as longPtr   _
) as long ' }

' sqlite3_bind_null {
declare ptrSafe function sqlite3_bind_null   lib "winsqlite3.dll" ( _
     byVal    stmt            as longPtr , _
     byVal    pos             as long      _
) as long
' }

' }

' sqlite3_step ' {
declare ptrSafe function sqlite3_step          lib "winsqlite3.dll" ( _
     byVal     stmt           as longPtr   _
) as long ' }


' sqlite3_reset ' {
declare ptrSafe function sqlite3_reset         lib "winsqlite3.dll" ( _
     byVal     stmt           as longPtr   _
) as long ' }


' sqlite3_column_* {

' sqlite3_column_double ' {
declare ptrSafe function sqlite3_column_double lib "winsqlite3.dll" ( _
     byVal     stmt           as longPtr , _
     byVal     iCol           as integer   _
) as double ' }

' sqlite3_column_int ' {
declare ptrSafe function sqlite3_column_int    lib "winsqlite3.dll" ( _
     byVal     stmt           as longPtr , _
     byVal     iCol           as integer   _
) as integer ' }

' sqlite3_column_text ' {
'
' The string returned from SQLite needs to be converted to
' a wide character string in VBA. The following declaration (whose
' name ends in an underscore) first gets the pointer to the (ASCII or UTF8) string.
' Further below, another function is declared, sqlite3_column_text, that
' takes the pointer and converts it into a wide character string, suitable
' for a VBA string.
'

declare ptrSafe function sqlite3_column_text_  lib "winsqlite3.dll" alias "sqlite3_column_text" ( _
     byVal     stmt           as longPtr , _
     byVal     iCol           as integer   _
) as longPtr ' }

' }

' sqlite3_column_type ' {
'
' Returns one of the five data types (SQLITE_INTEGER, …) of
' a selected column.
'
declare ptrSafe function sqlite3_column_type   lib "winsqlite3.dll" ( _
     byVal     stmt           as longPtr , _
     byVal     iCol           as integer   _
) as integer ' }

' MultiByteToWideChar {
private declare ptrSafe function MultiByteToWideChar lib "kernel32" ( _
   byVal CodePage       as long   , _
   byVal dwFlags        as long   , _
   byVal lpMultiByteStr as longPtr, _
   byVal cbMultiByte    as long   , _
   byVal lpWideCharStr  as longPtr, _
   byVal cchWideChar    as long     _
) as long ' }

' WideCharToMultiByte {
private declare ptrSafe function WideCharToMultiByte lib "kernel32" ( _
  byVal CodePage          as long   , _
  byVal dwFlags           as long   , _
  byVal lpWideCharStr     as longPtr, _
  byVal cchWideChar       as long   , _
  byVal lpMultiByteStr    as longPtr, _
  byVal cchMultiByte      as long   , _
  byVal lpDefaultChar     as longPtr, _
  byVal lpUsedDefaultChar as longPtr  _
) as long ' }

function utf8ptrToString(byVal pUtf8String as longPtr) as string ' {
'
' Found @ https://github.com/govert/SQLiteForExcel/blob/master/Source/SQLite3VBAModules/Sqlite3_64.bas
'
    dim buf     as string
    dim cSize   as long
    dim retVal  as long

  ' cSize includes the terminating null character
    cSize = MultiByteToWideChar(CP_UTF8, 0, pUtf8String, -1, 0, 0)

    if cSize <= 1 then ' {
        Utf8ptrToString = ""
        exit function
    end if ' }

    Utf8ptrToString = string(cSize - 1, "*") ' and a termintating null char.

    retVal = MultiByteToWideChar(CP_UTF8, 0, pUtf8String, -1, strPtr(Utf8ptrToString), cSize)
    if retVal = 0 then ' {
       err.raise 1000, "utf8ptrToString", "Utf8ptrToString error: " & err.lastDllError
       exit function
    end if ' }

end function ' }

function stringToUtf8bytes(byVal txt as string) as byte() ' {

    dim bSize  as long
    dim retVal as long
    dim buf()  as byte

    bSize = WideCharToMultiByte(CP_UTF8, 0, strPtr(txt), -1, 0, 0, 0, 0)

    if bSize = 0 then ' {
        exit function
    end if ' }

    ReDim buf(bSize)

    retVal = WideCharToMultiByte(CP_UTF8, 0, strPtr(txt), -1, varPtr(buf(0)), bSize, 0, 0)

    if retVal = 0 then
        err.raise 1000, "stringToUtf8bytes", "stringToUtf8bytes error: " & err.lastDllError
        exit function
    end if

    stringToUtf8bytes = buf

end function ' }

' { sqlite3_bind_text

function sqlite3_bind_text  ( _
     byVal    stmt            as longPtr , _
     byVal    pos             as long    , _
     byVal    val             as string  , _
     byVal    len_            as integer , _
     byVal    whatIsThis      as longPtr   _
) as long ' }

  dim arrayVariant as variant
  arrayVariant = stringToUtf8bytes(val)

' dim x() as byte
' x = stringToUtf8bytes(val)

  sqlite3_bind_text = sqlite3_bind_text_(stmt, pos, varPtr(arrayVariant          ), len_, whatIsThis)
' sqlite3_bind_text = sqlite3_bind_text_(stmt, pos, varPtr(stringToUtf8bytes(val)), len_, whatIsThis)
' sqlite3_bind_text = sqlite3_bind_text_(stmt, pos,        stringToUtf8bytes(val ), len_, whatIsThis)

end function ' }

' { sqlite3_column_text
function sqlite3_column_text (             _
     byVal     stmt           as longPtr , _
     byVal     iCol           as integer   _
) as string

    sqlite3_column_text = utf8ptrToString(sqlite3_column_text_(stmt, iCol))

end function ' }
Github repository winsqlite3.dll-4-VBA, path: /winsqlite3_4_VBA.bas

Test

option explicit


sub main() ' {

    dim db as longPtr

    db = openDB(environ("temp") & "\test.db")

    execSQL db, "create table tab(foo, bar, baz)"

    execSQL db, "insert into tab values(1, 'one', null);"
    execSQL db, "insert into tab values(2,  2.2 ,'two');"

    dim stmt as longPtr
    stmt = prepareStmt(db, "insert into tab values(?, ?, ?)")

    checkBindRetval(sqlite3_bind_int (stmt, 1, 3              ))
    checkBindRetval(sqlite3_bind_text(stmt, 2,"three", -1, 0  ))
    checkBindRetval(sqlite3_bind_int (stmt, 3, 333            ))
    checkStepRetval(sqlite3_step     (stmt))

  ' sqlite3_reset(stmt) still seems necesssary although the documentation says
  ' that in version after 3.6.something, it should not be necessary anymore...
  '
  ' TODO: or should sqlite3_clear_bindings be used?
  '
    sqlite3_reset(stmt) ' Or sqlite3_clear_bindings() ?

    checkBindRetval(sqlite3_bind_int (stmt, 1, 55             ))
    checkBindRetval(sqlite3_bind_text(stmt, 2,"four" , -1, 0  ))
    checkBindRetval(sqlite3_bind_null(stmt, 3                 ))
    checkStepRetval(sqlite3_step     (stmt))
    sqlite3_reset(stmt) ' Or sqlite3_clear_bindings() ?

    checkBindRetval(sqlite3_bind_int (stmt, 1, 42                 ))
    checkBindRetval(sqlite3_bind_text(stmt, 2,"Umlauts"   , -1, 0 ))
    checkBindRetval(sqlite3_bind_text(stmt, 3,"äöü ÄÖÜ éÉ", -1, 0 ))
    checkStepRetval(sqlite3_step     (stmt))
'   sqlite3_reset(stmt) ' Or sqlite3_clear_bindings() ?

    sqlite3_finalize  stmt

    selectFromTab(db)

    closeDB(db)

end sub ' }

function openDB(fileName as string) as longPtr ' {

    dim res as longPtr

    res = sqlite3_open(fileName, openDB)
    if res <> SQLITE_OK then
       err.raise 1000, "openDB", "sqlite_open failed, res = " & res
    end if

    debug.print("SQLite db opened, db = " & openDB)

end function ' }

sub closeDB(db as longPtr) ' {

    dim res as longPtr

    res = sqlite3_close(db)
    if res <> SQLITE_OK then
       err.raise 1000, "closeDB", "sqlite_open failed, res = " & res
    end if

end sub ' }

sub checkBindRetval(retVal as long) ' {

    if retVal = SQLITE_OK then
       exit sub
    end if

    if retVal = SQLITE_TOOBIG then
       err.raise 1000, "checkBindRetval", "bind failed: String or BLOB exceeds size limit"
    end if

    if retVal = SQLITE_MISUSE then
       err.raise 1000, "checkBindRetval", "bind failed: Library used incorrectly"
    end if

    err.raise 1000, "checkBindRetval", "bind failed, retVal = " & retVal

end sub ' }

sub checkStepRetval(retVal as long) ' {

    if retVal = SQLITE_DONE then
       exit sub
    end if

    err.raise 1000, "checkStepRetval", "step failed, retVal = " & retVal

end sub ' }

sub execSQL(db as longPtr, sql as string) ' {

    dim res    as longPtr
    dim errmsg as string

    res = sqlite3_exec(db, sql, 0, 0, errmsg)
    if res <> SQLITE_OK then
       err.raise 1000, "execSQL", "sqlite3_exec failed, res = " & res
    end if

end sub ' }

function prepareStmt(db as longPtr, sql as string) as longPtr ' {

    dim res    as longPtr

    res = sqlite3_prepare_v2(db, sql, -1, prepareStmt, 0)
    if res <> SQLITE_OK then
       err.raise 1000, "prepareStmt", "sqlite3_prepare failed, res = " & res
    end if

    debug.print("stmt = " & prepareStmt)

end function ' }

sub selectFromTab(db as longPtr) ' {

    dim stmt as longPtr
    stmt = prepareStmt(db, "select * from tab where foo > ? order by foo")

    sqlite3_bind_int stmt, 1, 2

    dim rowNo as long

    while sqlite3_step(stmt) <> SQLITE_DONE ' {

      rowNo = rowNo + 1

      dim colNo as long
      colNo = 0
      while colNo <= 2 ' {

         if     sqlite3_column_type(stmt, colNo) = SQLITE_INTEGER then

                cells(rowNo, colNo + 1) = sqlite3_column_int(stmt, colNo)

         elseIf sqlite3_column_type(stmt, colNo) = SQLITE_FLOAT   then

                cells(rowNo, colNo + 1) = sqlite3_column_double(stmt, colNo)

         elseIf sqlite3_column_type(stmt, colNo) = SQLITE_TEXT    then

                cells(rowNo, colNo + 1) = sqlite3_column_text(stmt, colNo)

         elseIf sqlite3_column_type(stmt, colNo) = SQLITE_NULL    then

                cells(rowNo, colNo + 1) ="n/a"

         else

                cells(rowNo, colNo + 1) ="?"

         end if

         colNo = colNo + 1

      wend ' }

    wend ' }

    sqlite3_finalize stmt

end sub ' }
Github repository winsqlite3.dll-4-VBA, path: /test.bas

TODO

The sqlite_bind_* functions should accept a variant and then determine the variant's underlying data type and based upon this type call sqlite_bind_DATATYPE_() in winsqlite3.dll.

See also

The C interface to sqlite
The headerfile winsqlite3.h shows the parameters and data types used for accessing C:\Windows\System32\winsqlite3.dll.

Links

SQLiteForExcel (on github) is a small, easy-to-use, open source SQL library to give access to sqlite from VBA.

Index