[Class] SQLiteDB - Update on 2022-10-04

Post your working scripts, libraries and tools for AHK v1.1 and older
arcticir
Posts: 693
Joined: 17 Nov 2013, 11:32

Re: [Class] SQLiteDB

25 Aug 2015, 02:27

There are a lot of wrong, has been unable to use.
Image
just me
Posts: 9424
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB

25 Aug 2015, 03:11

No scripts errors, only SQLite errors?
arcticir
Posts: 693
Joined: 17 Nov 2013, 11:32

Re: [Class] SQLiteDB

25 Aug 2015, 03:27

Yes, only SQLite errors.

LV2
SQLite 3.8.11.1
WIN 10 64
just me
Posts: 9424
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB

25 Aug 2015, 03:57

Interesting! Using the v2 sample script I get lots of script errors here. I'll remove the v2 sources and give it a try again as soon as v2 will become a release candidate.
arcticir
Posts: 693
Joined: 17 Nov 2013, 11:32

Re: [Class] SQLiteDB

25 Aug 2015, 05:19

thank you.
sowen
Posts: 3
Joined: 19 Aug 2015, 21:04

Re: [Class] SQLiteDB

27 Aug 2015, 21:17

Hi All,
I am able to get this lib to write to a database quite easily, but i'm having a tough time reading data back out....I just don't seem to be able to deconstruct the GUI and get a simple "index type" output....ie: maybe one row of the query output to a variable (A_Index)?
Anyway someone could post a "dummies" sample script ??
just me
Posts: 9424
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB

28 Aug 2015, 01:35

Not really sure what you are expecting but I hope this might help you.

As an example:

After you successfully have called DB.GetTable(SQL, Result), Result will contain an object as defined in the nested class _Table with the following properties:

Code: Select all

Result.ColumnCount      ; Number of columns in the result table         (Integer)
Result.RowCount         ; Number of rows in the result table            (Integer)
Result.ColumnNames      ; Names of columns in the result table          (Array)
Result.Rows             ; Rows of the result table                      (Array of Arrays)
Result.HasNames         ; Does ColumnNames contain names?               (Bool)
Result.HasRows          ; Does Rows contain rows?                       (Bool)
Result._CurrentRow      ; Row index of last returned row                (Integer)
The array ColumnNames will contain ColumnCount entries if filled (i.e. HasNames is true). The array Rows will contain RowCount entries if filled (i.e. HasRows is true). Each entry of Rows contains an array of ColumnCount column values.

You can retrieve single rows (i.e. arrays of columns values) of the result using the built-in methods Result.GetRow(RowIndex, MyRow) or Result.Next(MyRow). MyRow will contain an array of ColumnCount values in this case if successfull. You can get the column values by index (e.g MyColumn := MyRow[1] will store the value of the 1st column in MyColumn). This methods will additionally update _CurrentRow with the index of the last retrieved row.

Also, you can access the Result.Rows array directly (e.g. MyValue := Result.Rows[3, 4] will store the value of the 4th column of the 3rd row in MyValue).
sowen
Posts: 3
Joined: 19 Aug 2015, 21:04

Re: [Class] SQLiteDB

28 Aug 2015, 20:21

Thank you!
That Sir, was exactly what I needed !
Jeramy
Posts: 20
Joined: 18 Feb 2015, 05:42

Re: [Class] SQLiteDB

27 Sep 2015, 05:04

It seriously took me far, far, far too long to figure out how to query the table for data.

Here's a working sample script to make it easier for everyone:

Code: Select all

