'WARNING!: Before you can use this Include file...
' You MUST adjust ALL 23 of the "LIB" portion in the sqlite dll declarations
'  to where YOUR sqlite dll is located.
' There is no way to know ahead of time where someone's sqlite dll is located.
'  (this include file is hard-coded is where mine is located)

' revised version of the "sqlite.inc" file (and renamed to avoid confusion)
' something not mentioned in the original version...
' make sure the sqlite.dll is "newer" than v3.6.23.1
' the version used at the time of this update was v3.8.7
' you can download the newest dll from sqlite.org (under the Download menu option)
'
' 1) added some new functions that sqlite.org says should be used (instead of the old ones).
'    (the old ones have been removed)
'
' 2) fixed some errors in the way parameters were passed to sqlite functions (changing ByVal or ByRef mostly).
'    like when the prepare function is called... you'll now be informed if there is a syntax error in the query/statement you assigned to the dbstmt variable before calling sql3comm.
'    (note: the "semi" descriptive error message will be passed back to your main program via the sqlcomm dbErrMsg parameter)
'    the one that will baffle you is... the "NEAR" ??? error message (it tells you "where" the error is, but not "what" the error is).
'    you'll have to figure it out on your own (perhaps a typo?) or check online to see if someone else had the same query/statement problem.
'
' 3) reduced the sqlcomm input parameters to just 3.
'    but there are 3 global variables that you must make sure are set before calling sqlcomm.
'    the main reason for doing this is because... odds are you're only going to change the 3 input parameter
'    every time you call sqlcomm, the other 3 parameters won't change UNTIL you open a new database file.
'    the 3 global variables are... dbflags, dbfile, dbsepar (which can be changed, if/when need be).
'     dbflags is new (and required) because of the new open sqlite function used.
'     dbfile is the filename of a sqlite database file.
'     dbsepar is the separator that you want to separate items with.
'    the 3 input parameter are... dbstmt, dbErrMsg, dbStrLst
'     (you could, if you want, make these global in your main program)
'     (but be aware... if you do... make 100% sure you empty all 3 of them as soon as you're done retrieveing the information from them)
'     dbstmt is the sql query or statement you want to execute (passed to sqlcomm ByVal)
'     dbErrMsg is an empty string variable (passed to sqlcomm ByRef)
'     dbStrLst is an empty QStringList (passed to sqlcomm ByRef)
'
' 4) changed the object type that the results are returned in.
'    call sql3comm with a QStringList now (instead of a QStringGird).
'    the reason for changing this is because you might not want a grid automatically populated with the results.
'    in your main program... parse the returned list and select what "you want" displayed in a grid (if anything).
'    (you could also use the information, from the returned list, to make decisions, create graphs, etc.)
'    the first item in the list will always contain column names (separated by the "Global" dbsepar variable below).
'    any items below the first item in the list... will be the records (each item "within a record" is also separated by the same dbsepar variable).
'    what you'll get is something that looks like...
'    (this is ONLY an example and, in this example, commas will be used as a separator)
'    (pretend if you will... this is the information stored in a table called CUSTOMERS)
'    list.item(0) : NAME,COUNTRY,ADDRESS,CITY,STATE,ZIP_CODE,PHONE,EMAIL <-- Column Names
'    list.item(1) : John Doe,USA,123 Main St.,New York,New York,12345,18005551234,doej@yahoo.com <--- Record #1
'    list.item(2) : Mary Doe,USA,123 Main St.,New York,New York,12345,18005551234,marydoe@gmail.com <-- Record #2
'    etc.,etc.
'    (side note: you might only get column names but no records, that's because there are no records in that table)
'    (also note: records are not in any kind of order. the oldest is on top, the newest on the bottom)
'
'    if you did want to display the list, from the example above, in a grid...
'    grid.fixedcols = 0
'    grid.fixedrows = 1
'    defint y = tally(list.item(0), dbsepar) + 1
'    grid.colcount = y
'    defint x = list.itemcount
'    grid.rowcount = x
'    for x = 0 to grid.rowcount - 1
'      for y = 0 to grid.colcount - 1
'        grid.cell(y,x) = field$(list.item(x), dbsepar, y + 1)
'      next y
'    next x
'
'
' some details about sqlite databases (if you're a newbie)...
' sqlite database files are used for storing large amounts of information in a compact form.
' (something akin to a MS Access database file, but different in that... Access costs $$$, and SQLite is free)
'
' typically... the information you want to work with, from a sqlite database file, will be stored within a "table".
' (note: a sqlite database file might contain just one table OR numerous tables)
' each table contains... column names followed by records (if any).
' you retrieve the column names and records from a table using the "SELECT" keyword.
' (note: column names are always text, whereas records might contain a mixture of text, numbers, and other datatypes)
' i mention this because... you'll need to know the datatype, of each column, if you use the "UPDATE" or "INSERT" keywords.
' (the datatypes you provide should be the same datatypes as the one you're updating or inserting)
'
' since we're on the subject of datatypes...
' a particular datatype applies to a particular column (and any records items that are within that column).
' let's use the example from above...
' columns... NAME,COUNTRY,ADDRESS,CITY,STATE,EMAIL are probably text datatypes
' columns... ZIP_CODE,PHONE are probably integer datatypes
' i say "probably" because (and this comment is taken right out of the help files)...
'  {SQLite uses dynamic run-time typing. So just because a column is declared to contain
'   a particular type does not mean that the data stored in that column is of the declared type.
'   SQLite is strongly typed, but the typing is dynamic not static. Type is associated with
'   individual values, not with the containers used to hold those values.}
'
' now back to details about sqlite database files...
' if you already know the name of the table you want to work with... getting the information is fairly easy.
' using the example above again... your query would look something like... "SELECT * FROM CUSTOMERS;"
' (where * means ALL columns and records, from a table called CUSTOMERS)
' (a special side note: ALL queries/statements must be terminated with a semicolon ";", otherwise you'll get an error message)
'
' if you wanted just the NAMEs from the table CUSTOMERS...
' your query would look something like... "SELECT NAME FROM CUSTOMERS;"
'
' if you wanted just the ZIP_CODEs from the table CUSTOMERS...
' your query would look something like... "SELECT ZIP_CODE FROM CUSTOMERS;"
'
' but let's say... you DON'T know the name of the table you want to work with...
' (one reason for not knowing might be because... it's a database file you did not created)
' then you'll have to get a list of them first (before you can proceed to get the columns and records from a table)
'
' lucky for us... all sqlite databases keep the names of the tables in something called "sqlite_master"
' (sqlite_master holds the table names along with some other data)
'
' to get a list of table names use... "SELECT * FROM sqlite_master WHERE type='table';"
' using this query will give you data from the sqlite_master, but will limit it to only tables.
' (which includes any table names plus some other information related to those tables)
' you'll want to start your processing by first extracting the table name(s) from the returned list.
' list.item(0) will contain, like always, column names (in this case... type, name, tbl_name, rootpage, sql)
' the table name(s), you'll want to extract, will be under the 'name' column in the returned list (tbl_name, I think, might be just an alias for name).
' use {for x = 1 to list.itemcount-1 : ??? = Field$(list.item(x), dbsepar, 2) : next x} to extract the table name(s)
' (??? can be a variable or object, whatever you're needs might be)
'
' once you've obtained records from a table, you can...
'  update a record (change individual items within a single record)
'  delete a record (delete individual records, the whole record not just part of a record)
'    note on delete: the remaining records are not automatically resequenced
'  insert a record (insert a new record, a whole record not just part of a record)
'    note on insert: the new record will be inserted at the bottom of the list
' there's so much more you can do, but those are the three most common actions you'll perform on a sql database.
'
' to learn more about sqlite... visit sqlite.org AND numerous other websites.
' learning sqlite is like learning a whole new programming language (and... the langauge is huge!)

