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

Post your working scripts, libraries and tools for AHK v1.1 and older
User avatar
CheshireCat
Posts: 11
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: 9423
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: 443
Joined: 07 Oct 2013, 04:05
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

#Include %A_ScriptDir%\..\lib\SQLiteDB\Class_SQLiteDB.ahk

DB := new SQLiteDB

DBFileName := A_ScriptDir . "\test.db"
If FileExist(DBFileName) {
   FileDelete, %DBFileName%
}

If !DB.OpenDB(DBFileName) {
   MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
   ExitApp
}

SQL := "CREATE TABLE Test (Name, Fname, Phone, Room, PRIMARY KEY(Name ASC, FName ASC));"
If !DB.Exec(SQL)
   MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode

DB.Exec("BEGIN TRANSACTION;")
SQLStr := ""
_SQL := "INSERT INTO Test VALUES('Name#', 'Fname#', 'Phone#', 'Room#');"
Loop, 1000 {
   StringReplace, SQL, _SQL, #, %A_Index%, All
   SQLStr .= SQL
}
If !DB.Exec(SQLStr)
   MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
DB.Exec("COMMIT TRANSACTION;")
SQLStr := ""

If !DB.CloseDB()
   MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
... 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

; 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: 443
Joined: 07 Oct 2013, 04:05
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

Code: Select all

#NoEnv
at the beginning of the script.
Why is this needed?
just me
Posts: 9423
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

OpenDB(DBPath, Access = "W", Create = True) {
this is not the latest version.
User avatar
CheshireCat
Posts: 11
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: 9423
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: 9423
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: 443
Joined: 07 Oct 2013, 04:05
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

; 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: 9423
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: 443
Joined: 07 Oct 2013, 04:05
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

_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: 9423
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
geek
Posts: 1052
Joined: 02 Oct 2013, 22:13
Location: GeekDude
Contact:

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

22 Dec 2016, 18:17

How do I sanitize/escape user input before putting it into the SQL statement?
just me
Posts: 9423
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

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: 9423
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

; ======================================================================================================================
; Exec() callback function sample
; ======================================================================================================================
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, "UPtr"), "UTF-8") . "`n"
      . "Value of first column: " . StrGet(NumGet(ColumnValues + 0, "UPtr"), "UTF-8")
   Return 0
}
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

      . "Name of second column: " . StrGet(NumGet(ColumnNames + (1 * A_PtrSize), "UPtr"), "UTF-8") . "`n"
DanielToward13
Posts: 74
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

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: 9423
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 (v1)”

Who is online

Users browsing this forum: No registered users and 114 guests