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

Post your working scripts, libraries and tools
robodesign
Posts: 714
Joined: 30 Sep 2017, 03:59
Facebook: marius.sucan
GitHub: mariussucan
Location: Romania
Contact:

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

21 Nov 2020, 14:58

kczx3 wrote:
21 Nov 2020, 12:03
I don’t think that applies. First, connections to the same database by different AHK processes is not the same as multiple connections across threads. Second, it looks as though SQLite is compiled in a thread safe manner by default.
True. I have different AHK processes. And, yes - it is compiled to be thread safe. So, in theory, it should work. And I commented RecordSet.Free(), everything works fine. It is just that line that seems to cause issues. I also updated to the latest SqlLite3.dll, and it is all the same. It crashes. Any suggestions? Based on my understanding, one should serialize the database connection[s).

Best regards, Marius.
-------------------------
KeyPress OSD v4: GitHub or forum. (presentation video)
Quick Picto Viewer: GitHub or forum.
AHK GDI+ expanded / compilation library (on GitHub)
My home page.
just me
Posts: 7429
Joined: 02 Oct 2013, 08:51
Location: Germany

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

22 Nov 2020, 05:13

Hi,

would you please provide a code sample showing how you process the record sets?

Just out of curiosity, why do you need multipe scripts working on the same database file simultaneously?
robodesign
Posts: 714
Joined: 30 Sep 2017, 03:59
Facebook: marius.sucan
GitHub: mariussucan
Location: Romania
Contact:

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

22 Nov 2020, 08:45

Hello

Code: Select all

; main thread opens the database like this:
  activeSQLdb := new SQLiteDB
  If !activeSQLdb.OpenDB(fileName)
     Return -1


; each thread opens the database in this manner:
    activeSQLdb := new SQLiteDB
    If activeSQLdb.OpenDB(sqlDBfile, "R", 0)
       ExitApp

; each thread executes this loop:

Loop, % count
    retrieveSQLdbEntryImgHisto(filesArray[A_Index, 1])


; ===================
retrieveSQLdbEntryImgHisto(fullPath, imgIndex) {
  RecordSet := ""
  SplitPath, fullPath, fileNamu, imgPath
  activeSQLdb.EscapeStr(imgPath)
  activeSQLdb.EscapeStr(fileNamu)
  SQL := "SELECT imgmedian, imgavg, imghpeak, imghlow FROM images WHERE (imgfile=" fileNamu " AND imgfolder=" imgPath ");"
  If !activeSQLdb.Query(SQL, RecordSet)
     Return 0

  obju := []
  Loop
  {
     If (Row[1] || Row[2] || Row[3])
     {
        obju.median := Row[1]
        obju.avg := Row[2]
        obju.peak := Row[3]
        obju.low := Row[4]
     }
     RC := RecordSet.Next(Row)
  } Until (RC<1)

  RecordSet.Free()
  if (obju.median>0 || obju.avg>0 || obju.peak>0)
  {
     filesArray[imgIndex, 2] := obju
  } Else
  {
    ; load image file and blah blah 
  }
}

Please note, single threaded, i never experienced crashes.

The database is created in a different session with this structure:

Code: Select all

   activeSQLdb := new SQLiteDB
   activeSQLdb.OpenDB(newFileNamu)

   SQL := "CREATE TABLE images (imgfile TEXT COLLATE NOCASE, imgfolder TEXT COLLATE NOCASE, fsize NUMERIC, fmodified NUMERIC, fcreated NUMERIC, imgwidth NUMERIC, imgheight NUMERIC, imgframes NUMERIC, imgpixfmt TEXT, imgmedian NUMERIC, imgavg NUMERIC, imghpeak NUMERIC, imghlow NUMERIC, PRIMARY KEY(imgfolder ASC, imgfile ASC));"
   SQL .= "CREATE TABLE imagesData (imgfile TEXT COLLATE NOCASE, imgCaption TEXT, imgAudio TEXT COLLATE NOCASE, PRIMARY KEY(imgfile ASC));"
   SQL .= "CREATE TABLE dynamicfolders (imgfolder TEXT COLLATE NOCASE, fmodified NUMERIC, PRIMARY KEY(imgfolder ASC));"
   SQL .= "CREATE TABLE staticfolders (imgfolder TEXT COLLATE NOCASE, fmodified NUMERIC, PRIMARY KEY(imgfolder ASC));"
   SQL .= "CREATE TABLE settings (paramz TEXT COLLATE NOCASE, valuez TEXT COLLATE NOCASE, PRIMARY KEY(paramz ASC, valuez ASC));"
   If !activeSQLdb.Exec(SQL)
      Return activeSQLdb.ErrorMsg