' Result Codes -------------------------------------------------------------------------------------
Const SQLITE_OK         =  0   '/* Successful result */
Const SQLITE_ERROR      =  1   '/* SQL error or missing database */
Const SQLITE_INTERNAL   =  2   '/* An internal logic error in SQLite */
Const SQLITE_PERM       =  3   '/* Access permission denied */
Const SQLITE_ABORT      =  4   '/* Callback routine requested an abort */
Const SQLITE_BUSY       =  5   '/* The database file is locked */
Const SQLITE_LOCKED     =  6   '/* A table in the database is locked */
Const SQLITE_NOMEM      =  7   '/* A malloc() failed */
Const SQLITE_READONLY   =  8   '/* Attempt to write a readonly database */
Const SQLITE_INTERRUPT  =  9   '/* Operation terminated by sqlite_interrupt() */
Const SQLITE_IOERR      = 10   '/* Some kind of disk I/O error occurred */
Const SQLITE_CORRUPT    = 11   '/* The database disk image is malformed */
Const SQLITE_NOTFOUND   = 12   '/* (Internal Only) Table or record not found */
Const SQLITE_FULL       = 13   '/* Insertion failed because database is full */
Const SQLITE_CANTOPEN   = 14   '/* Unable to open the database file */
Const SQLITE_PROTOCOL   = 15   '/* Database lock protocol error */
Const SQLITE_EMPTY      = 16   '/* (Internal Only) Database table is empty */
Const SQLITE_SCHEMA     = 17   '/* The database schema changed */
Const SQLITE_TOOBIG     = 18   '/* Too much data for one row of a table */
Const SQLITE_CONSTRAINT = 19   '/* Abort due to constraint violation */
Const SQLITE_MISMATCH   = 20   '/* Data type mismatch */
Const SQLITE_MISUSE     = 21   '/* Library used incorrectly */
Const SQLITE_NOLFS      = 22   '/* Uses OS features not supported on host */
Const SQLITE_AUTH       = 23   '/* Authorization denied */
Const SQLITE_FORMAT     = 24   '/* Auxiliary database format error */
Const SQLITE_RANGE      = 25   '/* 2nd parameter to sqlite3_bind out of range */
Const SQLITE_NOTADB     = 26   '/* File opened that is not a database file */
Const SQLITE_NOTICE     = 27   '/* Notifications from sqlite3_log() */
Const SQLITE_WARNING    = 28   '/* Warnings from sqlite3_log() */
Const SQLITE_ROW        = 100  '/* sqlite3_step() has another row ready */
Const SQLITE_DONE       = 101  '/* sqlite3_step() has finished executing */

