Hibernate 3.0.5:

When calling executeUpdate() on a DB2 stored procedure {?=call soh_updTable1(?,?,?,?)} an ArrayIndexOutOfBoundsException is thrown (shown below).

It is possible to work around this Exception using a true OUT parameter in the procedure and call syntax of {call soh_updTable1(?,?,?,?)}, however, when the EntityPersister checks to see if the update was successful using the return value from the executeUpdate() function a StateObjectException is thrown.

Now, that is understable since I didn't specify the return placeholder in my procedure call [{call soh_updTable1(?,?,?,?)}] but even if I was to include it I would need to use stmt.getInt(1) to get the return value (I've included some Pseudo java code at the end of the post to help illustrate).

The problem is that DB2 is putting the return value into the 1st parameter position so that stmt.getInt(1) is needed to check the return value. This will cause check(update.executeUpdate() , id, j ) [line 1995 of the BasicEntityPersister] to always throw the StateObjectException()

Am I doing something wrong here in using the DB2 Dialect? Please let me know if any further information is required.

Cheers,
Dan.



java.lang.ArrayIndexOutOfBoundsException: -1
at com.ibm.db2.jcc.b.bd.i(bd.java:265)
at com.ibm.db2.jcc.b.bd.e(bd.java:255)
at com.ibm.db2.jcc.b.bd.registerOutParameter(bd.java:243)
at org.apache.commons.dbcp.DelegatingCallableStatement.registerOutParameter(DelegatingCallableStatement.java:92)
at org.hibernate.persister.entity.BasicEntityPersister.update(BasicEntityPersister.java:1959)
at org.hibernate.persister.entity.BasicEntityPersister.updateOrInsert(BasicEntityPersister.java:1918)
at org.hibernate.persister.entity.BasicEntityPersister.update(BasicEntityPersister.java:2158)
at org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:75)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:223)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:137)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:274)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:678)
at org.springframework.orm.hibernate3.HibernateAccessor.flushIfNecessary(HibernateAccessor.java:210)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:311)
at org.springframework.orm.hibernate3.HibernateTemplate.update(HibernateTemplate.java:578)
at org.springframework.orm.hibernate3.HibernateTemplate.update(HibernateTemplate.java:574)
at com.yoogalu.db2.DB2Home.update(DB2Home.java:37)
at com.yoogalu.db2.DB2TestService.main(DB2TestService.java:43)

Pseudo java code

import java.sql.*;

public class TestProc {

public static void main(String[] args) {

String url = args[0];
String user = args[1];
String password = args[2];
String driver = args[3];
try {
Class.forName(driver);

} catch (Exception e) {
e.printStackTrace();
return;
}

Connection conn = null;

try {
conn = DriverManager.getConnection(url, user, password);
String sql = "{?=call soh_updTable1(?,?,?,?,?,?,?,?,?,?)}";
System.out.println("sql = " + sql);

CallableStatement stmt = conn.prepareCall(sql);

stmt.registerOutParameter(1, Types.INTEGER);
stmt.setInt(2, 1);
stmt.setNull(3, Types.CHAR);
stmt.setInt(4, 0);
stmt.setInt(5, 88);
stmt.setInt(6, 77);
stmt.setInt(7, 76);
stmt.setInt(8, 75);
stmt.setString(9, "01");
stmt.setString(10, "028");
stmt.setString(11, "0000");

int output = stmt.executeUpdate();
System.out.println("output = " + output); // --> -1 will be printed here
output = stmt.getInt(1);
System.out.println("output = " + output); // --> 1 will be printed here

stmt.close();

} catch (Exception e) {
e.printStackTrace();
}
finally{
try{
if(conn!=null)
conn.close();
}catch(Exception e){}
}
}

}[/b]

the db2dialect were contributed to us (reported as being equal to what mssql does)

if this is not the case, then report it in jira (patches are very welcome ;)

Also some links to db2 docs stating how they want it would be appreciated.

/max

Gidday Max,

The db2dialect is equal when returning resultsets.

My suspision is that the db2 driver doesn't return the number of records (when using a stored procedure to update) updated in the same manner as most. I believe the driver returns the number of records affected in the first parameter position, eg.



not



It would appear that db2 always returns -1 using the later code. The way I see it, is that this can't be fixed using a patch because the later is fundemental to the way hibernate works.

Any thoughts?

Regards,
dan.

wrap driver to return the first parameter as update count.

its up to your sp code to return the updated values (as stated in the docs ;)

Cheers baliukas, I'll take a look at this.

Max, we've tried everything to get the proc to return the number of records but nothing worked (the update count was always returned via the first parameter). Do you have a link to the doc you mention? I'd be more than happy to be wrong.

Cheers,
Dan.

Just an update, I've managed to wrap the db2 driver to do what I want. In the end I needed to wrap the Connection and CallableStatement classes as well as implement my own Driver class.

Seems to be working now.

Thanks for your help.
/dan

Hello,

I had the same problem and I solved it by modifying the Hibernate code (I removed the check on the “-1” return value). I know that was an ugly fix and you seemed to have a better fix. Can you send more details on it please.


Regards,

François

Hello,

I've finally managed to wrap the db2 driver but was not satisfied by this workaround. After searching the cause of the problem and a possible solution I submitted a patch, to the Hibernate code, which enable us to use stock IBM's DB2 jdbc driver. You can see my proposition at:

http://opensource2.atlassian.com/projec ... e/HHH-1271

Francois J.