Class_SQLiteDB v2.0.6 - 2023-11-28

[viv]

@just me

Sorry, I didn't pay attention to the reply.After
It works fine in my case

Code: Select all

DB := SQLiteDB()
DB.OpenDB("")
DB.EnableLoadExtension()
DB.LoadExtension("D:\OneDrive\Program\AHK\lib\sqlite3\libsimple\simple.dll") ; https://github.com/wangfenjin/simple
DB.Exec("CREATE VIRTUAL TABLE t1 USING fts5(text, tokenize = 'simple');")
DB.Exec("INSERT INTO t1 VALUES ('你好');")
DB.GetTable("select simple_highlight(t1, 0, '[', ']') as text from t1 where text match simple_query('nh');", &arr)
MsgBox(arr.Rows[1][1])
DB.CloseDB()

[just me]

Hi @viv,

thanks for testing. I added the methods to the class script.

[mprost]

Hi.

Thank you for taking the time to port your excellent class to v2.

I've recently started using it and noticed that the error returned by OpenDB when the database doesn't exist and Create (the 3rd parameter) is false is ErrorMsg <out of memory>, ErrorCode <14>, instead of ErrorMsg <unable to open database file>, ErrorCode <14>.

I think this happens because, when the call to This._SetError(RC) is done in line 117, This._Handle is 0, so the subsequent call to This._ErrMsg(), and then to DllCall("SQLite3.dll\sqlite3_errmsg", "Ptr", This._Handle, "Cdecl UPtr"), doesn't find This._Handle correctly specified.

This can be easily tested with this code:

Code: Select all

#Requires AutoHotkey v2
#Include lib\Class_SQLiteDB.ahk

logSQLite:=SQLiteDB()
logSQLite.OpenDB("inexisting.db","W",false)
MsgBox(logSQLite.ErrorMsg . "`n" . logSQLite.ErrorCode)

