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

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

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

16 Mar 2019, 06:59

Hello!

I plan on creating a new script which will offer the user the possibility to search through an entire book, about 500 pages or so. Plain text. Organized by chapters and sections.

Is SQ lite a good idea to use for this? If not, what would be better suited?

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.
AHKStudent
Posts: 1053
Joined: 05 May 2018, 12:23

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

16 Mar 2019, 10:23

kczx3 wrote:
15 Mar 2019, 20:28
WHERE instr(frame, “will”) or instr(flame, “Jim”)
thank you
automatLife84
Posts: 23
Joined: 11 Jan 2019, 18:42

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

25 Mar 2019, 11:27

can i use it with access db .mdb ?
just me
Posts: 7728
Joined: 02 Oct 2013, 08:51
Location: Germany

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

26 Mar 2019, 02:46

Whenever Microsoft will use SQLite as the database engine used for Access .mdb files, yes.
noodleit
Posts: 1
Joined: 28 Mar 2019, 17:01

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

28 Mar 2019, 17:13

Hi just me. Thank you very much for the work you've done creating the class and examples. They work well for me so great job, I've found them very useful. The program I've made does what i want. I thought the job was done but!!! on compiling to an exe the program reports it can't find the DLL. It finds the dll fine as an ahk file. Perhaps I'm compiling in the wrong format?
just me
Posts: 7728
Joined: 02 Oct 2013, 08:51
Location: Germany

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

29 Mar 2019, 07:22

Per default, the script/exe tries to find the SQLite3.dll in the script's/exe's folder. This can be changed by creating a file SQLiteDB.ini in the script's/exe's folder with the following contents:

Code: Select all

[Main]
DllPath=FullyQualifiedPathToSQLite3.dll
Also, you have to supply the appropriate dll version for your exe (32/64-bit).
User avatar
hasantr
Posts: 772
Joined: 05 Apr 2016, 14:18
Location: İstanbul

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

17 May 2019, 07:19

I didn't find out how to transfer the results to my own ListView.
More examples?
Dixtroy
Posts: 4
Joined: 05 Jul 2019, 03:40

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

05 Jul 2019, 04:18

Hello, Ive just tried to develop the prepared mode, based on this class, and it works well on text and int. (files attached)

The idea is that, prepare 2 or more table for insert, do inserts, and close connection.

prepare.ahk is a short example:

Code: Select all

#Include Class_SQLiteDB.ahk

DBFileName :=A_Scriptdir "\test.db"

DB := new SQLiteDB

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

DB.Prepare( {  ID: 1
             , Q: "INSERT INTO t1 ( t, nu, i, r, no ) VALUES ( ?, ?, ?, ?, ? );"
	     , T: [ "text", "double", "int", "double", "blob" ]  } )

DB.Prepare( {  ID: 2
             , Q: "INSERT INTO t1 ( t, r, i ) VALUES ( ?, ?, ? );"
	     , T: [ "text", "double", "int" ] } )

DB.Step( {  ID: 1
		, D: [ ["AA", 0.113456789, 119561023, 1323436.12345, "adfasfasfafas"]
               , ["BB", 0.123456789, 219561023, 2323436.12345, "bdfasfasfafas"]
	       , ["CC", 0.323456789, 319561023, 3323436.12345, "cdfasfasfafas"]
		, ["DD", 0.423456789, 419561023, 4323436.12345, "ddfasfasfafas"] ] } )

										
DB.Step( {  ID: 2
		, D: [ ["AA", 0.113456789, 119561023]
               , ["BB", 0.123456789, 219561023]
		, ["CC", 0.323456789, 319561023]
		, ["DD", 0.423456789, 419561023] ] } )


DB.Finalize( {  ID: [ 1, 2 ] } )
It opens DB, prepare 2 table (in this case the table is same:) do inserts and close all connections.
The first prepared insert has lot of problems, the text looks like as first 2 letter ob blob value, double values goes to 0 or something wrong.
The second prepared insert works well on text and integer, but double goes to 0.0. It is maybe a data conversation problem.

Can someone help in this project?
Attachments
prepare.7z
(10.22 KiB) Downloaded 72 times
just me
Posts: 7728
Joined: 02 Oct 2013, 08:51
Location: Germany

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

