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.
[Class] SQLiteDB - Update on 2022-10-04
- CheshireCat
- Posts: 11
- Joined: 07 Mar 2016, 15:58
Re: [Class] SQLiteDB - Update on 2016-01-28
Hello CheshireCat,
I don't know a SQL statement or an API function which would export a database.
I don't know a SQL statement or an API function which would export a database.
- hoppfrosch
- Posts: 443
- Joined: 07 Oct 2013, 04:05
- Location: Rhine-Maine-Area, Hesse, Germany
- Contact:
Re: [Class] SQLiteDB - Update on 2016-01-28
Hi JustMe,
I used code-pieces out of your example to create a example database:
... 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:
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
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
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!!!!!!
....
}
Using a filename only rather than a fulyl qualified path does not work either.
Running AutoHotkey : v1.1.23.01 Unicode 32-bit
- hoppfrosch
- Posts: 443
- Joined: 07 Oct 2013, 04:05
- Location: Rhine-Maine-Area, Hesse, Germany
- Contact:
Re: [Class] SQLiteDB - Update on 2016-01-28
I solved my problem above, using
at the beginning of the script.
Why is this needed?
Code: Select all
#NoEnv
Why is this needed?
Re: [Class] SQLiteDB - Update on 2016-01-28
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:this is not the latest version.
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) {
- CheshireCat
- Posts: 11
- Joined: 07 Mar 2016, 15:58
Re: [Class] SQLiteDB - Update on 2016-01-28
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.
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.
Re: [Class] SQLiteDB - Update on 2016-01-28
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.
Re: [Class] SQLiteDB - Update on 2016-03-28
*Update on 2016-03-28*
Added PRAGMA support.
Added PRAGMA support.
- hoppfrosch
- Posts: 443
- Joined: 07 Oct 2013, 04:05
- Location: Rhine-Maine-Area, Hesse, Germany
- Contact:
Re: [Class] SQLiteDB - Update on 2016-03-28
I figured out how to GET a PRAGMA - but how to SET it?
What I tried:
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.
Re: [Class] SQLiteDB - Update on 2016-03-28
I would try the Exec method.
- hoppfrosch
- Posts: 443
- Joined: 07 Oct 2013, 04:05
- Location: Rhine-Maine-Area, Hesse, Germany
- Contact:
Re: [Class] SQLiteDB - Update on 2016-03-28
Aaaah ...
Thx
Thx
Re: [Class] SQLiteDB - Update on 2016-03-28
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:
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
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
}
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
Re: [Class] SQLiteDB - Update on 2016-03-28
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.
You might want to try the GetTable method instead. It returns the whole result table including RowCount.
Re: [Class] SQLiteDB - Update on 2016-03-28
Thank you, kind sir (gender assumed). Much appreciated.
-t
-t
Re: [Class] SQLiteDB - Update on 2016-03-28
How do I sanitize/escape user input before putting it into the SQL statement?
Re: [Class] SQLiteDB - Update on 2016-03-28
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.
Re: [Class] SQLiteDB - Update on 2016-03-28
Hello
I have a question regarding the SQLite Callback function. In the example it looks like this:
Can someone give me an example of how to get the name and values of the second (and third,...) columns?
Thank you
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
}
Thank you
Re: [Class] SQLiteDB - Update on 2016-03-28
Apparently I didn't update all parts of the example. The callback sample should be:
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
; ======================================================================================================================
; 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
}
Code: Select all
. "Name of second column: " . StrGet(NumGet(ColumnNames + (1 * A_PtrSize), "UPtr"), "UTF-8") . "`n"
-
- Posts: 74
- Joined: 18 May 2017, 10:56
Re: [Class] SQLiteDB - Update on 2016-03-28
How perform the below query in your library?
It would be nice if I could put the IDs in an array and then perform the query.
Code: Select all
SELECT body FROM table1 WHERE user IN ('id1', 'id2', 'id3')
Re: [Class] SQLiteDB - Update on 2016-03-28
- 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: yxldh and 48 guests