• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

JDBCExceptionReport and Hibernate

 
Ranch Hand
Posts: 77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 995
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Can you post the mapping and table description? It will be easier to figure out the problem.

/pope
 
Payam Fard
Ranch Hand
Posts: 77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 995
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 995
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
author
Posts: 76
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 1312
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Please post your java source code where occured error.
 
Payam Fard
Ranch Hand
Posts: 77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Here is my code:

 
Payam Fard
Ranch Hand
Posts: 77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Any problems you see with the code?
 
Alexandru Popescu
Ranch Hand
Posts: 995
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 76
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 995
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 1312
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Can you run this query in sql command ?



Yes, it runs fine.
 
Payam Fard
Ranch Hand
Posts: 77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 1312
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

I think, Both sql (SQL and HQL) not mismatch.



What do you mean?
 
Payam Fard
Ranch Hand
Posts: 77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
reply
    Bookmark Topic Watch Topic
  • New Topic