-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 
  Previous topic | Next topic 
Author Message
wohler
 Post subject: createSQLQuery and quoted identifiers
PostPosted: Tue Jun 12, 2007 1:02 pm 
Newbie

Joined: Mon Jun 11, 2007 7:55 pm
Posts: 4
I have a field "dec" with the following annotation:

Code:
@Column(name = "`DEC`", nullable = false)

When I perform

Code:
session.createSQLQuery(s);
query.addEntity(Kic.class);
query.list();

I get

Code:
java.sql.SQLException: Column not found: "DEC"

I'd think that since Hibernate knows about the quoting, it should be able to DTRT when marshalling the results. Bug?

Hibernate version:
3.2.3

Mapping documents:
Code:
Entity
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
public abstract class StellarObject {

    @Id
    @Column(nullable = false)
    protected int keplerId;

    @Column(nullable = false)
    protected double ra;

    @Column(name = "`DEC`", nullable = false)
    protected double dec;

    ...


Code:
@Entity
@Table(name = "CM_KIC")
public class Kic extends StellarObject {
    ...
}


Code between sessionFactory.openSession() and session.close():
Code:
        String s = "select * from CM_KIC where...";
        SQLQuery query = dbService.getSession().createSQLQuery(s);
        query.addEntity(Kic.class);
        List<Kic> results = (List<Kic>) query.list(); <- Exception here


Name and version of the database you are using:
Oracle 10.

Log, SQL, and full stack trace:
Code:
2007-06-12 09:50:23,804 DEBUG [main:SessionImpl.<init>] opened session at timestamp: 11816670238
2007-06-12 09:50:23,817 DEBUG [main:QueryPlanCache.getNativeSQLQueryPlan] unable to locate native-sql query plan in cache; generating (select * from CM_KIC kic  where  kic.KEPLER_ID > 1 and kic.KEPLER_ID < 5)
2007-06-12 09:50:23,821 DEBUG [main:SQLCustomQuery.<init>] starting processing of sql query [select * from CM_KIC kic  where  kic.KEPLER_ID > 1 and kic.KEPLER_ID < 5]
2007-06-12 09:50:23,827 DEBUG [main:SQLQueryReturnProcessor.addPersister] mapping alias [Kic] to entity-suffix [0_]
2007-06-12 09:50:23,833 DEBUG [main:SessionImpl.listCustomQuery] SQL query: select * from CM_KIC kic  where  kic.KEPLER_ID > 1 and kic.KEPLER_ID < 5
2007-06-12 09:50:23,839 DEBUG [main:AbstractBatcher.logOpenPreparedStatement] about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
2007-06-12 09:50:23,839 DEBUG [main:ConnectionManager.openConnection] opening JDBC connection
2007-06-12 09:50:23,839 DEBUG [main:DriverManagerConnectionProvider.getConnection] total checked-out connections: 0
2007-06-12 09:50:23,839 DEBUG [main:DriverManagerConnectionProvider.getConnection] using pooled JDBC connection, pool size: 0
2007-06-12 09:50:23,840 DEBUG [main:AbstractBatcher.log]
    /* dynamic native SQL query */ select
        top ? *
    from
        CM_KIC kic 
    where
        kic.KEPLER_ID > 1
        and kic.KEPLER_ID < 5
Hibernate:
    /* dynamic native SQL query */ select
        top ? *
    from
        CM_KIC kic 
    where
        kic.KEPLER_ID > 1
        and kic.KEPLER_ID < 5
2007-06-12 09:50:23,840 DEBUG [main:AbstractBatcher.getPreparedStatement] preparing statement
2007-06-12 09:50:23,842 DEBUG [main:AbstractBatcher.logOpenResults] about to open ResultSet (open ResultSets: 0, globally: 0)
2007-06-12 09:50:23,843 DEBUG [main:Loader.doQuery] processing result set
2007-06-12 09:50:23,843 DEBUG [main:Loader.doQuery] result set row: 0
2007-06-12 09:50:23,843 DEBUG [main:NullableType.nullSafeGet] returning '2' as column: KEPLER_ID
2007-06-12 09:50:23,844 DEBUG [main:Loader.getRow] result row: EntityKey[gov.nasa.kepler.hibernate.cm.Kic#2]
2007-06-12 09:50:23,844 DEBUG [main:Loader.loadFromResultSet] Initializing object from ResultSet: [gov.nasa.kepler.hibernate.cm.Kic#2]
2007-06-12 09:50:23,846 DEBUG [main:AbstractEntityPersister.hydrate] Hydrating entity: [gov.nasa.kepler.hibernate.cm.Kic#2]
2007-06-12 09:50:23,847 INFO  [main:NullableType.nullSafeGet] could not read column value from result set: "DEC"; Column not found: "DEC"
2007-06-12 09:50:23,847 DEBUG [main:AbstractBatcher.logCloseResults] about to close ResultSet (open ResultSets: 1, globally: 1)
2007-06-12 09:50:23,848 DEBUG [main:AbstractBatcher.logClosePreparedStatement] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2007-06-12 09:50:23,848 DEBUG [main:AbstractBatcher.closePreparedStatement] closing statement
2007-06-12 09:50:23,851 DEBUG [main:JDBCExceptionReporter.logExceptions] could not execute query [select * from CM_KIC kic  where  kic.KEPLER_ID > 1 and kic.KEPLER_ID < 5]
java.sql.SQLException: Column not found: "DEC"
   at org.hsqldb.jdbc.Util.sqlException(Util.java:61)
   at org.hsqldb.jdbc.Util.sqlException(Util.java:70)
   at org.hsqldb.jdbc.jdbcResultSet.findColumn(jdbcResultSet.java:1671)
   at org.hsqldb.jdbc.jdbcResultSet.getDouble(jdbcResultSet.java:1159)
   at org.hibernate.type.DoubleType.get(DoubleType.java:22)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:163)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:154)
   at org.hibernate.type.AbstractType.hydrate(AbstractType.java:81)
   at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2096)
   at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1380)
   at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1308)
   at org.hibernate.loader.Loader.getRow(Loader.java:1206)
   at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:580)
   at org.hibernate.loader.Loader.doQuery(Loader.java:701)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
   at org.hibernate.loader.Loader.doList(Loader.java:2220)
   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
   at org.hibernate.loader.Loader.list(Loader.java:2099)
   at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
   at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
   at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
   at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
   at gov.nasa.kepler.hibernate.cm.KicCrud.retrieveKics(KicCrud.java:191)
   at gov.nasa.kepler.hibernate.cm.KicCrudTest.testRetrieveKicsWithQuery(KicCrudTest.java:468)
   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:597)
   at org.junit.internal.runners.TestMethodRunner.executeMethodBody(TestMethodRunner.java:99)
   at org.junit.internal.runners.TestMethodRunner.runUnprotected(TestMethodRunner.java:81)
   at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
   at org.junit.internal.runners.TestMethodRunner.runMethod(TestMethodRunner.java:75)
   at org.junit.internal.runners.TestMethodRunner.run(TestMethodRunner.java:45)
   at org.junit.internal.runners.TestClassMethodsRunner.invokeTestMethod(TestClassMethodsRunner.java:71)
   at org.junit.internal.runners.TestClassMethodsRunner.run(TestClassMethodsRunner.java:35)
   at org.junit.internal.runners.TestClassRunner$1.runUnprotected(TestClassRunner.java:42)
   at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
   at org.junit.internal.runners.TestClassRunner.run(TestClassRunner.java:52)
   at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38)
   at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
