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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

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…
In this post we will learn different types of Android Layout and some basics of an Android App.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Suggested Courses
Course of the Month13 days, 22 hours left to enroll

801 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