I think these changes to OpenDB would fix it (I'm not suggesting this code, just trying to illustrate the issue). Instead of:

Code: Select all

RC := DllCall("SQlite3.dll\sqlite3_open_v2", "Ptr", UTF8, "UPtrP", &HDB, "Int", Flags, "Ptr", 0, "Cdecl Int")
If (RC) {
   This._Path := ""
   Return This._SetError(RC)
}
This._Handle := HDB
Return True

this other code:

Code: Select all

RC := DllCall("SQlite3.dll\sqlite3_open_v2", "Ptr", UTF8, "UPtrP", &HDB, "Int", Flags, "Ptr", 0, "Cdecl Int")
If (RC) {
   This._Path := ""
   currentHandle:=This._Handle
   This._Handle := HDB
   ReturnValue:=This._SetError(RC)
   This._Handle := currentHandle
   Return ReturnValue
}
This._Handle := HDB
Return True

Cheers,

mprost

[just me]

Hi @mprost, thanks for reporting. It should be fixed.

[20170201225639]

Thanks justme for porting this over!

The latest v2 alpha throws an error on this line, saying it cannot be unset

Code: Select all

Next(&Row?) => This.Step(&Row?)

I changed it to

Code: Select all

Next(&Row) => This.Step(&Row)

But I'm wondering if this is enough or if there's adjustment elsewhere that needs to be made.

[just me]

Probably related to v2.1-alpha.2. I don't know, whether it is intended (@lexikos ?).

[lexikos]

The error is correct. As per the operator precedence table, the ? operator has higher precedence than &. This means ? is being applied to the variable, which is being passed to &, not the parameter. & can't accept unset. This wasn't detected in v2.0 because the operator was implemented in a very simple way that actually doesn't obey operator precedence rules, or even evaluate as an operator (it just modifies the variable reference).

When you call This.Step(&Row) (and This.Step(&Row?) in v2.0), you are always supplying a value for the parameter. If Row was omitted, you are passing a reference to the local Row variable. If it was not omitted, &Row returns the original VarRef which was passed to Next.

The valid contexts for unset are detailed in the v2.1-alpha.2 documentation, under "An unset expression may consist of" and "Unset expressions are valid only within the following contexts".

If what you want is for this.Next(&Row) to result in this.Step(&Row) and this.Next() to result in this.Step(), what you need to do is remove & and use only ?. When you declare the parameter as ByRef, unset does not reliably indicate whether the parameter was omitted: Row could be an alias to an unset variable, or it could be just an unset variable. So even if &Row? was to evaluate as something like IsSet(Row) ? &Row : unset, it probably isn't what you want.

[just me]

@lexikos,
thanks, now I see the difference. The first is a function definition whereas the second is a function call.
From my tests

Code: Select all

Next(&Row?) => This.Step(&Row)

is doing what I want. Do you agree?

BTW: How can I check whether a VarRef has been passed for an optional ByRef parameter? I didn't find somthing reliable as yet.

[lexikos]

is doing what I want. Do you agree?

I neither agree nor disagree, as I don't know what you want. I explained what This.Step(&Row) does already. You only need ? in the function definition if you are going to call This.Next() (omitting the parameter).

BTW: How can I check whether a VarRef has been passed for an optional ByRef parameter?

You can't.

what you need to do is remove & and use only ?. When you declare the parameter as ByRef, unset does not reliably indicate whether the parameter was omitted: Row could be an alias to an unset variable, or it could be just an unset variable.

[just me]

I neither agree nor disagree, as I don't know what you want.

If you look at the script you see that Next() is defined as

Code: Select all

Next(&Row?) {
   ...
}

This should explain what I want. I want to call a function/method from another function passing an optional ByRef parameter as is.

You can't.

That's poor. Why didn't you take over IsByRef() (or a replacement) from v1.1?

[sashaatx]

This is the AHK v2.0 version of my Class_SQLiteDB.

Breaking changes:

• Removed the Query() method and the embedded _RecordSet class. Use the Prepare() method instead.
• Removed StoreBlob() method. You can achhieve the same result using:

  Code: Select all

  DB.Prepare(SQL, &ST)
  ST.Bind(BlobArr)
  ST.Step()
  ST.Free()

• Changed the Prepare() method and renamed the embedded _Statement class returned by Prepare()to _Prepared.
• Changed the embedded _Prepared class:
  • Added ColumnCount, ColumnNames, and CurrentStep properties.
  • Changed the Bind() method to accept an array of SQL parameters.
  • Changed the Step() method to accept an optional VarRef to return the current result row, if any.
  • Added a Next() method as an alas for Step().

Change history:

• 2.0.0 on 2023-01-05
  Initial release
• 2.0.1 on 2023-04-03
  Added EnableLoadExtension() and LoadExtension() methods.
• 2.0.2 on 2023-07-24
  Added _ErrStr() method.
  Fixed bug in _SetError() when the DB could not be opened.

Class Script:

Code: Select all

#Requires AutoHotkey v2.0.0
; ======================================================================================================================
; Function:         Class definitions as wrappers for SQLite3.dll to work with SQLite DBs.
; AHK version:      AHK 2.0.2 (U32/U64)
; Tested on:        Win 10 Pro (x64), SQLite 3.40.1 (x64)
; Version:          2.0.0/2023-01-05/just me    - Initial release for AHK 2.0
;                   2.0.1/2023-04-03/just me    - Added EnableLoadExtension() method
;                                               - Added LoadExtension() method
;                   2.0.2/2023-07-24/just me    - Added _ErrStr() method
;                                               - Fixed bug in _SetError() when the DB could not be opened
; Remarks:          Names of "private" properties / methods are prefixed with an underscore,
;                   they must not be set / called by the script!
;
;                   SQLite3.dll file is assumed to be in the script's folder, otherwise you have to
;                   provide an INI-File SQLiteDB.ini in the script's folder containing the path:
;                   [Main]
;                   DllPath=Path to SQLite3.dll
;
;                   Encoding of SQLite DBs is assumed to be UTF-8
;                   Minimum supported SQLite3.dll version is 3.6
;                   Download the current version of SQLite3.dll (and also SQlite3.exe) from www.sqlite.org
; ======================================================================================================================
; This software is provided 'as-is', without any express or implied warranty.
; In no event will the authors be held liable for any damages arising from the
; use of this software.
; ======================================================================================================================
; CLASS SQliteDB - SQLiteDB main class
; ======================================================================================================================
Class SQLiteDB {
   ; ===================================================================================================================
   ; CONSTRUCTOR __New
   ; ===================================================================================================================
   __New() {
      This._Path := ""                  ; Database path                                 (String)
      This._Handle := 0                 ; Database handle                               (Pointer)
      This._Stmts := Map()              ; Valid prepared statements                     (Map)
      If (SQLiteDB._RefCount = 0) {
         SQLiteDLL := SQLiteDB._SQLiteDLL
         If !FileExist(SQLiteDLL)
            If FileExist(A_ScriptDir . "\SQLiteDB.ini") {
               SQLiteDLL := IniRead(A_ScriptDir . "\SQLiteDB.ini", "Main", "DllPath", SQLiteDLL)
               SQLiteDB._SQLiteDLL := SQLiteDLL
         }
         If !(DLL := DllCall("LoadLibrary", "Str", SQLiteDB._SQLiteDLL, "UPtr")) {
            MsgBox("DLL " . SQLiteDLL . " does not exist!", "SQLiteDB Error", 16)
            ExitApp
         }
         LibVersion := StrGet(DllCall("SQlite3.dll\sqlite3_libversion", "Cdecl UPtr"), "UTF-8")
         If (VerCompare(LibVersion, SQLiteDB._MinVersion) < 0) {
            DllCall("FreeLibrary", "Ptr", DLL)
            MsgBox("Version " . LibVersion . " of SQLite3.dll is not supported!`n`n" .
                   "You can download the current version from www.sqlite.org!",
                   "SQLiteDB ERROR", 16)
            ExitApp
         }
         SQLiteDB.Version := LibVersion
      }
      SQLiteDB._RefCount += 1
   }
   ; ===================================================================================================================
   ; DESTRUCTOR __Delete
   ; ===================================================================================================================
   __Delete() {
      If (This._Handle)
         This.CloseDB()
      SQLiteDB._RefCount -= 1
      If (SQLiteDB._RefCount = 0) {
         If (DLL := DllCall("GetModuleHandle", "Str", SQLiteDB._SQLiteDLL, "UPtr"))
            DllCall("FreeLibrary", "Ptr", DLL)
      }
   }
   ; ===================================================================================================================
   ; Properties
   ; ===================================================================================================================
    ErrorMsg := ""              ; Error message                           (String) 
    ErrorCode := 0              ; SQLite error code / ErrorLevel          (Variant)
    Changes := 0                ; Changes made by last call of Exec()     (Integer)
    SQL := ""                   ; Last executed SQL statement             (String)
   ; ===================================================================================================================
   ; METHOD OpenDB         Open a database
   ; Parameters:           DBPath      - Path of the database file
   ;                       Access      - Wanted access: "R"ead / "W"rite
   ;                       Create      - Create new database in write mode, if it doesn't exist
   ; Return values:        On success  - True
   ;                       On failure  - False, ErrorMsg / ErrorCode contain additional information
   ; Remarks:              If DBPath is empty in write mode, a database called ":memory:" is created in memory
   ;                       and deletet on call of CloseDB.
   ; ===================================================================================================================
   OpenDB(DBPath, Access := "W", Create := True) {
      Static SQLITE_OPEN_READONLY  := 0x01 ; Database opened as read-only
      Static SQLITE_OPEN_READWRITE := 0x02 ; Database opened as read-write
      Static SQLITE_OPEN_CREATE    := 0x04 ; Database will be created if not exists
      Static MEMDB := ":memory:"
      This.ErrorMsg := ""
      This.ErrorCode := 0
      HDB := 0
      If (DBPath = "")
         DBPath := MEMDB
      If (DBPath = This._Path) && (This._Handle)
         Return True
      If (This._Handle)
         Return This._SetError(0, "you must first close DB`n" . This._Path)
      Flags := 0
      Access := SubStr(Access, 1, 1)
      If (Access != "W") && (Access != "R")
         Access := "R"
      Flags := SQLITE_OPEN_READONLY
      If (Access = "W") {
         Flags := SQLITE_OPEN_READWRITE
         If (Create)
            Flags |= SQLITE_OPEN_CREATE
      }
      This._Path := DBPath
      UTF8 := This._StrToUTF8(DBPath)
      HDB := 0
      RC := DllCall("SQlite3.dll\sqlite3_open_v2", "Ptr", UTF8, "UPtrP", &HDB, "Int", Flags, "Ptr", 0, "Cdecl Int")
      If (RC) {
         This._Path := ""
         Return This._SetError(RC, This._ErrStr(RC) . "`n" . DBPath)
      }
      This._Handle := HDB
      Return True
   }
   ; ===================================================================================================================
   ; METHOD CloseDB        Close database
   ; Parameters:           None
   ; Return values:        On success  - True
   ;                       On failure  - False, ErrorMsg / ErrorCode contain additional information
   ; ===================================================================================================================
   CloseDB() {
      This.ErrorMsg := ""
      This.ErrorCode := 0
      This.SQL := ""
      If !(This._Handle)
         Return True
      For Each, Stmt in This._Stmts
         DllCall("SQlite3.dll\sqlite3_finalize", "Ptr", Stmt, "Cdecl Int")
      If (RC := DllCall("SQlite3.dll\sqlite3_close", "Ptr", This._Handle, "Cdecl Int"))
         Return This._SetError(RC)
      This._Path := ""
      This._Handle := ""
      This._Stmts := Map()
      Return True
   }
   ; ===================================================================================================================
   ; METHOD AttachDB       Add another database file to the current database connection
   ;                       http://www.sqlite.org/lang_attach.html
   ; Parameters:           DBPath      - Path of the database file
   ;                       DBAlias     - Database alias name used internally by SQLite
   ; Return values:        On success  - True
   ;                       On failure  - False, ErrorMsg / ErrorCode contain additional information
   ; ===================================================================================================================
   AttachDB(DBPath, DBAlias) {
      Return This.Exec("ATTACH DATABASE '" . DBPath . "' As " . DBAlias . ";")
   }
   ; ===================================================================================================================
   ; METHOD DetachDB       Detaches an additional database connection previously attached using AttachDB()
   ;                       http://www.sqlite.org/lang_detach.html
   ; Parameters:           DBAlias     - Database alias name used with AttachDB()
   ; Return values:        On success  - True
   ;                       On failure  - False, ErrorMsg / ErrorCode contain additional information
   ; ===================================================================================================================
   DetachDB(DBAlias) {
      Return This.Exec("DETACH DATABASE " . DBAlias . ";")
   }
   ; ===================================================================================================================
   ; METHOD Exec           Execute SQL statement
   ; Parameters:           SQL         - Valid SQL statement
   ;                       Callback    - Name of a callback function to invoke for each result row coming out
   ;                                     of the evaluated SQL statements.
   ;                                     The function must accept 4 parameters:
   ;                                     1: SQLiteDB object
   ;                                     2: Number of columns
   ;                                     3: Pointer to an array of pointers to columns text
   ;                                     4: Pointer to an array of pointers to column names
   ;                                     The address of the current SQL string is passed in A_EventInfo.
   ;                                     If the callback function returns non-zero, DB.Exec() returns SQLITE_ABORT
   ;                                     without invoking the callback again and without running any subsequent
   ;                                     SQL statements.  
   ; Return values:        On success  - True, the number of changed rows is given in property Changes
   ;                       On failure  - False, ErrorMsg / ErrorCode contain additional information
   ; ===================================================================================================================
   Exec(SQL, Callback := "") {
      This.ErrorMsg := ""
      This.ErrorCode := 0
      This.SQL := SQL
      If !(This._Handle)
         Return This._SetError(0, "Invalid database handle!")
      CBPtr := 0
      Err := 0
      If (Type(Callback) = "Func") && (Callback.MinParams = 4)
         CBPtr := CallbackCreate(Callback, "C", 4)
      UTF8 := This._StrToUTF8(SQL)
      RC := DllCall("SQlite3.dll\sqlite3_exec", "Ptr", This._Handle, "Ptr", UTF8, "Int", CBPtr, "Ptr", ObjPtr(This),
                    "UPtrP", &Err, "Cdecl Int")
      If (CBPtr)
         CallbackFree(CBPtr)
      If (RC) {
         This.ErrorMsg := StrGet(Err, "UTF-8")
         This.ErrorCode := RC
         DllCall("SQLite3.dll\sqlite3_free", "Ptr", Err, "Cdecl")
         Return False
      }
      This.Changes := This._Changes()
      Return True
   }
   ; ===================================================================================================================
   ; METHOD GetTable       Get complete result for SELECT query
   ; Parameters:           SQL         - SQL SELECT statement
   ;                       ByRef TB    - Variable to store the result object (TB _Table)
   ;                       MaxResult   - Number of rows to return:
   ;                          0          Complete result (default)
   ;                         -1          Return only RowCount and ColumnCount
   ;                         -2          Return counters and array ColumnNames
   ;                          n          Return counters and ColumnNames and first n rows
   ; Return values:        On success  - True, TB contains the result object
   ;                       On failure  - False, ErrorMsg / ErrorCode contain additional information
   ; ===================================================================================================================
   GetTable(SQL, &TB, MaxResult := 0) {
      TB := ""
      This.ErrorMsg := ""
      This.ErrorCode := 0
      This.SQL := SQL
      If !(This._Handle)
         Return This._SetError(0, "Invalid database handle!")
      Names := ""
      Err := 0, RC := 0, GetRows := 0
      I := 0, Rows := Cols := 0
      Table := 0
      If !IsInteger(MaxResult)
         MaxResult := 0
      If (MaxResult < -2)
         MaxResult := 0
      UTF8 := This._StrToUTF8(SQL)
      RC := DllCall("SQlite3.dll\sqlite3_get_table", "Ptr", This._Handle, "Ptr", UTF8, "UPtrP", &Table,
                    "IntP", &Rows, "IntP", &Cols, "UPtrP", &Err, "Cdecl Int")
      If (RC) {
         This.ErrorMsg := StrGet(Err, "UTF-8")
         This.ErrorCode := RC
         DllCall("SQLite3.dll\sqlite3_free", "Ptr", Err, "Cdecl")
         Return False
      }
      TB := SQLiteDB._Table()
      TB.ColumnCount := Cols
      TB.RowCount := Rows
      If (MaxResult = -1) {
         DllCall("SQLite3.dll\sqlite3_free_table", "Ptr", Table, "Cdecl")
         Return True
      }
      If (MaxResult = -2)
         GetRows := 0
      Else If (MaxResult > 0) && (MaxResult <= Rows)
         GetRows := MaxResult
      Else
         GetRows := Rows
      Offset := 0
      Names := []
      Names.Length := Cols
      Loop Cols {
         Names[A_Index] := StrGet(NumGet(Table + Offset, "UPtr"), "UTF-8")
         Offset += A_PtrSize
      }
      TB.ColumnNames := Names
      TB.HasNames := True
      TB.Rows.Length := GetRows
      Loop GetRows {
         ColArr := []
         ColArr.Length := Cols
         Loop Cols {
            ColArr[A_Index] := (Pointer := NumGet(Table + Offset, "UPtr")) ? StrGet(Pointer, "UTF-8") : ""
            Offset += A_PtrSize
         }
         TB.Rows[A_Index] := ColArr
      }
      If (GetRows)
         TB.HasRows := True
      DllCall("SQLite3.dll\sqlite3_free_table", "Ptr", Table, "Cdecl")
      Return True
   }
   ; ===================================================================================================================
   ; Prepared statement 10:54 2019.07.05. by Dixtroy
   ;  DB := SQLiteDB()
   ;  DB.OpenDB(DBFileName)
   ;  DB.Prepare 1 or more, just once
   ;  DB.Step 1 or more on prepared one, repeatable
   ;  DB.Finalize at the end
   ; ===================================================================================================================
   ; ===================================================================================================================
   ; METHOD Prepare        Prepare database table for further actions.
   ; Parameters:           SQL         - SQL statement to be compiled
   ;                       ByRef ST    - Variable to store the statement object (Class _Statement)
   ; Return values:        On success  - True, ST contains the statement object
   ;                       On failure  - False, ErrorMsg / ErrorCode contain additional information
   ; Remarks:              You have to pass one ? for each column you want to assign a value later.
   ; ===================================================================================================================
   Prepare(SQL, &ST) {
      This.ErrorMsg := ""
      This.ErrorCode := 0
      This.SQL := SQL
      If !(This._Handle)
         Return This._SetError(0, "Invalid database handle!")
      Stmt := 0
      UTF8 := This._StrToUTF8(SQL)
      RC := DllCall("SQlite3.dll\sqlite3_prepare_v2", "Ptr", This._Handle, "Ptr", UTF8, "Int", -1,
                    "UPtrP", &Stmt, "Ptr", 0, "Cdecl Int")
      If (RC)
         Return This._SetError(RC)
      ColumnNames := []
      ColumnCount := DllCall("SQlite3.dll\sqlite3_column_count", "Ptr", Stmt, "Cdecl Int")
      If (ColumnCount > 0) {
         ColumnNames.Length := ColumnCount
         Loop ColumnCount {
            StrPtr := DllCall("SQlite3.dll\sqlite3_column_name", "Ptr", Stmt, "Int", A_Index - 1, "Cdecl UPtr")
            ColumnNames[A_Index] := StrGet(StrPtr, "UTF-8")
         }
      }
		ST := SQLiteDB._Prepared()
      ST.ColumnCount := ColumnCount
      ST.ColumnNames := ColumnNames
      ST.ParamCount := DllCall("SQlite3.dll\sqlite3_bind_parameter_count", "Ptr", Stmt, "Cdecl Int")
      ST._Handle := Stmt
      ST._DB := This
      This._Stmts[Stmt] := Stmt
      Return True
   }
   ; ===================================================================================================================
   ; METHOD CreateScalarFunc  Create a scalar application defined function
   ; Parameters:              Name  -  the name of the function
   ;                          Args  -  the number of arguments that the SQL function takes
   ;                          Func  -  a pointer to AHK functions that implement the SQL function
   ;                          Enc   -  specifies what text encoding this SQL function prefers for its parameters
   ;                          Param -  an arbitrary pointer accessible within the funtion with sqlite3_user_data()
   ; Return values:           On success  - True
   ;                          On failure  - False, ErrorMsg / ErrorCode contain additional information
   ; Documentation:           www.sqlite.org/c3ref/create_function.html
   ; ===================================================================================================================
   CreateScalarFunc(Name, Args, Func, Enc := 0x0801, Param := 0) {
      ; SQLITE_DETERMINISTIC = 0x0800 - the function will always return the same result given the same inputs
      ;                                 within a single SQL statement
      ; SQLITE_UTF8 = 0x0001
      This.ErrorMsg := ""
      This.ErrorCode := 0
      If !(This._Handle)
         Return This._SetError(0, "Invalid database handle!")
      RC := DllCall("SQLite3.dll\sqlite3_create_function", "Ptr", This._Handle, "AStr", Name, "Int", Args,
                    "Int", Enc, "Ptr", Param, "Ptr", Func, "Ptr", 0, "Ptr", 0, "Cdecl Int")
      Return (RC) ? This._SetError(RC) : True
   }
   ; ===================================================================================================================
   ; METHOD EnableLoadExtension  Enable or disable the sqlite3_load_extension() interface
   ; Parameters:                 Enable (1 = enable, 0 = disable)
   ; Return values:              On success  - True
   ;                             On failure  - False, ErrorMsg / ErrorCode contain additional information
   ; Documentation:              www.sqlite.org/c3ref/enable_load_extension.html
   ;                             #define SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION 1005 /* int int* */
   ; ===================================================================================================================
   EnableLoadExtension(Enable := 1) {
      RC := DllCall("SQLite3.dll\sqlite3_db_config", "Ptr", This._Handle, "Int", 1005, "Int", !!Enable,
                    "Ptr", 0, "Cdecl Int")
      Return (RC) ? This._SetError(RC) : True
   }
   ; ===================================================================================================================
   ; METHOD LoadExtension     Enable or disable the sqlite3_load_extension() interface
   ; Parameters:              File - Name of the shared library containing extension
   ;                          Proc - Name of the entry point. Derived from File if omitted.
   ; Return values:           On success  - True
   ;                          On failure  - False, ErrorMsg / ErrorCode contain additional information
   ; Documentation:           https://www.sqlite.org/c3ref/load_extension.html
   ; ===================================================================================================================
   LoadExtension(File, Proc?) {
      RC := IsSet(Proc) ? DllCall("SQLite3.dll\sqlite3_load_extension", "Ptr", This._Handle, "AStr", File,
                                  "AStr", Proc, "Ptr", 0, "Cdecl Int")
                        : DllCall("SQLite3.dll\sqlite3_load_extension", "Ptr", This._Handle, "AStr", File,
                                  "Ptr", 0, "Ptr", 0, "Cdecl Int")
      Return (RC) ? This._SetError(RC) : True
   }
   ; ===================================================================================================================
   ; METHOD LastInsertRowID   Get the ROWID of the last inserted row
   ; Parameters:              ByRef RowID - Variable to store the ROWID
   ; Return values:           On success  - True, RowID contains the ROWID
   ;                          On failure  - False, ErrorMsg / ErrorCode contain additional information
   ; ===================================================================================================================
   LastInsertRowID(&RowID) {
      This.ErrorMsg := ""
      This.ErrorCode := 0
      This.SQL := ""
      If !(This._Handle)
         Return This._SetError(0, "Invalid database handle!")
      RowID := DllCall("SQLite3.dll\sqlite3_last_insert_rowid", "Ptr", This._Handle, "Cdecl Int64")
      Return True
   }
   ; ===================================================================================================================
   ; METHOD TotalChanges   Get the number of changed rows since connecting to the database
   ; Parameters:           ByRef Rows  - Variable to store the number of rows
   ; Return values:        On success  - True, Rows contains the number of rows
   ;                       On failure  - False, ErrorMsg / ErrorCode contain additional information
   ; ===================================================================================================================
   TotalChanges(&Rows) {
      This.ErrorMsg := ""
      This.ErrorCode := 0
      This.SQL := ""
      If !(This._Handle)
         Return This._SetError(0, "Invalid database handle!")
      Rows := DllCall("SQLite3.dll\sqlite3_total_changes", "Ptr", This._Handle, "Cdecl Int")
      Return True
   }
   ; ===================================================================================================================
   ; METHOD SetTimeout     Set the timeout to wait before SQLITE_BUSY or SQLITE_IOERR_BLOCKED is returned,
   ;                       when a table is locked.
   ; Parameters:           TimeOut     - Time to wait in milliseconds
   ; Return values:        On success  - True
   ;                       On failure  - False, ErrorMsg / ErrorCode contain additional information
   ; ===================================================================================================================
   SetTimeout(Timeout := 1000) {
      This.ErrorMsg := ""
      This.ErrorCode := 0
      This.SQL := ""
      If !(This._Handle)
         Return This._SetError(0, "Invalid database handle!")
      If !IsInteger(Timeout)
         Timeout := 1000
      If (RC := DllCall("SQLite3.dll\sqlite3_busy_timeout", "Ptr", This._Handle, "Int", Timeout, "Cdecl Int"))
         Return This._SetError(RC)
      Return True
   }
   ; ===================================================================================================================
   ; METHOD EscapeStr      Escapes special characters in a string to be used as field content
   ; Parameters:           Str         - String to be escaped
   ;                       Quote       - Add single quotes around the outside of the total string (True / False)
   ; Return values:        On success  - True
   ;                       On failure  - False, ErrorMsg / ErrorCode contain additional information
   ; ===================================================================================================================
   EscapeStr(&Str, Quote := True) {
      This.ErrorMsg := ""
      This.ErrorCode := 0
      This.SQL := ""
      If !(This._Handle)
         Return This._SetError(0, "Invalid database handle!")
      If IsNumber(Str)
         Return True
      OP := Buffer(16, 0)
      StrPut(Quote ? "%Q" : "%q", OP, "UTF-8")
      UTF8 := This._StrToUTF8(Str)
      Ptr := DllCall("SQLite3.dll\sqlite3_mprintf", "Ptr", OP, "Ptr", UTF8, "Cdecl UPtr")
      Str := StrGet(Ptr, "UTF-8")
      DllCall("SQLite3.dll\sqlite3_free", "Ptr", Ptr, "Cdecl")
      Return True
   }
   ; ===================================================================================================================
   ; METHOD ExtErrCode     Gets the extended result code in case of errors.
   ; Parameters:           None.
   ; Return values:        On success  - Extended result code
   ;                       On failure  - 0
   ; Remarks:              Extended result code list -> https://www.sqlite.org/rescode.html#extrc
   ; ===================================================================================================================
   ExtErrCode() {
      If !(This._Handle)
         Return 0
      Return DllCall("SQLite3.dll\sqlite3_extended_errcode", "Ptr", This._Handle, "Cdecl Int")
   }
   ; ===================================================================================================================
   ; PRIVATE _Changes
   ; ===================================================================================================================
   _Changes() {
      Return DllCall("SQLite3.dll\sqlite3_changes", "Ptr", This._Handle, "Cdecl Int")
   }
   ; ===================================================================================================================
   ; PRIVATE _ErrMsg
   ; ===================================================================================================================
   _ErrMsg() {
      If (RC := DllCall("SQLite3.dll\sqlite3_errmsg", "Ptr", This._Handle, "Cdecl UPtr"))
         Return StrGet(RC, "UTF-8")
      Return ""
   }
   ; ===================================================================================================================
   ; PRIVATE _ErrCode
   ; ===================================================================================================================
   _ErrCode() {
      Return DllCall("SQLite3.dll\sqlite3_errcode", "Ptr", This._Handle, "Cdecl Int")
   }
   ; ===================================================================================================================
   ; PRIVATE _ErrStr
   ; ===================================================================================================================
   _ErrStr(ErrCode) {
      Return StrGet(DllCall("SQLite3.dll\sqlite3_errstr", "Int", ErrCode, "Cdecl UPtr"), "UTF-8")
   }
   ; ===================================================================================================================
   ; PRIVATE _SetError
   ; ===================================================================================================================
   _SetError(RC, Msg?) {
      This.ErrorMsg := IsSet(Msg) ? Msg : This._ErrMsg()
      This.ErrorCode := RC
      Return False
   }
   ; ===================================================================================================================
   ; PRIVATE _StrToUTF8
   ; ===================================================================================================================
   _StrToUTF8(Str) {
      UTF8 := Buffer(StrPut(Str, "UTF-8"), 0)
      StrPut(Str, UTF8, "UTF-8")
      Return UTF8
   }
   ; ===================================================================================================================
   ; PRIVATE _Returncode
   ; ===================================================================================================================
   _ReturnCode(RC) {
      Static RCODE := {SQLITE_OK:           0, ; Successful result
                       SQLITE_ERROR:        1, ; SQL error or missing database
                       SQLITE_INTERNAL:     2, ; NOT USED. Internal logic error in SQLite
                       SQLITE_PERM:         3, ; Access permission denied
                       SQLITE_ABORT:        4, ; Callback routine requested an abort
                       SQLITE_BUSY:         5, ; The database file is locked
                       SQLITE_LOCKED:       6, ; A table in the database is locked
                       SQLITE_NOMEM:        7, ; A malloc() failed
                       SQLITE_READONLY:     8, ; Attempt to write a readonly database
                       SQLITE_INTERRUPT:    9, ; Operation terminated by sqlite3_interrupt()
                       SQLITE_IOERR:       10, ; Some kind of disk I/O error occurred
                       SQLITE_CORRUPT:     11, ; The database disk image is malformed
                       SQLITE_NOTFOUND:    12, ; NOT USED. Table or record not found
                       SQLITE_FULL:        13, ; Insertion failed because database is full
                       SQLITE_CANTOPEN:    14, ; Unable to open the database file
                       SQLITE_PROTOCOL:    15, ; NOT USED. Database lock protocol error
                       SQLITE_EMPTY:       16, ; Database is empty
                       SQLITE_SCHEMA:      17, ; The database schema changed
                       SQLITE_TOOBIG:      18, ; String or BLOB exceeds size limit
                       SQLITE_CONSTRAINT:  19, ; Abort due to constraint violation
                       SQLITE_MISMATCH:    20, ; Data type mismatch
                       SQLITE_MISUSE:      21, ; Library used incorrectly
                       SQLITE_NOLFS:       22, ; Uses OS features not supported on host
                       SQLITE_AUTH:        23, ; Authorization denied
                       SQLITE_FORMAT:      24, ; Auxiliary database format error
                       SQLITE_RANGE:       25, ; 2nd parameter to sqlite3_bind out of range
                       SQLITE_NOTADB:      26, ; File opened that is not a database file
                       SQLITE_ROW:        100, ; sqlite3_step() has another row ready
                       SQLITE_DONE:       101} ; sqlite3_step() has finished executing
      Return RCODE.HasOwnProp(RC) ? RCODE.%RC% : ""
   }
   ; +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
   ; +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
   ; PRIVATE Properties ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
   ; +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
   ; +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
   Static Version := ""
   Static _SQLiteDLL := A_ScriptDir . "\SQLite3.dll"
   Static _RefCount := 0
   Static _MinVersion := "3.6"
   ; ===================================================================================================================
   ; CLASS _Table
   ; Object returned from method GetTable()
   ; _Table is an independent object and does not need SQLite after creation at all.
   ; ===================================================================================================================
   Class _Table {
      ; ----------------------------------------------------------------------------------------------------------------
      ; CONSTRUCTOR  Create instance variables
      ; ----------------------------------------------------------------------------------------------------------------
      __New() {
          This.ColumnCount := 0          ; Number of columns in the result table         (Integer)
          This.RowCount := 0             ; Number of rows in the result table            (Integer)
          This.ColumnNames := []         ; Names of columns in the result table          (Array)
          This.Rows := []                ; Rows of the result table                      (Array of Arrays)
          This.HasNames := False         ; Does var ColumnNames contain names?           (Bool)
          This.HasRows := False          ; Does var Rows contain rows?                   (Bool)
          This._CurrentRow := 0          ; Row index of last returned row                (Integer)
      }
      ; ----------------------------------------------------------------------------------------------------------------
      ; METHOD GetRow      Get row for RowIndex
      ; Parameters:        RowIndex    - Index of the row to retrieve, the index of the first row is 1
      ;                    ByRef Row   - Variable to pass out the row array
      ; Return values:     On failure  - False
      ;                    On success  - True, Row contains a valid array
      ; Remarks:           _CurrentRow is set to RowIndex, so a subsequent call of NextRow() will return the
      ;                    following row.
      ; ----------------------------------------------------------------------------------------------------------------
      GetRow(RowIndex, &Row) {
         Row := ""
         If (RowIndex < 1 || RowIndex > This.RowCount)
            Return False
         If !This.Rows.Has(RowIndex)
            Return False
         Row := This.Rows[RowIndex]
         This._CurrentRow := RowIndex
         Return True
      }
      ; ----------------------------------------------------------------------------------------------------------------
      ; METHOD Next        Get next row depending on _CurrentRow
      ; Parameters:        ByRef Row   - Variable to pass out the row array
      ; Return values:     On failure  - False, -1 for EOR (end of rows)
      ;                    On success  - True, Row contains a valid array
      ; ----------------------------------------------------------------------------------------------------------------
      Next(&Row) {
         Row := ""
         If (This._CurrentRow >= This.RowCount)
            Return -1
         This._CurrentRow += 1
         If !This.Rows.Has(This._CurrentRow)
            Return False
         Row := This.Rows[This._CurrentRow]
         Return True
      }
      ; ----------------------------------------------------------------------------------------------------------------
      ; METHOD Reset       Reset _CurrentRow to zero
      ; Parameters:        None
      ; Return value:      True
      ; ----------------------------------------------------------------------------------------------------------------
      Reset() {
         This._CurrentRow := 0
         Return True
      }
   }
   ; ===================================================================================================================
   ; CLASS _Prepared
   ; Object returned from method Prepare()
   ; The life-cycle of a prepared statement object usually goes like this:
   ; 1. Create the prepared statement object (PST) by calling DB.Prepare().
   ; 2. Bind values to parameters using the PST.Bind() method of the statement object if needed.
   ; 3. Run the SQL by calling PST.Step() one or more times.
   ; 4. Reset the prepared statement using PTS.Reset() then go back to step 2. Do this zero or more times.
   ; 5. Destroy the object using PST.Free().
   ; The lifetime of a prepared statement depends on the lifetime of the related SQLiteDB object.
   ; The rows (records) of the result of a query statement can be fetched sequentially by successive calls of
   ; PST.Step(&Row)
   ; ===================================================================================================================
   Class _Prepared {
      ; ----------------------------------------------------------------------------------------------------------------
      ; CONSTRUCTOR  Create instance variables
      ; ----------------------------------------------------------------------------------------------------------------
      __New() {
         This.ColumnCount := 0         ; Number of columns in the result               (Integer)
         This.ColumnNames := []        ; Names of columns in the result                (Array)
         This.CurrentStep := 0         ; Index of current step                         (Integer)
         This.ErrorMsg := ""           ; Last error message                            (String)
         This.ErrorCode := 0           ; Last SQLite error code / ErrorLevel           (Variant)
         This._Handle := 0             ; Query handle                                  (Pointer)
         This._DB := {}                ; SQLiteDB object                               (Object)
      }
      ; ----------------------------------------------------------------------------------------------------------------
      ; DESTRUCTOR   Clear instance variables
      ; ----------------------------------------------------------------------------------------------------------------
      __Delete() {
         If This.HasOwnProp("_Handle") && (This._Handle != 0)
            This.Free()
      }
      ; ----------------------------------------------------------------------------------------------------------------
      ; METHOD Bind        Bind values to SQL parameters.
      ; Parameters:        Params      -  Array of SQL parameters.
      ;                                   The index within the array determines the indes of the SQL parameter.
      ;                                   Each array element must be an associative array with one key/value pair:
      ;                                      Key   = one of the types defined below in Types
      ;                                      Value = type dependend value of the parameter
      ; Return values:     On success  - True
      ;                    On failure  - False, ErrorMsg / ErrorCode contain additional information
      ; ----------------------------------------------------------------------------------------------------------------
      Bind(Params) {
         Static Types := {Blob: 1, Double: 1, Int: 1, Int64: 1, Null: 1, Text: 1}
         This.ErrorMsg := ""
         This.ErrorCode := 0
         If !(This._Handle) {
            This.ErrorMsg := "Invalid statement handle!"
            Return False
         }
         For Index, Param In Params {
            If (Index < 1) || (Index > This.ParamCount)
               Return This._SetError(0, "Invalid parameter index: " . Index . "!")
            For Type, Value In Param {
               If !Types.HasOwnProp(Type)
                  Return This._SetError(0, "Invalid parameter type " . Type . " at index " Index . "!")
               Switch Type {
                  Case "Blob":
                     ; Value = Buffer object
                     If !(Type(Value) = "Buffer")
                        Return This._SetError(0, "Invalid blob object at index " . Index . "!")
                     ; Let SQLite always create a copy of the BLOB
                     RC := DllCall("SQlite3.dll\sqlite3_bind_blob", "Ptr", This._Handle, "Int", Index, "Ptr", Value,
                                   "Int", Value.Size, "Ptr", -1, "Cdecl Int")
                     If (RC)
                        Return This._SetError(RC)
                  Case "Double":
                     ; Value = double value
                     If !IsFloat(Value)
                        Return This._SetError(0, "Invalid value for double at index " . Index . "!")
                     RC := DllCall("SQlite3.dll\sqlite3_bind_double", "Ptr", This._Handle, "Int", Index, "Double", Value,
                                   "Cdecl Int")
                     If (RC)
                        Return This._SetError(RC)
                  Case "Int":
                     ; Value = integer value
                     If !IsInteger(Value)
                        Return This._SetError(0, "Invalid value for int at index " . Index . "!")
                     RC := DllCall("SQlite3.dll\sqlite3_bind_int", "Ptr", This._Handle, "Int", Index, "Int", Value,
                                   "Cdecl Int")
                     If (RC)
                        Return This._SetError(RC)
                  Case "Int64":
                     ; Value = integer value
                     If !IsInteger(Value)
                        Return This._SetError(0, "Invalid value for int64 at index " . Index . "!")
                     RC := DllCall("SQlite3.dll\sqlite3_bind_int64", "Ptr", This._Handle, "Int", Index, "Int64", Value,
                                   "Cdecl Int")
                     If (RC)
                        Return This._SetError(RC)
                  Case "Null":
                     RC := DllCall("SQlite3.dll\sqlite3_bind_null", "Ptr", This._Handle, "Int", Index, "Cdecl Int")
                     If (RC)
                        Return This._SetError(RC)
                  Case "Text":
                     ; Value = zero-terminated string
                     UTF8 := This._DB._StrToUTF8(Value)
                     ; Let SQLite always create a copy of the text
                     RC := DllCall("SQlite3.dll\sqlite3_bind_text", "Ptr", This._Handle, "Int", Index, "Ptr", UTF8,
                                   "Int", -1, "Ptr", -1, "Cdecl Int")
                     If (RC)
                        Return This._SetError(RC)
               }
               Break
            }
         }
         Return True
      }
      ; ----------------------------------------------------------------------------------------------------------------
      ; METHOD Step        Execute the statement and get next row of the query result if available.
      ; Parameters:        ByRef Row   - Optional: Variable to store the row array
      ; Return values:     On success  - True, Row contains the row array
      ;                    On failure  - False, ErrorMsg / ErrorCode contain additional information
      ;                                  -1 for EOR (end of records)
      ; ----------------------------------------------------------------------------------------------------------------
      Step(&Row?) {
         Static SQLITE_INTEGER := 1, SQLITE_FLOAT := 2, SQLITE_BLOB := 4, SQLITE_NULL := 5
         Static EOR := -1
         This.ErrorMsg := ""
         This.ErrorCode := 0
         If !(This._Handle)
            Return This._SetError(0, "Invalid query handle!")
         RC := DllCall("SQlite3.dll\sqlite3_step", "Ptr", This._Handle, "Cdecl Int")
         If (RC = This._DB._ReturnCode("SQLITE_DONE"))
            Return (This._SetError(RC, "EOR") | EOR)
         If (RC != This._DB._ReturnCode("SQLITE_ROW"))
            Return This._SetError(RC)
         This.CurrentStep += 1
         If !IsSetRef(&Row)
            Return True
         Row := []
         RC := DllCall("SQlite3.dll\sqlite3_data_count", "Ptr", This._Handle, "Cdecl Int")
         If (RC < 1)
            Return True
         Row.Length := RC
         Loop RC {
            Column := A_Index - 1
            ColumnType := DllCall("SQlite3.dll\sqlite3_column_type", "Ptr", This._Handle, "Int", Column, "Cdecl Int")
            Switch ColumnType {
               Case SQLITE_BLOB:
                  BlobPtr := DllCall("SQlite3.dll\sqlite3_column_blob", "Ptr", This._Handle, "Int", Column, "Cdecl UPtr")
                  BlobSize := DllCall("SQlite3.dll\sqlite3_column_bytes", "Ptr", This._Handle, "Int", Column, "Cdecl Int")
                  If (BlobPtr = 0) || (BlobSize = 0)
                     Row[A_Index] := ""
                  Else {
                     Blob := Buffer(BlobSize)
                     DllCall("Kernel32.dll\RtlMoveMemory", "Ptr", Blob, "Ptr", BlobPtr, "Ptr", BlobSize)
                     Row[A_Index] := Blob
                  }
               Case SQLITE_INTEGER:
                  Int := DllCall("SQlite3.dll\sqlite3_column_int64", "Ptr", This._Handle, "Int", Column, "Cdecl Int64")
                  Row[A_Index] := Int
               Case SQLITE_FLOAT:
                  Float := DllCall("SQlite3.dll\sqlite3_column_double", "Ptr", This._Handle, "Int", Column, "Cdecl Double")
                  Row[A_Index] := Float
               Case SQLITE_NULL:
                  Row[A_Index] := ""
               Default:
                  Pointer := DllCall("SQlite3.dll\sqlite3_column_text", "Ptr", This._Handle, "Int", Column, "Cdecl UPtr")
                  Row[A_Index] := StrGet(Pointer, "UTF-8")
            }
         }
         Return True
      }
      ; ----------------------------------------------------------------------------------------------------------------
      ; METHOD Next        Alternative name for Step().
      ; ----------------------------------------------------------------------------------------------------------------
      Next(&Row?) => This.Step(&Row?)
      ; ----------------------------------------------------------------------------------------------------------------
      ; METHOD Reset       Reset result pointer of the prepared statement.
      ; Parameters:        ClearBindings  - Clear bound SQL parameter values (True/False)
      ; Return values:     On success     - True
      ;                    On failure     - False, ErrorMsg / ErrorCode contain additional information
      ; Remarks:           After a call of this method you can access the query result via Next() again.
      ; ----------------------------------------------------------------------------------------------------------------
      Reset(ClearBindings := True) {
         This.ErrorMsg := ""
         This.ErrorCode := 0
         If !(This._Handle)
            Return This._SetError(0, "Invalid query handle!")
         If (RC := DllCall("SQlite3.dll\sqlite3_reset", "Ptr", This._Handle, "Cdecl Int"))
            Return This._SetError(RC)
         If (ClearBindings) && (RC := DllCall("SQlite3.dll\sqlite3_clear_bindings", "Ptr", This._Handle, "Cdecl Int"))
            Return This._SetError(RC)
         This.CurrentStep := 0
         Return True
      }
      ; ----------------------------------------------------------------------------------------------------------------
      ; METHOD Free        Free the prepared statement.
      ; Parameters:        None
      ; Return values:     On success  - True
      ;                    On failure  - False, ErrorMsg / ErrorCode contain additional information
      ; Remarks:           After the call of this method further access on the query result is impossible.
      ; ----------------------------------------------------------------------------------------------------------------
      Free() {
         This.ErrorMsg := ""
         This.ErrorCode := 0
         If !(This._Handle)
            Return True
         If (RC := DllCall("SQlite3.dll\sqlite3_finalize", "Ptr", This._Handle, "Cdecl Int"))
            Return This._SetError(RC)
         This._DB._Stmts.Delete(This._Handle)
         This._Handle := 0
         This._DB := 0
         Return True
      }
      ; ----------------------------------------------------------------------------------------------------------------
      ; METHOD _SetError   Internally used for error handling
      ; Parameters:        RC - SQLite error code
      ;                    Msg - error message (optional), if omitted the SQLite error text will be set
      ; ----------------------------------------------------------------------------------------------------------------
      _SetError(RC, Msg?) {
         This.ErrorMsg := IsSet(Msg) ? Msg : This._DB._ErrMsg()
         This.ErrorCode := RC
         Return False
      }
   }
}
; ======================================================================================================================
; Exemplary custom callback function regexp()
; Parameters:        Context  -  handle to a sqlite3_context object
;                    ArgC     -  number of elements passed in Values (must be 2 for this function)
;                    Values   -  pointer to an array of pointers which can be passed to sqlite3_value_text():
;                                1. Needle
;                                2. Haystack
; Return values:     Call sqlite3_result_int() passing 1 (True) for a match, otherwise pass 0 (False).
; ======================================================================================================================
SQLiteDB_RegExp(Context, ArgC, Values) {
   Result := 0
   If (ArgC = 2) {
      AddrN := DllCall("SQLite3.dll\sqlite3_value_text", "Ptr", NumGet(Values + 0, "UPtr"), "Cdecl UPtr")
      AddrH := DllCall("SQLite3.dll\sqlite3_value_text", "Ptr", NumGet(Values + A_PtrSize, "UPtr"), "Cdecl UPtr")
      Result := RegExMatch(StrGet(AddrH, "UTF-8"), StrGet(AddrN, "UTF-8"))
   }
   DllCall("SQLite3.dll\sqlite3_result_int", "Ptr", Context, "Int", !!Result, "Cdecl") ; 0 = false, 1 = trus
}

Sample script:

Code: Select all

#Requires AutoHotkey v2.0.0
; ======================================================================================================================
; Function:       Sample script for Class_SQLiteDB.ahk
; AHK version:    AHK 2.0.2
; Tested on:      Win 10 Pro (x64)
; Author:         just me
; Version:        2.0.0/2023-01-02/just me    - Initial release for AHK 2.08
; ======================================================================================================================
; AHK Settings
; ======================================================================================================================
#SingleInstance Force
SetWorkingDir(A_ScriptDir)
OnExit((*) => Main_Close())
; ======================================================================================================================
; Includes
; ======================================================================================================================
#Include Class_SQLiteDB.ahk
; ======================================================================================================================
; Start & GUI
; ======================================================================================================================
CBBSQL := ["SELECT * FROM Test"]
DBFileName := A_ScriptDir . "\TEST.DB"
Title := "SQL Query/Command ListView Function GUI"
If FileExist(DBFileName)
   Try FileDelete(DBFileName)
Main := Gui("+Disabled +LastFound +OwnDialogs", Title)
Main.MarginX := 10
Main.MarginY := 10
Main.OnEvent("Close", Main_Close)
Main.OnEvent("Escape", Main_Close)
Main.AddText("w100 h20 0x200 vTX", "SQL statement:")
Main.AddComboBox("x+0 ym w590 Choose1 Sort vSQL", CBBSQL)
Main["SQL"].GetPos(&X, &Y, &W, &H)
Main["TX"].Move( , , , H)
Main.AddButton("ym w80 hp Default", "Run").OnEvent("Click", RunSQL)
Main.AddText("xm h20 w100 0x200", "Table name:")
Main.AddEdit("x+0 yp w150 hp vTable", "Test")
Main.AddButton("Section x+10 yp wp hp", "Get Table").OnEvent("Click", GetTable)
Main.AddButton("x+10 yp wp hp", "Get Result").OnEvent("Click" , GetResult)
Main.AddGroupBox("xm w780 h330", "Results")
LV := Main.AddListView("xp+10 yp+18 w760 h300 vResultsLV +LV0x00010000")
SB:= Main.AddStatusBar()
Main.Show()
; ======================================================================================================================
; Use Class SQLiteDB : Initialize and get lib version
; ======================================================================================================================
SB.SetText("SQLiteDB new instance")
DB := SQLiteDB()
Sleep(1000)
SB.SetText("Version")
Main.Title := Title . " - SQLite3.dll v " . SQLiteDB.Version
Sleep(1000)
; ======================================================================================================================
; Use Class SQLiteDB : Open/Create database and table
; ======================================================================================================================
SB.SetText("OpenDB - " . DBFileName)
If !DB.OpenDB(DBFileName) {
   MsgBox("Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode, "SQLite Error", 16)
   ExitApp
}
Sleep(1000)
SB.SetText("Exec: CREATE TABLE")
SQL := "CREATE TABLE Test (Name, Fname, Phone, Room, PRIMARY KEY(Name ASC, FName ASC));"
If !DB.Exec(SQL)
   MsgBox("Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode, "SQLite Error", 16)
Sleep(1000)
SB.SetText("Exec: INSERT 1000 rows")
Start := A_TickCount
DB.Exec("BEGIN TRANSACTION;")
SQLStr := ""
_SQL := "INSERT INTO Test VALUES('Näme#', 'Fname#', 'Phone#', 'Room#');"
Loop 1000 {
   SQL := StrReplace(_SQL, "#", A_Index)
   SQLStr .= SQL
}
If !DB.Exec(SQLStr)
   MsgBox("Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode, "SQLite Error", 16)
DB.Exec("COMMIT TRANSACTION;")
SQLStr := ""
SB.SetText("Exec: INSERT 1000 rows done in " . (A_TickCount - Start) . " ms")
Sleep(1000)
; ======================================================================================================================
; Use Class SQLiteDB : Using Exec() with callback function
; ======================================================================================================================
SB.SetText("Exec: Using a callback function")
SQL := "SELECT COUNT(*) FROM Test;"
DB.Exec(SQL, SQLiteExecCallBack)
; ======================================================================================================================
; Use Class SQLiteDB : Get some informations
; ======================================================================================================================
SB.SetText("LastInsertRowID")
RowID := ""
If !DB.LastInsertRowID(&RowID)
   MsgBox("Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode, "SQLite Error", 16)
LV.Opt("-Redraw")
LV.Delete()
Loop LV.GetCount("Col")
   LV.DeleteCol(1)
LV.InsertCol(1,"", "LastInsertedRowID")
LV.Add("", RowID)
LV.Opt("+Redraw")
Sleep(1000)
; ======================================================================================================================
; Start of query using GetTable() : Get the first 10 rows of table Test
; ======================================================================================================================
SQL := "SELECT * FROM Test;"
SB.SetText("SQLite_GetTable : " . SQL)
Result := ""
If !DB.GetTable(SQL, &Result, 10)
   MsgBox("Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode, "SQLite Error", 16)
ShowTable(LV, Result)
Sleep(1000)
; ======================================================================================================================
; Start of query using Prepare() : Get the column names for table Test
; ======================================================================================================================
SQL := "SELECT * FROM Test;"
SB.SetText("Prepare : " . SQL)
Prepared := ""
If !DB.Prepare(SQL, &Prepared)
   MsgBox("Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode, "SQLite Error", 16)
LV.Opt("-ReDraw")
LV.Delete()
ColCount := LV.GetCount("Col")
Loop ColCount
   LV.DeleteCol(1)
LV.InsertCol(1,"", "Column names")
Loop Prepared.ColumnCount
   LV.Add("", Prepared.ColumnNames[A_Index])
LV.ModifyCol(1, "AutoHdr")
Prepared.Free()
LV.Opt("+ReDraw")
; ======================================================================================================================
; End of query using Prepare()
; ======================================================================================================================
Main.Opt("-Disabled")
Return
; ======================================================================================================================
; Gui Subs
; ======================================================================================================================
Main_Close(*) {
   If !DB.CloseDB()
      MsgBox("Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode, "SQLite Error", 16)
   ExitApp
}
; ======================================================================================================================
; Other Subs
; ======================================================================================================================
; "One step" query using GetTable()
; ======================================================================================================================
GetTable(GuiCtrl, Info) {
   Table := Main["Table"].Text
   SQL := "SELECT * FROM " . Table . ";"
   SB.SetText("GetTable: " . SQL)
   Start := A_TickCount
   Result := ""
   If !DB.GetTable(SQL, &Result)
      MsgBox("Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode, "SQLite Error", 16)
   ShowTable(LV, Result)
   SB.SetText("GetTable: " . SQL . " done (including ListView) in " . (A_TickCount - Start) . " ms")
}
; ======================================================================================================================
; Show results for prepared query using Prepare()
; ======================================================================================================================
GetResult(GuiCtrl, Info) {
   Table := Main["Table"].Text
   SQL := "SELECT * FROM " . Table . ";"
   SB.SetText("Query: " . SQL)
   Start := A_TickCount
   Prepared := ""
   If !DB.Prepare(SQL, &Prepared)
      MsgBox("Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode, "SQLite Error", 16)
   ShowResult(LV, Prepared)
   SB.SetText("Prepare: " . SQL . " done (including ListView) in " . (A_TickCount - Start) . " ms")
}
; ======================================================================================================================
; Execute SQL statement using Exec() / GetTable()
; ======================================================================================================================
RunSQL(CtrlObj, Info) {
   SQL := Trim(Main["SQL"].Text)
   If (SQL = "") {
      SB.SetText("No SQL statement entered!")
      Return
   }
   If (Main["SQL"].Value = 0)
      Main["SQL"].Add([SQL])
   If (SubStr(SQL, -1) != ";")
      SQL .= ";"
   Result := ""
   If RegExMatch(SQL, "i)^\s*SELECT\s") {
      SB.SetText("GetTable: " . SQL)
      If !DB.GetTable(SQL, &Result)
         MsgBox("Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode, "SQLite Error", 16)
      Else
         ShowTable(LV, Result)
      SB.SetText("GetTable: " . SQL . " done!")
   }
   Else {
      SB.SetText("Exec: " . SQL)
      If !DB.Exec(SQL)
         MsgBox("Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode, "SQLite Error", 16)
      Else
         SB.SetText("Exec: " . SQL . " done!")
   }
}
; ======================================================================================================================
; Exec() callback function sample
; ======================================================================================================================
SQLiteExecCallBack(DB, ColumnCount, ColumnValues, ColumnNames) {
   This := ObjFromPtrAddRef(DB)
   Main.Opt("+OwnDialogs") ; required for the MsgBox
   MsgBox("SQLite version: " . SQLiteDB.Version . "`n" .
          "SQL statement: " . This.SQL . "`n" .
          "Number of columns: " . ColumnCount . "`n" .
          "Name of first column: " . StrGet(NumGet(ColumnNames, "Ptr"), "UTF-8") . "`n" .
          "Value of first column: " . StrGet(NumGet(ColumnValues, "Ptr"), "UTF-8"),
          A_ThisFunc, 0)
   Return 0
}
; ======================================================================================================================
; Show results
; ======================================================================================================================
ShowTable(LV, Table) {
   LV.Opt("-Redraw")
   LV.Delete()
   Loop LV.GetCount("Col")
      LV.DeleteCol(1)
   If (Table.HasNames) {
      Loop Table.ColumnCount
         LV.InsertCol(A_Index, "", Table.ColumnNames[A_Index])
      If (Table.HasRows) {
         Loop Table.Rows.Length
            LV.Add("", Table.Rows[A_Index]*)
      }
      Loop Table.ColumnCount
         LV.ModifyCol(A_Index, "AutoHdr")
   }
   LV.Opt("+Redraw")
}
; ----------------------------------------------------------------------------------------------------------------------
ShowResult(LV, Prepared) {
   LV.Opt("-Redraw")
   LV.Delete()
   Loop LV.GetCount("Col")
      LV.DeleteCol(1)
   If (Prepared.ColumnCount > 0) {
      Loop Prepared.ColumnCount
         LV.InsertCol(A_Index, "", Prepared.ColumnNames[A_Index])
      Row := ""
      RC := Prepared.Step(&Row)
      While (RC > 0) {
         LV.Add("", Row*)
         RC := Prepared.Step(&Row)
      }
      If (RC = 0)
         MsgBox("Msg:`t" . Prepared.ErrorMsg . "`nCode:`t" . Prepared.ErrorCode, A_ThisFunc, 16)
      Loop Prepared.ColumnCount
         LV.ModifyCol(A_Index, "AutoHdr")
   }
   LV.Opt("+Redraw")
}

Alpha version:

This is the first v2 alpha of my Class_SQLiteDB. Most of the testing has been done by @jNizM, thanks.

Change history:

• Bugfix in _ErrMsg() on 2021-10-13 (THX viv)
• Minor fixes on 2022-07-14 (THX T-Rock -> https://www.autohotkey.com/boards/viewtopic.php?p=472660#p472660)
• Bugfix in Prepare() on 2022-10-04
  Changed DllCall parameter type PtrP to UPtrP

Class script:
Class_SQLiteDB.zip

Sample script:
SQLiteDB_sample.zip

I'm not an SQL expert, I just have a high level question.
Would this SQL class library be considered an ORM sql class or (standard/classic/other) sql.

I use (in python) mostly sqlaclch, peewee, or premade libraries with ORM sqlite work, and using one would be great.

[just me]

Hi @sashaatx, I'm also not an SQL expert but in all probability it cannot be considered as an ORM SQL class.

[just me]

Hi @20170201225639, the bug should be fixed.

[bshogeman]

With Class_SQLiteDB.ahk version 2.0.3/2023-08-28
I keep getting errors about 'Warning: This local variable has the same name as a global variable'.
StrPtr at line 314, Float at line 765 and so on.
I think maybe DB.GetTable isn't the right command and that is maybe responsible for the issues.
I could not find an example of query a database for data. In the V1 version it worked. But now rebuilding it to V2.

Code: Select all

#Requires AutoHotkey v2.0.0
#Include Class_SQLiteDB.ahk
global LocalAppData := EnvGet("LocalAppData")
if FileExist(LocalAppData "\Adobe\OOBE\opm.db")
		{
			if FileExist(A_ScriptDir "\SQLite3.dll")
			{
				DB := SQLiteDB()
				DB.OpenDB(LocalAppData "\Adobe\OOBE\opm.db", "R",  false)
				RecordSet := ""
				DB.GetTable("SELECT COUNT(key) FROM opm_data WHERE subDomain='SignInUsers';", &RecordSet, 1)
				If RecordSet.Row[1][1] = 1
				{
					SignInAdobe := "1"
					RecordSet.Free()
					DB.CloseDB()
				} else {
					RecordSet.Free()
					DB.CloseDB()
					Try FileDelete(LocalAppData "\Adobe\OOBE\opm.db")
				}
			} else {
				; Can't determinate if Adobe is signed-in without sqlite3.dll so continue
				SignInAdobe := "1"
			}
		} else {
			If WinExist("Creative Cloud Desktop ahk_class CreativeCloudDesktopWindowClass ahk_exe Creative Cloud.exe")
				CreativeCloud()
			If WinExist("Adobe Acrobat Reader ahk_class EmbeddedWB ahk_exe adobe_licensing_wf_acro.exe")
				Acrobat()
		}

[just me]

@bshogeman, thanks. It will be fixed soon.

[just me]

@bshogeman, should be done.

[lexikos]

I want to call a function/method from another function passing an optional ByRef parameter as is.

I anticipated this in my first post and gave you the answer before you asked:

If what you want is for this.Next(&Row) to result in this.Step(&Row) and this.Next() to result in this.Step(), what you need to do is remove & and use only ?.

Given your complete lack of response to this answer, I had to assume you were trying to do something different, and that any other attempts to infer what you want would be futile.

This method is 100% reliable. I have a lot more to say, but will post it elsewhere.

[just me]

@lexikos, ok:

Code: Select all

      Next(Param?) => This.Step(Param)

[lexikos]

@just me no, I was referring to the original code:

Code: Select all

Next(&Row?) => This.Step(&Row?)

Just remove &:

Code: Select all

Next(Row?) => This.Step(Row?)

Without the second ? it would throw UnsetError if Param is unset.

[just me]

Done.