Hi,
I can't successful use sql-insert and sql-update tags with stored procedure written in Sybase. I tried different approaches, but none of them worked.
In first approach I used these tags with stored proc and ?, like this.
Code:
<sql-update callable="true">{ ? = call upd_account_sp(?, ?) }</sql-update>
This caused the following Sybase JDBC exception at the time of parameter binding.
Code:
Caused by: msjava.dbpool.DBPoolSQLException: JZ0SC: Callable Statement: attempt to set the return status as an input parameter. (DataSource: NrSybaseUpdateDatasource, Type: SYBASE)
at com.sybase.jdbc3.jdbc.ErrorMessage.raiseError(Unknown Source) ~[jconn3.jar:na]
at com.sybase.jdbc3.jdbc.ParamManager.doSetParam(Unknown Source) ~[jconn3.jar:na]
at com.sybase.jdbc3.jdbc.ParamManager.setParam(Unknown Source) ~[jconn3.jar:na]
at com.sybase.jdbc3.jdbc.SybPreparedStatement.a(Unknown Source) ~[jconn3.jar:na]
at com.sybase.jdbc3.jdbc.SybPreparedStatement.a(Unknown Source) ~[jconn3.jar:na]
at com.sybase.jdbc3.jdbc.SybPreparedStatement.setString(Unknown Source) ~[jconn3.jar:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.6.0_18]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) ~[na:1.6.0_18]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) ~[na:1.6.0_18]
at java.lang.reflect.Method.invoke(Method.java:597) ~[na:1.6.0_18]
at msjava.dbpool.connectionwrapper.internal.JdbcProxyingUtils$SQLExceptionHandlingInvocationHandler.invoke(JdbcProxyingUtils.java:252) ~[msjava_dbpool.jar:1.2]
at $Proxy18.setString(Unknown Source) ~[na:na]
at org.hibernate.type.StringType.set(StringType.java:26) ~[hibernate3.jar:3.2.6.ga]
at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:136) ~[hibernate3.jar:3.2.6.ga]
at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:107) ~[hibernate3.jar:3.2.6.ga]
at org.hibernate.persister.entity.AbstractEntityPersister.dehydrate(AbstractEntityPersister.java:2002) ~[hibernate3.jar:3.2.6.ga]
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2376) ~[hibernate3.jar:3.2.6.ga]
In second approach I removed ? from the proc's call and got farther, but still received different exception.
Code:
<sql-update callable="true">{ call upd_account_sp(?, ?) }</sql-update>
The stored procedure ran and the record was updated in database, but I got exception after update executed successfuly.
Code:
org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1
at org.hibernate.jdbc.Expectations$BasicExpectation.checkBatched(Expectations.java:61) ~[hibernate3.jar:3.2.6.ga]
at org.hibernate.jdbc.Expectations$BasicExpectation.verifyOutcome(Expectations.java:46) ~[hibernate3.jar:3.2.6.ga]
at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:24) ~[hibernate3.jar:3.2.6.ga]
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2408) ~[hibernate3.jar:3.2.6.ga]
at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:2312) ~[hibernate3.jar:3.2.6.ga]
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2612) ~[hibernate3.jar:3.2.6.ga]
at org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:96) ~[hibernate3.jar:3.2.6.ga]
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279) ~[hibernate3.jar:3.2.6.ga]
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263) ~[hibernate3.jar:3.2.6.ga]
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:168) ~[hibernate3.jar:3.2.6.ga]
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298) ~[hibernate3.jar:3.2.6.ga]
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27) [hibernate3.jar:3.2.6.ga]
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000) [hibernate3.jar:3.2.6.ga]
at org.springframework.orm.hibernate3.HibernateAccessor.flushIfNecessary(HibernateAccessor.java:390) [org.springframework.orm.jar:3.0.2.RELEASE]
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:407) [org.springframework.orm.jar:3.0.2.RELEASE]
at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374) [org.springframework.orm.jar:3.0.2.RELEASE]
at org.springframework.orm.hibernate3.HibernateTemplate.saveOrUpdate(HibernateTemplate.java:737) [org.springframework.orm.jar:3.0.2.RELEASE]
I looked at code, documentation and stored proc's code and realized Hibernate code is expecting number of updates returned from the call to executeUpdate. So I made changes to Sybase stored proc to return a value back to a caller.
To my knowledge the way to return value from Sybase stored procs is either with return statement or select @@rowcount statement, none of wich worked with Hibernate in case of use of sql-update, sql-insert tags. Use of return statement had no effect on row count returned by executeUpdate, and using select statement caused Sybase JDBC exception related to the fact of improper use of executeUpdate to get resultset - select @@rowcount result in resultset being returned from stored proc.
Thank you
Login
FAQ
Search