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

Post your working scripts, libraries and tools
User avatar
CheshireCat
Posts: 6
Joined: 07 Mar 2016, 15:58

Re: [Class] SQLiteDB - Update on 2016-01-28

08 Mar 2016, 14:38

Hello there,
This is excellent work, you have done a magnificent job with this Class.

I have a question though. I'm working on creating a simple SQLite editor, and I would like to be able to export the contents of a table on a database to a CSV file at some point. I know I can either do it with AHK by parsing the data I get out of the database, delimiting it with a "," and then saving it to a file, or by using the "sqlite3.exe" that comes with the "sqlite3.dll", which has the option of exporting to ".csv" by passing command line arguments, which I can also do via AHK. However I was wondering if exporting to ".csv" file can be done with "class_sqlite" by using an SQL statement maybe?

Any insight would be much appreciated. Thank you in advance.
just me
Posts: 5431
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB - Update on 2016-01-28

09 Mar 2016, 03:37

Hello CheshireCat,

I don't know a SQL statement or an API function which would export a database.
User avatar
hoppfrosch
Posts: 318
Joined: 07 Oct 2013, 04:05
GitHub: hoppfrosch
Location: Rhine-Maine-Area, Hesse, Germany
Contact:

Re: [Class] SQLiteDB - Update on 2016-01-28

09 Mar 2016, 04:29

Hi JustMe,

I used code-pieces out of your example to create a example database:

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



... but the database-file A_SriptDir."\test.db" is NOT created. Stepping through the code via Debugger (Scite4Autohotkey) everything looks fine (no error with SQL-Statements or -execution), but the database is not available in the end ...

Am I missing anything?

-------
Edit: Further Debugging shows the following:

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

; File Class_SQLiteDB.ahk
OpenDB(DBPath, Access = "W", Create = True) {
....
This._Path := DBPath ; DBPath is: d:\Projects\AHK\Test\test.db - which is correct
UTF8 := This._StrToUTF8(DBPath) ; Returns an empty string!!!!!!
....
}


so the UTF8-Conversion of my fully qualified path fails ...
Using a filename only rather than a fulyl qualified path does not work either.

Running AutoHotkey : v1.1.23.01 Unicode 32-bit
User avatar
hoppfrosch
Posts: 318
Joined: 07 Oct 2013, 04:05
GitHub: hoppfrosch
Location: Rhine-Maine-Area, Hesse, Germany
Contact:

Re: [Class] SQLiteDB - Update on 2016-01-28

09 Mar 2016, 04:53

I solved my problem above, using



at the beginning of the script.
Why is this needed?
just me
Posts: 5431
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB - Update on 2016-01-28

09 Mar 2016, 06:07

Hi hoppfrosch,

I don't know, maybe one of your environment variables is interfering with the variable names used in the script.

BTW: The current way of string conversion isn't actually safe. I think I'll update the script.
Also:

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

OpenDB(DBPath, Access = "W", Create = True) {
this is not the latest version.
User avatar
CheshireCat
Posts: 6
Joined: 07 Mar 2016, 15:58

Re: [Class] SQLiteDB - Update on 2016-01-28

27 Mar 2016, 13:34

I have another question...
Supposed I open a database which contains several tables. One of the tables has columns set up, but no actual rows of data yet. I would like to be able to firstly get the names for each column and thereafter obtain the data type of the columns as well, (Text, Integer, Real, etc.). I'm trying to use PRAGMA table_info(table_name), but I'm not having any luck. How would I be able to go about doing something like that?

Any help or insight would be greatly appreciated.
just me
Posts: 5431
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB - Update on 2016-01-28

28 Mar 2016, 01:46

Sorry, I'm neither a SQLite nor a SQL expert. I just wrapped some API functions. Maybe someone with more knowledge will jump in and answer your question; or you repeat your question in Ask For Help.
just me
Posts: 5431
Joined: 02 Oct 2013, 08:51
Location: Germany

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

29 Mar 2016, 01:44

*Update on 2016-03-28*
Added PRAGMA support.
User avatar
hoppfrosch
Posts: 318
Joined: 07 Oct 2013, 04:05
GitHub: hoppfrosch
Location: Rhine-Maine-Area, Hesse, Germany
Contact:

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

21 Apr 2016, 03:02

I figured out how to GET a PRAGMA - but how to SET it?

What I tried:

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

; Get the value of the pragma -> works!
SQL := "PRAGMA user_version;"
if !db.GetTable(SQL, TB)
throw, { what: "DB SQLite Error", message: base.ErrorMsg, extra: base.ErrorCode, file: A_LineFile, line: A_LineNumber }

; Set the value of the pragma -> does not work!
SQL := "PRAGMA user_version=17;"
if !db.GetTable(SQL, TB)
throw, { what: "DB SQLite Error", message: base.ErrorMsg, extra: base.ErrorCode, file: A_LineFile, line: A_LineNumber }
Last edited by hoppfrosch on 21 Apr 2016, 03:24, edited 1 time in total.
just me
Posts: 5431
Joined: 02 Oct 2013, 08:51
Location: Germany

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

21 Apr 2016, 03:10

I would try the Exec method. ;)
User avatar
hoppfrosch
Posts: 318
Joined: 07 Oct 2013, 04:05
GitHub: hoppfrosch
Location: Rhine-Maine-Area, Hesse, Germany
Contact:

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

