[b]Hibernate version:[/b] 3.0.2

[b]Situation:[/b]
I have defined a SP based named query; but it needs a parameter set prior to being called. My invocation doesn't seem to work:

List records =
lo_HbmSessCommon.getNamedQuery("divisions_on_file_spr")
.setParameter("org1","752")
.list();

The syntax for the call in the mapping file uses what I hope is a valid example of a named parameter.

[b]Mapping documents:[/b]
<hibernate-mapping default-cascade="none" default-access="property" auto-import="true">

<class name="us.tx.state.oag.OagCommon.hbm.HbmOagDivisionTable"
table="division">

<composite-id>
<key-property name="Org1" column="org1" type="string"/>
<key-property name="DateEffective" column="dt_eff" type="timestamp"/>
</composite-id>

<property name="Address1" column="address1" type="string" update="true" insert="true"/>
<property name="Address2" column="address2" type="string" update="true" insert="true"/>
<property name="Address3" column="address3" type="string" update="true" insert="true"/>
<property name="AlternateCode" column="alternate_code" type="string" update="true" insert="true"/>
<property name="AlternatePca" column="alternate_pca" type="string" update="true" insert="true"/>
<property name="AnalystEmplId" column="analyst_id" type="string" update="true" insert="true"/>
<property name="City" column="city" type="string" update="true" insert="true"/>
<property name="DivisionName" column="division_name" type="string" update="true" insert="true"/>
<property name="DateInactive" column="dt_inactive" type="timestamp" update="true" insert="true"/>
<property name="MailCode" column="mail_code" type="string" update="true" insert="true"/>
<property name="Org2" column="org2" type="string" update="true" insert="true"/>
<property name="Org3" column="org3" type="string" update="true" insert="true"/>
<property name="Org4" column="org4" type="string" update="true" insert="true"/>
<property name="Org5" column="org5" type="string" update="true" insert="true"/>
<property name="Org6" column="org6" type="string" update="true" insert="true"/>
<property name="PrimaryPca" column="primary_pca" type="string" update="true" insert="true"/>
<property name="ReportsTo" column="reports_to" type="string" update="true" insert="true"/>
<property name="SectionPrefix" column="section_prefix" type="string" update="true" insert="true"/>
<property name="SetId" column="setid" type="string" update="true" insert="true"/>
<property name="ShortName" column="short_name" type="string" update="true" insert="true"/>
<property name="State" column="state" type="string" update="true" insert="true"/>
<property name="Zip" column="zip" type="string" update="true" insert="true"/>

<property name="UpdatedWho" column="up_user" type="string" update="true" insert="true"/>
<property name="UpdatedWhen" column="up_date" type="timestamp" update="true" insert="true"/>

</class>

<sql-query name="divisions_on_file_spr" callable="true">
<return alias="div" class="us.tx.state.oag.OagCommon.hbm.HbmOagDivisionTable"/>
{ ? = call divisions_on_file_spr(:org1) }
</sql-query>

</hibernate-mapping>

[b]Error:[/b]
16:28:52,296 INFO [STDOUT] Hibernate: { ? = call divisions_on_file_spr(?) }
16:28:52,328 WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: 07000
16:28:52,328 ERROR [JDBCExceptionReporter] Parameter #2 has not been set.
16:29:03,421 INFO [STDOUT] PortletLog|1|7/5/06|16:29:03:421|
OagWfPersonnelActionPortlet - org.hibernate.exception.SQLGrammarException: could not execute
query

You can't mix positional and named parameters. And seeing as calling stored procs in 3.0.2 required a positional parameter, you have to use another one to pass in the other parameter.

This flaw is rectified in newer releases, where the leading "? =" is no longer needed.

what gave you the impression that ? = is not no longer needed ?
(it has always been needed for db's that required it in their call syntax)

Oops, that's sql-insert.. I got my callables mixed up.

heh...now you guys have me more confused. :)

With this as the definition:

<sql-query name="divisions_on_file_spr" callable="true">
<return alias="div" class="us.tx.state.oag.OagCommon.hbm.HbmOagDivisionTable"/>
{ ? = call divisions_on_file_spr(?) }
</sql-query>

This invocation:

Query lo_query = lo_HbmSessCommon.getNamedQuery("divisions_on_file_spr");
lo_query.setParameter(2,"752");
List records = lo_query.list();

Fails on the setParameter call:

java.lang.IllegalArgumentException: Positional parameter does not exist: 2 in query:

Considering there are two ?'s in that statement, I'm not sure why this is happening.

I was referencing this article:

http://www.javalobby.org/java/forums/t38756.html

when trying to use the

{ ? = call divisions_on_file_spr ( :org1 ) }

syntax.

Ok, I think I know what part of the problem is: .setParameter is offset from 0, not 1. So, the following:

Query lo_query = po_HbmSessCommon.getNamedQuery("divisions_on_file_spr");
lo_query.setParameter(1,"752");

Does run, but this:
List records = lo_query.list();

Gives me:
java.lang.ArrayStoreException

?

Here's the latest. Looks like Hibernate is renaming columns, though I can't guess why, since I supplied their names.

[b]Mapping:[/b]

<hibernate-mapping default-cascade="none" default-access="property" auto-import="true">

<class name="us.tx.state.oag.OagCommon.hbm.HbmOagDivisionTable"
table="division">

<composite-id>
<key-property name="Org1" column="org1" type="string"/>
<key-property name="DateEffective" column="dt_eff" type="timestamp"/>
</composite-id>

