-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 1 post ] 
  Previous topic | Next topic 
Author Message
rmarriott30
 Post subject: <sql-update> does not work with Oracle stored procedur
PostPosted: Tue Jun 10, 2008 12:28 pm 
Newbie

Joined: Tue Jun 10, 2008 12:09 pm
Posts: 1
Location: Copenhagen
Hibernate version: 3.2.6

Name and version of the database you are using: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

I am trying to call an update stored procedure - when I try to match the example in the Hibernate document of a function returning a number equal to the SQL%ROWCOUNT, I get the following error:

131451 [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] DEBUG entity.AbstractEntityPersister - 2008-06-10 17:54:44,664 Updating
entity: [no.netcom.ninja.core.referencetables.SystemDefaults#AUTOCOMPLETE_RUN_TIME]
131451 [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] DEBUG jdbc.AbstractBatcher - 2008-06-10 17:54:44,664 about to open Prep
aredStatement (open PreparedStatements: 0, globally: 0)
131451 [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] DEBUG hibernate.SQL - 2008-06-10 17:54:44,664 { ? = call ninjadevmain.r
eference_tables.update_system_defaults (?, ?, ?, ?)}
131452 [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] DEBUG jdbc.AbstractBatcher - 2008-06-10 17:54:44,665 preparing callable
statement
131452 [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] DEBUG entity.AbstractEntityPersister - 2008-06-10 17:54:44,665 Dehydrat
ing entity: [no.netcom.ninja.core.referencetables.SystemDefaults#AUTOCOMPLETE_RUN_TIME]
131452 [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] DEBUG type.StringType - 2008-06-10 17:54:44,665 binding '17:17' to para
meter: 1
131453 [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] DEBUG type.StringType - 2008-06-10 17:54:44,666 binding 'STRING' to par
ameter: 2
131453 [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] DEBUG type.StringType - 2008-06-10 17:54:44,666 binding 'Time of day wh
ere auto completer wakes up' to parameter: 3
131453 [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] DEBUG type.StringType - 2008-06-10 17:54:44,666 binding 'AUTOCOMPLETE_R
UN_TIME' to parameter: 4
131454 [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] DEBUG jdbc.AbstractBatcher - 2008-06-10 17:54:44,667 about to close Pre
paredStatement (open PreparedStatements: 1, globally: 1)
131454 [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] DEBUG jdbc.AbstractBatcher - 2008-06-10 17:54:44,667 closing statement
131463 [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] DEBUG util.JDBCExceptionReporter - 2008-06-10 17:54:44,676 could not up
date: [no.netcom.ninja.core.referencetables.SystemDefaults#AUTOCOMPLETE_RUN_TIME] [{ ? = call ninjadevmain.reference_tables.update_system_
defaults (?, ?, ?, ?)}]
java.sql.SQLException: Manglende IN- eller OUT-parameter i indeks:: 5
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1711)
at oracle.jdbc.driver.OraclePreparedStatement.addBatch(OraclePreparedStatement.java:10488)
at oracle.jdbc.driver.OracleCallableStatement.addBatch(OracleCallableStatement.java:2003)
at weblogic.jdbc.wrapper.CallableStatement_oracle_jdbc_driver_T4CCallableStatement.addBatch(Unknown Source)
at org.hibernate.jdbc.BatchingBatcher.addToBatch(BatchingBatcher.java:31)
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2408)
at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:2312)
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2612)
at org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:96)
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:298)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
at no.netcom.ninja.core.system.persistence.RetrieveStaticData.updateSystemDefaults(RetrieveStaticData.java:91)
at dk.telia.ninja.sp.xmlinterface.process.SP_NPConfirmation_2.validateParameters(SP_NPConfirmation_2.java:200)
at dk.telia.ninja.sp.xmlinterface.process.SP_NPConfirmation_2.process1(SP_NPConfirmation_2.java:124)
at dk.telia.ninja.sp.xmlinterface.process.SP_NPConfirmation_2.process(SP_NPConfirmation_2.java:80)
at dk.telia.util.xmlservlet.XMLServlet.processRequest(Unknown Source)
at dk.telia.util.xmlservlet.XMLServlet.doPost(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at weblogic.servlet.internal.ServletStubImpl$ServletInvocationAction.run(ServletStubImpl.java:1077)
at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:465)
at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:348)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:7047)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:121)
at weblogic.servlet.internal.WebAppServletContext.invokeServlet(WebAppServletContext.java:3902)
at weblogic.servlet.internal.ServletRequestImpl.execute(ServletRequestImpl.java:2773)
at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:224)
at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:183)