05 Jul 2019, 07:18

Where did you find the syntax of the sqlite3_bind_...() functions?

:arrow: Binding Values To Prepared Statements:

  • Int:

    Code: Select all

    int sqlite3_bind_int(sqlite3_stmt*, int, int);
    Expects an integer value as the third argument (AHK type: "Int").
  • Double:

    Code: Select all

    int sqlite3_bind_double(sqlite3_stmt*, int, double);
    Expects a double value as the third argument (AHK type: "Double").
  • Blob:

    Code: Select all

    int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
    Expects 5 arguments. The third argument is a pointer to the blob data ("Ptr"). The fourth argument must be the size of the blob data in bytes ("Int"). The fifth argument ("Ptr") should be one of the constants SQLITE_STATIC (0) or SQLITE_TRANSIENT (-1).
  • Text:

    Code: Select all

    int sqlite3_bind_text(sqlite3_stmt*,int,const char*,int,void(*)(void*));
    Expects 5 arguments. The third argument is a pointer to an (UTF-8) string ("Ptr"). The fourth argument must be the size of the string in bytes or -1 for a zero terminated string ("Int"). For the fifth argument see Blob.
If your additions shall run in a 32-bit environment, you should add Cdecl to the DllCalls.
Dixtroy
Posts: 4
Joined: 05 Jul 2019, 03:40

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

08 Jul 2019, 01:47

My solution is based on https www.sqlite.org /c3ref/stmt.html prepared statement.
As you wrote the syntaxes, I had saw it, but it does not helps.
The problem was on timing, due to common UTF8 variable, the value must be converted to UTF8 (if it even double!), and when I started to use UTF8[1..n] variables for each binding, it works well.
I am on 64-bit, so it might be problem on 32-bit.

Code: Select all

    Step( P ) {
			Loop, % P.D.length()
      {
				r:=A_Index
				Loop, % This.ColumnTypes[ P.ID ].length()
				{
          This._StrToUTF8( P.D[ r ][ A_Index ] , UTF8%A_Index% )
					if( This.ColumnTypes[ P.ID ][ A_Index ] == "int" || This.ColumnTypes[ P.ID ][ A_Index ] == "double") {
						RC := DllCall( "C:\Program Files\AutoHotkey\Extras\DLL\SQLite3.dll\sqlite3_bind_" This.ColumnTypes[ P.ID ][ A_Index ]
												 , "Ptr"
												 , This.Stmt[ P.ID ]
												 , "Int"
												 , A_Index
												 , "Ptr"
												 , &UTF8%A_Index% )
					} else if( This.ColumnTypes[ P.ID ][ A_Index ] == "text" || This.ColumnTypes[ P.ID ][ A_Index ] == "blob" ) {
						RC := DllCall( "C:\Program Files\AutoHotkey\Extras\DLL\SQLite3.dll\sqlite3_bind_" This.ColumnTypes[ P.ID ][ A_Index ]
												 , "Ptr"
												 , This.Stmt[ P.ID ]
												 , "Int"
												 , A_Index
												 , "Ptr"
												 , &UTF8%A_Index%
												 , "Int"
												 , -1 )
					}
					 If (ErrorLeveL) {
							This.ErrorMsg := "DLLCall sqlite3_bind_" This.ColumnTypes[ P.ID ][ A_Index ] " failed!"
							This.ErrorCode := ErrorLevel
							MsgBox, 16, SQLite Error %A_ThisFunc%, % "Msg:`t" . This.ErrorMsg . "`nCode:`t" .  This.ErrorCode
							Return False
					 }
					 If (RC) {
							This.ErrorMsg := This._ErrMsg()
							This.ErrorCode := RC
							MsgBox, 16, SQLite Error %A_ThisFunc%, % "Msg:`t" . This.ErrorMsg . "`nCode:`t" .  This.ErrorCode
							Return False
					 }
         
         }
      RC := DllCall("C:\Program Files\AutoHotkey\Extras\DLL\SQLite3.dll\sqlite3_step"
              , "Ptr"
              , This.Stmt[ P.ID ] )
      If (ErrorLevel) {
         This.ErrorMsg := "DLLCall sqlite3_step failed!"
         This.ErrorCode := ErrorLevel
         MsgBox, 16, SQLite Error %A_ThisFunc%, % "Msg:`t" . This.ErrorMsg . "`nCode:`t" .  This.ErrorCode
         Return False
      }
      If (RC) && (RC <> This._ReturnCode("SQLITE_DONE")) {
         This.ErrorMsg := This._ErrMsg()
         This.ErrorCode := RC
         MsgBox, 16, SQLite Error %A_ThisFunc%, % "Msg:`t" . This.ErrorMsg . "`nCode:`t" .  This.ErrorCode
         Return False
      }
      
      RC := DllCall("C:\Program Files\AutoHotkey\Extras\DLL\SQLite3.dll\sqlite3_reset"
              , "Ptr"
              , This.Stmt[ P.ID ] )
      }
      
    }
