I am trying to use Stored Procedures(MS SQL Server 2000 + iNet Opta Drviers) with Hibernate 3.0.


When Hibernate is trying to get data from the stored procedure.. it is trying to get it by the colums ALIAS, the call is failing with the following exception. I trying to list the result of the query..

Query namedQuery = s.getNamedQuery("getaccountid");
List list = namedQuery.list();




Can some one tell me how to avaoid colums ALIAS getting used for Stored Procedure or how to fix this issue.


FYI: here is the stack and the mapping...


---------------EXCEPTION STACK----------------
21:06:07,501 ERROR JDBCExceptionReporter - [TDS Driver]Column 'SYNCID0_' not found.
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1518)
at org.hibernate.loader.Loader.list(Loader.java:1498)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:103)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1340)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:151)
at com.patientkeeper.ge.hibernate.GeDatabase.main(GeDatabase.java:125)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:78)
Caused by: java.sql.SQLException: [TDS Driver]Column 'SYNCID0_' not found.
at com.inet.tds.n.findColumn(Unknown Source)
at com.inet.tds.n.getLong(Unknown Source)
at org.hibernate.type.LongType.get(LongType.java:26)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:68)
at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:691)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:280)
at org.hibernate.loader.Loader.doQuery(Loader.java:387)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:206)
at org.hibernate.loader.Loader.doList(Loader.java:1515)



---------------EXCEPTION STACK END----------------






-------------------------MAPPING--------------------
My Mapping is:-
<?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="data" default-access="field">

<class name="Account">
<id name="syncid" type="java.lang.Long" column="SYNCID">
<generator class="increment"/>
</id>
<property name="syncversion" type="java.lang.Long" not-null="false" column="SYNCVERSION"/>

<sql-insert > </sql-insert>
<sql-update> </sql-update>
<sql-delete> </sql-delete>

</class>



<sql-query name="getaccountid" callable="true">
<return alias="" class="Account">
<return-property name="syncid" column="SYNCID"/>
<return-property name="syncversion" column="SYNCVERSION"/>
</return>
{ call getaccountid }
</sql-query>
</hibernate-mapping>

return-property should do that for you.

are you sure that mapping is the one you use for that code ?

Max,
Thanks for the reply.


Yes. I am sure that I am using this mapping.

this project is using hibernate 2 for lots of other purpose. I am trying to use hibernate 3 because of the Stored Procedure support.


Thanks,
Umesh

but i dont get it since i have a unit test that does not fail that is identical.

put a failing testcase in jira (with mapping and src code) and i'll have a look.

I have the same issue with aliases when used with stored procedures.
I have tried SQL query with return-property set and I got the same error.

Can you please let me know if you got this working.

Max, you said that you got this working in your unit test case. Is this test case in cvs. If so, can you please give me the dir where I can locate it. I might be missing something.

search for the return-property in test dir and you will find...

afair its in sqltest

I'm also seeing this problem. Aliases with the suffix "0_" are being generated for id columns, but seemingly not for regular property columns.

Checked my code and mapping, comparing with MSSQLTest.testEntityStoredProcedure(), and I can't see what I'm doing wrong. Does that test really pass?

In the Hibernate source, I've got down to BasicEntityPersister.getIdentifierAliases(). This method has a comment which suggests it used to look very like getPropertyAliases(), but now it's a bit different. Could this be something to do with the problem?

This is actually my first time ever using Hibernate, so I'm pretty far out of my depth and I could be completely wrong... ;)

Yes. I observed similar thing. The stored procedures worked only under following conditions.

1. Only one id column. I could not get this working with composite ids.
2. The property name of the id must match the column name in the database table. if I tried to use a different property name, it gives me the invalid "0_" error.

all of this you say I cannot reproduce - you need to provide me with a failing test case.

Composite ids does work (at least in our unit tests).
Your refer to them via e.g. "id.prop1"

/max

Yep, fair enough.

What I'll do first, as a sanity check, is prove that the Hibernate test passes with my setup. If it does (and hopefully it will) then I'll put together a failing case for you.

why is your alias empty in the mapping files ?

<return alias="" class="Account">

ok guys now i have done my best to make my unit tests fail and i simply can't ,) ....so please come up with a failing test case...

Max, Here's my mapping file that fails.

Note that if I change the name of the id field to "id" in the return property alias. I can get it working.

I have done multiple tests with variopus names to my id field, however, it does not complain only if I name my identifier field as "id" in the return-property. I use Sybase server.

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

<hibernate-mapping>

<class name="Record" table="empty_string_table">
<id name="ident">
<generator class="assigned"></generator>
</id>

<property name="dtUpdated"></property>
<property name="m1"></property>
<property name="m2"></property>
<property name="m3"></property>

<loader query-ref="spGetRecords"></loader>
</class>

<sql-query name="spGetRecords" callable="true">

<return alias="record" class="Record">
<return-property name="ident" column="identifier"/>
<return-property name="dtUpdated" column="date_updated"/>
<return-property name="m1" column="message1"/>
<return-property name="m2" column="message2"/>
<return-property name="m3" column="message3"/>
</return>

{ call get_all_rows () }

</sql-query>

</hibernate-mapping>

So, my question is should the return-property name of the identifier always be "id".

In the above example, if I simply replace the line

<return-property name="ident" column="identifier"/>
with
<return-property name="id" column="identifier"/>

I can get it working.

ok i can verify that.

See http://opensource.atlassian.com/project ... se/HHH-317

note: using "id" is the exact same so its a perfect valid workaround.