#NoEnv
#SingleInstance force
SetWorkingDir, %A_ScriptDir%
SetBatchLines, -1
#Include Class_SQLiteDB.ahk
db := new SQLiteDB
DBLoc = %A_Scriptdir%\SQLiteDB.sqlite ; Change to match the name of your database as needed
db.OpenDB(DBLoc)
sql := "Select * from test;" ; Change the table name to match your table
DB.GetTable(sql, Result)
loop % Result.RowCount
{
I := a_index ; Set the row value for each row loop
     loop % Result.ColumnCount
          {
          msgbox % Result.Rows[I, a_index] ; show the value of row I, column a_index for table Test - you can assign to a variable instead and make use of the data, or just msgbox it to show.
          }
}
db.CloseDB()
Thank you so much for this bit of code, Just Me. Working with a high level scripting language like AHK has its benefits for learning the fundamentals and working with data objects on every level. From there, the mysteries of lower level languages are within reach.
Jeramy
Posts: 20
Joined: 18 Feb 2015, 05:42

Re: [Class] SQLiteDB

27 Sep 2015, 06:46

As an added bonus, here's another snippet that enables you to look up all the tables on a database, so you could apply needed changes across any/all tables across the DB in one shot

Code: Select all

#NoEnv
#SingleInstance force
SetWorkingDir, %A_ScriptDir%
SetBatchLines, -1
#Include Class_SQLiteDB.ahk
db := new SQLiteDB
DBLoc = %A_Scriptdir%\SQLiteDB.sqlite ; Change to match the name of your database as needed
db.OpenDB(DBLoc)
TableLookup := "select name from sqlite_master where type='table';" ; This will produce a list of all tables in the Database
db.GetTable(TableLookup, results)
loop % Results.RowCount
{
I := a_index ; Set the row value for each row loop
     loop % Results.ColumnCount
          {
          msgbox % Results.Rows[I, a_index] ; show the name of each table in the database
          }
}
db.CloseDB()
User avatar
Avi
Posts: 193
Joined: 30 Sep 2013, 09:51
Location: India
Contact:

Re: [Class] SQLiteDB

20 Nov 2015, 01:32

@just me

I notice that if a image is manually added (or overwritten) using other means like http://sqlitebrowser.org then your sample code doesn't work to fetch the image.. Infact the IsObject( Row[A_index] ) evaluates to false in this case. Therefore a 0kb output image is created.. Is this a bug ?

I have attached the TEST.db file that I was using below. The image blob contained in it is valid as the sqlite browser export feature works fine.

Using sqlite v3.8 / v3.9 x32 bit

Code: Select all

; ======================================================================================================================
; Script Function:  Sample script for Class_SQLiteDB.ahk
; AHK Version:      L 1.1.00.00 (U32)
; Language:         English
; Tested on:        Win XPSP3, Win VistaSP2 (32 Bit)
; Author:           just me
; ======================================================================================================================
; AHK Settings
; ======================================================================================================================
#NoEnv
; #Warn
; #Warn LocalSameAsGlobal, Off
#SingleInstance force
SetWorkingDir, %A_ScriptDir%
SetBatchLines, -1
; ======================================================================================================================
; Includes
#Include Class_SQLiteDB.ahk
; ======================================================================================================================
; Get the Google logo or store a picture named Original.jpg in the script's folder and comment this out
;FileDelete, Original.jpg
;URLDownloadToFile, http://www.google.de/intl/de_ALL/images/logos/images_logo_lg.jpg, Original.jpg
;UrlDownloadToFile, http://aviaryan.in/images/profile.jpg, Original.jpg
; ======================================================================================================================
; Start
FileDelete, Blob.jpg
DBFileName := A_ScriptDir . "\TEST.DB"
If FileExist(DBFileName) {
   SB_SetText("Deleting " . DBFileName)
   ;FileDelete, %DBFileName%
}
; ======================================================================================================================
; Use Class SQLiteDB : Create new instance
DB := new SQLiteDB
; ======================================================================================================================
; Use Class SQLiteDB : Open/create database and table, insert a BLOB from a GIF file
If !DB.OpenDB(DBFileName) {
   MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
   ExitApp
}
; HFILE := FileOpen("Original.jpg", "r")
; Size := HFILE.RawRead(BLOB, HFILE.Length)
; HFILE.Close()
; If !DB.Exec("CREATE TABLE Test (TextType, BlobType);")
;    MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
; DB.Exec("BEGIN TRANSACTION;")
; ; ? stands for an automatically numbered parameter (here: 1) to use in BlobArray
; SQL := "INSERT INTO Test VALUES('Text', ?);"
; ; Create the BLOB array
; BlobArray := []
; BlobArray.Insert({Addr: &BLOB, Size: Size}) ; will be inserted as element 1
; If !DB.StoreBLOB(SQL, BlobArray)
;    MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
; DB.Exec("COMMIT TRANSACTION;")
; ======================================================================================================================
; Start of query using Query() : Get the BLOB from table Test
HFILE := FileOpen("Blob.jpg", "w")
If !DB.Query("SELECT * FROM Test;", RecordSet)
   MsgBox, 16, SQLite Error: Query, % "Msg:`t" . RecordSet.ErrorMsg . "`nCode:`t" . RecordSet.ErrorCode
