SQL Server JDBC - Re: Problem reusing the same connection in Sql Server 2005 with ms

Author

Re: Problem reusing the same connection in Sql Server 2005 with ms

Joe Weinstein

2006-01-26, 4:53 pm

Ron Mihu wrote:

> Ok,
>
> I am really new at this and am having some issues and not sure where to
> start. Here is what I am seeing.
>
> I connect to sql server 2005 just fine.
>
> So I try and load some tables. If the database is empty, it loads.
>
> If there is already a row there, I catch the exception (Violation of PRIMARY
> KEY constraint 'PK_tte_summary_inde
x'. Cannot insert duplicate key in object
> 'dbo. tte_summary_index'),
try and reuse the same connection to run a stored
> proc to delete the record. When I do this I get:
> com.microsoft.sqlserver.jdbc.SQLServerException: Server failed to resume the
> transaction, desc: 3a00000001. I went ahead and used logger to catch all
> the events.
> ...
> What do I need to do to reuse the connection? If I close the connection, it
> works, but to me that is a VERY Expensive price to pay to keep working. This
> would work using the 2000 driver if I added 'SelectMethod=Cursor
' to the
> connection string. I found where the name had changed to
> 'selectMethod-cursor' in the 2005 driver, but it doesn't work the same.
>
> I Googled this problem without any luck. Isn't there anyone else having
> this problem? Or, am I really missing the boat here?

It would be good to show us the full original stack trace of
the exception you got. However, I can surmise it has to do with
the issue we've been thrashing out in another thread, to do with
how the new driver implements setAutoCommit(), commit(), rollback()
and the problem with MS SQLServer that it will completely (needlessly)
terminate a transaction for some user-level errors.
You shouldn't need to set autoCommit false all the time. Just
call getAutoCommit() and it it says true, then set it false.
Do you *need* all inserts to be one transaction? If not,
it will avoid the current driver problem to just do the inserts
until you're complete. I could come up with JDBC that would
be a hack, but it would get around the tx issue. However,
the best way to do it might be to add a qualification to your
insert SQL so it *checks* whether the row is already there first,
and if so, it could either delete it and insert the new version,
or skip it if the old version is still OK. Don't rely on failures
as information. Here's a cheap example:

Statement stmt = c.createStatement();
stmt.executeUpdate("create table #foo(bar int)");

String insert_if_absent =
"declare @qwe int select @qwe = count(*) from #foo where bar = 1 "
+ " if (@qwe = 0) insert into #foo values(1)";

stmt. executeUpdate(insert
_if_absent);
stmt. executeUpdate(insert
_if_absent);
stmt. executeUpdate(insert
_if_absent);
stmt. executeUpdate(insert
_if_absent);

ResultSet r = stmt.executeQuery("select count(*) from #foo");
while(r.next())
System.out.println("We inserted " + r.getString(1) + " row." );
I get:
We inserted 1 row.

HTH,
Joe Weinstein at BEA Systems

>
> Thanks,
>
> Ron Mihu
>
> public void loadCommonTables( Summary summary ) throws SQLException {
> Connection con = ConnectionSingleton. getConnectionInstanc
e();
> con. setAutoCommit(false)
;
> /* Load the Index Table */
> try {
> TTESummaryIndex.store( summary );
> } catch (SQLException reload) {
> if ( reload.getMessage().contains("Violation of PRIMARY KEY
> constraint 'PK_tte_summary_inde
x'. Cannot insert duplicate key in object
> 'dbo.tte_summary_index'")) {
> System.out.println("Record already exists. Will reload due
> to Violation of PRIMARY KEY constraint 'PK_tte_summary_inde
x'.");
> // con.rollback();
> // con.close();
> remove();
> System.out.println("Reset Complete: Retry Insert of Record.");
>
> if ( con.isClosed()) {
> System.out.println("The connection is Closed.");
> }
>
> TTESummaryIndex.store( summary );
> } else {
> System.out.println("Database Error: " + reload.getMessage());
> System.out.println("Record already exists. Will reload.");
> remove();
> con.commit();
> System.out.println("Reset Complete: Retry Insert of Record.");
> TTESummaryIndex.store( summary );
> }
> }
>
> /* Load the Product Table */
> TTESummaryProduct.store( summary );
>
> /* Load the Product2 Table */
> TTESummaryProduct2.store( summary );
>
> /* Load the Reticle Table */
> TTESummaryReticle.store( summary );
>
> /* Load the Fab Special Work Request Table */
> TTESummaryFabSpecial
WorkRequest.store( summary );
>
> /* Load the Verticle Table */
> TTESummaryVerticle.store( summary );
>
> /* Load the Output Lot Reference Table */
> TTESummaryOutputLotR
ef.store( summary );
>
> /* Load the Excursion Table */
> TTESummaryExcursion.store( summary );
> }

Ron Mihu

2006-01-26, 4:53 pm

Thanks Joe! What I went ahead and did was rolled all this logic into the
stored proc. Not really the right thing to do, but we will address that
later. This gets me past this problem.

Thanks again!

Ron Mihu
Test Engineering Software Engineer
Micron Technology, Inc.

"Joe Weinstein" wrote:

>
>
> Ron Mihu wrote:
>
>
> It would be good to show us the full original stack trace of
> the exception you got. However, I can surmise it has to do with
> the issue we've been thrashing out in another thread, to do with
> how the new driver implements setAutoCommit(), commit(), rollback()
> and the problem with MS SQLServer that it will completely (needlessly)
> terminate a transaction for some user-level errors.
> You shouldn't need to set autoCommit false all the time. Just
> call getAutoCommit() and it it says true, then set it false.
> Do you *need* all inserts to be one transaction? If not,
> it will avoid the current driver problem to just do the inserts
> until you're complete. I could come up with JDBC that would
> be a hack, but it would get around the tx issue. However,
> the best way to do it might be to add a qualification to your
> insert SQL so it *checks* whether the row is already there first,
> and if so, it could either delete it and insert the new version,
> or skip it if the old version is still OK. Don't rely on failures
> as information. Here's a cheap example:
>
> Statement stmt = c.createStatement();
> stmt.executeUpdate("create table #foo(bar int)");
>
> String insert_if_absent =
> "declare @qwe int select @qwe = count(*) from #foo where bar = 1 "
> + " if (@qwe = 0) insert into #foo values(1)";
>
> stmt. executeUpdate(insert
_if_absent);
> stmt. executeUpdate(insert
_if_absent);
> stmt. executeUpdate(insert
_if_absent);
> stmt. executeUpdate(insert
_if_absent);
>
> ResultSet r = stmt.executeQuery("select count(*) from #foo");
> while(r.next())
> System.out.println("We inserted " + r.getString(1) + " row." );
> I get:
> We inserted 1 row.
>
> HTH,
> Joe Weinstein at BEA Systems
>
>
>

Sponsored Links