Hi
I am developing a Database application. If I use transaction mode (i.e. con.setAutoCommit(false)) then exception occured as follows:

Can't start a cloned connection while in a manual transaction mode

But when I swtich off the transaction mode then it works just fine, but I want to use the transaction mode.

Anyone can help me why it is happening and how to overcome?

thanks in advance

 

 

i have the same problem :))) i just cannot understand why cannot jdbc driver create several connections when using transactions. or should we use connection pooling? i thought jdbc driver is automatically creating connections on demand, and connection pool just saves new connection creation time

any ideas would be of great appreciation

bastibubu

 

 

Sorry, haven't understood yet:
What do you do?

Do you really clone a connection? That would be no good thing at all. A Connection object represents a physical connection to the DBMS; a cloned Connection object would use the same physical connection.

Or do you just get an exception by calling con.setAutoCommit( false ) ?
Then I'd suspect you are in an transaction already and try to switch the transaction mode.
That sounds strange, but notice: even if you are first in AutoCommit( true ) mode, while you are going through a queried ResultSet, you are inside a transaction. The AutoCommit mode will close this transaction automatically as soon as the query is "completed", by
- closing the ResultSet
- closing the Statement or
- reaching the last row of the ResultSet.

If this is your situation, then I'd try to call con.setAutoCommit( false ) directly after opening the connection.

Please - both of you - report your further experiences!
It's really interesting - for me and maybe for others, too.

 

 

Hi
Thanks for your attention.
I am creating a single connection as soon as the user access the Application and the same connection is being passed to different Swing GUI as parameter where the connection object is used to do all these JDBC job.

It was do fine using other Drivers but while I started using Microsofts JDBC Driver for SQL Server 2000 I have been experiencing this problem.

Now I explain about my implementation.
Before I start retrieving /saving records in table(s) I wrote, connection.setAutoCommit(false) and after all transactions is complete and resultSet and Statement is closed, I passed connection.setAutoCommit(true).
In this case the exception occured as I explained (cloned connection).

But If I close the transaction mode, it works just fine, no exceptions and updates the database.

Ho you have any idea

 

 

Before connection.setAutoCommit(true),
have you done connection.commit() or connection.rollback() ?

 

 

Hi
I have done like this-

try{
connection.setAutoCommit(false);
Statement stmt=connection.createStatement();
........


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

}catch(SQLException ex){
ex.printStackTrace();
.....
...
connection.rollback();
}
}

 

 

Haven't tested this out, but I think it's a risk in general to switch transaction mode on and off.

So I'd close the statement before commit even, to be sure that no rest of the transaction "survives".

After switching the AutoCommit, create a new statement.

Could you try this and report again?
What's the behaviour, which exceptions rise?

 

 

Haven't tested this out, but I think it's a risk in general to switch transaction mode on and off.

So I'd close the statement before commit even, to be sure that no rest of the transaction "survives".

After switching the AutoCommit, create a new statement.

Could you try this and report again?
What's the behaviour, which exceptions rise?

 

 

Hi
I closed the statement before doing the Commit statement. Moreover, I used the same statement to execute several sql to handle data in different, Any suggestions?

 

 

i am using tomcat as a container. i think it should automatically create new connection instance, instead of trying to clone existing one. i am creating transactions the same way as u have written here mortoza.

strange is that i thought this was a mssql driver problem. but it appears that it behaves the same way with other drivers too, like JTurbo, or odbc bridge. error messages ar different, but they comply somehow to each other. JTurbo says that Socket is already in use. i thought may be my MSSQL server doesnt allow somehow simultanous connections, but after testing with test application appears that it do can.

so i am confused now :)) where is the problem. what code tries to clone connection instead of creating a new one.

P.S. if i switch transaction mode off, then everything is ok :))

 

 

bastibubu,
you seem to be not able to use transaction at all in your environment.
That looks bad. Sorry, can't help.

mortoza,
if I understood right, you get that exception only if you switch the AutoCommit mode.
Could you as workaround stay in AutoCommit( false ) mode? You would have to do the commits explicitely.

Is that possible for you? Or do other problems arise then?

 

 

Dear Hartmut
You are right. When I set AutoCommit(false) then this problem arises. More surprising thing is sometiomes it works for once. I need to put off the autoCommit mode off in order to ensure data integrety in my database application.

Any help?

 

 

Hi,

You need to use the property 'SelectMethod' for the driver in the URL and set it to 'cursor'

So your driver url should be like :

"jdbc:microsoft:sqlserver://dbmachine:1433;SelectMethod=cursor"



Sandeep Soni.

 

 

It works ...thanks :)

 

 

nice one. fixed my stored procedure by callable statement error as well
(same error message, same cause, i suspect)

kevin.