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

[robodesign]

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.

[AHKStudent]

WHERE instr(frame, “will”) or instr(flame, “Jim”)

thank you

[automatLife84]

can i use it with access db .mdb ?

[just me]

Whenever Microsoft will use SQLite as the database engine used for Access .mdb files, yes.

[noodleit]

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]

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

[hasantr]

I didn't find out how to transfer the results to my own ListView.
More examples?

[Dixtroy]

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?

[just me]

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

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]

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]

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]

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.

[kczx3]

The problem is that just me never actually passes the timeout parameter to sqlite3_busy_timeout.

[gmanhunt]

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()?

[kczx3]

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]

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]

*Update on 2019-07-12*

[xh4528]

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]

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

[hasantr]

' 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]]]]))))