OdbcTransaction problem

[raduvv]

Hi

I have an OdbcTransaction problem working with MySQL 5.0 and the ODBC Driver 3.5. I have a simple test table called Students with 2 columns: Name VARCHAR(50) and Grade INT

In the following code I am doing 2 UPDATE commands, the 1st one is correct and the 2nd is intentionately wrong so as to test the Rollback method. However, at the end, the Rollback does not seem to work, as the 1st UPDATE does not get rollback-ed ?

Here is my code:

OdbcConnection con = new OdbcConnection(DefaultConnection);
con.Open();

OdbcCommand myCommand = con.CreateCommand();
OdbcTransaction myTrans;

myTrans = con.BeginTransaction();
myCommand.Transaction = myTrans;

try
{
myCommand.CommandText = "INSERT INTO Students VALUES (\"Tom\", 10)";
myCommand.ExecuteNonQuery();

myCommand.CommandText = "INSERT INTO Students VALUES (\"John\"\", 8)";
myCommand.ExecuteNonQuery();

myTrans.Commit();
}

catch(Exception exc)
{
try
{
myTrans.Rollback();
}
catch(Exception exc2)
{
MessageBox.Show(exc2.ToString());
}
}

con.Close();


The 2nd UPDATE SQL command is purposely incorrect: see the extra ". However, what I get is the 1st UPDATE executed, even if with the myTrans.Rollback() method ?

Could you please help ?

radu

[Igor Soukhov]

Hi

I have an OdbcTransaction problem working with MySQL 5.0 and the ODBC Driver 3.5. I have a simple test table called Students with 2 columns: Name VARCHAR(50) and Grade INT

In the following code I am doing 2 UPDATE commands, the 1st one is correct and the 2nd is intentionately wrong so as to test the Rollback method. However, at the end, the Rollback does not seem to work, as the 1st UPDATE does not get rollback-ed ?

Here is my code:

OdbcConnection con = new OdbcConnection(DefaultConnection);
con.Open();

OdbcCommand myCommand = con.CreateCommand();
OdbcTransaction myTrans;

myTrans = con.BeginTransaction();
myCommand.Transaction = myTrans;

try
{
myCommand.CommandText = "INSERT INTO Students VALUES (\"Tom\", 10)";
myCommand.ExecuteNonQuery();

myCommand.CommandText = "INSERT INTO Students VALUES (\"John\"\", 8)";
myCommand.ExecuteNonQuery();

myTrans.Commit();
}

catch(Exception exc)
{
try
{
myTrans.Rollback();
}
catch(Exception exc2)
{
MessageBox.Show(exc2.ToString());
}
}

con.Close();


The 2nd UPDATE SQL command is purposely incorrect: see the extra ". However, what I get is the 1st UPDATE executed, even if with the myTrans.Rollback() method ?

Could you please help ?

radu

Are you absolutely sure that the following code


myTrans.Rollback();


gets called ?

Put a breakpoint to find out is it called or not.

[raduvv]

Yes, myTrans.Rollback() is called.

That's why it is so puzzling, it is the exact copy of MSDN's sample and it does not work ??

Any suggestions?

[Igor Soukhov]

Yes, myTrans.Rollback() is called.

That's why it is so puzzling, it is the exact copy of MSDN's sample and it does not work ??

Any suggestions?

That's weird mate. Just did sanity check (though I'm using MS SQL Server 2005 instead of MySQL 5) and it worked like a charm.

I guess there might be a problem either with MySQL 5 or with the ODBC driver.

[exterminator]

I am not sure if this is there in MySQL but in SQL Server there is a auto-commit option. You need to set it to false or off. Check for this in the MySQL documentation. Hope this helps. Regards.

[raduvv]

Thanks guys for taking the time for reading and answering to my problem, it is really appreciated.

It was indeed a MySQL 5 setting !

And for anyone else who may incounter this problem in the future, here is the answer:

1. Under MySQL Administrator -> Service Control -> Configure Service, set the option "Support for InnoDB (Select this option to enable support for transaction"

2. When creating your table, make sure it is of InnoDB type

That's all, the transactions worked like a charm.

Thanks to all of you, great posting on this forum,
radu

[Igor Soukhov]

I am not sure if this is there in MySQL but in SQL Server there is a auto-commit option. You need to set it to false or off. Check for this in the MySQL documentation. Hope this helps. Regards.

raduvv shown us, that he was using explicit outer transaction (myTrans = con.BeginTransaction();).

Therefore every implicit inner transaction (created automatically as the result of the auto-commit option turned ON) would be rolled back anyway when outer transaction rolled back (myTrans.Rollback();)

[exterminator]

raduvv shown us, that he was using explicit outer transaction (myTrans = con.BeginTransaction();).Thanks Igor for correcting me. Regards.

Relevant links for those who might be interested - MS SQL Server - Explicit transactions (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_md_06_7j1y.asp) and MS SQL Server - Implicit transactions (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_7mur.asp).