[Class] SQLiteDB - Update on 2016-03-28

Post your working scripts, libraries and tools
arcticir
Posts: 513
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: 5431
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB

25 Aug 2015, 03:11

No scripts errors, only SQLite errors?
arcticir
Posts: 513
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: 5431
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: 513
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: 5431
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] [Download] GeSHi © Codebox Plus

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] [Expand] [Download] GeSHi © Codebox Plus



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] [Expand] [Download] GeSHi © Codebox Plus

User avatar
Avi
Posts: 193
Joined: 30 Sep 2013, 09:51
Facebook: avi.aryan.ap
Google: +AviAryan
GitHub: aviaryan
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] [Expand] [Download] GeSHi © Codebox Plus

Attachments
TEST.db.zip
(11.71 KiB) Downloaded 98 times
Writes at Dev Letters

Clipjump Clipboard Manager : More Scripts

Image
just me
Posts: 5431
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] [Download] GeSHi © Codebox Plus

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
Facebook: avi.aryan.ap
Google: +AviAryan
GitHub: aviaryan
Location: India
Contact:

Re: [Class] SQLiteDB

20 Nov 2015, 09:06

just me wrote:Hi Avi,

in case of your DB

Code: [Select all] [Download] GeSHi © Codebox Plus

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

Image
just me
Posts: 5431
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
Facebook: avi.aryan.ap
Google: +AviAryan
GitHub: aviaryan
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

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] [Expand] [Download] (Untitled.ahk)GeSHi © Codebox Plus



Thank you for your help
Asus Maximus VIII Extrême - DDR4 16Go - Intel Core i7 - GTX 1080
just me
Posts: 5431
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] [Download] GeSHi © Codebox Plus

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] [Download] GeSHi © Codebox Plus

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: 20
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] [Download] GeSHi © Codebox Plus

...
Static _MinVersion := 360
...
If (SubStr(RegExReplace(This.Base.Version, "\."), 1, 4) < This.Base._MinVersion) {
...
just me
Posts: 5431
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”

Who is online

Users browsing this forum: arcticir and 10 guests