Need help in JDBC Transaction

[sbhavan]

I have to update 5 tables simultaniously which are inter related with each other. My requirement is if all tables are updated successfully then all the updates will be commited. if any one of the query is failed due to some reason all the queried should be rolledback.

The way I handled the transactions is

I set the auto commit to false
executed the update queries
set the auto commit to true

But I am getting the following error message:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Can't start ma
nual transaction mode because there are cloned connections.
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source
)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.microsoft.jdbc.base.BaseConnection.setAutoCommit(Unknown Source)
at org.apache.tomcat.dbcp.dbcp.DelegatingConnection.setAutoCommit(Delega
tingConnection.java:268)
at org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrap
per.setAutoCommit(PoolingDataSource.java:293)

the sample code is:

....
connection.setAutoCommit(false);

statement.executeUpdate(query1);
statement.executeUpdate(query2);
statement.executeUpdate(query3);
statement.executeUpdate(query4);
statement.executeUpdate(query5);

connection.setAutoCommit(true);
.....

I've writen the code in the above maner. Because of my code is very lengthy I can't provide the exact code here. But I think this will help you to understand my problem.

Please suggest me a solution to solve this problem. If there is any other way to handle this scenario please suggest me the better way.

Thanking You.

[masijade]

before setAutoCommit(true) you need to do a commit(), but your problem comes before this. And you're real problem, at least IMHO, is using the JDBC-ODBC in Tomcat. The bridge is not threaded and Servlets/JSPs are. They do not work well together. So, that is quite possibly also your real problem and not only my opinion. More than that I cannot (without much more information) and, truthfully, will not say.

[~s.o.s~]

> Please suggest me a solution to solve this problem.

A simple google search would have solved it for you; please consider searching the web/forum archives before posting a query. Anyways, read this and this.

> If there is any other way to handle this scenario please suggest me the better way.

Two ways come to mind: Either use the executeBatch functionality provided by the JDBC API or push those five queries into a stored procedure which, when blows up brings down the entire transaction.

> And you're real problem, at least IMHO, is using the JDBC-ODBC in Tomcat.

I don't think he is using a bridge type driver given the package name com.microsoft.jdbc.* and [Microsoft][SQLServer 2000 Driver for JDBC] .

> The bridge is not threaded and Servlets/JSPs are.

I really don't see how this is different from the way threading is handled in Java I/O classes which do make native calls; the threading is most probably handled in the Java implementation of JdbcOdbc. Some real problems with ODBC implementations are invoking third party native code, installation required on the client machine, debugging nightmares etc. :-)

[masijade]

> > And you're real problem, at least IMHO, is using the JDBC-ODBC in Tomcat.

I don't think he is using a bridge type driver given the package name com.microsoft.jdbc.* and [Microsoft][SQLServer 2000 Driver for JDBC] .

Oops, you're right. Whenever I see that "[Microsoft]" I blank out and think JDBC-ODBC (I have never used SQL Server, so I usually only see it when someone is posting an error message that has to do with the bridge and access). ;-)

> The bridge is not threaded and Servlets/JSPs are.

I really don't see how this is different from the way threading is handled in Java I/O classes which do make native calls; the threading is most probably handled in the Java implementation of JdbcOdbc. Some real problems with ODBC implementations are invoking third party native code, installation required on the client machine, debugging nightmares etc. :-)

Maybe, maybe not. The way the thing is used makes a difference (at the very least in performance as the things usually spend more time using a connection and the like than other things). And, normally, DB's being such a large part of an application, it is just that much more likely to be a problem (or at the very least a drag). You can't even have multiple open statements on a single connection.

http://java.sun.com/products/jdbc/faq.html#15

In any case, the JDBC-ODBC bridge should always be, IMHO, the "driver of last resort". There must be a reason that the Driver is one of the warned against sun.* classes, even if it is not "advertised" as such and used in the tutorials (now that JavaDB comes with the jdk, maybe they'll eventually change the tutorial to use that). ;-)

[sbhavan]

I've solved this problem.

I have passed the queries and the connection to another method and did the transaction in that method. Now it is working fine.

I've done it in the following manner.

{
...
UpdateTransaction(Query1, Query2, Query3, Query4, Query5, connection);
...
}

public void UpdateTransaction(String q1, String q2, String q3, String q4, String q5, Connection con)
{
try
{
con.setAutoCommit(false);
Statement st=con.createStatement();
st.executeUpdate(q1);
st.executeUpdate(q2);
st.executeUpdate(q3);
st.executeUpdate(q4);
st.executeUpdate(q5);

con.commit();
}
catch(SQLException se)
{
try
{
con.rollback();
}
}
finally
{
con.setAutoCommit(true);
}

[sbhavan]

Thanks For you suggestions.

[~s.o.s~]

Consider using code tags to post your code next time. Read the FAQ's.

[Ezzaral]

Ok, with your method

public void UpdateTransaction(String q1, String q2, String q3, String q4, String q5, Connection con)

what happens when you only want to execute 2 statements or you need to execute 10 statements? Consider using a List as a parameter instead of coding in an arbitrary set of numbered parameters.

Also, you need to make sure you close your statement in the finally block. Always close statements when you are finished with them.