If (RecordSet.HasRows) {
   If (RecordSet.Next(Row) < 1) {
      MsgBox, 16, %A_ThisFunc%, % "Msg next r:`t" . RecordSet.ErrorMsg . "`nCode:`t" . RecordSet.ErrorCode
      Return
   }
   Loop, % RecordSet.ColumnCount {
      If IsObject(Row[A_Index]) {
         Size := Row[A_Index].Size
         Addr := Row[A_Index].GetAddress("Blob")
         If !(Addr) || !(Size) {
            MsgBox, 0, Error, BlobAddr = %Addr% - BlobSize = %Size%
         } Else {
            VarSetCapacity(MyBLOBVar, Size) ; added
            DllCall("Kernel32.dll\RtlMoveMemory", "Ptr", &MyBLOBVar, "Ptr", Addr, "Ptr", Size) ; added
            HFILE.RawWrite(&MyBLOBVar, Size) ; changed
            ; HFILE.RawWrite(Addr + 0, Size) ; original
         }
      }
   }
}
RecordSet.Free()
HFILE.Close()
RecordSet.Free()
; ======================================================================================================================
; Show the result
Gui, Margin, 20, 20
Gui, Add, Text, Section, Original from Google:
;Gui, Add, Pic, y+5, Original.jpg
Gui, Add, Text, ys, Copy out of database:
Gui, Add, Pic, y+5, Blob.jpg
Gui, Show, , Pictures
;FileDelete, Original.jpg
;FileDelete, Blob.jpg
Return
GuiClose:
GuiEscape:
ExitApp
Attachments
TEST.db.zip
(11.71 KiB) Downloaded 243 times
Writes at Dev Letters

Clipjump Clipboard Manager : More Scripts (updated 2019)

Image
just me
Posts: 9424
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB

20 Nov 2015, 03:57

Hi Avi,

in case of your DB

Code: Select all

ColumnType := DllCall("SQlite3.dll\sqlite3_column_type", "Ptr", This._Handle, "Int", Column, "Cdecl Int")
retrieves 3 (SQLITE_TEXT) for the BlobType column. So the script tries to get UTF-8 encoded text as the column value and obviously fails. How did you add the picture?
User avatar
Avi
Posts: 193
Joined: 30 Sep 2013, 09:51
Location: India
Contact:

Re: [Class] SQLiteDB

20 Nov 2015, 09:06

just me wrote:Hi Avi,

in case of your DB

Code: Select all