21 Apr 2016, 03:45

Aaaah ... :headwall:

Thx :bravo:
TodWulff
Posts: 7
Joined: 27 Jul 2015, 21:15

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

28 Jul 2016, 20:17

Good day. Struggling a bit with a seemingly simple implementation of your Class.

If I query the DB and get a table in response, and then I run the following on the RecordSet, I have an unexpected result:

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

_interate_recordset(RecordSet) {
temp := RecordSet.HasNames
msgbox,,,HasNames = %temp% ; As Expected: http://i.imgur.com/oLB2xMN.png
temp := RecordSet.HasRows
msgbox,,,HasRows = %temp% ; As Expected: http://i.imgur.com/MvPsUzD.png
temp := RecordSet.RowCount
msgbox,,,RowCount = %temp% ; **** UNEXPECTED ****: http://i.imgur.com/OcQRDvy.png
temp := RecordSet.ColumnCount
msgbox,,,ColumnCount = %temp% ; As Expected: http://i.imgur.com/SWAFTNM.png
}


Given that the ResultSet has 4 rows of results, in addition to Column Names Row, it seems that the RowCount attribute is not correct.?.

Am I being thick headed or is something genuinely amiss? Please advise. Thank you, in advance.

-t

AHK version is 1.1.22.02
Class version is 0.0.07.00/2016-03-28/just me
SQLite version is 3.13.0 https://www.dropbox.com/s/hstkw358ww8safl/sqlite3.dll
Code: http://p.ahkscript.org/?p=c302671d (#includes your Class library)
DB: https://www.dropbox.com/s/my17d93lxqkhsm2/proj.db
Host box vitals: http://i.imgur.com/rKV6utO.png
just me
Posts: 5431
Joined: 02 Oct 2013, 08:51
Location: Germany

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

29 Jul 2016, 01:17

Hello, the so called RecordSet object returned by Query relies on the Prepared Statement Object of SQLite. It does not support the property RowCount. HasRows is set depending on the result of an internal call of sqlite3_step.

You might want to try the GetTable method instead. It returns the whole result table including RowCount.
TodWulff
Posts: 7
Joined: 27 Jul 2015, 21:15

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

29 Jul 2016, 01:47

Thank you, kind sir (gender assumed). Much appreciated.

-t
just me
Posts: 5431
Joined: 02 Oct 2013, 08:51
Location: Germany

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

23 Dec 2016, 03:10

The EscapeStr method provides some basic escaping for single quotes and backslashes.(see sqlite3_mprintf). If you want more, you have to do it by yourself.
reynosa
Posts: 3
Joined: 18 May 2017, 05:37

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

19 May 2017, 04:36

Hello

I have a question regarding the SQLite Callback function. In the example it looks like this:

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

SQLiteExecCallBack(DB, ColumnCount, ColumnValues, ColumnNames) {
This := Object(DB)
MsgBox, 0, %A_ThisFunc%
, % "SQLite version: " . This.Version . "`n"
. "SQL statement: " . StrGet(A_EventInfo) . "`n"
. "Number of columns: " . ColumnCount . "`n"
. "Name of first column: " . StrGet(NumGet(ColumnNames + 0, "UInt"), "UTF-8") . "`n"
. "Value of first column: " . StrGet(NumGet(ColumnValues + 0, "UInt"), "UTF-8")
Return 0
}


Can someone give me an example of how to get the name and values of the second (and third,...) columns?

Thank you :-)
just me
Posts: 5431
Joined: 02 Oct 2013, 08:51
Location: Germany

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

25 May 2017, 09:36

Apparently I didn't update all parts of the example. The callback sample should be:

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

To get the second or another column, you have to multiply the 0-based column index by A_PtrSize and add the result to the passed pointer:

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

      . "Name of second column: " . StrGet(NumGet(ColumnNames + (1 * A_PtrSize), "UPtr"), "UTF-8") . "`n"
DanielToward13
Posts: 63
Joined: 18 May 2017, 10:56

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

31 May 2017, 09:55

How perform the below query in your library?

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

SELECT body FROM table1 WHERE user IN ('id1', 'id2', 'id3')


It would be nice if I could put the IDs in an array and then perform the query.
just me
Posts: 5431
Joined: 02 Oct 2013, 08:51
Location: Germany

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

31 May 2017, 16:01

  • Create a valid SQLite SQL statement.
  • Pass it either to GetTable() or Query()
  • Evaluate the result as shown in the sample script.

Return to “Scripts and Functions”

Who is online

Users browsing this forum: binghez, cocobanana and 17 guests