MyODBC transactions MySQL

Hi Experts,

How do I go about doing transactions with a MYODBC driver for MySQL?

Thanks,
Mike
mikexpertAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
BEGIN TRANSACTION to start a transaction ...
COMMIT / ROLLBACK to commit or rollback the transaction...
maybe you have a specific problem which you need to explain?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
akshah123Commented:
Just so that you know:

 MySQL Server (version 3.23-max and all versions 4.0 and above) supports transactions with the InnoDB and BDB transactional storage engines.

So before you begin, make sure that you can have transactions on your database...
akshah123Commented:
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

mikexpertAuthor Commented:
I was aware of the calls to BEGIN TRANSACTION and COMMIT.  I get the report that my SQL statement has an error.  I guess my database doesn't support it.  How can I tell if my server uses InnoDB or BDB?

Thanks again,
Mike
akshah123Commented:
well, you have to look at each table.  Use "SHOW CREATE TABLE" syntax to see the type of each table.  Again, it depends on what your table type is because one server can  support multiple table types...for info on "show create table" check out:

http://dev.mysql.com/doc/mysql/en/show-create-table.html
mikexpertAuthor Commented:

Hello again guys,

My question is specifically for the MyODBC driver.  I am using the right kind of tables and I was using the right SQL.  Any suggestions?

Thanks!
Mike
akshah123Commented:
if your server supports transactional table types (InnoDB and BDB) make sure the disable transactions option is not set from the DSN configuration.
mikexpertAuthor Commented:
Thanks for your response akshah.

It's not set - so that's not it...  So just for peace of mind - you have used transactions with MyODBC?

It's so weird that it reports that there is an error in my SQL syntax...  I must be doing something really dumb...

Mike
akshah123Commented:
No I am sorry i have not. i suppose i am not the right guy to answer you question at this point...   :(

mikexpertAuthor Commented:
Thanks Ahshah - I have a feeling that all queries are immediately effective when using MyODBC (i.e., no transactions)....  I could be wrong though..

All the best,
Mike
akshah123Commented:
Mike, if you want to give the new test version of odbc a shot get it from here...


Windows:

ftp://ftp.mysql.com/pub/mysql/hidden/connectors/odbc/3.51.12/MyODBC-3.51.12-win32.exe 
SqueebeeCommented:
Late entry, but here is a transaction example using VB6 and MyODBC, showing that it can be done, but may vary depending on your client language:

       
2003-07-21
Transactions In VB/MySQL
Filed under: Visual Basic 6, MySQL — Mike @ 10:45 am Edit This

I finally got around to testing Transactions in VB6/MySQL. It is as simple as one would think. Just call connection.begintrans, run your commands, and call connection.committrans

Just remember that you need a InnoDB table to work on. Any MyISAM operations in the transaction cannot be rolled back.

I will go more indepth on this in a future article. In the meantime, below is a sample. Remember that mytable will have to be InnoDB.

Private Sub Transaction_Test()
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

conn.CursorLocation = adUseClient
conn.ConnectionString = “DRIVER={MySQL ODBC 3.51 Driver};” _
& “SERVER=127.0.0.1;” _
& “DATABASE=test;” _
& “UID=testuser;” _
& “PWD=12345;” _
& “OPTION=” & 1 + 2 + 8 + 32 + 2048 + 16384

conn.Open

conn.BeginTrans

rs.Open “SELECT * FROM mytable WHERE row_id = 115″, conn, adOpenStatic, adLockOptimistic, adCmdText
rs.Delete
rs.Close

conn.CommitTrans

conn.Close

Set conn = Nothing
Set rs = Nothing
End Sub
mikexpertAuthor Commented:
It's START TRANSACTION and COMMIT or ROLLBACK...

Thanks...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.