How to tell why sqlite update is failing?

[BryanWall]

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.
      

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 = 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)
    _SQLite_Shutdown()

 

[BryanWall]

Also, when the update fails, the value from the exec is False and @error = 0.

[Zedna]

I think you should look at my post again:

 

EDIT: You shouldn't create new topic in such case because in original one there are many good tips for you ...

Edited January 30 by Zedna

[BryanWall]

First, _SQLite_ErrMsg returns "not an error".

 

Second, on the shouldn't create a new topic, that is different than my normal experience with these types of forums. I though it was generally bad form to post a question then after an answer to that question, or during I guess, to suddenly switch the topic to something else. I get they are related, but they seemed to me to be fundamentally different since the the other solution works. It is just one set it fails on but does still work if the same code is put into another window and ran. 

Edit: I'm starting to suspect that the issue has nothing to do with sqlite. I'm starting to think that the filewrite is failing for some reason since the code to create a copy of it when update fails isn't working.

Edited January 30 by BryanWall

[Zedna]

Just friendly hint:

Next time in your new topic at least add link to original one which is related and this new topic only expands that original.

Edited January 30 by Zedna

[argumentum]

19 hours ago, BryanWall said:

        ;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 " & _

 

21 minutes ago, Zedna said:

at least add link to original one

True. I wanted to have a go at it but can't replicate the DB without the fields. Posting running code makes it easier to run it and see.
-Not that I know much about databases but I tend to learn in the process- 

[BryanWall]

Noted. I will remember to do so in the future.

Where I'm at currently, unfortunately, has nothing to do with sqlite. 

I'll post some sample code below but basically I'm reading a txt file, converting some of the data to hex and writing the hex to a temp file. 

The temp file is then written to sqlite blob.

The problem I'm seeing, is that the temp file is suddenly disappearing.

... never mind. I think the file is disappearing because of variable setting/exit conditions being done wrong.

If that is the case, apologies for wasting everyone's time and I will look into deleting this if I'm right. 

[BryanWall]

And yup, purely an ID-10-T error. 

Short version is that I had logic to find end of a chunk by using math based on $Sample which worked fine until the last chunk when Sample gets set to a lower value. 

I replaced with explicitly calculating $EndOfChunk every time I set Sample and test that instead of doing bad math every loop.

Am I correct in thinking I should delete this since it is functionally useless to anyone?

Before code:

;Samples set to MaxSamples at beginning
If $x = ($Samples * ($Chunk+1))  or $x = $TCLineCount - 1 Then
  ;do stuff
  If $TCLineCount < ($Samples * ($Chunk+1)) then ;change samples to rows left
    $Samples = $TCLineCount - ($Samples * ($Chunk)) - 1
  EndIf

Now code:
      

If $x = $EndOfChunk or $x = $TCLineCount - 1 Then
    ;Do stuff
    If $TCLineCount < ($Samples * ($Chunk+1)) then ;change samples to rows left
        $Samples = $TCLineCount - ($Samples * ($Chunk)) - 1
        $EndOfChunk = $TCLineCount - 1
    EndIf

 

[Zedna]

Deleting of solved posts is not correct way on the forum.

If everybody would be deleting (solved) posts then the whole forum would be useless.