Sqlite bug - (Moved)

[Ervan]

Hi!

In last versions of sqlite it is possible to execute an UPDATE query with return values. I found that the _SqliteGetTable2d function executes the given query incorrectly while the _SQLite_GetTable2d function does correctly.

For example: UPDATE table SET Value = 'value' WHERE id IN (SELECT id FROM table WHERE Value > 0 LIMIT 5) RETURNING id'. After _SQLite_GetTable2d DB has 10 changes instead 5.

First 5 changes appear after this code execution (in the Sqlite.au3, _SQLite_GetTable2d func):

While True
        $iRval_Step = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_step", "ptr", $hQuery)
        If @error Then
            $iError = @error
            _SQLite_QueryFinalize($hQuery)
            Return SetError(3, $iError, $SQLITE_MISUSE) ; DllCall error
        EndIf
        Switch $iRval_Step[0]
            Case $SQLITE_ROW
                $iRows += 1
            Case $SQLITE_DONE
                ExitLoop
            Case Else
                _SQLite_QueryFinalize($hQuery)
                Return SetError(3, $iError, $iRval_Step[0])
        EndSwitch
    WEnd

And the last 5 changes appear after Finalize in the end of the func:

Return (_SQLite_QueryFinalize($hQuery))

So in the end we have 10 changes but only 5 returns (last changes)

[Ervan]

Edit: I found that the _SqliteGetTable2d function executes the given query incorrectly while the _SQLite_GetTable function does correctly.

[Jos]

Moved to the appropriate forum.

Moderation Team

[jchd]

Our SQLite UDF predates the introduction of the RETURNING clause by several years, so it isn"t surprising nothing was setup at that time to support the newest versions of SQLite. I wouldn't call this a bug.

[jchd]

The cause of the issue is that the current function performs the query twice: once for counting the output rows, once for populating the resulting array.

It's of course possible to rewrite _SQLiteGetTable2D() to change the way it works but I currently don't have much time to devote to this task. Given that the UDF has a number of really important issues (e.g. everything is returned as text instead of datatypes) I don't expect to fix this problem anytime soon, at least not in the next few months.

[Ervan]

I will wait for the fix. Thanks!