Update SQLite database

[BryanWall]

I am trying to update a blob filled with hex. An individual blob can contain as much as 8,388,616 bytes. 

I can connect and read from the database just fine, however my attempts at writing to it aren't working. I have tried updating a string in another table just to make sure this isn't a blob issue and that wasn't successful either. 

The database I am trying to update is a 3rd party one using sqlite as a base. I can view and run queries on it using DB Browser for SQLite.

The exec statement returns 21 if that means anything.

In case it matters, when the query and fetchdata rows are left there, the close line seems to trigger "unable to close due to unfinalized statements or unfinished backups" error message in the console.

I can put the exact same update statements in DB Browser and execute them just fine. 

expandcollapse popup

#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <File.au3>


Global $blob
Global $TPDB = "D:\Applanix test\Demo\DR\TC.TPDB"
Global $BlobSource = "D:\Applanix test\AutomationTest\TPDBBLOB 1901 338 0.txt"
Global $hQuery
Global $aRow
Global $sqlOut

_SQLite_Startup()
If @error Then
        ConsoleWrite(@error & @CRLF)
        MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite3.dll Can't be Loaded!")
        Exit -1
EndIf
if FileExists($TPDB) Then
    $DBhndl = _SQLite_Open($TPDB)
    ConsoleWrite(@error & @CRLF)
    ConsoleWrite("Handle:" & $DBhndl & @CRLF)
EndIf

$sql = 'Select M.* ' _
    & 'From Measurement as M where drive_link_id = 339 and chunk_num = 1 '
