There are a lot of wrong, has been unable to use.
[Class] SQLiteDB - Update on 2022-10-04
Re: [Class] SQLiteDB
No scripts errors, only SQLite errors?
Re: [Class] SQLiteDB
Yes, only SQLite errors.
LV2
SQLite 3.8.11.1
WIN 10 64
LV2
SQLite 3.8.11.1
WIN 10 64
Re: [Class] SQLiteDB
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.
Re: [Class] SQLiteDB
thank you.
Re: [Class] SQLiteDB
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 ??
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 ??
Re: [Class] SQLiteDB
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:
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).
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)
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).
Re: [Class] SQLiteDB
Thank you!
That Sir, was exactly what I needed !
That Sir, was exactly what I needed !
Re: [Class] SQLiteDB
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:
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.
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()
Re: [Class] SQLiteDB
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()
Re: [Class] SQLiteDB
@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
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 271 times
Re: [Class] SQLiteDB
Hi Avi,
in case of your DB 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?
in case of your DB
Code: Select all
ColumnType := DllCall("SQlite3.dll\sqlite3_column_type", "Ptr", This._Handle, "Int", Column, "Cdecl Int")
Re: [Class] SQLiteDB
I used the import feature of SQlite Browser ( http://sqlitebrowser.org ) to add the image.just me wrote:Hi Avi,
in case of your DBretrieves 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?Code: Select all
ColumnType := DllCall("SQlite3.dll\sqlite3_column_type", "Ptr", This._Handle, "Int", Column, "Cdecl Int")
Steps :-
1. Open database in sqlite browser.
2. Switch to "Browse Data" tab.
3. Double click on the Blob cell.
4. Import
Re: [Class] SQLiteDB
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'.
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'.
Re: [Class] SQLiteDB
Thanks. I created a new database with BLOB type and it worked perfectly. .. Sorry for bothering you.
-
- Posts: 45
- Joined: 10 Nov 2015, 12:49
Re: [Class] SQLiteDB
Hello,
how to display a specific value ?
Ex, if 1234568 found in Phone show msgbox. Otherwise do nothing
Thank you for your help
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
Asus Maximus VIII Extrême - DDR4 16Go - Intel Core i7 - GTX 1080
Re: [Class] SQLiteDB
"SELECT Phone FROM Test;" will retrieve a result set with only one column per row:
But if you only want to check for an exact match you could also use:
Code: Select all
Loop % Result.RowCount
{
If (Result.Rows[A_Index, 1] = 12345678)
MsgBox, DATA FOUND.
}
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
-
- Posts: 45
- Joined: 10 Nov 2015, 12:49
Re: [Class] SQLiteDB
Thank you this working
Asus Maximus VIII Extrême - DDR4 16Go - Intel Core i7 - GTX 1080
Re: [Class] SQLiteDB
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).
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) {
...
Re: [Class] SQLiteDB
THX for reporting, I will fix the script!
Return to “Scripts and Functions (v1)”
Who is online
Users browsing this forum: yzqiang and 81 guests