Execute a sql command outside a mult-statement transaction??

Posted on 2005-02-25
Medium Priority
Last Modified: 2012-06-27
I am having some problems dropping and creating a temp database table from my java code.  I get an error telling me that it is not allowed:

java.sql.SQLException: [BEA][Sybase JDBC Driver][Sybase]The 'DROP TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database.

I found documentation online saying that I have to turn ddl in transaction on, however the dba's do not advise me to do so.  I then read that I have two other options.  
1.)  Use a permanent object name and not use tempdb
2.)  Execute the command outside a multi-statement transaction

As the first option is not ok, is there a way I can execute a command outside a multi-statement transaction in java?  

Thanks for your help!
Question by:kassidy2010
  • 3
LVL 19

Expert Comment

by:Jim Cakalic
ID: 13405634

What's the source of your JDBC Connection? I assume that you are getting a Connection object from a Weblogic configured DataSource? If so then it is likely that the Connection has autoCommit false be default so that the container (Weblogic) can manage the transaction.

First, when you get the connection, try setting autoCommit to true. This might work. It probably won't.

What you are likely going to need to do is create a connection using the old-fashioned DriverManager mechanism. This way you can get a connection that Weblogic doesn't know about and won't be coordinated in any kind of container-managed transaction.

Caveat: I don't know enough about Weblogic or Sybase to tell you whether this will actually work. But it's worth a try.

LVL 19

Expert Comment

by:Jim Cakalic
ID: 13455931
Hi. Did you have a chance to try these suggestions?

Author Comment

ID: 13487526
Yes I did.  IT is a driver issue with Weblogic.  I am working with the support team at BEA to tell me what is wrong with the driver.  I will post the solution once I have it.

I used a different JDBC driver and it worked.  I can not use that JDBC driver because our production server uses the weblogic driver.  Not sure if this is in the same ball park as your solution.

Thanks for your help Jim!
LVL 19

Expert Comment

by:Jim Cakalic
ID: 13668160
That's strange. I never saw email for the comment posted on 3-8. I guess I have no objection to the recommended closing.


Accepted Solution

modulo earned 0 total points
ID: 13692731
PAQed with points refunded (125)

Community Support Moderator

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
Suggested Courses
Course of the Month12 days, 17 hours left to enroll

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question