[Class] SQLiteDB - Update on 2022-10-04
Re: [Class] SQLiteDB - Update on 2019-12-12
the error message is nonsensical. u need to be checking ErrorLevel and A_LastError after the DllCall and only then can u determine what the nature of the error is, not outright assert its because a file couldnt be found
Re: [Class] SQLiteDB - Update on 2019-12-12
Thanks a million Hoppfrosch, I wouldn't have guessed that in a million years. I reinstalled ahk and it got rid of the error. Now onwards with the sqlite adventure. : )
-
- Posts: 144
- Joined: 01 Feb 2017, 22:57
Re: [Class] SQLiteDB - Update on 2019-12-12
Thanks just me for the great class I've been using since forever.
I found what seems to me to be a minor problem. I was able to successfully execute a query in DB Browser for SQLite but Class_SQLiteDB kept returning nothing via GetTable. Then after some investigation I finally realized that the cause is a rather trivial one. The query I used is a recursive query starting with "WITH" (to get all nodes directly or indirectly connected to a given node in a directed graph), and GetTable judged this not to be a valid query because it doesn't start with SELECT or PRAGMA
(more info: https://www.sqlite.org/lang_with.html)
Changing it to (SELECT|PRAGMA|WITH) probably won't cover all cases (there's also 'VALUES'?), but at least it should no longer exclude this case.
I found what seems to me to be a minor problem. I was able to successfully execute a query in DB Browser for SQLite but Class_SQLiteDB kept returning nothing via GetTable. Then after some investigation I finally realized that the cause is a rather trivial one. The query I used is a recursive query starting with "WITH" (to get all nodes directly or indirectly connected to a given node in a directed graph), and GetTable judged this not to be a valid query because it doesn't start with SELECT or PRAGMA
Code: Select all
WITH RECURSIVE nodes(x)
AS (
SELECT 'Node_1'
UNION
SELECT Destination_Node FROM Edges JOIN nodes ON Source_Node=x
)
SELECT x FROM nodes;
Code: Select all
If !RegExMatch(SQL, "i)^\s*(SELECT|PRAGMA)\s") {
This.ErrorMsg := A_ThisFunc . " requires a query statement!"
Return False
}
Re: [Class] SQLiteDB - Update on 2021-10-10
Hi @20170201225639, thx for the information. I removed the checks completely.
Re: [Class] SQLiteDB - Update on 2021-10-10
Completely? Do you just let the dll handle the failure propagating to AHK then?
Re: [Class] SQLiteDB - Update on 2021-10-10
This is just the Select statement overview (https://www.sqlite.org/syntax/select-stmt.html).
There are many more (https://www.sqlite.org/lang_expr.html)
I think you should rely on the error message SQLite returns and not intercept all the tens of possibilities with regex beforehand.
There are many more (https://www.sqlite.org/lang_expr.html)
I think you should rely on the error message SQLite returns and not intercept all the tens of possibilities with regex beforehand.
[AHK] v2.0.5 | [WIN] 11 Pro (Version 22H2) | [GitHub] Profile
Re: [Class] SQLiteDB - Update on 2021-10-10
Hm at one point I thought sqlite3_get_table only supported SELECT and PRAGMA statements. Maybe that’s changed.
Re: [Class] SQLiteDB - Update on 2016-03-28
hey Just Me, Is there a way to do the reverse? Export a DB as a CSV File?just me wrote: ↑07 Sep 2017, 02:11SQL As Understood By SQLite
You might try the following with a copy of your DB file:
- Create a new instance of the class SQLiteDB:
Code: Select all
DB := New SQLiteDB
- Open your DB file:
Code: Select all
If !DB.OpenDB(FullDBFilePath) { MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
- Because you want to change many values, start a transaction:
Code: Select all
If !DB.Exec("BEGIN TRANSACTION;") MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
- Read and parse your CSV file, within the parsing-loop try to use:
Code: Select all
SQL := "UPDATE table-name SET user_amount = " . CSV_amount . " WHERE user_name = '" . CSV_name . "';" ; replace table-name with your table name. If !DB.Exec(SQL) MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
- After the parsing-loop has finished, commit the transaction to update the DB file:
Code: Select all
If !DB.Exec("COMMIT TRANSACTION;") MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
- Close the DB file:
Code: Select all
If !DB.CloseDB() MsgBox, 16, SQLite Error, % "Msg:`t" . DB.ErrorMsg . "`nCode:`t" . DB.ErrorCode
- Check the result.
Re: [Class] SQLiteDB - Update on 2021-10-10
@jsong55:
https://www.sqlitetutorial.net/sqlite-tutorial/sqlite-export-csv/
or do the query within an AHK script, convert the result table to CSV, and write it to a file.
https://www.sqlitetutorial.net/sqlite-tutorial/sqlite-export-csv/
or do the query within an AHK script, convert the result table to CSV, and write it to a file.
Re: [Class] SQLiteDB - Update on 2021-10-10
Hahha I was lazy to code from scratch
but alright let me contribute here. Just in case someone wants to do the same
but alright let me contribute here. Just in case someone wants to do the same
Code: Select all
SalesDB := New SQLiteDB
SalesDB.OpenDB("C:\Users\Desktop\AHK Scripts\SalesData.db")
arg:="SELECT * FROM CustomerFollowUp"
ExportDB2Csv(SalesDB,arg,"OutputFile.csv")
ExportDB2Csv(MyDB,arg,DestFile,OverWrite:=0) ; arg is for the SELECT statement; set OverWrite to 1 to Overwrite the Destination file, or 0 to append
{
; Last Updated - 2022-01-05, 15:39:33 ; https://www.autohotkey.com/boards/viewtopic.php?f=6&t=1064&p=435741#p435741
If !MyDB.GetTable(arg, objTable)
{
MsgBox, % "SQLite GETTABLE Error`n`nMessage: " . MyDB.ErrorMsg . "`nCode: " . MyDB.ErrorCode . "`nQuery: " . arg ; error message
return
}
For i,v in objTable.ColumnNames
{
If (i=1)
line:= """" v """"
Else
line.= ",""" v """"
}
Table:=line
For i,v in objTable.Rows
{
line:=""
For p,q in v
{
If (p=1)
line:= """" q """"
Else
line.= ",""" q """"
}
Table.="`n" line
}
if (OverWrite=1)
FileDelete, % DestFile
FileAppend,% Table,% DestFile
}
Re: [Class] SQLiteDB - Update on 2021-10-10
Anyway, what I'm trying to do is parse a load of image files in a folder and add them to a database based on an ascending index that can be identical for two or more files. Everything works fine for only one file per index, but when two or more files use the same index adding another file (blob) to a row will nullify the preceding one.
I tried using the Prepare statement as per the sample script. In desperation even loaded all filenames in a variable and sorted it in case directly looping through the folder may have provided non-sequenced indexes. All to no avail. All I managed lately was to add two separate entries for the same index, which is not useful for the purpose of the script. Any idea where I could be going so wrong or what would be the correct approach?
Nevermind, finally figured it out. It's about the Step and Reset position.
Part of my AHK work can be found here.
Re: [Class] SQLiteDB - Update on 2021-10-10
Hello all, quick question, is there a "select" functionality in SQLiteDB? I mean can you switch between two or more tables to do next() commands and it gives you the next of the selected table? This is very useful when you are scanning one table and at each row you do something with the other table like next, search for something, update etc. and when you select back to the first table it is siting where it was prior to the switch. This is to save the state of each table pointers per selected table. If you know foxpro that would be the SELECT SOMETABLE function. BTW I have already implemented the foxpro skip in ahk which goes to the next record and binds all table fields to byref vars with same fieldnames. Those vars will then be available as locals to the calling function. It's made my code a lot cleaner. And thanks for creating this GREAT library.
Re: [Class] SQLiteDB - Update on 2021-10-10
Hey there @just me, finally getting around to trying prepared statements. Having some trouble with what seems to be an error binding (specifically) Text values.
The methods do not throw an error and the parameters insert as an empty string instead of the expected value.
Am I doing this wrong? Is this a bug? Both? (lol)
sample results (plus a couple runs configured to insert nulls so you see the difference):
Ints do work and I have not tried Blobs.
Thanks in advance!
The methods do not throw an error and the parameters insert as an empty string instead of the expected value.
Am I doing this wrong? Is this a bug? Both? (lol)
Code: Select all
#include <class_sqlitedb>
db := new sqlitedb
db.opendb(a_scriptdir "\dummy.db")
If !db.exec("CREATE TABLE IF NOT EXISTS tweets(id,user,msg);")
msgbox % "table create error"
;if !db.exec("DELETE FROM TWEETS;")
;msgbox % "delete error"
if !db.prepare("INSERT OR IGNORE INTO tweets(id,user,msg) VALUES(?,?,?);",prepareSt)
msgbox % "prepare error"
;generate unique set of parameters across multiple tests
basis := a_tickcount
If !prepareSt.bind(1, "Int", basis) ;works
msgbox % "error binding id"
;slot 2 works with Int, issue is not position related
;If !prepareSt.bind(2, "Int", (basis + 1))
;msgbox % "error binding user"
If !prepareSt.bind(2, "Text", "user-" basis) ;inserts blank string (not a NULL)
msgbox % "error binding user"
If !prepareSt.bind(3, "Text", "msg-" basis) ;same as above
msgbox % "error binding msg"
If !prepareSt.step() ;works
msgbox % "error on step"
If !prepareSt.reset() ;works
msgbox % "error on reset"
Ints do work and I have not tried Blobs.
Thanks in advance!
Re: [Class] SQLiteDB - Update on 2021-10-10
At line 401 in Class_SQLiteDB.ahk you have to remove the ByRef in the function call so it would become This._DB._StrToUTF8(Param3, UTF8). After that it'll work.
The issue has been opened on GitHub as a PR but there has been no reaction so far from the developer.
The issue has been opened on GitHub as a PR but there has been no reaction so far from the developer.
Part of my AHK work can be found here.
Re: [Class] SQLiteDB - Update on 2022-09-18
Bug fixed, thx @Drugwash.
Re: [Class] SQLiteDB - Update on 2022-09-18
You're welcome. BTW, that's not my PR - just happened to notice it as I follow the repository.
Part of my AHK work can be found here.
Re: [Class] SQLiteDB - Update on 2022-09-18
Another bug report for you @just me. This one is super arcane, too.
The DLL call to set ST.ParamCount on Line 922 is passing the wrong variable. Instead of This._Handle it should be Stmt.
The DLL call to set ST.ParamCount on Line 922 is passing the wrong variable. Instead of This._Handle it should be Stmt.
explanation with screenshots
Huge shoutout to @CloakerSmoker and @ShatterCoder for helping me track down the bug!