<property name="Address1" column="address1" type="string" update="true" insert="true"/>
<property name="Address2" column="address2" type="string" update="true" insert="true"/>
<property name="Address3" column="address3" type="string" update="true" insert="true"/>
<property name="AlternateCode" column="alternate_code" type="string" update="true" insert="true"/>
<property name="AlternatePca" column="alternate_pca" type="string" update="true" insert="true"/>
<property name="AnalystEmplId" column="analyst_id" type="string" update="true" insert="true"/>
<property name="City" column="city" type="string" update="true" insert="true"/>
<property name="DivisionName" column="division_name" type="string" update="true" insert="true"/>
<property name="DateInactive" column="dt_inactive" type="timestamp" update="true" insert="true"/>
<property name="MailCode" column="mail_code" type="string" update="true" insert="true"/>
<property name="Org2" column="org2" type="string" update="true" insert="true"/>
<property name="Org3" column="org3" type="string" update="true" insert="true"/>
<property name="Org4" column="org4" type="string" update="true" insert="true"/>
<property name="Org5" column="org5" type="string" update="true" insert="true"/>
<property name="Org6" column="org6" type="string" update="true" insert="true"/>
<property name="PrimaryPca" column="primary_pca" type="string" update="true" insert="true"/>
<property name="ReportsTo" column="reports_to" type="string" update="true" insert="true"/>
<property name="SectionPrefix" column="section_prefix" type="string" update="true" insert="true"/>
<property name="SetId" column="setid" type="string" update="true" insert="true"/>
<property name="ShortName" column="short_name" type="string" update="true" insert="true"/>
<property name="State" column="state" type="string" update="true" insert="true"/>
<property name="Zip" column="zip" type="string" update="true" insert="true"/>

<property name="UpdatedWho" column="up_user" type="string" update="true" insert="true"/>
<property name="UpdatedWhen" column="up_date" type="timestamp" update="true" insert="true"/>

</class>
<!-- NOTE: Make sure the SP on Sybase ASE has its mode set to "ANY" or this won't run -->
<sql-query name="divisions_on_file_spr" callable="true">
<return alias="div" class="us.tx.state.oag.OagCommon.hbm.HbmOagDivisionTable"/>
{ call divisions_on_file_spr ( :org1 ) }
</sql-query>

</hibernate-mapping>

[b]Code:[/b]
try {
//!! test org sp
HbmOagDivisionTable hbmOagDivisionTable = null;
// turn chained mode off
try {
//Connection co_dbConn_common = com.sssw.fw.factory.EboFactory.getServiceLocator().getDbConnection("java:/common");
//PreparedStatement ls_chained_off = co_dbConn_common.prepareStatement("set chained off");
PreparedStatement lo_chained_off = po_HbmSessCommon.connection().prepareStatement("set chained off");
lo_chained_off.execute();
} catch (Exception e) {
co_oagLogger.log(OagLogger.LM_LOGGER_DATABASE, "*** ERROR: unable to turn SET CHAINED OFF");
co_oagLogger.log(OagLogger.LM_LOGGER_DATABASE, e.toString());
}

Transaction lo_hbmTrans2 = po_HbmSessCommon.beginTransaction();

Query lo_query = po_HbmSessCommon.getNamedQuery("divisions_on_file_spr");
lo_query.setParameter("org1","752");
List records = lo_query.list();


[b]Result:[/b]
13:33:19,453 INFO [STDOUT] Hibernate: { call divisions_on_file_spr ( ? ) }
13:33:23,390 WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: 07009
13:33:23,390 ERROR [JDBCExceptionReporter] Invalid column name org1_0_.
13:33:31,671 INFO [STDOUT] org.hibernate.exception.SQLGrammarException: could not execute query

[b]From the log file:[/b]
2006-07-06 13:33:19,453 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
2006-07-06 13:33:19,453 DEBUG [org.hibernate.SQL] { call divisions_on_file_spr ( ? ) }
2006-07-06 13:33:19,453 INFO [STDOUT] Hibernate: { call divisions_on_file_spr ( ? ) }
2006-07-06 13:33:19,453 DEBUG [org.hibernate.jdbc.AbstractBatcher] preparing statement
2006-07-06 13:33:19,453 DEBUG [org.hibernate.type.StringType] binding '752' to parameter: 1
2006-07-06 13:33:22,984 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to open ResultSet (open ResultSets: 0, globally: 0)
2006-07-06 13:33:23,000 DEBUG [org.hibernate.loader.Loader] processing result set
2006-07-06 13:33:23,000 DEBUG [org.hibernate.loader.Loader] result set row: 0
2006-07-06 13:33:23,000 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to close ResultSet (open ResultSets: 1, globally: 1)
2006-07-06 13:33:23,328 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2006-07-06 13:33:23,343 DEBUG [org.hibernate.jdbc.AbstractBatcher] closing statement
2006-07-06 13:33:23,375 DEBUG [org.hibernate.util.JDBCExceptionReporter] could not execute query [{ call divisions_on_file_spr ( ? ) }]
java.sql.SQLException: Invalid column name org1_0_.
at net.sourceforge.jtds.jdbc.JtdsResultSet.findColumn(JtdsResultSet.java:976)
at net.sourceforge.jtds.jdbc.JtdsResultSet.getString(JtdsResultSet.java:1217)
at org.jboss.resource.adapter.jdbc.WrappedResultSet.getString(WrappedResultSet.java:405)
at org.hibernate.type.StringType.get(StringType.java:16)
...
2006-07-06 13:33:23,390 WARN [org.hibernate.util.JDBCExceptionReporter] SQL Error: 0, SQLState: 07009
2006-07-06 13:33:23,390 ERROR [org.hibernate.util.JDBCExceptionReporter] Invalid column name org1_0_.
2006-07-06 13:33:31,671 INFO [STDOUT] org.hibernate.exception.SQLGrammarException: could not execute query
2006-07-06 13:34:07,359 DEBUG [org.hibernate.jdbc.JDBCContext] running Session.finalize()