' Extended Result Codes ----------------------------------------------------------------------------
Const SQLITE_IOERR_READ              = (SQLITE_IOERR      Or (1 SHL 8))
Const SQLITE_IOERR_SHORT_READ        = (SQLITE_IOERR      Or (2 SHL 8))
Const SQLITE_IOERR_WRITE             = (SQLITE_IOERR      Or (3 SHL 8))
Const SQLITE_IOERR_FSYNC             = (SQLITE_IOERR      Or (4 SHL 8))
Const SQLITE_IOERR_DIR_FSYNC         = (SQLITE_IOERR      Or (5 SHL 8))
Const SQLITE_IOERR_TRUNCATE          = (SQLITE_IOERR      Or (6 SHL 8))
Const SQLITE_IOERR_FSTAT             = (SQLITE_IOERR      Or (7 SHL 8))
Const SQLITE_IOERR_UNLOCK            = (SQLITE_IOERR      Or (8 SHL 8))
Const SQLITE_IOERR_RDLOCK            = (SQLITE_IOERR      Or (9 SHL 8))
Const SQLITE_IOERR_DELETE            = (SQLITE_IOERR      Or (10 SHL 8))
Const SQLITE_IOERR_BLOCKED           = (SQLITE_IOERR      Or (11 SHL 8))
Const SQLITE_IOERR_NOMEM             = (SQLITE_IOERR      Or (12 SHL 8))
Const SQLITE_IOERR_ACCESS            = (SQLITE_IOERR      Or (13 SHL 8))
Const SQLITE_IOERR_CHECKRESERVEDLOCK = (SQLITE_IOERR      Or (14 SHL 8))
Const SQLITE_IOERR_LOCK              = (SQLITE_IOERR      Or (15 SHL 8))
Const SQLITE_IOERR_CLOSE             = (SQLITE_IOERR      Or (16 SHL 8))
Const SQLITE_IOERR_DIR_CLOSE         = (SQLITE_IOERR      Or (17 SHL 8))
Const SQLITE_IOERR_SHMOPEN           = (SQLITE_IOERR      Or (18 SHL 8))
Const SQLITE_IOERR_SHMSIZE           = (SQLITE_IOERR      Or (19 SHL 8))
Const SQLITE_IOERR_SHMLOCK           = (SQLITE_IOERR      Or (20 SHL 8))
Const SQLITE_IOERR_SHMMAP            = (SQLITE_IOERR      Or (21 SHL 8))
Const SQLITE_IOERR_SEEK              = (SQLITE_IOERR      Or (22 SHL 8))
Const SQLITE_IOERR_DELETE_NOENT      = (SQLITE_IOERR      Or (23 SHL 8))
Const SQLITE_IOERR_MMAP              = (SQLITE_IOERR      Or (24 SHL 8))
Const SQLITE_IOERR_GETTEMPPATH       = (SQLITE_IOERR      Or (25 SHL 8))
Const SQLITE_IOERR_CONVPATH          = (SQLITE_IOERR      Or (26 SHL 8))
Const SQLITE_LOCKED_SHAREDCACHE      = (SQLITE_LOCKED     Or (1 SHL 8))
Const SQLITE_BUSY_RECOVERY           = (SQLITE_BUSY       Or (1 SHL 8))
Const SQLITE_BUSY_SNAPSHOT           = (SQLITE_BUSY       Or (2 SHL 8))
Const SQLITE_CANTOPEN_NOTEMPDIR      = (SQLITE_CANTOPEN   Or (1 SHL 8))
Const SQLITE_CANTOPEN_ISDIR          = (SQLITE_CANTOPEN   Or (2 SHL 8))
Const SQLITE_CANTOPEN_FULLPATH       = (SQLITE_CANTOPEN   Or (3 SHL 8))
Const SQLITE_CANTOPEN_CONVPATH       = (SQLITE_CANTOPEN   Or (4 SHL 8))
Const SQLITE_CORRUPT_VTAB            = (SQLITE_CORRUPT    Or (1 SHL 8))
Const SQLITE_READONLY_RECOVERY       = (SQLITE_READONLY   Or (1 SHL 8))
Const SQLITE_READONLY_CANTLOCK       = (SQLITE_READONLY   Or (2 SHL 8))
Const SQLITE_READONLY_ROLLBACK       = (SQLITE_READONLY   Or (3 SHL 8))
Const SQLITE_READONLY_DBMOVED        = (SQLITE_READONLY   Or (4 SHL 8))
Const SQLITE_ABORT_ROLLBACK          = (SQLITE_ABORT      Or (2 SHL 8))
Const SQLITE_CONSTRAINT_CHECK        = (SQLITE_CONSTRAINT Or (1 SHL 8))
Const SQLITE_CONSTRAINT_COMMITHOOK   = (SQLITE_CONSTRAINT Or (2 SHL 8))
Const SQLITE_CONSTRAINT_FOREIGNKEY   = (SQLITE_CONSTRAINT Or (3 SHL 8))
Const SQLITE_CONSTRAINT_FUNCTION     = (SQLITE_CONSTRAINT Or (4 SHL 8))
Const SQLITE_CONSTRAINT_NOTNULL      = (SQLITE_CONSTRAINT Or (5 SHL 8))
Const SQLITE_CONSTRAINT_PRIMARYKEY   = (SQLITE_CONSTRAINT Or (6 SHL 8))
Const SQLITE_CONSTRAINT_TRIGGER      = (SQLITE_CONSTRAINT Or (7 SHL 8))
Const SQLITE_CONSTRAINT_UNIQUE       = (SQLITE_CONSTRAINT Or (8 SHL 8))
Const SQLITE_CONSTRAINT_VTAB         = (SQLITE_CONSTRAINT Or (9 SHL 8))
Const SQLITE_CONSTRAINT_ROWID        = (SQLITE_CONSTRAINT Or (10 SHL 8))
Const SQLITE_NOTICE_RECOVER_WAL      = (SQLITE_NOTICE     Or (1 SHL 8))
Const SQLITE_NOTICE_RECOVER_ROLLBACK = (SQLITE_NOTICE     Or (2 SHL 8))
Const SQLITE_WARNING_AUTOINDEX       = (SQLITE_WARNING    Or (1 SHL 8))
Const SQLITE_AUTH_USER               = (SQLITE_AUTH       Or (1 SHL 8))

