aspose file tools*
The moose likes Object Relational Mapping and the fly likes JDBCExceptionReport and Hibernate Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "JDBCExceptionReport and Hibernate" Watch "JDBCExceptionReport and Hibernate" New topic
Author

JDBCExceptionReport and Hibernate

Payam Fard
Ranch Hand

Joined: Jan 31, 2003
Posts: 73
Hi all,

I am getting a JDBCExceptionReport exception complaining about invalid column name. I have the sql debugging on. Here is what is being reported by the hibernate debugger. If I run the generated sql in a sql client, it works fine. I thought maybe there is a problem with the way I have mapped my class, but the pathway_id it is complaining about is a NUMBER in the Oracle 8 database and I have defined it to be a Long in my class. I have to add that binding of the my tables to Java classes cause no errors.

Any ideas on how I might go about to fix this problem and/or debug it more would be greatly appreciated.

Here is the log:


Hibernate: select p.PATHWAY_ID as PATHWAY_ID0_, p.PATHWAY_NAME as PATHWAY_2_0_,
p.PATHWAY_DISPLAY as PATHWAY_3_0_, p.PATHWAY_DIAGRAM as PATHWAY_4_0_, p.TAXON as
TAXON0_ from BIO_Pathways p, Gene g, Reporter r, Pathway_Gene_Object pg, Gene_R
eporter gr where pg.pathway_id = p.pathway_id and pg.gene_id = g.gene_id and gr.
gene_id = g.gene_id and gr.reporter_id = r.reporter_id and r.reporter_id in (select arm.reporter_id from Array_Reporter_Map_V arm where arm.array_design_id = 7)

15:18:14,728 DEBUG BatcherImpl:115 - preparing statement
15:20:03,103 DEBUG Loader:115 - processing result set
15:20:03,134 DEBUG LongType:115 - returning '220' as column: PATHWAY_ID0_
15:20:03,149 DEBUG JDBCExceptionReporter:140 - SQL Exception
java.sql.SQLException: Invalid column name
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:222)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:285)
at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.j
ava:5153)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl
.java:698)
at oracle.jdbc.driver.OracleResultSet.getLong(OracleResultSet.java:1563)

at net.sf.hibernate.type.LongType.get(LongType.java:18)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:62)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:53)
at net.sf.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:352)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:203)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections
(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:955)
at net.sf.hibernate.loader.Loader.list(Loader.java:946)
at net.sf.hibernate.loader.SQLLoader.list(SQLLoader.java:92)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3802)
at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:52)
at gov.nih.nci.common.persistence.DefaultPersistenceManager.search(Unkno
wn Source)
at gov.nih.nci.common.manager.DefaultObjectManager.search(Unknown Source
)
at gov.nih.nci.common.remote.rmi.RMIObjectManagerRemote.search(Unknown S
ource)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at sun.rmi.server.UnicastServerRef.dispatch(Unknown Source)
at sun.rmi.transport.Transport$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.Transport.serviceCall(Unknown Source)
at sun.rmi.transport.tcp.TCPTransport.handleMessages(Unknown Source)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(Unknown Sour
ce)
at java.lang.Thread.run(Unknown Source)
15:20:03,165 WARN JDBCExceptionReporter:164 - SQL Error: 17006, SQLState: null
15:20:03,165 ERROR JDBCExceptionReporter:180 - Invalid column name
15:20:03,165 DEBUG BatcherImpl:115 - done closing: 0 open PreparedStatements, 0
open ResultSets
15:20:03,165 DEBUG BatcherImpl:115 - closing statement
15:20:03,165 DEBUG JDBCExceptionReporter:140 - SQL Exception
java.sql.SQLException: Invalid column name
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:222)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:285)
at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.j
ava:5153)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl
.java:698)
at oracle.jdbc.driver.OracleResultSet.getLong(OracleResultSet.java:1563)

