Sujit, it appears the new transaction handling code introduced in 4.6 doesn't work properly with Sybase. I get errors now that indicate that set chained mode is not supported for multi-statement batches. This used to work before you stuck in the conn.setAutoCommit(false) in the ConnectionRegistry.getConnection method. I'm able to workaround by removing the call.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thanks for the info and the workaround, I will try to figure out why Sybase SET CHAINED mode does not work with the transaction code, and put in a check.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
* The SQL standards-compatible mode, called chained mode, implicitly begins a transaction before any data retrieval or modification statement. These statements include: delete, insert, open, fetch, select, and update. You must still explicitly end the transaction with commit transaction or rollback transaction.
* The default mode, called unchained mode or Transact-SQL mode, requires explicit begin transaction statements paired with commit transaction or rollback transaction statements to complete the transaction.
Executing "set chained on" puts you in the chained mode. An example follows:
In chained transaction mode, Adaptive Server implicitly executes a begin transaction statement just before the following data retrieval or modification statements: delete, insert, open, fetch, select, and update. For example, the following group of statements produce different results, depending on which mode you use:
insert into publishers
values ("9906", null, null, null)
begin transaction
delete from publishers where pub_id = "9906"
rollback transaction
In unchained transaction mode, the rollback affects only the delete statement, so publishers still contains the inserted row. In chained mode, the insert statement implicitly begins a transaction, and the rollback affects all statements up to the beginning of that transaction, including the insert.
So I think that we should have a third transaction-support mode called "chained" which will not set the autocommit at all, but simply be based on the set chained on|off in the setup. The commit and rollback further at the end of the sql or call will function normally as before.
Let me know if this would be an acceptable solution (the default would have to be "on", for historical reasons, and since chained mode is only available in Sybase and its relatives, so you will have to change your test cases to "chained".
Thanks
Sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The call to setAutoCommit(true) effectively enables chained mode in Sybase. This doesn't work for our code base. I don't want chained mode at all. Normally I disable the transaction-support in all my tests since I don't want SQLUnit to do any commit or rollback internally. So why couldn't you call setAutoCommit(true) whenever transaction-support is 'on' and leave it alone when transaction-support is 'off'?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I now believe I understand the source of my errors. The <setup> handler was running the <set> tags twice. I was performing updates in the <set> tags and since chained mode was enabled and I turned off transaction support, there was no commit statement after the first statement was executed. So, I think the correct way is that auto commit should be disabled when transaction support is turned off. This will work for Sybase and perhaps SQL Server. Not so sure about the other databases though.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Your last post may well point to the source of the problem, so please download the version I mentioned and check to see if this problem also goes away.
Meanwhile, I thought about this whole transaction-support issue a bit more and I am putting them here so we can discuss this and see if we can come up with an optimal solution, or even if a solution is required at all.
So currently we have two transaction-support modes:
on - SQLUnit will handle your transactions. This was achieved by setting providing rollback on failure and commit on success for each sql or call executed. AutoCommit was on by default (its true if not set in Java). After version 4.7, autoCommit was always set explicitly to false.
off - SQLUnit will not touch your transactions., you do all COMMIT and ROLLBACK yourself. This was achieved by SQLUnit not doing any COMMITs or ROLLBACKs. Prior to version 4.7, autoCommit was set to default, which meant it was true. After version 4.7, autoCommit was set to false explicitly.
This created problems with some test cases that ran on an Oracle database and was pointed out by Bern Holmes and Jason Wilder. Specifically, they were testing the contents of a temp table after a procedure had finished. So in their case, since autocommit was implicitly set to true, even with transaction-mode=off, they could not see the temp table since that went away as soon as the stored procedure completed due to the implicit commit.
In your case, since you set transaction-support=off, changing the setting to:
transaction-support=on => setAutoCommit(true)
transaction-support=off => setAutoCommit(false)
will not work, since the CHAINED MODE seems to be complaining about the setAutoCommit(false) that is triggered (and should be triggered) by transaction-support=off.
So it seems to me that we could handle this in one of two ways:
1) Conditionally setAutoCommit(false) only when transaction-support=off, so it will be on when transaction-support=on.
2) Add a new transaction-support value "implicit" (I looked some more on google, and MS SQL Server 2000 calls CHAINED MODE the IMPLICIT TRANSACTION MODE, which seems to be more meaningful to me), where the operation would be something like this:
+---------------------+------+------+--------+
|transaction-support |on |off |implicit|
+---------------------+------+------+--------+
|setAutoCommit() |false |false |true |
|commit on success |yes |no |no |
|rollback on failure |yes |no |no |
+---------------------+------+------+--------+
So setting:
transaction-support="implicit" => SQLUnit will not issue any commits or rollbacks, but your transaction would be managed either by your database or by the java default auto-commit mode.
What do you think?
Thanks
Sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
This sounds worthwhile. The basic problem I have is that I don't want SQLUnit to manage any transactions and I want the auto commit to be left alone. By using "implicit", with the meaning that transaction support is off and the auto commit mode is the default from the driver, I don't think I have an issue with it. I would need to change my test case scripts so that I use transaction-support="implicit" instead of transaction-support="off", but at this time the number of test cases is small and I think I can manage it.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I added the implicit mode code to CVS, also added the documentation to explain the changes, its in docbook format, you can either generate the HTML locally if you have docbook installed or read the sqlunit-book.xml file with an editor, look for "Controlling Transactions". Here are the files that have changed:
Checking in docs/sqlunit-book.xml;
/cvsroot/sqlunit/sqlunit/docs/sqlunit-book.xml,v <-- sqlunit-book.xml
new revision: 1.91; previous revision: 1.90
done
Checking in docs/sqlunit.dtd;
/cvsroot/sqlunit/sqlunit/docs/sqlunit.dtd,v <-- sqlunit.dtd
new revision: 1.46; previous revision: 1.45
done
Checking in src/net/sourceforge/sqlunit/ConnectionRegistry.java;
/cvsroot/sqlunit/sqlunit/src/net/sourceforge/sqlunit/ConnectionRegistry.java,v <-- ConnectionRegistry.java
new revision: 1.21; previous revision: 1.20
done
Checking in src/net/sourceforge/sqlunit/handlers/ConnectionHandler.java;
/cvsroot/sqlunit/sqlunit/src/net/sourceforge/sqlunit/handlers/ConnectionHandler.java,v <-- ConnectionHandler.java
new revision: 1.9; previous revision: 1.8
done
As you mentioned earlier, you will still need to change the transaction-support to implicit for the tests which were giving error on CHAINED. Please let me know if the fixes above worked for you.
Based on your feedback, I will probably do a release with the three bug fixes sometime next week.
Thanks
Sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Sujit, it appears the new transaction handling code introduced in 4.6 doesn't work properly with Sybase. I get errors now that indicate that set chained mode is not supported for multi-statement batches. This used to work before you stuck in the conn.setAutoCommit(false) in the ConnectionRegistry.getConnection method. I'm able to workaround by removing the call.
Hi Mark,
Thanks for the info and the workaround, I will try to figure out why Sybase SET CHAINED mode does not work with the transaction code, and put in a check.
-sujit
Hi Mark,
From this URL:
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/53740
* The SQL standards-compatible mode, called chained mode, implicitly begins a transaction before any data retrieval or modification statement. These statements include: delete, insert, open, fetch, select, and update. You must still explicitly end the transaction with commit transaction or rollback transaction.
* The default mode, called unchained mode or Transact-SQL mode, requires explicit begin transaction statements paired with commit transaction or rollback transaction statements to complete the transaction.
Executing "set chained on" puts you in the chained mode. An example follows:
In chained transaction mode, Adaptive Server implicitly executes a begin transaction statement just before the following data retrieval or modification statements: delete, insert, open, fetch, select, and update. For example, the following group of statements produce different results, depending on which mode you use:
insert into publishers
values ("9906", null, null, null)
begin transaction
delete from publishers where pub_id = "9906"
rollback transaction
In unchained transaction mode, the rollback affects only the delete statement, so publishers still contains the inserted row. In chained mode, the insert statement implicitly begins a transaction, and the rollback affects all statements up to the beginning of that transaction, including the insert.
So I think that we should have a third transaction-support mode called "chained" which will not set the autocommit at all, but simply be based on the set chained on|off in the setup. The commit and rollback further at the end of the sql or call will function normally as before.
Let me know if this would be an acceptable solution (the default would have to be "on", for historical reasons, and since chained mode is only available in Sybase and its relatives, so you will have to change your test cases to "chained".
Thanks
Sujit
The call to setAutoCommit(true) effectively enables chained mode in Sybase. This doesn't work for our code base. I don't want chained mode at all. Normally I disable the transaction-support in all my tests since I don't want SQLUnit to do any commit or rollback internally. So why couldn't you call setAutoCommit(true) whenever transaction-support is 'on' and leave it alone when transaction-support is 'off'?
I now believe I understand the source of my errors. The <setup> handler was running the <set> tags twice. I was performing updates in the <set> tags and since chained mode was enabled and I turned off transaction support, there was no commit statement after the first statement was executed. So, I think the correct way is that auto commit should be disabled when transaction support is turned off. This will work for Sybase and perhaps SQL Server. Not so sure about the other databases though.
Hi Mark,
Your last post may well point to the source of the problem, so please download the version I mentioned and check to see if this problem also goes away.
Meanwhile, I thought about this whole transaction-support issue a bit more and I am putting them here so we can discuss this and see if we can come up with an optimal solution, or even if a solution is required at all.
So currently we have two transaction-support modes:
on - SQLUnit will handle your transactions. This was achieved by setting providing rollback on failure and commit on success for each sql or call executed. AutoCommit was on by default (its true if not set in Java). After version 4.7, autoCommit was always set explicitly to false.
off - SQLUnit will not touch your transactions., you do all COMMIT and ROLLBACK yourself. This was achieved by SQLUnit not doing any COMMITs or ROLLBACKs. Prior to version 4.7, autoCommit was set to default, which meant it was true. After version 4.7, autoCommit was set to false explicitly.
This created problems with some test cases that ran on an Oracle database and was pointed out by Bern Holmes and Jason Wilder. Specifically, they were testing the contents of a temp table after a procedure had finished. So in their case, since autocommit was implicitly set to true, even with transaction-mode=off, they could not see the temp table since that went away as soon as the stored procedure completed due to the implicit commit.
In your case, since you set transaction-support=off, changing the setting to:
transaction-support=on => setAutoCommit(true)
transaction-support=off => setAutoCommit(false)
will not work, since the CHAINED MODE seems to be complaining about the setAutoCommit(false) that is triggered (and should be triggered) by transaction-support=off.
So it seems to me that we could handle this in one of two ways:
1) Conditionally setAutoCommit(false) only when transaction-support=off, so it will be on when transaction-support=on.
2) Add a new transaction-support value "implicit" (I looked some more on google, and MS SQL Server 2000 calls CHAINED MODE the IMPLICIT TRANSACTION MODE, which seems to be more meaningful to me), where the operation would be something like this:
+---------------------+------+------+--------+
|transaction-support |on |off |implicit|
+---------------------+------+------+--------+
|setAutoCommit() |false |false |true |
|commit on success |yes |no |no |
|rollback on failure |yes |no |no |
+---------------------+------+------+--------+
So setting:
transaction-support="implicit" => SQLUnit will not issue any commits or rollbacks, but your transaction would be managed either by your database or by the java default auto-commit mode.
What do you think?
Thanks
Sujit
This sounds worthwhile. The basic problem I have is that I don't want SQLUnit to manage any transactions and I want the auto commit to be left alone. By using "implicit", with the meaning that transaction support is off and the auto commit mode is the default from the driver, I don't think I have an issue with it. I would need to change my test case scripts so that I use transaction-support="implicit" instead of transaction-support="off", but at this time the number of test cases is small and I think I can manage it.
Hi Mark,
I added the implicit mode code to CVS, also added the documentation to explain the changes, its in docbook format, you can either generate the HTML locally if you have docbook installed or read the sqlunit-book.xml file with an editor, look for "Controlling Transactions". Here are the files that have changed:
Checking in docs/sqlunit-book.xml;
/cvsroot/sqlunit/sqlunit/docs/sqlunit-book.xml,v <-- sqlunit-book.xml
new revision: 1.91; previous revision: 1.90
done
Checking in docs/sqlunit.dtd;
/cvsroot/sqlunit/sqlunit/docs/sqlunit.dtd,v <-- sqlunit.dtd
new revision: 1.46; previous revision: 1.45
done
Checking in src/net/sourceforge/sqlunit/ConnectionRegistry.java;
/cvsroot/sqlunit/sqlunit/src/net/sourceforge/sqlunit/ConnectionRegistry.java,v <-- ConnectionRegistry.java
new revision: 1.21; previous revision: 1.20
done
Checking in src/net/sourceforge/sqlunit/handlers/ConnectionHandler.java;
/cvsroot/sqlunit/sqlunit/src/net/sourceforge/sqlunit/handlers/ConnectionHandler.java,v <-- ConnectionHandler.java
new revision: 1.9; previous revision: 1.8
done
As you mentioned earlier, you will still need to change the transaction-support to implicit for the tests which were giving error on CHAINED. Please let me know if the fixes above worked for you.
Based on your feedback, I will probably do a release with the three bug fixes sometime next week.
Thanks
Sujit
Just an FYI.
I have tested the SQLUnit 4.6 release against Sybase SQL Anywhere 9.0.2.3044 and it the test suite include worked fine.
Dave
Hi Dave,
Thanks for checking this out, I will put in a warning on the release notes and user guide about the new transaction-support attribute.
-sujit