2007-06-12 09:50:23,854 WARN  [main:JDBCExceptionReporter.logExceptions] SQL Error: -28, SQLState: S0022
2007-06-12 09:50:23,854 ERROR [main:JDBCExceptionReporter.logExceptions] Column not found: "DEC"
2007-06-12 09:50:23,855 DEBUG [main:JDBCContext.afterNontransactionalQuery] after autocommit
2007-06-12 09:50:23,855 DEBUG [main:ConnectionManager.aggressiveRelease] aggressively releasing JDBC connection
2007-06-12 09:50:23,855 DEBUG [main:ConnectionManager.closeConnection] releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
2007-06-12 09:50:23,855 DEBUG [main:DriverManagerConnectionProvider.closeConnection] returning connection to pool, pool size: 1
2007-06-12 09:50:23,870 DEBUG [main:SessionImpl.close] closing session
2007-06-12 09:50:23,870 DEBUG [main:ConnectionManager.cleanup] connection already null in cleanup : no action
Top
 Profile  
 
wohler
 Post subject: Re: createSQLQuery and quoted identifiers
PostPosted: Tue Jun 12, 2007 4:04 pm 
Newbie

Joined: Mon Jun 11, 2007 7:55 pm
Posts: 4
wohler wrote:
Name and version of the database you are using:
Oracle 10.


Here's the stack trace under Oracle. The information above, which was slightly more illuminating as it highlighted the broken column, was actually for HSQLDB.

