Problems with _SQLite_FetchData()

[mikkokh]

Hi.

I have large script, about 3600 lines. After updating to AutoIt 3.3.0.0 from 3.3.2.0, I get different result with _SQLite_ErrMsg() after executing of _SQLite_FetchData() command.

With AutoIt v3.3.0.0...

_SQLite_ErrCode() returns "100"

_SQLite_ErrMsg() returns "not an error"

And with AutoIt v3.3.2.0...

_SQLite_ErrCode() returns "100"

_SQLite_ErrMsg() returns "unknown error"

So, is _SQLite_ErrCode()'s result "100" error or is it not? And what that error code means?

Thanks.

Edited December 29, 2009 by mikkokh

[PsaltyDS]

It may have changed as the version of SQLite included with AutoIt was updated (see Version History in the help file). According to the SQLite web site, a return code of 100 = sqlite3_step() has another row ready. Can you post a short demo that creates a small DB in memory and performs an operation that produces the results you describe for _SQLite_ErrCode() and _SQLite_ErrMsg()?

[mikkokh]

...Can you post a short demo that creates a small DB in memory and performs an operation that produces the results you describe for _SQLite_ErrCode() and _SQLite_ErrMsg()?

Hi.

Thanks about answer!

Here is one short and messy demo with that same problem...

demo.au3

[PsaltyDS]

I think the operable part is in SQLite C Interface: Error Codes And Messages:

The sqlite3_errcode() interface returns the numeric result code or extended result code for the most recent failed sqlite3_* API call associated with a database connection. If a prior API call failed but the most recent API call succeeded, the return value from sqlite3_errcode() is undefined.

It's just not valid to get error codes and messages when no error has occured. You should only be checking errors when the performed function returns <> $SQLITE_OK:

_SQLite_FetchNames($hQuery, $aNames)
MsgBox(0, "code -> 'msg'", StringFormat("%-10s  %-10s  %-10s  %-10s", $aNames[0], $aNames[1], $aNames[2], $aNames[3]))
While 1
    $RET = _SQLite_FetchData($hQuery, $aRow)
    Switch $RET
        Case $SQLITE_OK
            MsgBox(64, StringFormat("%s -> '%s'", $SQLITE_OK, "SQLITE_OK"), StringFormat(" %-10s  %-10s  %-10s  %-10s ", $aRow[0], $aRow[1], $aRow[2], $aRow[3]))
        Case $SQLITE_DONE
            MsgBox(64, StringFormat("%s -> '%s'", $SQLITE_DONE, "SQLITE_DONE"), "Done, exiting fetch loop.")
            ExitLoop
        Case Else
            MsgBox(16, StringFormat("%s -> '%s'", _SQLite_ErrCode(), _SQLite_ErrMsg()), "Error, exiting fetch loop: " & $RET)
            ExitLoop
    EndSwitch
WEnd

During your fetch loop $SQLITE_OK is returned from the function, and you shouldn't be checking error code. When the fetch hits EOL, you get $SQLITE_DONE returned and SQLITE_OK for error code (not an error). It's also not safe to make those $aRow array references if there was an error because the array may not be valid or have the expected number of elements.

[mikkokh]

Hi.

So, it seems that SQLite.dll is not that "idiot proof" than I have believed...

Thanks about the hint, PsaltyDS!

[jchd]

So, it seems that SQLite.dll is not that "idiot proof" than I have believed...

Hi,

Don't believe that. SQLite _must_ have a way to indicate common, normal, non-error situations like a query returning an empty rowset, availability of another row for you to fetch, end of rowset, just for talking about result of queries. Now there are other non-fatal situations that may need to take care of, like "base is busy".

All in all, the SQLite interface isn't that dumb.