' Flags For File Open Operations -------------------------------------------------------------------
Const SQLITE_OPEN_READONLY       = 1     '/* Ok for sqlite3_open_v2() */
Const SQLITE_OPEN_READWRITE      = 2     '/* Ok for sqlite3_open_v2() */
Const SQLITE_OPEN_CREATE         = 4     '/* Ok for sqlite3_open_v2() */
Const SQLITE_OPEN_DELETEONCLOSE  = 8     '/* VFS only */
Const SQLITE_OPEN_EXCLUSIVE      = 10    '/* VFS only */
Const SQLITE_OPEN_AUTOPROXY      = 20    '/* VFS only */
Const SQLITE_OPEN_URI            = 40    '/* Ok for sqlite3_open_v2() */
Const SQLITE_OPEN_MEMORY         = 80    '/* Ok for sqlite3_open_v2() */
Const SQLITE_OPEN_MAIN_DB        = 100   '/* VFS only */
Const SQLITE_OPEN_TEMP_DB        = 200   '/* VFS only */
Const SQLITE_OPEN_TRANSIENT_DB   = 400   '/* VFS only */
Const SQLITE_OPEN_MAIN_JOURNAL   = 800   '/* VFS only */
Const SQLITE_OPEN_TEMP_JOURNAL   = 1000  '/* VFS only */
Const SQLITE_OPEN_SUBJOURNAL     = 2000  '/* VFS only */
Const SQLITE_OPEN_MASTER_JOURNAL = 4000  '/* VFS only */
Const SQLITE_OPEN_NOMUTEX        = 8000  '/* Ok for sqlite3_open_v2() */
Const SQLITE_OPEN_FULLMUTEX      = 10000 '/* Ok for sqlite3_open_v2() */
Const SQLITE_OPEN_SHAREDCACHE    = 20000 '/* Ok for sqlite3_open_v2() */
Const SQLITE_OPEN_PRIVATECACHE   = 40000 '/* Ok for sqlite3_open_v2() */
Const SQLITE_OPEN_WAL            = 80000 '/* VFS only */