;ConsoleWrite($sql & @CRLF)
_SQLite_Query($DBhndl,$sql,$hQuery)
_SQLite_FetchData($hQuery,$aRow)
;filewrite("D:\Bryan\Applanix test\Demo\DR\" & $aRow[0] & " " & $aRow[1] & " " & $aRow[3] & ".txt",$aRow[2])
;_ArrayDisplay($aRow)

$sql = "update measurement " & _
        "set samples = X'0004000000000020038d91f7c0571137e2c55c96404160b0856e696ac21f28f6c29e3852c29f70a4038d91fdc0571137e2c55c96404160b0856e696ac21d0000c29be148c29d7ae1' " & _
        "where drive_link_id = 339 and chunk_num = 0 "
;$sql = 'Update drive Set name = "YES" where id = 317'


_SQLite_Exec($TPDB,$sql)

_SQLite_Close()

 

[jchd]

51 minutes ago, BryanWall said:

In case it matters, when the query and fetchdata rows are left there, the close line seems to trigger "unable to close due to unfinalized statements or unfinished backups" error message in the console.

Indeed, the statement handle has not been finalized, hence the error.

Simple fix:

$sql = 'Select M.* ' _
    & 'From Measurement as M where drive_link_id = 339 and chunk_num = 1 '
;ConsoleWrite($sql & @CRLF)
_SQLite_Query($DBhndl,$sql,$hQuery)
_SQLite_FetchData($hQuery,$aRow)
_SQLite_QueryFinalize($hQuery)      ; <<<<<<<<<<<<<<
;filewrite("D:\Bryan\Applanix test\Demo\DR\" & $aRow[0] & " " & $aRow[1] & " " & $aRow[3] & ".txt",$aRow[2])
;_ArrayDisplay($aRow)

$sql = "update measurement " & _
        "set samples = X'0004000000000020038d91f7c0571137e2c55c96404160b0856e696ac21f28f6c29e3852c29f70a4038d91fdc0571137e2c55c96404160b0856e696ac21d0000c29be148c29d7ae1' " & _
        "where drive_link_id = 339 and chunk_num = 0 "
;$sql = 'Update drive Set name = "YES" where id = 317'      ; ###########   'YES'

_SQLite_Exec($TPDB,$sql)

_SQLite_Close()

That's why I recommend using _SQLite_GetTable[2d] or similar instead of *_Query + *_FetchData + *_QueryFinalize.

Also _SQLite_FetchData() has to be set in a loop to fill an array if ever more than one row are selected. Finaly, SQL strings use single quotes; double quotes are for schema names.

Edited January 15, 2024 by jchd
Fixed typo

[BryanWall]

"error: _SQLite_Finalize(): undefined function."

**** Edit

Looked through the list and I assume you mean _SQLite_QueryFinalize(). That does resolve the console error related to the close line.

*** end edit

Also, any ideas on how to solve the main issue of the update not running? Even if I comment out the query/fetch rows, while the close line doesn't generate an error, the exec line doesn't work. 

Edited January 15, 2024 by BryanWall

[jchd]

Sorry for the typo (now fixed).

What is the exact SQL definition of table measurement? Any trigger or constraint on it?

What are the error code returned by the exec and the message printed in Scite console?

[Zedna]

There is missing semicolon at the end of each SQL command -> I'm not sure if it is optional or not.

Also there is missing COMMIT;

[BryanWall]

Quote

What is the exact SQL definition of table measurement? Any trigger or constraint on it?

Here you go, and not that I'm seeing. Or to be more accurate, the only constraint is the primary key one. 

There are 0 triggers, views, or indices.

CREATE TABLE "measurement" (
    "id"    INTEGER,
    "drive_link_id"    INTEGER,
    "samples"    BLOB,
    "chunk_num"    INTEGER,
    PRIMARY KEY("id" AUTOINCREMENT)
);

 

and 

20 minutes ago, Zedna said:

There is missing semicolon at the end of each SQL command -> I'm not sure if it is optional or not.

Also there is missing COMMIT;

I updated relevant parts to below, no change. And the _SQLLite_Exec returns 21

$sql = "Update drive Set name = 'YES' where id = 317; " & @CRLF & _
        "Commit;"


ConsoleWrite(_SQLite_Exec($TPDB,$sql) & @CRLF)

*** Edit:

Also, @error is 0 after the.... correction, 0 with console wrap but 2 without it. 

So " Call prevented by SafeMode"

Edited January 15, 2024 by BryanWall

[Zedna]

Trt to run _SQLite_ErrMsg() to get more detailed error message.

[BryanWall]

"Library used incorrectly" Is the result. 

[BryanWall]

Also, I just wrote a small test code in python and it updated the database just fine. 

I guess worst case scenario I can create a .py or .exe to handle this part but I'd rather not if I don't have to. Seems like this should be something autoit should be able to do just fine.

[BryanWall]

And when I try to change it to the below, I get an out of memory error.

$sql = "Update drive Set name = 'YES' where id = 317; "

_SQLite_SafeMode(False)
_SQLite_Exec($TPDB,$sql)
ConsoleWrite(@error & @CRLF)
ConsoleWrite(_SQLite_ErrMsg($TPDB) & @CRLF)

 

[jchd]

Don't play with safemode. Rather check error return of every invokation to the library. Post new simplified code which fails and (if possible) your DB if there is no privacy concern so I can replicate your issue.

[Zedna]

Look here:

https://www.sqlite.org/limits.html

especially at point 1. and 3. and  sqlite3_bind_XXXX() part ...

 

1.

Quote

The current implementation will only support a string or BLOB length up to 231-1 or 2147483647. And some built-in functions such as hex() might fail well before that point.

 

3.

Quote

 

Maximum Length Of An SQL Statement

The maximum number of bytes in the text of an SQL statement is limited to SQLITE_MAX_SQL_LENGTH which defaults to 1,000,000,000.

If an SQL statement is limited to be a million bytes in length, then obviously you will not be able to insert multi-million byte strings by embedding them as literals inside of INSERT statements. But you should not do that anyway. Use host parameters for your data. Prepare short SQL statements like this:

INSERT INTO tab1 VALUES(?,?,?);

Then use the sqlite3_bind_XXXX() functions to bind your large string values to the SQL statement. The use of binding obviates the need to escape quote characters in the string, reducing the risk of SQL injection attacks. It also runs faster since the large string does not need to be parsed or copied as much.

...

 

 

Edited January 16, 2024 by Zedna

[BryanWall]

5 hours ago, jchd said:

Rather check error return of every invokation to the library. Post new simplified code which fails and ...

There are likely security concerns so I won't be able to post copy of TPDB. 

Also, I have no idea how to check ever invocation to the library or to simplify the code at issue. 

..... and as I was posting a version without the excess select calls and the like, wondering how to simplify the one sql and exec line that was failing..... I realized the issue.

I had this:

_SQLite_Exec($$TPDB,$sql)

Instead of this:

_SQLite_Exec($DBhndl,$sql) 

 

[Zedna]

If you work only with 1 DB in your script then you can use -1 (use last opened database) like this:

_SQLite_Exec(-1,$sql) 

Edited January 16, 2024 by Zedna

[BryanWall]

@Zedna It may just do one db, but I may end up splitting this into a separate au3 file and run them in parallel depending on the time it takes since the overall process will need to update a few sites worth of data across 1 to 4ish dbs. So in case it matters for that, I was inclined to use the specific reference to avoid potential issues.

 

For 3 from your earlier post... it is greek to me without practical examples. 

My initial intent is to take the .txt I am converting into hex to inject into/replace the existing blob, convert it line by line, then add to prior input. 

So roughly 

$sql = "update measurement " & _
        "set samples = X'" & $InitialBytes & "' " & _
        "where drive_link_id = 339 and chunk_num = 0; "

while 1
    $line = FileReadline($fHndl)
    if @error = -1 then Exit
    $HexInj = ConverttoHex($line)
    $sql = "update measurement " & _
        "set samples = samples || X'" & HexInj & "' " & _
        "where drive_link_id = 339 and chunk_num = 0; "

    _SQLite_Exec($DBhndl,$sql)
wend

Would this work and/or work about as quickly or is that greek way going to be better?

[Zedna]

According this:

https://www.sqlite.org/c3ref/blob_write.html

I think that for working with BLOB column you have to use specific SQLLite/SQL commands and not generic EXEC + UPDATE table SET col = '...'

But I'm not 100% sure as I don't work with BLOBs in my SQLite AU3 projects.

Also look at _SQLite_Encode() in helpfile -> there is also example (INSERT) for working with BLOBs

 

Edited January 16, 2024 by Zedna

[Zedna]

Search this forum for +SQLite +Bind

here is some relevant result giving idea to right way, but not exact solution for your UPDATE

 

[Zedna]

Also look here:

https://www.sqlite.org/c3ref/blob.html

https://www.sqlite.org/c3ref/blob_open.html

 

Edited January 16, 2024 by Zedna

[BryanWall]

I will look into those. Thank you for the input.

[jchd]

FYI, here's a little piece of code demonstrating that everything should work as intended.

expandcollapse popup

#include <SQLite.au3>

Local $TPDB = "Test.sq3"

_SQLite_Startup()
If @error Then
        ConsoleWrite(@error & @CRLF)
        MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite3.dll Can't be Loaded!")
        Exit -1
EndIf

Local $DBhndl = _SQLite_Open($TPDB)
ConsoleWrite(@error & @CRLF)

Local $sql = _
"drop table measurement;" & _
"CREATE TABLE measurement (" & _
    "id INTEGER PRIMARY KEY," & _
    "drive_link_id INTEGER," & _
    "samples BLOB," & _
    "chunk_num INTEGER" & _
");"
_SQLite_Exec($DBhndl, $sql)

$sql = _
"insert into measurement (drive_link_id, samples, chunk_num) values " & _
    "(330, ""X'0004000000000020038d91f7c0571137e2c55c96404160b0856e696ac'"", 0)," & _
    "(331, ""X'000400000789f020038d91f7c0571137e2c55c96404160b0856e696ac'"", 0)," & _
    "(332, ""X'0006543000000020038d91f7c0571137e2c55c96404160b0856e696ac'"", 2)," & _
    "(333, ""X'0004000abcd00020038d91f7c0571137e2c55c96404160b0856e696ac'"", 7)," & _
    "(338, ""X'00040000000007d8b40d91f7c0571137e2c55c96404160b0856e696ac'"", 1)," & _
    "(339, ""X'0004000000000020038d9aaaaaaaa137e2c55c96404160b0856e696ac'"", 0);"
_SQLite_Exec($DBhndl, $sql)

$sql = "Select * From Measurement where drive_link_id = 338 and chunk_num = 1;"
Local $aRows, $iRows, $iCols
_SQLite_GetTable2D($DBhndl, $sql, $aRows, $iRows, $iCols)
_ArrayDisplay($aRows)

$sql = "update measurement set " & _
        "samples = ""X'fedcba9876543210'"", " & _
        "chunk_num = 23 " & _
        "where drive_link_id = 338 and chunk_num = 1;"
_SQLite_Exec($DBhndl, $sql)

$sql = "Select * From Measurement where drive_link_id = 338;"
_SQLite_GetTable2D($DBhndl, $sql, $aRows, $iRows, $iCols)
_ArrayDisplay($aRows)

_SQLite_Close($DBhndl)
_SQLite_Shutdown()

BTW you don't need autoincerment.