[Springframework-developer] Turning off autocommit in JBoss / Oracle datasource

[Levkovitch, L. <lle...@ac...>]

I think I've sent this to a wrong list before... Sorry!
=========================
Thanks to Rod and Colin for responses and advice, and big thanks to Dmitriy
for on-site debugging!

My problem was actually in my own code. Because I am using some special
features of Oracle 9 (namely, XMLType), I have to get a connection and
create my own prepared statements with it. I was getting this connection
from my DAO's datasource (getDs().getConnection()), instead of using
DataSourceUtils.getConnection(getDs()) as the documentation instructs. As
soon as I changed that, all my transactions started to work perfectly! 

Now I understand that I don't really have to change the autocommit property,
but in case someone is interested, here is what I found out (and it isn't
much!). According to JBoss forums, autocommit is SUPPOSED to be off with my
configuration (which is really just the default Oracle config file for
JBoss). 
Oracle's instructions for creating a datasource file do not discuss
autocommit at all, and it seems that there's no property in the datasource
configuration that controls it; one can only change it programmatically. 
I guess in my case it is off by default, but then something switches it on
as JBoss starts up with whatever services I have set up there. Not that it
bothers me now, since Spring takes care of everything.  

Once again - thanks, everyone!
Lidia

-----Original Message-----
Folks,

I am using a datasource to have my Spring DAOs connect to Oracle database.
Apparently, with my set up, autocommit is turned on, and this is not what I
need! I have actually put this line of code inside one of the DAOs'
afterPropertiesSet() method:
logger.debug("autocommit is " + getDs().getConnection().getAutoCommit());
and this prints "autocommit is true". 

Is there a way to configure my datasource so that autocommit is set to
false? I am using JBoss 3.2.1 with Oracle 9.2. In one of JBoss forums it
actually.

The datasource is defined in myapp-ds.xml file like this:
(this is based on the example that came with JBoss) <datasources>
  <local-tx-datasource>
    <jndi-name>myapp-ds</jndi-name>
 
	<connection-url>jdbc:oracle:thin:@[my database
name]</connection-url>
    <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
    <user-name>[my user name]</user-name>
    <password>[my password]</password>
    <min-pool-size>3</min-pool-size>
    <max-pool-size>30</max-pool-size>
    <blocking-timeout-millis>3000</blocking-timeout-millis>
    <idle-timeout-minutes>15</idle-timeout-minutes> 
	
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleEx
ceptionSorter</exception-sorter-class-name>
  </local-tx-datasource>
</datasources>

All my DAO objects use the dataSource bean which is defined in my
application context as follows: <bean id="dataSource"
class="org.springframework.jndi.JndiObjectFactoryBean"
singleton="true" lazy-init="default" autowire="default"
dependency-check="default">
- <property name="jndiName">
  <value>myapp-ds</value> 
  </property>
- <property name="resourceRef">
  <value>true</value> 
  </property>
  </bean>

Thank you very much!
Lidia