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



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_index'. 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.getConnectionInstance();
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_index'. 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_index'.");
// 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 */
TTESummaryFabSpecialWorkRequest.store( summary );
/* Load the Verticle Table */
TTESummaryVerticle.store( summary );
/* Load the Output Lot Reference Table */
TTESummaryOutputLotRef.store( summary );
/* Load the Excursion Table */
TTESummaryExcursion.store( summary );
} 

.

Relevant Pages

  • Re: Driver AutoCommit issue
    ... Isn't the XA driver for the distributed transaction, I need to install the path sqljdbc_xa.dll and xa_install.sql before using the XA driver. ... I created a TX aware datasource using com.microsoft.sqlserver.jdbc.SQLServerDriver and tried to get the connection from the TX aware data source. ... I set the auto commit to false on connection as soon as I get the connection from the datasoruce. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Driver AutoCommit issue
    ... Isn't the XA driver for the distributed transaction, ... the connection pool was getting created. ... When in the code do you do a commit? ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Driver AutoCommit issue
    ... I am not using the driver from a stand alone application, ... JDBC in any WebLogic/EJB transaction. ... When in the code do you do a commit? ... Is the connection you're using shared, ...
    (microsoft.public.sqlserver.jdbcdriver)
  • RE: Snapshot isolation with pooled connections
    ... This test case demonstrates a bug in the Microsoft SQL Server 2005 JDBC ... the driver does not initialize connections properly. ... time a connection is borrowed from the pool, ... When a snapshot transaction conflict occurs, ...
    (microsoft.public.sqlserver.jdbcdriver)
  • [bug] stuck localhost TCP connections, v2.6.26-rc3+
    ... That connection has been stuck for 9 ... and it occured on two boxes, both are SMP ... # Bus options ... # Generic Driver Options ...
    (Linux-Kernel)
Loading