6

I'm using MySQL 5.0.27 and am trying to get transactions to work. I followed this tutorial:

http://dev.mysql.com/doc/refman/5.0/es/connector-net-examples-mysqltransaction.html

and still cannot get these to work. The table I am trying to update is InnoDB and have tried to execute 'set autocommit=0' but it doesn't seem to be doing anything.... The code I've written is the following:

public int transactionUpdate()
{
    MySqlConnection connection = new MySqlConnection(connStr);
    connection.Open();
    MySqlCommand command = connection.CreateCommand();
    MySqlTransaction trans;
    trans = connection.BeginTransaction();
    command.Connection = connection;
    command.Transaction = trans;
    try
    {
        command.CommandText = "SET autocommit = 0";
        command.executeNonQuery();
        command.CommandText = "UPDATE TBL.rec_lang rl SET rl.lang_code = 'en-us' WHERE rl.recording=123456";
        command.executeNonQuery();
        command.CommandText = "UPDATE TBL.rec_lang rl SET rl.lang_code = en-us WHERE rl.recording=123456";      
        command.executeNonQuery();
        trans.Commit();
    }
    catch(Exception ex)
    {
        try
        {
            trans.Rollback();
        }
        catch(MySqlException mse)
        {
            log.error(mse);
        }
    }
}

The second command fails as it is missing the ' around 'en-us'. This should roll back the first query as well to a previous value but it isn't. Can you tell me what I'm doing wrong???

MySQLConnector v. 6.3.6.0

MySQL v. 5.0.27

C# VS2010

Austin
  • 1,521
  • 5
  • 15
  • 27
  • 5
    You're swallowing the exception `ex`. Not good! Don't do that! – Mark Byers Mar 16 '11 at 22:01
  • What storage engine are you using? Do transaction work directly using an admin or console? – Jaroslav Jandek Mar 16 '11 at 22:08
  • storage engine is InnoDB as far as i can tell (in SQLYog -> select table, right click advanced properties, engine = InnoDB Version 10) – Austin Mar 16 '11 at 22:11
  • Good. InnoDB supports transactions... And the second question? Try the query manually, using `START TRANSACTION; UPDATE ...; ROLLBACK;`. That way you will know it's a .NET/Connector/code problem and not your DB. – Jaroslav Jandek Mar 16 '11 at 22:18
  • I'm not awfully familiar with c# so this might be a total dead end, but one thing I did notice is that you (apparently) change a parameter relating to transactions (autocommit) after you've initiated the transaction. What would happen if you set autocommit before your beginTransaction() call? – GordonM Mar 16 '11 at 23:04

1 Answers1

3

I had a second database open that had bad data showing ><... this method works. Turns out I didn't even need:

command.CommandText = "SET autocommit = 0";  
command.executeNonQuery();

So this code does work for transactions.

TheLethalCoder
  • 6,668
  • 6
  • 34
  • 69
Austin
  • 1,521
  • 5
  • 15
  • 27