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

Post your working scripts, libraries and tools for AHK v1.1 and older
swagfag
Posts: 6222
Joined: 11 Jan 2017, 17:59

Re: [Class] SQLiteDB - Update on 2019-12-12

Post by swagfag » 03 May 2021, 06:59

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

pmobin
Posts: 17
Joined: 14 Apr 2016, 12:50

Re: [Class] SQLiteDB - Update on 2019-12-12

Post by pmobin » 03 May 2021, 13:37

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. : )

20170201225639
Posts: 144
Joined: 01 Feb 2017, 22:57

Re: [Class] SQLiteDB - Update on 2019-12-12

Post by 20170201225639 » 29 Sep 2021, 20:23

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

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;
(more info: https://www.sqlite.org/lang_with.html)

Code: Select all

If !RegExMatch(SQL, "i)^\s*(SELECT|PRAGMA)\s") {
	This.ErrorMsg := A_ThisFunc . " requires a query statement!"
	Return False
}
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.

just me
Posts: 9406
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB - Update on 2021-10-10

Post by just me » 10 Oct 2021, 04:55

Hi @20170201225639, thx for the information. I removed the checks completely.

User avatar
kczx3
Posts: 1640
Joined: 06 Oct 2015, 21:39

Re: [Class] SQLiteDB - Update on 2021-10-10

Post by kczx3 » 10 Oct 2021, 06:35

Completely? Do you just let the dll handle the failure propagating to AHK then?

User avatar
jNizM
Posts: 3183
Joined: 30 Sep 2013, 01:33
Contact:

Re: [Class] SQLiteDB - Update on 2021-10-10

Post by jNizM » 10 Oct 2021, 06:48

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.
[AHK] v2.0.5 | [WIN] 11 Pro (Version 22H2) | [GitHub] Profile

User avatar
kczx3
Posts: 1640
Joined: 06 Oct 2015, 21:39

Re: [Class] SQLiteDB - Update on 2021-10-10

Post by kczx3 » 10 Oct 2021, 13:48

Hm at one point I thought sqlite3_get_table only supported SELECT and PRAGMA statements. Maybe that’s changed.

jsong55
Posts: 218
Joined: 30 Mar 2021, 22:02

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

Post by jsong55 » 25 Dec 2021, 10:29

just me wrote:
07 Sep 2017, 02:11
:arrow: SQL 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.
hey Just Me, Is there a way to do the reverse? Export a DB as a CSV File?

just me
Posts: 9406
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB - Update on 2021-10-10

Post by just me » 26 Dec 2021, 05:58

@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.

:xmas:

jsong55
Posts: 218
Joined: 30 Mar 2021, 22:02

Re: [Class] SQLiteDB - Update on 2021-10-10

Post by jsong55 » 26 Dec 2021, 06:33

Hahha I was lazy to code from scratch

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
    }

User avatar
Drugwash
Posts: 850
Joined: 29 May 2014, 21:07
Location: Ploieşti, Romania
Contact:

Re: [Class] SQLiteDB - Update on 2021-10-10

Post by Drugwash » 08 Mar 2022, 11:51

Most likely my fault but can't seem to be able to add more than a blob to a row. Maybe I ate stupid mushrooms or something. :)
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?


Image

Nevermind, finally figured it out. It's about the Step and Reset position.
Part of my AHK work can be found here.

pmobin
Posts: 17
Joined: 14 Apr 2016, 12:50

Re: [Class] SQLiteDB - Update on 2021-10-10

Post by pmobin » 14 Mar 2022, 11:28

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.

just me
Posts: 9406
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB - Update on 2021-10-10

Post by just me » 16 Mar 2022, 03:57


Qriist
Posts: 81
Joined: 11 Sep 2016, 04:02

Re: [Class] SQLiteDB - Update on 2021-10-10

Post by Qriist » 17 Sep 2022, 20:52

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)

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"
sample results (plus a couple runs configured to insert nulls so you see the difference): Image


Ints do work and I have not tried Blobs.

Thanks in advance!

User avatar
Drugwash
Posts: 850
Joined: 29 May 2014, 21:07
Location: Ploieşti, Romania
Contact:

Re: [Class] SQLiteDB - Update on 2021-10-10

Post by Drugwash » 17 Sep 2022, 21:46

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.
Part of my AHK work can be found here.

Qriist
Posts: 81
Joined: 11 Sep 2016, 04:02

Re: [Class] SQLiteDB - Update on 2021-10-10

Post by Qriist » 17 Sep 2022, 22:13

Drugwash wrote:
17 Sep 2022, 21:46
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.
That worked! Thanks so much!

User avatar
Drugwash
Posts: 850
Joined: 29 May 2014, 21:07
Location: Ploieşti, Romania
Contact:

Re: [Class] SQLiteDB - Update on 2021-10-10

Post by Drugwash » 17 Sep 2022, 22:19

You're welcome. :)
Part of my AHK work can be found here.

just me
Posts: 9406
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB - Update on 2022-09-18

Post by just me » 18 Sep 2022, 03:39

Bug fixed, thx @Drugwash.

User avatar
Drugwash
Posts: 850
Joined: 29 May 2014, 21:07
Location: Ploieşti, Romania
Contact:

Re: [Class] SQLiteDB - Update on 2022-09-18

Post by Drugwash » 18 Sep 2022, 03:48

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.

Qriist
Posts: 81
Joined: 11 Sep 2016, 04:02

Re: [Class] SQLiteDB - Update on 2022-09-18

Post by Qriist » 19 Sep 2022, 18:20

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.
explanation with screenshots
Huge shoutout to @CloakerSmoker and @ShatterCoder for helping me track down the bug!

Post Reply

Return to “Scripts and Functions (v1)”