I have verified that each of the referenced columns are exact matches to the database. Also, the generated query (repeated below) runs PERFECTLY in Toad.
However, Hibernate tells me:



but Hibernate, inexplicably, does NOT tell me WHICH column it doesn't like.

Hibernate Version: 3.0.5
Mapping Document:

Code Between session open & close:

<CODE>
Session session = getSession();
List list = null;

try
{
SQLQuery sqlQuery = session.createSQLQuery( pSQLStatement );
sqlQuery.addEntity( "pcl", PricedClaimLineVarianceImpl.class );
sqlQuery.addEntity( "pc", PricedClaimImpl.class );
sqlQuery.addEntity( "pcb", PricedClaimBatchImpl.class );
sqlQuery.addEntity( "cl", ClaimLineImpl.class );
sqlQuery.addEntity( "c", ClaimImpl.class );
sqlQuery.addEntity( "p", PractitionerImpl.class );
sqlQuery.addEntity( "priorPcl", PricedClaimLineVarianceImpl.class );
list = sqlQuery.list();
}
catch( Exception e )
{
mLog.error( "SQLQuery threw an exception", e );
}
finally
{
session.close();
}
</CODE>

Full stack trace of any exception that occurs:

<CODE>
10:04:35,428 WARN [JDBCExceptionReporter] SQL Error: 17006, SQLState: null
10:04:35,428 ERROR [JDBCExceptionReporter] Invalid column name
10:04:57,111 ERROR [ClaimProcessingWorkflowServiceBeanSession] SQLQuery threw an exception
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70)
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.custom.CustomLoader.list(CustomLoader.java:112)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1414)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:153)
at com.ingenix.transact.provider.transcontract.workflow.claimprocessing.ejb.ClaimProcessingWorkflowServiceImpl.executeAndCSVFormatRowsUsingJDBC(ClaimProcessingWorkflowServiceImpl.java:1627)
at com.ingenix.transact.provider.transcontract.workflow.claimprocessing.ejb.ClaimProcessingWorkflowServiceImpl.getRecalcExportData(ClaimProcessingWorkflowServiceImpl.java:584)
at com.ingenix.transact.provider.transcontract.workflow.claimprocessing.ejb.ClaimProcessingWorkflowServiceImpl.getRecalcCSVExport(ClaimProcessingWorkflowServiceImpl.java:546)
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 org.jboss.invocation.Invocation.performCall(Invocation.java:345)
at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:214)
at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:185)
at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:130)
at org.jboss.webservice.server.ServiceEndpointInterceptor.invoke(ServiceEndpointInterceptor.java:51)
at org.jboss.ejb.plugins.CallValidationInterceptor.invoke(CallValidationInterceptor.java:48)
at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:105)
at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:335)
at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:166)
at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:139)
at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:192)
at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:122)
at org.jboss.ejb.SessionContainer.internalInvoke(SessionContainer.java:624)
at org.jboss.ejb.Container.invoke(Container.java:873)
at sun.reflect.GeneratedMethodAccessor124.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:141)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:72)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:249)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644)
at org.jboss.invocation.jrmp.server.JRMPInvoker$MBeanServerAction.invoke(JRMPInvoker.java:805)
at org.jboss.invocation.jrmp.server.JRMPInvoker.invoke(JRMPInvoker.java:406)
at sun.reflect.GeneratedMethodAccessor161.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:261)
at sun.rmi.transport.Transport$1.run(Transport.java:148)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.Transport.serviceCall(Transport.java:144)
at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:460)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:701)
at java.lang.Thread.run(Thread.java:534)
Caused by: java.sql.SQLException: Invalid column name
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java:6221)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:1557)
at oracle.jdbc.driver.OracleResultSet.getLong(OracleResultSet.java:1540)
at org.jboss.resource.adapter.jdbc.WrappedResultSet.getLong(WrappedResultSet.java:338)
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:759)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:292)
at org.hibernate.loader.Loader.doQuery(Loader.java:412)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
... 45 more
</CODE>

Name and version of the database you are using: Oracle 9i

The generated SQL:

