Hi,
I have an @SQLUpdate statement in my entity which I have recently had to update to add an NVL around one of the attributes.
Code:
@org.hibernate.annotations.SQLUpdate(sql = "update test_table set test_ser=nvl(?,0)")
However when I run this I get the error below
Code:
org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not update: [lu.scoteqint.domain.entity.instruction.detail.SwitchWithdrawalMovementInstruction#635631]; bad SQL grammar [update test_table set test_ser=nvl(?,0)]; nested exception is java.sql.SQLException: A syntax error has occurred.
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.orm.hibernate3.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:424)
at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:407)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:411)
at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
at org.springframework.orm.hibernate3.HibernateTemplate.flush(HibernateTemplate.java:881)
at
...
Caused by: java.sql.SQLException: A syntax error has occurred.
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:408)
at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3461)
at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3774)
at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2580)
at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2496)
at com.informix.jdbc.IfxSqli.executePrepare(IfxSqli.java:1360)
at com.informix.jdbc.IfxPreparedStatement.e(IfxPreparedStatement.java:320)
at com.informix.jdbc.IfxPreparedStatement.a(IfxPreparedStatement.java:300)
at com.informix.jdbc.IfxPreparedStatement.<init>(IfxPreparedStatement.java:170)
at com.informix.jdbc.IfxSqliConnect.h(IfxSqliConnect.java:6537)
at com.informix.jdbc.IfxSqliConnect.prepareStatement(IfxSqliConnect.java:2348)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534)
at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:116)
at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:109)
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2380)
at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:2325)
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2625)
at org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:115)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:168)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1028)
at org.springframework.orm.hibernate3.HibernateTemplate$28.doInHibernate(HibernateTemplate.java:883)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406)
... 43 more
Caused by: java.sql.SQLException
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:408)
at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3779)
... 66 more
I can see the sql in the console as
Code:
Hibernate: update test_table set test_ser=nvl(?,0)
which works fine when I run the sql directly.
I have created a new function "nvl_int" and when I call that instead of "nvl" that also works.
I have updated the Dialect to try and use the NVLFunction to no avail.
I think this is a problem around the dynamic typing of the nvl response, but cannot be certain.
We are using Informix 11.5.
I'd appreciate if anyone has any ideas.
Thanks,
Barry