' sqlite.dll functions (currently) used in this include file ---------------------------------------
Declare Function sqlite3_libversion_number _
        Lib "d:\sqlite3\sqlite3.dll" Alias "sqlite3_libversion_number" _
        () As Long

Declare Function sqlite3_open_v2 _
        Lib "d:\sqlite3\sqlite3.dll" Alias "sqlite3_open_v2" _
        (ByVal zFilename As String, ByRef ppDb As Long, _
         ByVal Flags As Long, ByRef zVfs As String) As Long

Declare Function sqlite3_extended_result_codes _
        Lib "d:\sqlite3\sqlite3.dll" Alias "sqlite3_extended_result_codes" _
        (ByRef ppDb As Long, ByVal OnOff As Long) As Long

Declare Function sqlite3_complete _
        Lib "d:\sqlite3\sqlite3.dll" Alias "sqlite3_complete" _
        (ByVal zSQL As String) As Long

Declare Function sqlite3_prepare_v2 _
        Lib "d:\sqlite3\sqlite3.dll" Alias "sqlite3_prepare_v2" _
        (ByVal ppDb As Long, ByVal zSQL As String, ByVal nBytes As Long, _
         ByRef ppStmt As Long, ByVal pzTail As Long) As Long

Declare Function sqlite3_column_count _
        Lib "d:\sqlite3\sqlite3.dll" Alias "sqlite3_column_count" _
        (ByVal ppStmt As Long) As Long

Declare Function sqlite3_column_database_name _
        Lib "d:\sqlite3\sqlite3.dll" Alias "sqlite3_column_database_name" _
        (ByVal ppStmt As Long, ByVal nColumn As Long) As String

Declare Function sqlite3_step _
        Lib "d:\sqlite3\sqlite3.dll" Alias "sqlite3_step" _
        (ByVal ppStmt As Long) As Long

Declare Function sqlite3_column_name _
        Lib "d:\sqlite3\sqlite3.dll" Alias "sqlite3_column_name" _
        (ByVal ppStmt As Long, ByVal nColumn As Long) As Long

Declare Function sqlite3_column_text _
        Lib "d:\sqlite3\sqlite3.dll" Alias "sqlite3_column_text" _
        (ByVal ppStmt As Long, ByVal nColumn As Long) As Long

Declare Function sqlite3_finalize _
        Lib "d:\sqlite3\sqlite3.dll" Alias "sqlite3_finalize" _
        (ByVal ppStmt As Long) As Long

Declare Function sqlite3_close_v2 _
        Lib "d:\sqlite3\sqlite3.dll" Alias "sqlite3_close_v2" _
       (ByVal ppDb As Long) As Long

Declare Function sqlite3_errmsg _
        Lib "d:\sqlite3\sqlite3.dll" Alias "sqlite3_errmsg" _
        (ByVal ppDb As Long) As Long