select
c.INVOICE_NUMBER,
cl.LINE_NUMBER,
cl.PROC_CODE,
cl.MODIFIER_LIST,
cl.UNITS,
cl.START_DATE_OF_SERVICE,
cl.POS_CODE,
cl.DIAGNOSIS_LIST,
cl.PRACTITIONER_EID,
p.LASTNAME,
cl.SPECIALTY_CODE,
cl.CHARGED_AMOUNT,
priorPcl.EXPECTED_AMOUNT,
pcl.EXPECTED_AMOUNT,
pcl.ALLOWED_AMOUNT,
pcl.CURRENT_PAID_AMOUNT,
priorPcl.CONTRACTUAL_VARIANCE,
pcl.CONTRACTUAL_VARIANCE,
priorPcl.PAYMENT_VARIANCE,
pcl.PAYMENT_VARIANCE,
priorPcl.CLAIM_LINE_VARIANCE_STATUS, pcl.CLAIM_LINE_VARIANCE_STATUS
from
CLAIM c,
CLAIM_LINE cl,
PRACTITIONER p,
PRICED_CLAIM pc,
PRICED_CLAIM_LINE pcl,
PRICED_CLAIM_LINE priorPcl,
PRICED_CLAIM_BATCH pcb
where
pcb.WORKFLOW_IID = '1055'
and
pcb.PRICED_CLAIM_BATCH_IID = pc.PRICED_CLAIM_BATCH_IID
and
pcl.PRICED_CLAIM_IID = pc.PRICED_CLAIM_IID
and
pc.CLAIM_IID = c.CLAIM_IID
and
c.CLAIM_IID = cl.CLAIM_IID
and
cl.CLAIM_LINE_IID = pcl.CLAIM_LINE_IID
and
cl.PRACTITIONER_EID = p.PRACTITIONER_EID (+)
and
pc.PRIOR_PRICED_CLAIM_IID = priorPcl.PRICED_CLAIM_IID (+)
and
( priorPcl.CLAIM_LINE_IID is null
OR
priorPcl.CLAIM_LINE_IID = pcl.CLAIM_LINE_IID
)
order by
c.INVOICE_NUMBER,
cl.LINE_NUMBER

Though this response will not help you a lot, I'd like you to notice that the initial error message is not produced by Hibernate but by Oracle:


So don't complain about Hibernate but about Oracle...

Trying to be more helpful: are you definitely using the save schema and user as your code to test the statement?

Erik

Erik,
Thank you for the attempt but did you notice that the query runs fine in Toad?
Also, don't you find it curious that Hibernate does not bother to mention which column it was?

I did, but in my environment we have many databases and users and more than once someone came saying this and that does not work - and we found out that he was using the wrong database/schema/user. I was just trying to sort out indirect causes of the error.


For sure Hibernate could be better as far as error messages are concerned (as any software I know), but I always had a special love for Oracle JDBC drivers that are (IMO) far worse than Hibernate.

Your problem looks like either
1) a Hibernate bug
2) an Oracle JDBC driver error
3) some unexpected problem in your environment.

You could investigate alternative 1 through debugging.
Alternative 2 is a bit more difficult to find out - you may try with a different version of the driver.
Alternative 3 in all in your hand...

Erik

Um. Hibernate is just passing you back exactly the exception that Oracle throws. If Oracle doesnt tell us what the column was, how could Hibernate possibly guess??

Gavin,
Thank you for the reply. The generated query--taken from the log--runs fine in Toad and in SQLPlus. How would you proceed in this case?

If I run the exact query as it appears in the log, it runs perfectly in SQLPlus. The only unknown is what Hibernate is doing with it.

How would you proceed in this case?

I have seen this error "Invalid column name" all over the forums but I really don't ever see a solution.

Blaming Oracle is okay but it runs in Oracle! The only unknown is what happens to it in Hibernate.

My only option, as I see it, is wading into the source and spending my weekend on this issue. I will do so but I WISH Hibernate's overall error messages were more verbose. Searching the net, I see that as a common complaint.

You can scoff at me but that is not going to help the legions of others who will encounter the same baffling exception messages.

Thank you anyway for your time.

Um. Really the user forum is not for this level of hand-holding. You need to be able to make a bit more of a decent effort to solve these kinds of problems for yourself, sorry.

Hibernate is most certainly NOT the only unknown here. Neither Toad nor SQLPlus are using Java or the Oracle JDBC driver. Neither are sharing your JDBC configuration, etc.

You should proceed by actually running the query *in Java* for a start.

session.connection().prepareStatement("the generated sql").executeQuery()

And as I already explained to you, the "baffling exception message" is Oracle's message, not ours.

I think you are having trouble thinking in terms of the architecture of Hibernate, which just acts as a client of JDBC. Stop blaming us for things that are Oracle's fault. And don't believe everything you read on the internet, a lot of it is written by people with just as serious misconceptions as you are demonstrating in this thread.

Gavin,
I just ran the query using JDBC from the exact same unit test where the previous one failed and it ran perfectly. That tends to remove some variables. That's using a connection from the same Hibernate session.

And as for the "hand holding" comment, I don't think that is necessary.

If you are going to host a forum then you really shouldn't fault people for using it.

So, I'll back off and leave this forum to you and the rest of the Hibernate team. Us newbees who have the audacity to ask questions can just go pound sand, eh?

Why must these exchanges always devolve into rudeness? Is that really necessary?

From my humble point of view getting the column name would not help you at all. The statement works, so you would get the name of a correct column - and would still wonder what's going on. I've seen many weird error in the context of Hibernate - and only a small minority were actually caused by Hibernate itself.

