Sign in to follow this  
Followers 0
jedliu

HELP!Why the SQL can't commit immediately?

7 posts in this topic

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

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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")

*GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes

Share this post


Link to post
Share on other sites

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...

#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()

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0