Hi Everyone,

I am having trouble getting hibernate to select a special RRN column from an AS/400 database. RRN is a relative record number that is handled internally by the AS/400. I need the RRN as part of a composite key as there is no primary key on the table. (table will never be changed or corrected)
I do not know how to get hibernate to generate the correct SQL for it.

SQL generated is:
select this_.RRN(UTRNFBC) as RRN5_0_ from UTRNFBC this_ where //etc

This "this_." in front of the field makes the sql unworkable. I can run the sql manually on the database and if i remove the "this_." from the sql in front of the RRN field, it works. Does anyone know how to make hibernate do this automatically..

I found a similar post at:
http://forum.hibernate.org/viewtopic.php?t=972184
but this solution doesn't work for me. Perhaps because of the different dialects.

I must enter RRN(TABLENAME) to use this RRN field. RRN on its own like in the linked post is invalid.

Here are some SQL's ive tried manually.
//this works - select RRN(this_) from UTRNFBC this_
//this doesn't - select RRN(UTRNFBC) from UTRNFBC this_
//this works - select RRN(UTRNFBC) from UTRNFBC

Note: there are other columns in the sql that ive taken out for readability

Hibernate version: 3.0.5

Mapping documents:
<composite-id name="key" class="UnitTransactionKey">
<key-property name="id" column="RRN(UTRNPF)"/>
</composite-id>

Full stack trace of any exception that occurs:

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:1596)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:111)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1322)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:300)
at com.ff.model2.life400.facade.UnitTransactionDetailServiceBean.findByTransactionNumber(UnitTransactionDetailServiceBean.java:60)
at com.ff.model2.life400.facade.TestUnitTransactionDetail.testFindByTransactionNumber(TestUnitTransactionDetail.java:24)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:40)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:90)
Caused by: java.sql.SQLException: [SQL5016] Qualified object name RRN not valid. Cause . . . . . : One of the following has occurred: -- The syntax used for the qualified object name is not valid for the naming option specified. With system naming, the qualified form of an object name is schema-name/object-name. With SQL naming the qualified form of an object name is authorization-name.object-name. -- The syntax used for the qualified object name is not allowed. User-defined types cannot be qualified with the schema in the system naming convention on parameters and SQL variables of an SQL procedure or function. Recovery . . . : Do one of the following and try the request again: -- If you want to use the SQL naming convention, verify the SQL naming option in the appropriate SQL command and qualify the object names in the form authorization-id.object-name. -- If you want to use the system naming convention, specify the system naming option in the appropriate SQL command and qualify the object names in the form schema-name/object-name. -- With the system naming convention, ensure the user-defined types specified for parameters and variables in an SQL routine can be found in the current path.
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:650)
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:621)
at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1481)
at com.ibm.as400.access.AS400JDBCPreparedStatement.<init>(AS400JDBCPreparedStatement.java:185)
at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:1903)
at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:1726)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:396)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:334)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:88)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1162)
at org.hibernate.loader.Loader.doQuery(Loader.java:390)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
... 29 more
Name and version of the database you are using:
AS/400
The generated SQL (show_sql=true):
select this_.RRN(UTRNFBC) as RRN5_0_ from UTRNFBC this_ where //etc