Yet I have coded it exactly as recommended in the Hibernate online document.
If I replace the FUNCTION with an Oracle PROCEDURE with no OUT parameters, I get the following error:

167105 [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] DEBUG entity.AbstractEntityPersister - 2008-06-10 16:24:02,823 Updating
entity: [no.netcom.ninja.core.referencetables.SystemDefaults#AUTOCOMPLETE_RUN_TIME]
167105 [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] DEBUG jdbc.AbstractBatcher - 2008-06-10 16:24:02,823 about to open Prep
aredStatement (open PreparedStatements: 0, globally: 0)
167105 [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] DEBUG hibernate.SQL - 2008-06-10 16:24:02,823 {call ninjadevmain.refere
nce_tables.update_system_defaults (?, ?, ?, ?)}
167105 [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] DEBUG jdbc.AbstractBatcher - 2008-06-10 16:24:02,823 preparing callable
statement
167107 [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] DEBUG entity.AbstractEntityPersister - 2008-06-10 16:24:02,825 Dehydrat
ing entity: [no.netcom.ninja.core.referencetables.SystemDefaults#AUTOCOMPLETE_RUN_TIME]
167107 [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] DEBUG type.StringType - 2008-06-10 16:24:02,825 binding '17:17' to para
meter: 1
167107 [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] DEBUG type.StringType - 2008-06-10 16:24:02,825 binding 'STRING' to par
ameter: 2
167107 [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] DEBUG type.StringType - 2008-06-10 16:24:02,825 binding 'Time of day wh
ere auto completer wakes up' to parameter: 3
167108 [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] DEBUG type.StringType - 2008-06-10 16:24:02,826 binding 'AUTOCOMPLETE_R
UN_TIME' to parameter: 4
167108 [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] DEBUG jdbc.AbstractBatcher - 2008-06-10 16:24:02,826 Executing batch si
ze: 1
167111 [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] ERROR jdbc.AbstractBatcher - 2008-06-10 16:24:02,829 Exception executin
g batch:
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)
at org.hibernate.jdbc.Expectations$BasicExpectation.verifyOutcome(Expectations.java:46)
at org.hibernate.jdbc.BatchingBatcher.checkRowCounts(BatchingBatcher.java:68)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:266)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:168)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
at no.netcom.ninja.core.system.persistence.RetrieveStaticData.updateSystemDefaults(RetrieveStaticData.java:91)
at dk.telia.ninja.sp.xmlinterface.process.SP_NPConfirmation_2.validateParameters(SP_NPConfirmation_2.java:200)
at dk.telia.ninja.sp.xmlinterface.process.SP_NPConfirmation_2.process1(SP_NPConfirmation_2.java:124)
at dk.telia.ninja.sp.xmlinterface.process.SP_NPConfirmation_2.process(SP_NPConfirmation_2.java:80)
at dk.telia.util.xmlservlet.XMLServlet.processRequest(Unknown Source)
at dk.telia.util.xmlservlet.XMLServlet.doPost(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at weblogic.servlet.internal.ServletStubImpl$ServletInvocationAction.run(ServletStubImpl.java:1077)
at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:465)
at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:348)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:7047)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:121)
at weblogic.servlet.internal.WebAppServletContext.invokeServlet(WebAppServletContext.java:3902)
at weblogic.servlet.internal.ServletRequestImpl.execute(ServletRequestImpl.java:2773)
at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:224)
at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:183)
167113 [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] DEBUG jdbc.AbstractBatcher - 2008-06-10 16:24:02,831 about to close Pre
paredStatement (open PreparedStatements: 1, globally: 1)


If replace the FUNCTION or PROCEDURE with an UPDATE statement, it works, but I need to use FUNCTION or stored PROCEDURE.