' sqlite.dll functions (currently) NOT used and "untested" (parameters might be right, might be wrong) ---------------------
declare function sqlite3_last_insert_rowid lib "d:\sqlite3\sqlite3.dll" alias "sqlite3_last_insert_rowid" ( byval ppDb as long ) as long
declare function sqlite3_changes lib "d:\sqlite3\sqlite3.dll" alias "sqlite3_changes" ( byval ppDb as long ) as long
declare sub sqlite3_interrupt lib "d:\sqlite3\sqlite3.dll" alias "sqlite3_interrupt" ( byval ppDb as long )
declare function sqlite3_create_function lib "d:\sqlite3\sqlite3.dll" alias "sqlite3_create_function" ( byval ppDb as long, zName as string, byval nArg as long, byval eTextRep as long, byval pAny as long, byval pFunction as long, byval pStep as long, byval pFinal as long ) as long
declare function sqlite3_create_collation lib "d:\sqlite3\sqlite3.dll" alias "sqlite3_create_collation" ( byval ppDb as long, zName as string, byval pref16 as long, byval pAny as long, byval pCompare as long) as long

' sqlite.dll functions (currently) NOT used and "tested" (paramters are correct) -------------------------------------------
Declare Function sqlite3_errcode Lib "d:\sqlite3\sqlite3.dll" Alias "sqlite3_errcode" (ByVal ppDb As Long) As Long
Declare Function sqlite3_get_table Lib "d:\sqlite3\sqlite3.dll" Alias "sqlite3_get_table" (ByVal ppDb As Long, ByVal zSQL As String, ByVal lpTable As Long, ByVal nRow As Long, ByVal nColumn As Long, ByVal lpErrMsg As Long) As Long
Declare Function sqlite3_free_table Lib "d:\sqlite3\sqlite3.dll" Alias "sqlite3_free_table" (ByVal lpTable As Long) As Long
Declare Function sqlite3_libversion Lib "d:\sqlite3\sqlite3.dll" Alias "sqlite3_libversion" () As Long
Declare Function sqlite3_table_column_metadata Lib "d:\sqlite3\sqlite3.dll" Alias "sqlite3_table_column_metadata" (ByVal ppDb As Long, ByVal zDbName As String, ByVal zTableName As String, ByVal zColumnName As String, ByVal pzDataType As Long, ByVal pzCollSeq As Long, ByVal pNotNull As Long, ByVal pPrimaryKey As Long, ByVal pAutoinc As Long) As Long

' Global Variables (to help reduce the clutter of having to define them in your main program) ------
'  these variables MUST be assigned a value BEFORE calling sqlcomm
DefLng dbflags = SQLITE_OPEN_READWRITE 'file flags is assigned a default, but you can change it in your main program (use one of the constants, found above, under... Flags For File Open Operations).
DefStr dbfile  = "" 'you must assign the name of a file containing a database (common file extensions: .db, .sql).
DefStr dbsepar = Chr$(9) 'separator is assigned a default, but you can change it in your main program (Chr$(9) aka TAB... seems to work best).

' Get Last Error Message from the database handle --------------------------------------------------
Function sql3errMsg (ByVal hSQLdb As Long) As String
    Dim pzErr As Long
        pzErr = sqlite3_errmsg(hSQLdb)
    Result = IIF(pzErr = 0, "", VarPtr$(pzErr))
End Function

