Hey all. I am trying to have Hibernate call a DB/2 stored procedure with two in fields and an out field { TestSP( in first, in second, out concat )} which simply takes the first two parameters and concatinates them (this is more for a proof of concept than for any real business use). When I try to call the procedure with just the first two parameters { TestSP( :one, :two )} I'm told that Hibernate can't find the procedure with the specified parameters. If I add the third parameter to the call { TestSP( :one, :two, :concat )} and don't do q.setParameter( ":concat", " "); then Hibernate tells me it needs an input for the concat parameter. If I run the code as typed below, I get the exception listed below. Our dba guy says that an OUT parameter is necessary for any DB/2 procedure yet I've seen forum posts with people saying they've got stored procedures with DB/2 to work. Am I missing something here? Is there a way to do this with Hibernate or will I have to use JDBC calls?

Thanks for any help,
-B

Hibernate version:
3.2rc2


Mapping documents:
<hibernate-mapping>
<sql-query name="TestSP" callable="true">
<return alias="sp" class="tlc.domain.object.Sp">
<return-property name="total" column="concat"/>
</return>
{call LKLibr.TestSP( :one, :two, :concat )}
</sql-query>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
Query q = session.getNamedQuery( "TestSP" );
q.setParameter( "one", "it" );
q.setParameter( "two", "works" );
q.setParameter( "concat", new String() );

Sp sp = ( Sp )q.uniqueResult();

Full stack trace of any exception that occurs:
Hibernate:
/* named native SQL query TestSP */ {call LkLibr.TestSP( ?, ?, ? )}
INFO org.hibernate.type.StringType - could not bind value '' to parameter: 3; Parameter type not valid.
WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: -99999, SQLState: HY105
ERROR org.hibernate.util.JDBCExceptionReporter - Parameter type not valid.
Exception in thread "main" org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2148)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:118)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1658)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:164)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:756)
at tlc.util.SPTest.main(SPTest.java:20)
Caused by: java.sql.SQLException: Parameter type not valid.
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:389)
at com.ibm.as400.access.AS400JDBCPreparedStatement.setValue(AS400JDBCPreparedStatement.java:2835)
at com.ibm.as400.access.AS400JDBCPreparedStatement.setString(AS400JDBCPreparedStatement.java:2506)
at com.mchange.v2.c3p0.impl.NewProxyCallableStatement.setString(NewProxyCallableStatement.java:3044)
at org.hibernate.type.StringType.set(StringType.java:26)
at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:83)
at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:65)
at org.hibernate.loader.Loader.bindNamedParameters(Loader.java:1748)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1577)
at org.hibernate.loader.Loader.doQuery(Loader.java:661)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2145)
... 8 more

Name and version of the database you are using:
IBM DB/2 AS/400 V5R3M0

Stored Procedure:
Create Procedure LKLibr.TestSP(
IN first Char(10),
IN second Char(10),
OUT concat Char(25))
Language SQL
Contains SQL

MAIN: BEGIN

DECLARE CatString Char(25);

Set CatString = Trim(first) || ' ' || Trim(second);
Set concat = CatString;

END MAIN

Stored procedures must return a resultset -- see section 16.2.3 of reference. You could return a "dummy" resultset (SELECT 0 FROM sysibm.sysdummy1) or return the result of your concatenation. I've used stored procedures several time now on an iSeries but I haven't tried to use output parameter(s).

Curtis ...