If you send more of your code I may try to reproduce your problem (although in a slightly different environment - but this would help sort out the origin of the problem).

I really think you should be cooler about all that. Silly errors are part of our job :-) or ;-) ?

FYI: I'm answering your post in my free time, am not related to Hibernate in any way, only trying to help and learn from other's problems.

Erik

Oh, geez. I just looked at what you are doing.

So, you are writing handcoded SQL. But you have not read the Hibernate documentation which tells you about using the {..} syntax in native SQL queries.

And you blame Hibernate for your shortcomings, and for crappy Oracle JDBC drivers.

Sheesh!

Gavin,
I beg to differ. The SQL I displayed in the original post was the GENERATED SQL. Here is the original from my .java file:

"select "
+ "{c}.INVOICE_NUMBER, "
+ "{cl}.LINE_NUMBER, "
+ "{cl}.PROC_CODE, "
+ "{cl}.MODIFIER_LIST, "
+ "{cl}.UNITS, "
+ "{cl}.START_DATE_OF_SERVICE, "
+ "{cl}.POS_CODE, "
+ "{cl}.DIAGNOSIS_LIST, "
+ "{cl}.PRACTITIONER_EID, "
+ "{p}.LASTNAME, "
+ "{cl}.SPECIALTY_CODE, "
+ "{cl}.CHARGED_AMOUNT, "
+ "{priorPcl}.EXPECTED_AMOUNT, "
+ "{pcl}.EXPECTED_AMOUNT, "
+ "{pcl}.ALLOWED_AMOUNT, "
+ "{pcl}.CURRENT_PAID_AMOUNT, "
+ "{priorPcl}.CONTRACTUAL_VARIANCE, "
+ "{pcl}.CONTRACTUAL_VARIANCE, "
+ "{priorPcl}.PAYMENT_VARIANCE, "
+ "{pcl}.PAYMENT_VARIANCE, "
+ "{priorPcl}.CLAIM_LINE_VARIANCE_STATUS, "
+ "{pcl}.CLAIM_LINE_VARIANCE_STATUS "
+ "from "
+ " CLAIM {c}, "
+ " CLAIM_LINE {cl}, "
+ " PRACTITIONER {p}, "
+ " PRICED_CLAIM {pc}, "
+ " PRICED_CLAIM_LINE {pcl}, "
+ " PRICED_CLAIM_LINE {priorPcl}, "
+ " PRICED_CLAIM_BATCH {pcb} "
+ "where "
+ "{pcb}.WORKFLOW_IID = '" + pRw.getID() + "' "
+ "and {pcb}.PRICED_CLAIM_BATCH_IID = {pc}.PRICED_CLAIM_BATCH_IID "
+ "and {pcl}.PRICED_CLAIM_IID = {pc}.PRICED_CLAIM_IID "
+ "and {pc}.CLAIM_IID = {c}.CLAIM_IID "
+ "and {c}.CLAIM_IID = {cl}.CLAIM_IID "
+ "and {cl}.CLAIM_LINE_IID = {pcl}.CLAIM_LINE_IID "
+ "and {cl}.PRACTITIONER_EID = {p}.PRACTITIONER_EID (+) "
+ "and {pc}.PRIOR_PRICED_CLAIM_IID = {priorPcl}.PRICED_CLAIM_IID (+) "
+ "and "
+ "( "
+ " {priorPcl}.CLAIM_LINE_IID is null "
+ " OR "
+ " {priorPcl}.CLAIM_LINE_IID = {pcl}.CLAIM_LINE_IID "
+ ") "
+ "order by "
+ "{c}.INVOICE_NUMBER, {cl}.LINE_NUMBER "
*/

Dear Sirs,

Could we all calm down a bit? And try to understand the problem without accusing each other or Hibernate or Oracle?

@tyronehed: please send more of your code (as much as possible), so that I can reproduce this strange behaviour.

We'll find the error - and I would bet a 100€ that it is not caused by Hibernate, but I may be wrong...

Erik

Oh, well it would have been useful to actually post that in the first place, right?

Now, if you turn on the Hibernate debug log, you will see *exactly* which column Hibernate is trying to find in the JDBC result set that you forgot to list in your handwritten SQL.

Of course, if Oracle JDBC did not suck, Oracle would have told you in the exception.

And if you weren't lazy, you would have turned on the Hibernate log before wasting everyone's time here.

Erik,

Thank you for your courtesy. However, for you to reproduce this error you would need my tables also, in addition to an instance of JBoss to run it on. I'll fight this on my own.

I think my only hope is to step through the source. I had just seen this error so many times in the forum that I had hoped it was an obvious fix. I have stepped through the source about 20 times but it's pretty cryptic.

But, so that I don't have to have Gavin ridicule me anymore, I'm going to bail on this forum.

Thank you kindly. Have a great day.

Ty