HELP!Why the SQL can't commit immediately?

[jedliu]

AutoIt is extremely great, I LOVE it, and thanks for all your hard work and contribution.

I have a problem, please see the code as below.

The problem is in the while circle, the UPDATE statement can't commit immediately. Everytime, the commit result is 0.

Until while ends, the UPDATE can commit.

How can make the UPDATE operation to commit immediately?

Thanks in advance.

Global $db_path = "update.db";database path
_SQLite_Startup();Load SQLite.dll
_SQLite_Open($db_path)
Dim $rows, $aRow
_SQLite_Query(-1, "SELECT ID FROM MYTABLE where lastUpdateDate != '2009/01/05' or lastUpdateDate is null order by id", $rows)
While _SQLite_FetchData($rows, $aRow) = $SQLITE_OK
    Local $sql = "update MYTABLE set lastUpdateDate='1-2-1' where id='"& $aRow[0] &"' "
    $err = _SQLite_Exec(-1, $sql)
    MsgBox(0, "Result", "Commit Result:" & $err)
WEnd
_SQLite_Close()

[DjDeep00]

AutoIt is extremely great, I LOVE it, and thanks for all your hard work and contribution.

I have a problem, please see the code as below.

The problem is in the while circle, the UPDATE statement can't commit immediately. Everytime, the commit result is 0.

Until while ends, the UPDATE can commit.

How can make the UPDATE operation to commit immediately?

Thanks in advance.

Global $db_path = "update.db";database path
_SQLite_Startup();Load SQLite.dll
_SQLite_Open($db_path)
Dim $rows, $aRow
_SQLite_Query(-1, "SELECT ID FROM MYTABLE where lastUpdateDate != '2009/01/05' or lastUpdateDate is null order by id", $rows)
While _SQLite_FetchData($rows, $aRow) = $SQLITE_OK
    Local $sql = "update MYTABLE set lastUpdateDate='1-2-1' where id='"& $aRow[0] &"' "
    $err = _SQLite_Exec(-1, $sql)
    MsgBox(0, "Result", "Commit Result:" & $err)
WEnd
_SQLite_Close()

jedliu,

Not sure why are you expecting a 0 for failure? Here is how I would do it...

$err = _SQLite_Exec(-1, $sql)
    If $SQLITE_OK = $err Then
        MsgBox(4096, "", "Worked")
    Else
        MsgBox(4096, "", _SQLite_ErrCode())
    EndIf

[KaFu]

I guess the sqlite.dll is still occupied by the first query. Try to read the result into an array with something like _SQLite_GetTable2d(-1, $sSQLite_Query, $aResult, $iRows, $iColumns), afterwards loop through the array and update the records.

[ProgAndy]

I think, you can't have 2 queries at the same time. No matter if they are send with differnet commands.

Solutions: Save all IDs first to an array and then do the UPDAte commands.

You could also do this in one query i think:

$err = _SQLite_Exec(-1, "update MYTABLE set lastUpdateDate='1-2-1' where lastUpdateDate != '2009/01/05' or lastUpdateDate is null")

[DjDeep00]

I guess the sqlite.dll is still occupied by the first query.

I think, you can't have 2 queries at the same time. No matter if they are send with differnet commands.

These were my first assumptions, but I wasn't really sure. After tweaking the help file example...

expandcollapse popup

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

Local $hQuery, $aRow, $aNames
_SQLite_Startup()
_SQLite_Open(); open :memory: Database
_SQLite_Exec(-1, "CREATE TABLE aTest (a,b,c);")
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('c','2','World');")
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('b','3','Hello');")
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('a','1','Hello');")
_SQLite_Query(-1, "SELECT ROWID,* FROM aTest ORDER BY a;", $hQuery)
_SQLite_FetchNames($hQuery, $aNames)

ConsoleWrite(StringFormat(" %-10s  %-10s  %-10s  %-10s ", $aNames[0], $aNames[1], $aNames[2], $aNames[3]) & @CR)
While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK; Read Out the next Row
    ConsoleWrite(StringFormat(" %-10s  %-10s  %-10s  %-10s ", $aRow[0], $aRow[1], $aRow[2], $aRow[3]) & @CR)
    Local $sql = "update aTest set a='test' where c='" & $aRow[3] & "'"
    $err = _SQLite_Exec(-1, $sql)
    If $SQLITE_OK = $err Then
    ;MsgBox(4096, "", "Worked")
    Else
        MsgBox(4096, "", _SQLite_ErrCode())
    EndIf
WEnd


ConsoleWrite("**********************************************************" & @CRLF)

_SQLite_Query(-1, "SELECT ROWID,* FROM aTest ORDER BY a;", $hQuery)
_SQLite_FetchNames($hQuery, $aNames)
ConsoleWrite(StringFormat(" %-10s  %-10s  %-10s  %-10s ", $aNames[0], $aNames[1], $aNames[2], $aNames[3]) & @CR)
While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK; Read Out the next Row
    ConsoleWrite(StringFormat(" %-10s  %-10s  %-10s  %-10s ", $aRow[0], $aRow[1], $aRow[2], $aRow[3]) & @CR)
WEnd

_SQLite_Exec(-1, "DROP TABLE aTest;")
_SQLite_Close()
_SQLite_Shutdown()

[leos]

I think that your 2 querys are equivalent to:

"update MYTABLE set lastUpdateDate='1-2-1' where ((lastUpdateDate != '2009/01/05') or (lastUpdateDate is null)"

In the case I am right, it is better to execute only the last single query.

[jedliu]

Thanks to all your guys, it is really helpful.

And now I am using _SQLite_GetTable2d function to get the result into an array,

and then use the array to do the updating. It works now.

Thanks a lot!