ColumnType := DllCall("SQlite3.dll\sqlite3_column_type", "Ptr", This._Handle, "Int", Column, "Cdecl Int")
retrieves 3 (SQLITE_TEXT) for the BlobType column. So the script tries to get UTF-8 encoded text as the column value and obviously fails. How did you add the picture?
I used the import feature of SQlite Browser ( http://sqlitebrowser.org ) to add the image.
Steps :-
1. Open database in sqlite browser.
2. Switch to "Browse Data" tab.
3. Double click on the Blob cell.
4. Import
Writes at Dev Letters

Clipjump Clipboard Manager : More Scripts (updated 2019)

Image
just me
Posts: 9424
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB

20 Nov 2015, 09:37

Hello,

the SQLite Browser shows that the 'BlobType' column of the 'Test' table is defined as a 'TEXT' type. SQLite is able to handle such type conflicts. But as long as the type returned from SQLite is 'TEXT' SQLiteDB won't handle it as a 'BLOB'.
User avatar
Avi
Posts: 193
Joined: 30 Sep 2013, 09:51
Location: India
Contact:

Re: [Class] SQLiteDB

20 Nov 2015, 12:43

Thanks. I created a new database with BLOB type and it worked perfectly. .. Sorry for bothering you.
Writes at Dev Letters

Clipjump Clipboard Manager : More Scripts (updated 2019)

Image
Marvin1023
Posts: 45
Joined: 10 Nov 2015, 12:49

Re: [Class] SQLiteDB

13 Jan 2016, 18:08

Hello,

how to display a specific value ?

Ex, if 1234568 found in Phone show msgbox. Otherwise do nothing

Code: Select all

#NoEnv
#SingleInstance force
SetWorkingDir, %A_SCRIPTDIR%
SetBatchLines, -1
#Include Class_SQLiteDB.ahk

DBFileName := A_ScriptDir . "\TEST.DB"
DB := new SQLiteDB
If !DB.OpenDB(DBFileName) {
   MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
   ExitApp
}
GoSub, SELECT_SQL
Return

SELECT_SQL:
SQL := "SELECT Phone FROM Test;"
If !DB.GetTable(SQL, Result)
   MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode

Loop % Result.RowCount
  {
    DATA := A_INDEX
    Loop % Result.ColumnCount
      {
        If Result.Rows=1234568
		  {
		    msgbox, DATA FOUND.
		  }
        Else
		  {
		    ; Continue
		  }
      }		
  }
Return
Thank you for your help
Asus Maximus VIII Extrême - DDR4 16Go - Intel Core i7 - GTX 1080
just me
Posts: 9424
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB

14 Jan 2016, 03:34

"SELECT Phone FROM Test;" will retrieve a result set with only one column per row:

Code: Select all

Loop % Result.RowCount
{
   If (Result.Rows[A_Index, 1] = 12345678)
      MsgBox, DATA FOUND.
}
But if you only want to check for an exact match you could also use:

Code: Select all

SELECT_SQL:
SQL := "SELECT Phone FROM Test Where Phone = '12345978';"
If !DB.GetTable(SQL, Result)
   MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
If Result.HasRows
   MsgBox, DATA FOUND
Marvin1023
Posts: 45
Joined: 10 Nov 2015, 12:49

Re: [Class] SQLiteDB

15 Jan 2016, 19:08

Thank you this working :)
Asus Maximus VIII Extrême - DDR4 16Go - Intel Core i7 - GTX 1080
T-Rock
Posts: 27
Joined: 01 Feb 2015, 09:11

Re: [Class] SQLiteDB

28 Jan 2016, 09:24

Hello just me, first of all thanks for the class, it's great.

I just downloaded the latest SQLite DLL Version 3.10.2
This is not working any more with the check against _MinVersion := 36, because you extract only the first to numbers of the actual version number (in this case 31) and an error message pops up.
I work around it right now by changing _MinVersion to 360 and compare it with the first four numbers of the actual version string (3102).

Code: Select all

...
Static _MinVersion := 360
...
If (SubStr(RegExReplace(This.Base.Version, "\."), 1, 4) < This.Base._MinVersion) {
...
just me
Posts: 9424
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB

28 Jan 2016, 10:59

THX for reporting, I will fix the script!

Return to “Scripts and Functions (v1)”

Who is online

Users browsing this forum: No registered users and 119 guests