just me
Posts: 7728
Joined: 02 Oct 2013, 08:51
Location: Germany

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

08 Jul 2019, 03:03

Are you sure that your last version is working as intended for Int and Double types?
IMO,it shouldn't. And I cannot see the need to convert BLOB data to UTF-8.

Related to reusing UTF8 variables see SQLITE_TRANSIENT.
gmanhunt
Posts: 18
Joined: 14 Mar 2018, 01:30

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

10 Jul 2019, 10:55

Hello,

Apologies for the silly question, but is there a way to make the script wait if a database is busy/being used and then retry?

I've taken a look at the source file, and I saw there's a SetTimeout(Timeout := 1000) function there. I'm assuming that if the database is busy, the script will wait 1000ms before trying again, and I did put that in my script but it is not working. I still have a few earlier entries that were missing from the database.

How I tested this was:
Script A -> Insert 1000 lines into Table 1, Column 1
Script B -> Insert 1000 lines into Table 1, Column 2
Script C -> Run Script A, Sleep 10, Run Script B

I'm just testing things out, before using it in my workplace.

Hopefully there's a solution to this.
User avatar
kczx3
Posts: 1250
Joined: 06 Oct 2015, 21:39

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

10 Jul 2019, 12:30

The problem is that just me never actually passes the timeout parameter to sqlite3_busy_timeout.
gmanhunt
Posts: 18
Joined: 14 Mar 2018, 01:30

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

11 Jul 2019, 03:16

kczx3 wrote:
10 Jul 2019, 12:30
The problem is that just me never actually passes the timeout parameter to sqlite3_busy_timeout.
Is there a way to pass the parameter to sqlite3_busy_timeout then?

EDIT:
SQLiteDB is way too advanced for me to modify, are there any other methods I can implement outside of modifying the source file? Is there a function that allows the script to wait for SQLite to be ready before db.exec()?
User avatar
kczx3
Posts: 1250
Joined: 06 Oct 2015, 21:39

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

11 Jul 2019, 06:47

The fix should be fairly straight forward.

Replace the SetTimeout method with this version:

Code: Select all

   ; ===================================================================================================================
   ; METHOD SetTimeout     Set the timeout to wait before SQLITE_BUSY or SQLITE_IOERR_BLOCKED is returned,
   ;                       when a table is locked.
   ; Parameters:           TimeOut     - Time to wait in milliseconds
   ; Return values:        On success  - True
   ;                       On failure  - False, ErrorMsg / ErrorCode contain additional information
   ; ===================================================================================================================
   SetTimeout(Timeout := 1000) {
      This.ErrorMsg := ""
      This.ErrorCode := 0
      This.SQL := ""
      If !(This._Handle) {
         This.ErrorMsg := "Invalid dadabase handle!"
         Return False
      }
      If Timeout Is Not Integer
         Timeout := 1000
      RC := DllCall("SQLite3.dll\sqlite3_busy_timeout", "Ptr", This._Handle, "Int", Timeout, "Cdecl Int")
      If (ErrorLevel) {
         This.ErrorMsg := "DLLCall sqlite3_busy_timeout failed!"
         This.ErrorCode := ErrorLevel
         Return False
      }
      If (RC) {
         This.ErrorMsg := This._ErrMsg()
         This.ErrorCode := RC
         Return False
      }
      Return True
   }
