Transaction problem with Sybase, Rollback doesn't happen

amy

mons.dbcp.BasicDataSourcequot; destroy-method=quot;closequot;gt; lt;property name=quot;driverClassNamequot;gt;   lt;valuegt;com.sybase.jdbc3.jdbc.SybDriverlt;/valuegt; lt;/propertygt; lt;property name=quot;uclquot;gt;   lt;valuegt;jdbc:sybase:Tds:jmartinez:5000/banexlt;/valuegt; lt;/propertygt; lt;property name=quot;usernamequot;gt;   lt;valuegt;banex1lt;/valuegt; lt;/propertygt; lt;property name=quot;passwordquot;gt;   lt;valuegt;banex1lt;/valuegt; lt;/propertygt; lt;property name=quot;maxActivequot;gt;   lt;valuegt;10lt;/valuegt; lt;/propertygt; lt;property name=quot;maxIdlequot;gt;   lt;valuegt;2lt;/valuegt; lt;/propertygt;
lt;/beangt;
lt;bean id=quot;direccionDaoquot; class=quot;cr.fi.banex.data.spring.dao.DireccionDaoquot; singleton=quot;falsequot;gt;lt;property name=quot;jdbcTemplatequot;gt;lt;ref bean=quot;jdbcTemplatequot; /gt;lt;/propertygt;lt;/beangt;

lt;bean id=quot;baseTransactionProxyquot; class=quot;org..transaction.interceptor.TransactionProxyFactoryBeanquot;
abstract=quot;truequot;gt;
lt;property name=quot;transactionManagerquot;gt;lt;ref bean=quot;transactionManagerquot;/gt;lt;/propertygt;
lt;property name=quot;transactionAttributesquot;gt;
lt;propsgt;
lt;prop key=quot;*quot;gtROPAGATION_REQUIRES_NEW ,-Exceptionlt;/propgt;
lt;/propsgt;
lt;/propertygt;
lt;/beangt;

lt;bean id=quot;direccionesServicequot; parent=quot;baseTransactionProxyquot;gt;
lt;property name=quot;targetquot;gt;
lt;bean class=quot;cr.fi.banex.data.service.DireccionesServicequot;gt;
lt;/beangt;
lt;/propertygt;
lt;/beangt;
This is the service class

Code:
public class DireccionesService extends BaseService implements IDireccionesService {

public int actualizarDireccion (Direccion dir) {
try {FileSystemResource res = new FileSystemResource( quot;c:\\temp\\spring-config.xmlquot;);
XmlBeanFactory beanFactory = new XmlBeanFactory(res);
cr.fi.banex.data.spring.dao.DireccionDao direccionDao2 = (cr.fi.banex.data.spring.dao.DireccionDao) beanFactory.getBean(quot;direccionDaoquot;); direccionDao2.eliminarTelefonos(dir);
direccionDao2.insertarTelefonos(dir.getTelefonos());

return 1;

} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage());
}

}

}
This is the DAOCode:
public class DireccionDao extends Dao implements IDireccionDao {

private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate){
this.jdbcTemplate = jdbcTemplate;
}
public int eliminarTelefonos(Direccion dir) throws SQLException {

String sql = quot;delete from banex1.telefonos where codigoDireccion = quot; + dir.getCodigo();

return jdbcTemplate.update(sql);

}

public int insertarTelefono(Telefono tel) throws SQLException {

//insert2 causes the exception that should trigger the rollback
String sql = quot;insert2 into banex1.telefonos (codigoDireccion, telefono, descripcion) values (?,?,?) quot;;

Object args[] = new Object[] { new Integer(tel.getCodigoDireccion()), tel.getTelefono(), tel.getDescripcion()};
int types[] = new int[] { Types.INTEGER, Types.VARCHAR, Types.VARCHAR };

return jdbcTemplate.update(sql, args, types);

}

}
And I test it like thisCode:
FileSystemResource res = new FileSystemResource( quot;c:\\temp\\spring-config.xmlquot;);
XmlBeanFactory beanFactory = new XmlBeanFactory(res);
IDireccionesService sv = (IDireccionesService)  beanFactory.getBean(quot;direccionesServicequot;);
Direccion dir = new Direccion();
dir.setCodigo(1);

sv.actualizarDireccion(dir);
After the exception in the insert operation is thrown, and even though I get a message in the log that suggests that the rollback is taking place, the delete operation is not being rollbacked.Code:
2007-03-26 15:26:22,756 DEBUG org..transaction.interceptor.RuleBasedTransactionAttribute [rollbackOn] - Applying rules to determine whether transaction should rollback on java.lang.RuntimeException: PreparedStatementCallback; uncategorized SQLException for SQL [insert2 into banex1.telefonos (codigoDireccion, telefono, descripcion) values (?,?,?) ]; SQL state [ZZZZZ]; error code [156]; Incorrect syntax near the keyword 'into'.
; nested exception is com.sybase.jdbc3.jdbc.SybSQLException: Incorrect syntax near the keyword 'into'.

2007-03-26 15:26:22,756 DEBUG org..transaction.interceptor.RuleBasedTransactionAttribute [rollbackOn] - Winning rollback rule is: RollbackRule with pattern [Exception]
2007-03-26 15:26:22,756 DEBUG org..transaction.interceptor.TransactionInterceptor [doCloseTransactionAfterThrowing] - Invoking rollback for transaction on cr.fi.banex.data.service.IDireccionesService.actualizarDireccion due to throwable [java.lang.RuntimeException: PreparedStatementCallback; uncategorized SQLException for SQL [insert2 into banex1.telefonos (codigoDireccion, telefono, descripcion) values (?,?,?) ]; SQL state [ZZZZZ]; error code [156]; Incorrect syntax near the keyword 'into'.
Any help will be appreciated.

You need to remove the dependency lookup in the DireccionesService class.  You should inject the dependency instead of looking it up.  As you are creating a new BeanFactory this won't work.  I would also use an ApplicationContext instead of BeanFactory, it offers more features that you usually end up needing and then can't work out why the BeanFactory doesn't do what you expect.
docs/...tter-injection

Thank you for your answer, karldmoore, with that I could make it work.

I guess I wasn't in the Spring mindset yet, and I was thinking of creating my own factories instead of Spring's.

After injecting the dao in the service and using the application context to obtain the service bean, the rollback worked perfectly.

Thanks again.

Not a problem, I'm glad the information made sense and solved your problem.  It does take a while to get used to letting Spring manage your objects.  One BeanFactory should be enough for your code .