Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
The moose likes JDBC and the fly likes Can I call a stored proc without wrapping it in a transaction? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Can I call a stored proc without wrapping it in a transaction?" Watch "Can I call a stored proc without wrapping it in a transaction?" New topic
Author

Can I call a stored proc without wrapping it in a transaction?

David Peterson
author
Ranch Hand

Joined: Oct 14, 2001
Posts: 154
I'm using Microsoft SQL Server, but this is a general question about calling stored procs via JDBC.

I want to manage transactions within my stored proc (i.e. by placing BEGIN TRAN and COMMIT TRAN around relevant pieces). But whenever I call a stored proc through JDBC it always seems to be wrapped in a transaction - either implicitly with the 'auto-commit' setting turned on, or explicitly using commit().

Is there some way I can call SQL without having it wrapped in a transaction?

David
Reid M. Pinchback
Ranch Hand

Joined: Jan 25, 2002
Posts: 775
Databases have two kinds of statements: DML and DDL. DML is always in some form of transaction. Not a Java issue, just the reality of database architecture. Some databases will let you change the transaction isolation level so that different threads of execution can see each other's interim results, but the transactionality is still in effect - the changed isolation level just means that you don't have all the ACID properties.


Reid - SCJP2 (April 2002)
Jeff Albertson
Ranch Hand

Joined: Sep 16, 2005
Posts: 1780
By the way, it's usually not a good idea to have a java.sql.Connection's autoCommit property set to true -- that mode is intended for ad hoc SQL console work.


There is no emoticon for what I am feeling!
David Peterson
author
Ranch Hand

Joined: Oct 14, 2001
Posts: 154
Thanks for the replies.
 
jQuery in Action, 2nd edition
 
subject: Can I call a stored proc without wrapping it in a transaction?
 
Similar Threads
Urgent - Transaction
Java Database Connectivity
Unspecified transaction context
deadlock in oracle
Exception handling in EJB with Container Managed Transaction using Hibernate