The main database for tests contains 350 000 records, but i can create others smaller or bigger...

The purpose is: cache data collected from images [done, works fine] and then when needed, reuse it such that the files are not reloaded again - to have everything go much faster. The issue arrises when I spawn new instances to ping the database to see if the infos are cached or not.

Best regards, Marius.
-------------------------
KeyPress OSD v4: GitHub or forum. (presentation video)
Quick Picto Viewer: GitHub or forum.
AHK GDI+ expanded / compilation library (on GitHub)
My home page.
User avatar
kczx3
Posts: 1197
Joined: 06 Oct 2015, 21:39

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

22 Nov 2020, 10:13

Now you are saying threads... which is it? Separate processes or threads?
robodesign
Posts: 714
Joined: 30 Sep 2017, 03:59
Facebook: marius.sucan
GitHub: mariussucan
Location: Romania
Contact:

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

22 Nov 2020, 10:27

kczx3 wrote:
22 Nov 2020, 10:13
Now you are saying threads... which is it? Separate processes or threads?
Apologies for the misuse of the word(s). What I mean is new AHK_L or AHK_H processes. That is what I spawn.

Best regards, Marius.
-------------------------
KeyPress OSD v4: GitHub or forum. (presentation video)
Quick Picto Viewer: GitHub or forum.
AHK GDI+ expanded / compilation library (on GitHub)
My home page.
just me
Posts: 7429
Joined: 02 Oct 2013, 08:51
Location: Germany

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

22 Nov 2020, 11:59

I cannot see any apparent error in your script. Are you sure that the call of RecordSet.Free() within retrieveSQLdbEntryImgHisto() is causing the crash?
robodesign
Posts: 714
Joined: 30 Sep 2017, 03:59
Facebook: marius.sucan
GitHub: mariussucan
Location: Romania
Contact:

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

22 Nov 2020, 12:37

just me wrote:
22 Nov 2020, 11:59
I cannot see any apparent error in your script. Are you sure that the call of RecordSet.Free() within retrieveSQLdbEntryImgHisto() is causing the crash?
Yes, i get continuable exception error msgbox that indicates the line mentioned previously with the dllcall pertaining to that function... I even tried adding the flag for fullnomutex in the openv2 function, but it acts the same (it crashes). If i comment RecordSet.Free(), I get no crashes.

Anyways, i have abandoned this approach. I will try another approach to my endeavour.

Thank you for your interest in helping me.

Best regards, Marius.
-------------------------
KeyPress OSD v4: GitHub or forum. (presentation video)
Quick Picto Viewer: GitHub or forum.
AHK GDI+ expanded / compilation library (on GitHub)
My home page.
just me
Posts: 7429
Joined: 02 Oct 2013, 08:51
Location: Germany

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

23 Nov 2020, 04:15

robodesign wrote:Yes, i get continuable exception error msgbox ...
That's an AHK_H error, isn't it? If so, what happens if you run AHK_L?
robodesign
Posts: 714
Joined: 30 Sep 2017, 03:59
Facebook: marius.sucan
GitHub: mariussucan
Location: Romania
Contact:

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

23 Nov 2020, 05:24

just me wrote:
23 Nov 2020, 04:15
robodesign wrote:Yes, i get continuable exception error msgbox ...
That's an AHK_H error, isn't it? If so, what happens if you run AHK_L?
It just crashes. You can try it on your end as well ... Make a huge database of files on your system and then try run multiple instances of ahk_l on the same database / table to read randomly some records from it. Let me know if it works.

Best regards, Marius.
-------------------------
KeyPress OSD v4: GitHub or forum. (presentation video)
Quick Picto Viewer: GitHub or forum.
AHK GDI+ expanded / compilation library (on GitHub)
My home page.

Return to “Scripts and Functions”

Who is online

Users browsing this forum: AshleyM0101, Bing [Bot] and 23 guests