From: Peter Brawley
Date: January 26 2008 12:14am
Subject: Re: Transactions with ODBC
List-Archive: http://lists.mysql.com/mysql/211094
Message-Id: <479A7B69.7030800@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------050306030905070202060205"

--------------050306030905070202060205
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

>Does that mean what I did should have worked?

I'd want to know (i) the result of executing those cmds in a mysql 
client, and (ii) what sqlresult & acountinfo contain after each cmd.

PB

groups@stripped wrote:
> Thank you for your response. I am using InnoDB (picked that out of the docs). 
>
> Does that mean what I did should have worked? I should not have had 2 rows in that table after running the commands?
>
> Thanks again...
>
>
>
>   
>> I would like to wrap my updates top MySQL in transactions.
>>     
>
> Use InnoDB tables.
>
> PB
>
> groups@stripped wrote:
>   
>> I apologize if you saw this on the MySQL Forums but I have not gotten a response... Thanks for your help...
>>
>> I know this is probably a stupid question but I could use a nudge in the right direction.
>>
>> I would like to wrap my updates top MySQL in transactions. I am using ODBC as my means of communication (Visual FoxPro is the front end) and passing SQL statements off to the server one Execute at a time.
>>
>> I understand how I could do this with a stored procedure, or possibly even ADO, but I would prefer to send it one command at a time for various reasons.
>>
>> When I send the following series of commands through, though, it doesn't work although each statement tells me that it executed properly:
>>
>> *-- Set the connection string
>> ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;" 
>> + ; "DATABASE=test;" + ; "USER=test;"+; "PASSWORD=test;"+; "OPTION=3;"
>>
>> lnHandle = SQLSTRINGCONNECT(ConnectionString) &&Get the connection ? 
>> lnHandle &&Check the connection to make sure it worked.
>> lcSQL = "START TRANSACTION"
>> ? SQLEXEC(lnHandle, lcSQL)
>> lcSQL = "BEGIN"
>> ? SQLEXEC(lnHandle, lcSQL)
>> lcSQL = "INSERT INTO n1 (n1) VALUES (1)"
>> ? SQLEXEC(lnHandle, lcSQL)
>> lcSQL = "INSERT INTO n1 (n1) VALUES (2)"
>> ? SQLEXEC(lnHandle, lcSQL)
>> lcSQL = "ROLLBACK"
>> ? SQLEXEC(lnHandle, lcSQL)
>>
>> Yet, when I query the table after this runs, there are still two records despite the fact that I asked it to rollback the transaction and that all commands reported as executing OK.
>>
>> I am sure I am missing something stupid but I cannot find a decent doc on this (if you know of one, a reference would be appreciated).
>>
>> Thanks in advance for your help.
>>
>>
>>     
>
>   

--------------050306030905070202060205--