Here is my mapping file:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="no.netcom.ninja.core.referencetables">

<class name="SystemDefaults" table="SYSTEM_DEFAULTS">
<id name="key" column="KEY" />
<property name="value" column="VALUE"/>
<property name="valueType" column="VALUE_TYPE" />
<property name="description" column="DESCRIPTION" />
<!-- Try with a stored procedure - Hibernate always reports rows as 0 and exception results -->
<sql-update callable="true">{call ninjadevmain.reference_tables.update_system_defaults (?, ?, ?, ?)}</sql-update>
<!-- Try with a function - seems returned number is not liked for some reason, even though this is how it is documented by Hibernate
<sql-update callable="true">{ ? = call ninjadevmain.reference_tables.update_system_defaults (?, ?, ?, ?)}</sql-update>
-->
<!-- Try with direct SQL - this one works, but is not how I want it to happen
<sql-update>UPDATE SYSTEM_DEFAULTS SET VALUE=?, VALUE_TYPE=?, DESCRIPTION=? WHERE KEY=?</sql-update>
-->
</class>
<!-- stored procedure queries -->
<sql-query name="systemdefaults.getrecord" callable="true" >
<return class="SystemDefaults" />
<![CDATA[
{ ? = call ninjadevmain.reference_tables.get_system_defaults(:keyIn) }
]]>
</sql-query>
<!-- stored procedure updates
<sql-update name="systemdefaults.updrecord" callable="true" >
<![CDATA[
{call ninjadevmain.reference_tables.update_system_defaults (:rowCnt, :keyIn, :valueIn, :typeIn, :descIn) }
]]>
</sql-update>
-->
<!-- HQL queries -->
<query name="systemdefaults.all">from SystemDefaults</query>

</hibernate-mapping>


Please help.....

P.S. Here is the Hibernate documentation from section 16.3:
16.3. Custom SQL for create, update and delete

Hibernate3 can use custom SQL statements for create, update, and delete operations. The class and collection persisters in Hibernate already contain a set of configuration time generated strings (insertsql, deletesql, updatesql etc.). The mapping tags <sql-insert>, <sql-delete>, and <sql-update> override these strings:

<class name="Person">
<id name="id">
<generator class="increment"/>
</id>
<property name="name" not-null="true"/>
<sql-insert>INSERT INTO PERSON (NAME, ID) VALUES ( UPPER(?), ? )</sql-insert>
<sql-update>UPDATE PERSON SET NAME=UPPER(?) WHERE ID=?</sql-update>
<sql-delete>DELETE FROM PERSON WHERE ID=?</sql-delete>
</class>

The SQL is directly executed in your database, so you are free to use any dialect you like. This will of course reduce the portability of your mapping if you use database specific SQL.

Stored procedures are supported if the callable attribute is set:

<class name="Person">
<id name="id">
<generator class="increment"/>
</id>
<property name="name" not-null="true"/>
<sql-insert callable="true">{call createPerson (?, ?)}</sql-insert>
<sql-delete callable="true">{? = call deletePerson (?)}</sql-delete>
<sql-update callable="true">{? = call updatePerson (?, ?)}</sql-update>
</class>

The order of the positional parameters are currently vital, as they must be in the same sequence as Hibernate expects them.

You can see the expected order by enabling debug logging for the org.hibernate.persister.entity level. With this level enabled Hibernate will print out the static SQL that is used to create, update, delete etc. entities. (To see the expected sequence, remember to not include your custom SQL in the mapping files as that will override the Hibernate generated static sql.)

The stored procedures are in most cases (read: better do it than not) required to return the number of rows inserted/updated/deleted, as Hibernate has some runtime checks for the success of the statement. Hibernate always registers the first statement parameter as a numeric output parameter for the CUD operations:

CREATE OR REPLACE FUNCTION updatePerson (uid IN NUMBER, uname IN VARCHAR2)
RETURN NUMBER IS
BEGIN

update PERSON
set
NAME = uname,
where
ID = uid;

return SQL%ROWCOUNT;

END updatePerson;

_________________
Richard Marriott

Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 1 post ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.