at net.sf.hibernate.type.LongType.get(LongType.java:18)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:62)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:53)
at net.sf.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:352)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:203)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections
(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:955)
at net.sf.hibernate.loader.Loader.list(Loader.java:946)
at net.sf.hibernate.loader.SQLLoader.list(SQLLoader.java:92)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3802)
at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:52)
at gov.nih.nci.common.persistence.DefaultPersistenceManager.search(Unkno
wn Source)
at gov.nih.nci.common.manager.DefaultObjectManager.search(Unknown Source
)
at gov.nih.nci.common.remote.rmi.RMIObjectManagerRemote.search(Unknown S
ource)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at sun.rmi.server.UnicastServerRef.dispatch(Unknown Source)
at sun.rmi.transport.Transport$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.Transport.serviceCall(Unknown Source)
at sun.rmi.transport.tcp.TCPTransport.handleMessages(Unknown Source)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(Unknown Sour
ce)
at java.lang.Thread.run(Unknown Source)
15:20:03,165 WARN JDBCExceptionReporter:164 - SQL Error: 17006, SQLState: null
15:20:03,165 ERROR JDBCExceptionReporter:180 - Invalid column name
15:20:03,165 ERROR JDBCExceptionReporter:188 - SQLException occurred
java.sql.SQLException: Invalid column name
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:222)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:285)
at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.j
ava:5153)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl
.java:698)
at oracle.jdbc.driver.OracleResultSet.getLong(OracleResultSet.java:1563)

at net.sf.hibernate.type.LongType.get(LongType.java:18)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:62)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:53)
at net.sf.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:352)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:203)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections
(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:955)
at net.sf.hibernate.loader.Loader.list(Loader.java:946)
at net.sf.hibernate.loader.SQLLoader.list(SQLLoader.java:92)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3802)
at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:52)
at gov.nih.nci.common.persistence.DefaultPersistenceManager.search(Unkno
wn Source)
at gov.nih.nci.common.manager.DefaultObjectManager.search(Unknown Source
)
at gov.nih.nci.common.remote.rmi.RMIObjectManagerRemote.search(Unknown S
ource)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at sun.rmi.server.UnicastServerRef.dispatch(Unknown Source)
at sun.rmi.transport.Transport$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.Transport.serviceCall(Unknown Source)
at sun.rmi.transport.tcp.TCPTransport.handleMessages(Unknown Source)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(Unknown Sour
ce)
at java.lang.Thread.run(Unknown Source)

Thank you,
Payam.
Alexandru Popescu
Ranch Hand

Joined: Jul 12, 2004
Posts: 995
Can you post the mapping and table description? It will be easier to figure out the problem.

/pope


blog - InfoQ.com
Payam Fard
Ranch Hand

Joined: Jan 31, 2003
Posts: 73
Thanks for your response.

Sure here is the mapping files and table descriptions:

Gene table:


Gene mapping:



ArrayReportMap mapping:



Reporter table:



Reporter mapping:



GeneReporter table:



GeneReporter mapping:


Pathway table:



Pathway mapping:



pathway gene table:



pathway gene mapping:

Alexandru Popescu
Ranch Hand

Joined: Jul 12, 2004
Posts: 995
I think the tables you are showing use some relations which are not reflected into your mappings. This will reduce the power of Hibernate. Please consult the Hibernate reference document from the distro.

Try to use correctly Hibernate and create a good set of mappings. I will further investigate the issue.

/pope
Alexandru Popescu
Ranch Hand

Joined: Jul 12, 2004
Posts: 995
After some digging what I figured out is the following:
after the query is executed and a ResultSet was obtain Hibernate try to create the PATHWAY_ID object of type Long using new Long(resultSet.getLong(name)).
I am thinking that the JDBC driver you are using is reporting wrongly the error and in fact it faces a problem extracting a long value from a number field.

./pope
Gavin King
author
Ranch Hand

Joined: Aug 31, 2004
Posts: 76
Looks to me like you are using a native SQL query, right? You SQL does not look like it was generated by Hibernate.

Why not show us your native SQL query, and how you call it.


Co-Author of <a href="http://www.amazon.com/exec/obidos/ASIN/193239415X/ref=jranch-20" target="_blank" rel="nofollow">Hibernate in Action</a>
somkiat puisungnoen
Ranch Hand

Joined: Jul 04, 2003
Posts: 1312
Please post your java source code where occured error.


SCJA,SCJP,SCWCD,SCBCD,SCEA I
Java Developer, Thailand
Payam Fard
Ranch Hand

Joined: Jan 31, 2003
Posts: 73
Here is my code:

Payam Fard
Ranch Hand

Joined: Jan 31, 2003
Posts: 73
Any problems you see with the code?
Alexandru Popescu
Ranch Hand

Joined: Jul 12, 2004
Posts: 995
Originally posted by Payam Fard:
Any problems you see with the code?


The code seems oke to me, but still I believe the problem is the one posted by me before ;-).

