How to tell why sqlite update is failing?

[ShaneClark12]

Short version is I have a script that converts a string file to hex and updates blob (no control over design, it is legacy). Most of the times it works just fine. On one time running it, with less data than other updates, it fails without throwing any errors I can tell. 

Side note, there is a lot of data so I write the hex to a file, close it when ready to update, and use FileRead.

I can copy/past the code and run the same exact update statement with the same file, and it works fine. 

In all cases, $SQLITE_OK is the result of running _SQLite_Exec but no length check fails. 

I didn't include it below but I have tried adding a retry Do until loop with closing the database and sqlite and restarting it but it all fails.

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

        ;open tpdb
        $DBhndl = _SQLite_Open($TPDB)
        if @error or $DBhndl = 0 Then
            wLog(1,"Couldn't open:" & $TPDB)
            Exit
        EndIf
        $Drive_link_id = 346
        $Chunk = 4
        $HexTemp = "D:\346-4.txt"

        $sql = "Update measurement " & _
                "set samples = x'" & Fileread($HexTemp) & "' " & _
                "where drive_link_id = " & $Drive_link_id & " and chunk_num = " & $Chunk & "; "
            if _SQLite_Exec($DBhndl,$sql) <> $SQLITE_OK Then
                wLog(1,"ERROR updating TC blob: " & $Drive_link_id & "-" & $Chunk)
            Else ;success
                $sql = "Select length(samples) from measurement " & _
                    "where drive_link_id = " & $Drive_link_id & " and chunk_num = " & $Chunk & "; "
                $iRval = _SQLite_GetTableData2D($DBhndl, $sql, $aResult, $iRows, $aNames)
;~                 wLog(1,"TPDB for " & $DriveTC & " is " & $TPDB)
                if $iRval = $SQLITE_OK Then ;sql ran ok
                    If $aResult[0][0] = 0 Then
                        wLog(1,"ERROR Drive link and chunk not written:" & $Drive_link_id & "-" & $Chunk)
;~                         FileCopy($HexTemp,StringReplace($HexTemp,".txt","InjErr" & $Drive_link_id & "-" & $Chunk & ".txt"))
                        MsgBox(0,"ERROR", "Make sure copy of " & $HexTemp & " Exists for " & $Drive_link_id & "-" & $Chunk  )
                    EndIf
        ;~             wLog(1,"$Drive_link_id:" & $Drive_link_id)
                Else
                    wLog(1,"Error running: " & $sql)
                    Exit
                EndIf
;~                 wLog(1,"Rows written")

            EndIf

        ;close TC file
    FileClose($TCfhndl)

    ;close tpdb connection and sqlite
    _SQLite_Close($DBhndl

[SmOke_N]

"SELECT changes();"

You can run that before and after.

[Andreik]

Use _SQLite_FastEncode() when working with blobs.

expandcollapse popup

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

        ;open tpdb
        $DBhndl = _SQLite_Open($TPDB)
        if @error or $DBhndl = 0 Then
            wLog(1,"Couldn't open:" & $TPDB)
            Exit
        EndIf
        $Drive_link_id = 346
        $Chunk = 4
        $HexTemp = "D:\346-4.txt"

        $sql = "Update measurement " & _
                "set samples = " & _SQLite_FastEncode(ReadFileAsBinary($HexTemp)) & " " & _
                "where drive_link_id = " & $Drive_link_id & " and chunk_num = " & $Chunk & "; "
            if _SQLite_Exec($DBhndl,$sql) <> $SQLITE_OK Then
                wLog(1,"ERROR updating TC blob: " & $Drive_link_id & "-" & $Chunk)
            Else ;success
                $sql = "Select length(samples) from measurement " & _
                    "where drive_link_id = " & $Drive_link_id & " and chunk_num = " & $Chunk & "; "
                $iRval = _SQLite_GetTableData2D($DBhndl, $sql, $aResult, $iRows, $aNames)
;~                 wLog(1,"TPDB for " & $DriveTC & " is " & $TPDB)
                if $iRval = $SQLITE_OK Then ;sql ran ok
                    If $aResult[0][0] = 0 Then
                        wLog(1,"ERROR Drive link and chunk not written:" & $Drive_link_id & "-" & $Chunk)
;~                         FileCopy($HexTemp,StringReplace($HexTemp,".txt","InjErr" & $Drive_link_id & "-" & $Chunk & ".txt"))
                        MsgBox(0,"ERROR", "Make sure copy of " & $HexTemp & " Exists for " & $Drive_link_id & "-" & $Chunk  )
                    EndIf
        ;~             wLog(1,"$Drive_link_id:" & $Drive_link_id)
                Else
                    wLog(1,"Error running: " & $sql)
                    Exit
                EndIf
;~                 wLog(1,"Rows written")

            EndIf

    ;close tpdb connection and sqlite
    _SQLite_Close($DBhndl)

Func ReadFileAsBinary($sPath)
    Local $hFile = FileOpen($sPath, 16) ; FO_BINARY = 16
    Local $dData = FileRead($hFile)
    FileClose($hFile)
    Return $dData
EndFunc