Problem with multiple prepares and setAutoCommit(false)

[Doug Krawczynski]

I downloaded the latest MSSQL JDBC Driver and gave it a
try and ran into a problem.

I set the auto commit feature to OFF for my connection:

Connection.setAutoCommit(false)

then I try to prepare multiple SQL statements in a row
without closing them. This is so I can execute them
faster later. I get an error when the second prepare
statement is executing.

If I change to Connection.setAutoCommit(true) instead, it
works fine.

I previously used the same code when connecting to a DB2
database and it worked fine. It seems to be a feature-bug
with with either the JDBC driver or SQL Server. One would
think I should be able to prepare as many SQL statements
as I would like and still be able to determine when I
would like to BEGIN and COMMIT work.

Any thoughts or suggestions on this topic are appreciated.

Thanks

----------- ERROR Message I Got -------

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver
for JDBC]Can't start a cloned connection while in manual
transaction mode.
at
com.microsoft.jdbc.base.BaseExceptions.createException
(Unknown Source)
at
com.microsoft.jdbc.base.BaseExceptions.getException
(Unknown Source)
at
com.microsoft.jdbc.base.BaseConnection.getImplConnection
(Unknown Source)
at
com.microsoft.jdbc.base.BaseStatement.setupImplConnection
(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.<init>
(Unknown Source)
at
com.microsoft.jdbc.base.BasePreparedStatement.<init>
(Unknown Source)
at
com.microsoft.jdbc.base.BaseConnection.prepareStatement
(Unknown Source)
at
com.microsoft.jdbc.base.BaseConnection.prepareStatement
(Unknown Source)
at com.hca.mars.wf.mds.MDSDB.openPreparedStatements
(MDSDB.java:344)

[Joseph Weinstein]

Doug Krawczynski wrote:

> I downloaded the latest MSSQL JDBC Driver and gave it a
> try and ran into a problem.
>
> I set the auto commit feature to OFF for my connection:
>
> Connection.setAutoCommit(false)
>
> then I try to prepare multiple SQL statements in a row
> without closing them. This is so I can execute them
> faster later. I get an error when the second prepare
> statement is executing.
>
> If I change to Connection.setAutoCommit(true) instead, it
> works fine.
>

This is an effect of an oddity in the driver's internal architecture.
Without going into all the detail (you'll find it in previous posts)
the solution is to add a connection-time property:
selectMethod=cursor

Joe Weinstein at BEA

[Doug Krawczynski]

Joe,

Thanks for the quick reply to my problem. I'm sorry, but
I could not find the previous posts you mentioned, there
seems to be a problem with the search feature. Not being
familiar with the driver, how/where do you set this
connection-time property?

Thanks in advance,

Doug

[Joseph Weinstein]

Doug Krawczynski wrote:

java.util.Properties props = new java.util.Properties();

props.put("user", "sa");
props.put("password", "secret");
props.put("selectMethod", "cursor");

Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://QABOX:1433", "sa","");

[Sarah Parra [MSFT]]

This problem is documented in the following Knowledge Base article:

PRB: Cannot Start a Cloned Connection While in Manual Transaction Mode
ID: 313181
http://support.microsoft.com/?id=313181

Thanks!
Sarah Parra
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

--------------------
>Content-Class: urn:content-classes:message
>From: "Doug Krawczynski" <Doug.Krawc...@comcast.net>
>Sender: "Doug Krawczynski" <Doug.Krawc...@comcast.net>
>References: <101401c377ea$27fab420$a601...@phx.gbl>
<3F6098B3...@bea.com>
>Subject: Re: Problem with multiple prepares and setAutoCommit(false)
>Date: Thu, 11 Sep 2003 17:38:39 -0700
>Lines: 12
>Message-ID: <05ee01c378c6$35a50210$a401...@phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcN4xjWikGbtdqtQRFOW3+m0wgECkA==
>Newsgroups: microsoft.public.sqlserver.jdbcdriver
>Path: cpmsftngxa06.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.jdbcdriver:4975
>NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
>X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver