List:General Discussion« Previous MessageNext Message »
From:<groups Date:January 25 2008 7:44pm
Subject:Transactions with ODBC
View as plain text  
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.
Thread
Transactions with ODBCgroups25 Jan
  • Re: Transactions with ODBCPeter Brawley25 Jan
Re: Transactions with ODBCgroups25 Jan
  • Re: Transactions with ODBCPeter Brawley26 Jan