Re: Problem reusing the same connection in Sql Server 2005 with ms
- From: "Ron Mihu" <RonMihu@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 25 Jan 2006 10:01:03 -0800
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:
>
> > 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 );
> > }
>
>
.
- References:
- Problem reusing the same connection in Sql Server 2005 with ms jdb
- From: Ron Mihu
- Re: Problem reusing the same connection in Sql Server 2005 with ms jdb
- From: Joe Weinstein
- Problem reusing the same connection in Sql Server 2005 with ms jdb
- Prev by Date: Re: Problem reusing the same connection in Sql Server 2005 with ms jdb
- Next by Date: Re: execute() vs executeUpdate() on a prepared statement
- Previous by thread: Re: Problem reusing the same connection in Sql Server 2005 with ms jdb
- Next by thread: Re: execute() vs executeUpdate() on a prepared statement
- Index(es):
Relevant Pages
|
Loading
