Spring 2.5 simpleJdbcTemplate.update not updating records from AJAX call

[oakleyx1]

Hello,

I have an internal web app. Basically its just a jsp that calls a servlet that calls my DAO Manager classes that uses spring 2.5 jdbc.

My jsp uses AJAX to call the servlet to update a row in a table.

In my servlet i call following method in my DAO class:

public void updatePhysician(PhysicianInfo pi) {
        
   String strSQL = "UPDATE provider_tab \n"
      + "SET wc_active=:wcActive,  ds_active=:dsActive\n"
      + "WHERE rec_id=:id";
		
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("wcActive",  pi.getWcActive()    );
    params.put("dsActive",  pi.getDsActive()    );
    params.put("id",   pi.getId()    );
        
    try {
        int i = this.simpleJdbcTemplate.update(strSQL, params);
        logger.info("i is  " +  i);
    } catch (Exception e) {
        e.printStackTrace();
        logger.info("EXCEPTION " + e.getMessage());
    }		
}

Here is how my DAO class is defined in the Application context.

<bean id="physicianDAO" scope="request" 
	      class="com.whatever.services.PhysicianDBManagerDAO">
	      <property name="dataSource" ref="dataSource" />
	</bean>

So basically what is happening is that I can update any row (record) once. When I try and update that same row again making an ajax call it won't update. I'm getting no errors and this is from my logs:

2011-06-30 11:15:19,707 DEBUG [org.springframework.jdbc.core.JdbcTemplate] - <Executing prepared SQL update>
2011-06-30 11:15:19,707 DEBUG [org.springframework.jdbc.core.JdbcTemplate] - <Executing prepared SQL statement [UPDATE provider_tab
SET wc_active=?, ds_active=?
WHERE rec_id=?]>
2011-06-30 11:15:19,707 DEBUG [org.springframework.jdbc.datasource.DataSourceUtil s] - <Fetching JDBC Connection from DataSource>
2011-06-30 11:15:19,707 DEBUG [org.springframework.jdbc.core.JdbcTemplate] - <SQL update affected 0 rows>
2011-06-30 11:15:19,707 DEBUG [org.springframework.jdbc.datasource.DataSourceUtil s] - <Returning JDBC Connection to DataSource>
2011-06-30 11:15:19,707 INFO [com.whatever.services.PhysicianDBManagerDAO] - <i is 0>
2011-06-30 11:15:19,707 DEBUG [org.springframework.web.context.request.RequestCon textListener] - <Cleared thread-bound request context: org.apache.catalina.connector.RequestFacade@951a0>

I don't know if its caching or what.

Any ideas.

Thanks
-Dan

[Marten Deinum]

1) Why is your dao request scoped? It should be perfectly legal to use it as a singleton
2) I see nowhere something regarding transactions, update/insert/delete is useless without transactions.

[oakleyx1]

Ok I removed the scope and added this to my applicationContext.xml

<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" />
 </bean>

I'm still getting the same problem.

Anything else i should try?]

Thanks
-Dan

[oakleyx1]

Figured it out. It was the trigger in the database. Every time the record was updating or inserting it was inserting a new record id. The trigger was suppose to just insert an record id on the insert. DOH!

[Marten Deinum]

Also simply adding a DataSourceTransactionManager is not enabling transactions. You also need something that drives/triggers the transaction.. I suggest a read on the reference guide.

[oakleyx1]

ok thanks marten! i'll take a look!