gmanhunt
Posts: 18
Joined: 14 Mar 2018, 01:30

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

11 Jul 2019, 07:37

kczx3 wrote:
11 Jul 2019, 06:47
The fix should be fairly straight forward.

Replace the SetTimeout method with this version:

Code: Select all

   ; ===================================================================================================================
   ; METHOD SetTimeout     Set the timeout to wait before SQLITE_BUSY or SQLITE_IOERR_BLOCKED is returned,
   ;                       when a table is locked.
   ; Parameters:           TimeOut     - Time to wait in milliseconds
   ; Return values:        On success  - True
   ;                       On failure  - False, ErrorMsg / ErrorCode contain additional information
   ; ===================================================================================================================
   SetTimeout(Timeout := 1000) {
      This.ErrorMsg := ""
      This.ErrorCode := 0
      This.SQL := ""
      If !(This._Handle) {
         This.ErrorMsg := "Invalid dadabase handle!"
         Return False
      }
      If Timeout Is Not Integer
         Timeout := 1000
      RC := DllCall("SQLite3.dll\sqlite3_busy_timeout", "Ptr", This._Handle, "Int", Timeout, "Cdecl Int")
      If (ErrorLevel) {
         This.ErrorMsg := "DLLCall sqlite3_busy_timeout failed!"
         This.ErrorCode := ErrorLevel
         Return False
      }
      If (RC) {
         This.ErrorMsg := This._ErrMsg()
         This.ErrorCode := RC
         Return False
      }
      Return True
   }
Hello, the fix kinda works!
I ran 10 scripts writing 1000 entries each (950 more that what I need) between 70ms of each other worked flawlessly. For all intents and purposes that solved my problem, plus I have a better understanding of DllCall() now I think, so thank you very much!.

EDIT:
Okay, I've made some dumb mistakes, now it works perfectly, even at 1ms. Thanks kczx3!
just me
Posts: 7728
Joined: 02 Oct 2013, 08:51
Location: Germany

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

12 Jul 2019, 08:07

*Update on 2019-07-12*
xh4528
Posts: 3
Joined: 29 Jul 2019, 05:09

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

12 Aug 2019, 04:58

I want to use x86 sqlite.dll

i download sqlite version for "sqlite-dll-win32-x86-3290000.zip" And unzipped to the ScriptDir, When I run "SQLiteDB_sample.ahk" with Unicode 64-bit, Tips "sqlite3.dll does not exist"

when i run "SQLiteDB_sample.ahk" with Unicode 32-bit,Tips "#include file Class_SQLiteDB.ahk cannot be opened"

(Files are all in the same folder)

please help me, thanks
xh4528
Posts: 3
Joined: 29 Jul 2019, 05:09

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

12 Aug 2019, 05:12

xh4528 wrote:
12 Aug 2019, 04:58
I want to use x86 sqlite.dll

i download sqlite version for "sqlite-dll-win32-x86-3290000.zip" And unzipped to the ScriptDir, When I run "SQLiteDB_sample.ahk" with Unicode 64-bit, Tips "sqlite3.dll does not exist"

when i run "SQLiteDB_sample.ahk" with Unicode 32-bit,Tips "#include file Class_SQLiteDB.ahk cannot be opened"

(Files are all in the same folder)

please help me, thanks


I've solved it and it's working properly.

Modified line 20 of SQLiteDB_sample.ahk

Code: Select all

#Include, %A_ScriptDir%\Class_SQLiteDB.ahk
User avatar
hasantr
Posts: 772
Joined: 05 Apr 2016, 14:18
Location: İstanbul

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

19 Aug 2019, 06:39

' and " I need to enter some text into the Sqlite database that contains a large number of marks. But I get a mistake. I can't duplicate these signs because sometimes there are hundreds.

I cannot enter the following sample text into the sqlite database. Because there are cut marks.
Test"sample"test'sample"1:12'3""4'5%8'''99[ghhh[[58]{{fgfg]]]]))))

Return to “Scripts and Functions”

Who is online

Users browsing this forum: jacek678 and 16 guests