./pope
Payam Fard
Ranch Hand

Joined: Jan 31, 2003
Posts: 73
I am thinking that the JDBC driver you are using is reporting wrongly the error and in fact it faces a problem extracting a long value from a number field


How are you suggesting I should fix the above problem?
Gavin King
author
Ranch Hand

Joined: Aug 31, 2004
Posts: 76
You don't need to use {aliases} for the tables you are not returning from the query. So your code can be very much simplified, and then it will work ;-) Nice when that happens :-)
Alexandru Popescu
Ranch Hand

Joined: Jul 12, 2004
Posts: 995
Originally posted by Payam Fard:


How are you suggesting I should fix the above problem?


Either you change the NUMBER to BIGINT and keep the mapping to Long, either you use as mapping for NUMBER the corresponding Java type (which I cannot find now :-( ). Please try to find the mapping info in Oracle docs.

./pope

PS: sorry for the lack of memory :-(
somkiat puisungnoen
Ranch Hand

Joined: Jul 04, 2003
Posts: 1312
select p.PATHWAY_ID as PATHWAY_ID0_, p.PATHWAY_NAME as PATHWAY_2_0_,
p.PATHWAY_DISPLAY as PATHWAY_3_0_, p.PATHWAY_DIAGRAM as PATHWAY_4_0_, p.TAXON as
TAXON0_ from BIO_Pathways p, Gene g, Reporter r, Pathway_Gene_Object pg, Gene_R
eporter gr where pg.pathway_id = p.pathway_id and pg.gene_id = g.gene_id and gr.
gene_id = g.gene_id and gr.reporter_id = r.reporter_id and r.reporter_id in (select arm.reporter_id from Array_Reporter_Map_V arm where arm.array_design_id = 7)


Can you run this query in sql command ?
Payam Fard
Ranch Hand

Joined: Jan 31, 2003
Posts: 73
Can you run this query in sql command ?


Yes, it runs fine.
Payam Fard
Ranch Hand

Joined: Jan 31, 2003
Posts: 73
You don't need to use {aliases} for the tables you are not returning from the query. So your code can be very much simplified, and then it will work ;-) Nice when that happens :-)


I will try this to see whether it would fix it.
Payam Fard
Ranch Hand

Joined: Jan 31, 2003
Posts: 73
either you use as mapping for NUMBER the corresponding Java type (which I cannot find now :-(

I will try to see if I can find the corresponding Java type for NUMBER. Thanks.
somkiat puisungnoen
Ranch Hand

Joined: Jul 04, 2003
Posts: 1312
select p.PATHWAY_ID as PATHWAY_ID0_, p.PATHWAY_NAME as PATHWAY_2_0_,
p.PATHWAY_DISPLAY as PATHWAY_3_0_, p.PATHWAY_DIAGRAM as PATHWAY_4_0_, p.TAXON as
TAXON0_ from BIO_Pathways p, Gene g, Reporter r, Pathway_Gene_Object pg, Gene_R
eporter gr where pg.pathway_id = p.pathway_id and pg.gene_id = g.gene_id and gr.
gene_id = g.gene_id and gr.reporter_id = r.reporter_id and r.reporter_id in (select arm.reporter_id from Array_Reporter_Map_V arm where arm.array_design_id = 7)



AND
In your code

sqlStr = "select {g.*} " +"from BIO_Pathways {p}, Gene {g}, " +"Reporter {r}, Pathway_Gene_Object {pg}, " +"Gene_Reporter {gr} " +"where {pg}.pathway_id = {p}.pathway_id and {pg}.gene_id = {g}.gene_id " +"and {gr}.gene_id = {g}.gene_id and {gr}.reporter_id = {r}.reporter_id " +"and {r}.reporter_id in (select {arm}.reporter_id from Array_Reporter_Map_V arm " +"where {arm}.array_design_id = 7)";



I think, Both sql (SQL and HQL) not mismatch.
Payam Fard
Ranch Hand

Joined: Jan 31, 2003
Posts: 73
I think, Both sql (SQL and HQL) not mismatch.


What do you mean?
Payam Fard
Ranch Hand

Joined: Jan 31, 2003
Posts: 73
Thanks Gavin. I tried your suggestion and it worked fine.

The interesting fact is that both the working and the non-working version generate the same sql.

Thanks to all for responding to my posting.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBCExceptionReport and Hibernate