Code:
org.hibernate.exception.GenericJDBCException: could not execute query
   at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.loader.Loader.doList(Loader.java:2223)
   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
   at org.hibernate.loader.Loader.list(Loader.java:2099)
   at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
   at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
   at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
   at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
   at gov.nasa.kepler.hibernate.cm.KicCrud.hackQuery(KicCrud.java:271)
   at gov.nasa.kepler.hibernate.cm.KicCrud.retrieveKics(KicCrud.java:187)
   at gov.nasa.kepler.hibernate.ui.KicCrudProxy.retrieveKics(KicCrudProxy.java:177)
   at gov.nasa.kepler.ui.cm.TargetListEditor$QueryTask.doInBackground(TargetListEditor.java:1781)
   at gov.nasa.kepler.ui.cm.TargetListEditor$QueryTask.doInBackground(TargetListEditor.java:1)
   at org.jdesktop.swingworker.SwingWorker$1.call(Unknown Source)
   at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
   at java.util.concurrent.FutureTask.run(FutureTask.java:138)
   at org.jdesktop.swingworker.SwingWorker.run(Unknown Source)
   at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907)
   at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.SQLException: Invalid column name
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
   at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java:3047)
   at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:1861)
   at oracle.jdbc.driver.OracleResultSet.getDouble(OracleResultSet.java:1641)
   at org.hibernate.type.DoubleType.get(DoubleType.java:22)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:163)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:154)
   at org.hibernate.type.AbstractType.hydrate(AbstractType.java:81)
   at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2096)
   at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1380)
   at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1308)
   at org.hibernate.loader.Loader.getRow(Loader.java:1206)
   at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:580)
   at org.hibernate.loader.Loader.doQuery(Loader.java:701)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
   at org.hibernate.loader.Loader.doList(Loader.java:2220)
   ... 18 more
Top
 Profile  
 
jstuyts
 Post subject:
PostPosted: Thu Jan 17, 2008 6:49 am 
Newbie

Joined: Thu Jan 08, 2004 8:33 am
Posts: 9
Location: Abbekerk, the Netherlands
I use traditional Hibernate mapping files instead of annotations, and I encounter the same problem with SQL Server:
Code:
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The column name [unit_oid] is not valid.
   at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
   at com.microsoft.sqlserver.jdbc.SQLServerResultSet.findColumn(Unknown Source)
   at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getLong(Unknown Source)
   at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.getLong(DelegatingResultSet.java:239)
   at org.hibernate.type.LongType.get(LongType.java:28)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:163)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:154)
   at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:1097)
   at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:565)
   at org.hibernate.loader.Loader.doQuery(Loader.java:701)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
   at org.hibernate.loader.Loader.doList(Loader.java:2220)
   ... 47 more


Did somebody already help you with this problem? Or did you create an issue for it?

_________________
Johan Stuyts

Top
 Profile  
 
wohler
 Post subject:
PostPosted: Sat Jan 26, 2008 7:49 pm 
Newbie

Joined: Mon Jun 11, 2007 7:55 pm
Posts: 4
Johan, I did not submit an issue, nor have I received a response. However, I seemed to have worked out the problem since my post.

Interestingly, I am still using `DEC` but no longer get the exception. I think it's because I now use createQuery instead of createSQLQuery.

_________________
Bill Wohler <wohler@newt.com>

Top
 Profile  
 
jstuyts
 Post subject:
PostPosted: Mon Jan 28, 2008 4:33 am 
Newbie

Joined: Thu Jan 08, 2004 8:33 am
Posts: 9
Location: Abbekerk, the Netherlands
Hi Bill, thanks for your answer.

I did some debugging to see if this was an easy change in Hibernate, but it turned out the quoting is already present in the metadata. I did not want to touch that because it is used all over the place.

I decided to use the pragmatic option: remove all escaping from my Hibernate mapping files, and add a suffix to column names that are SQL keywords.

_________________
Johan Stuyts

Top
 Profile  
 
wohler
 Post subject:
PostPosted: Wed Jan 30, 2008 1:20 am 
Newbie

Joined: Mon Jun 11, 2007 7:55 pm
Posts: 4
Quote:
I decided to use the pragmatic option: remove all escaping from my Hibernate mapping files, and add a suffix to column names that are SQL keywords.


That's certainly a fine solution, but Hibernate should not require that. In our case, because the names of the database columns are controlled by an outside specification, I didn't have that luxury.

There is still a happy ending. As I said, it's working now, and I *think* it's because I switched from createSQLQuery to createQuery.

_________________
Bill Wohler <wohler@newt.com>

Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.