' Revised version of sql3comm ----------------------------------------------------------------------
'   dbstmt: (IN) a valid sqlite query or statement (one that starts with a sql keyword such as SELECT,INSERT,UPDATE,DELETE {see above for a SELECT example}).
' dbErrMsg: (IN/OUT) the name of an string variable
'           (if an error occurs... the error message will be passed back to your program via this parameter)
' dbStrLst: (IN/OUT) a name of an qstringlist
'           (it will be populated with the results (if any) from your query and passed back to your program via this parameter)
'   result: depends on the query/statement you've passed in the dbstmt parameter.
'           if a Select query... an error code number (if something went wrong) OR sqlite_ok (if all went well)
'           if an Update, or Insert, or Delete statement... it will be the number of records that were changed
Function sql3comm(ByVal dbstmt As String, ByRef dbErrMsg As String, ByRef dbStrLst As QStringList) As Long
    Dim hdb    As Long
    Dim hstmt  As Long
    Dim rezult As Long
    Dim pnext  As Long
    Dim lret   As Long
    Dim colcnt As Long
    Dim i      As Long
    Dim vRet   As Double

    Dim sNull   As String
    Dim columns As String
    Dim records As String
    Dim sRet    As String

    'just to make sure the sting variable (that was passed as a parameter) is empty
    dbErrMsg = ""

    'before doing anything... check the dll version to see if it is adequate
    vRet = sqlite3_libversion_number()
    sRet = Format$("%.0f",vRet)
    If Len(sRet) < 10 Then sRet = sRet + String$((10-Len(sRet)), "0")
    vRet = Val(sRet)
    If vRet <= 3006023000 Then
      dbErrMsg = "abort: DLL version 3.6.23.1 (or higher) is needed"
      Goto EF
    End If

    'if the dll version is ok... check to see if the variables have values
    ' the values might be right or not right
    ' hopefully the function calls, below, will catch the ones that are not right
    ' for the moment, at least, they are not empty
    ' if any od them are empty... return an appropriate error message and exit the function (in a safe way)
    If (dbflags = 0) Then
      dbErrMsg = "abort: dbflags (File Operation Flags) not set"
      Goto EF
    End If
    If (dbstmt = "") Then
      dbErrMsg = "abort: dbstmt (sql query/statement) is empty"
      Goto EF
    End If
    If (dbfile = "") Then
      dbErrMsg = "abort: dbfile (database filename) is empty"
      Goto EF
    End If
    If (dbsepar = "") Then
      dbErrMsg = "abort: dbsepar (separator) is empty"
      Goto EF
    End If

    'biggest advantage of using this newer version of open...
    ' the database is NOT automatically created if the dbfile parameter has a typo in it
    ' the old version automatically created a "new" database file if there was a typo in the filename
    ' which caused people to wonder... why is the database empty, when it shouldn't be
    'the second biggest advantage... extended result codes are automatically returned
    'after this function is called... hdb will hold a unique handle to the database file
    '(something similar to the handle when you use the MS API function "_lOpen")
    'the fourth parameter (sNull) is intentially set to "" because no VFS module will be used
    rezult = sqlite3_open_v2(dbfile, hdb, dbflags, sNull)

    If rezult <> SQLITE_OK Then
      Result = rezult
      dbErrMsg = "open check error: " + sql3errMsg(hdb)
      Goto EF
    End If

    'extended result codes are set to OFF by default
    ' so set it to ON (0 = OFF, "any" number = ON)
    ' (which significantly helps with any function called below)
    rezult = sqlite3_extended_result_codes(hdb, -1)

    'check the query/statment to see if it's a complete query/statement
    ' the following is a comment taken right out of the help files...
    ' {a statement is judged to be complete if it ends with a semicolon token
    '  and is not a prefix of a well-formed CREATE TRIGGER statement}
    rezult = sqlite3_complete(dbstmt)

    'an oddity here
    ' the result from calling complete: 1 if ok and 0 if not ok
    ' (just the opposite of normal results)
    If rezult <> 1 Then
      Result = rezult
      dbErrMsg = "complete check error: missing semicolon at end of query/statement" 'odds are it's missing the semicolon
      Goto EF
    End If

    'the prepare function compiles your query/statement (dbstmt) into bytecode (something similar to rapidq's bytecode)
    ' while it's being compiled... syntax checking is done
    'the third parameter is the length of the dbstmt
    'after this function is called (and successfully completes)...
    ' hstmt will hold the handle to the compiled bytecode
    ' pnext will point to the end of the first query/statement that's terminated by a semicolon
    ' i'm not sure but i think pnext can be used if dbstmt contains multiple queries/statements
    ' since prepare only compiles the first query/statement that terminates with a semicolon...
    ' i'm guessing to compile multiple queries/statements...
    ' you'd have to remove each query/statement after it's been compiled in a for/next loop
    ' kind of like: "stmt1;stmt2;stmt3;" compile to hstmt(0) / "stmt2;stmt3;" compile to hstmt(1) / "stmt3;" compile to hstmt(2)
    ' the pnext just helps you to know where to trim the dbstmt
    ' mind you... this is ONLY a guess
    rezult = sqlite3_prepare_v2(hdb, dbstmt, Len(dbstmt), hstmt, VarPtr(pnext))

    If rezult <> SQLITE_OK Then
      Result = rezult
      dbErrMsg = "prepare check error: " + sql3errMsg(hdb)
      Goto EF
    End If

    If (uCase$(Field$(dbstmt, " ", 1)) <> "SELECT") And (uCase$(Field$(dbstmt, " ", 1)) <> "PRAGMA") Then

      'this section of the IF/THEN deals with statements (ones that start with something like UPDATE, or INSERT, or DELETE)

      'i'll just explain here to avoid repetion below
      ' step runs the compiled bytecode
      ' but there's a catch to it
      ' you have to keep calling step until you get either...
      ' an error message or SQLITE_DONE
      'if the sqlite.dll is version 3.6.23.1 or older...
      ' you'd have to use the sqlite_reset function every time an error occurred
      ' so to get around that problem... make sure the dll you have is newer than v3.6.23.1
      ' as i said above... you can download the newest dll at sqlite.org
      rezult = sqlite3_step(hstmt)

      If rezult = SQLITE_ERROR Or rezult = SQLITE_MISUSE Then
        Result = rezult
        dbErrMsg = "step check error: " + sql3errMsg(hdb)
        Goto EF
      End If

      While rezult <> SQLITE_DONE
        If rezult = SQLITE_ERROR Or rezult = SQLITE_MISUSE Then
          Result = rezult
          dbErrMsg = "step check error: " + sql3errMsg(hdb)
          Goto EF
        End If
        rezult = sqlite3_step(hstmt)
      Wend

      'you must destroy the bytecode, otherwise all kinds of problems will occur!
      ' finalize destroys it for you
      rezult = sqlite3_finalize(hstmt)

      If rezult = SQLITE_ERROR Then
        Result = rezult
        dbErrMsg = "finalize check error: " + sql3errMsg(hdb)
        Goto EF
      End If

      'since this section deals with statements
      ' return the number of records that were changed
      ' and return an error message that should read... "not an error"
      Result = sqlite3_changes(hdb)
      dbErrMsg = sql3errMsg(hdb)

    Else

      'this section of the IF/THEN deals with queries (ones that start with SELECT or PRAGMA)

      'this function retrieves the number of columns in the result set returned by the prepared statement
      ' colcnt will be the "total" number of columns (just like .ItemCount in RapidQ)
      colcnt = sqlite3_column_count(hstmt)

      'just to make sure the qstringlist (that was passed as a parameter) is empty
      dbStrLst.Clear

      'first populate the stringlist with column names
      columns = ""
      For i = 0 To (colcnt - 1)
        columns = columns + VarPtr$(sqlite3_column_name(hstmt, i)) + dbsepar
      Next i
      columns = Left$(columns, Len(columns)-1)
      dbStrLst.AddItems(columns)

      'now populate the stringlist with records
      rezult = sqlite3_step(hstmt)
      While rezult = SQLITE_ROW
        records = ""
        For i = 0 To (colcnt - 1)
          records = records + VarPtr$(sqlite3_column_text(hstmt, i)) + dbsepar
        Next i
        records = Left$(records, Len(records)-1)
        dbStrLst.AddItems(records)
        rezult = sqlite3_step(hstmt)
      Wend
      If rezult <> SQLITE_DONE Then
        Result = rezult
        dbErrMsg = "step check error: " + sql3errMsg(hdb)
        Goto EF
      End If

      'if we got to here... give the ok
      Result = SQLITE_OK

    End If

EF: If hstmt Then lret = sqlite3_finalize(hstmt)
    if hdb Then lRet = sqlite3_close_v2(hdb)

    hdb     = 0
    hstmt   = 0
    rezult  = 0
    pnext   = 0
    lret    = 0
    colcnt  = 0
    i       = 0
    vRet    = 0
    sNull   = ""
    columns = ""
    records = ""
End Function

'there's no way of determining if a record item is a numeric value or a datetime value
' if you're lucky... hopefully the column name will give you a clue if it's a datetime value
' an excerpt from sqlite.org...
' SQLite does not have a storage class set aside for storing dates and/or times.
' Instead, the built-in Date And Time Functions of SQLite are capable of storing
' dates and times as TEXT, REAL, or INTEGER values:
'
' TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
' REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
' INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC. 
'
' Applications can chose to store dates and times in any of these formats
' and freely convert between formats using the built-in date and time functions.