Problem using createSQLQuery/creating native SQL

KakashiSensei · **Joined:** Thu Aug 31, 2006 9:53 am **Posts:** 6

Hibernate version: 2.1.7

Full stack trace of any exception that occurs:
10:27:06,756 WARN JDBCExceptionReporter:57 - SQL Error: 0, SQLState: 42703
10:27:06,756 ERROR JDBCExceptionReporter:58 - The column name id0_ was not found in this ResultSet.
10:27:06,787 WARN JDBCExceptionReporter:57 - SQL Error: 0, SQLState: 42703
10:27:06,787 ERROR JDBCExceptionReporter:58 - The column name id0_ was not found in this ResultSet.
net.sf.hibernate.exception.SQLGrammarException: error performing findBySQL
at net.sf.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:30)
at net.sf.hibernate.impl.SessionImpl.convert(SessionImpl.java:4110)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3858)
at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:52)
at nl.qqq.emr.dao.TransportDaoImpl.getAllTransports(TransportDaoImpl.java:422)
at test.DaoTest.testGetAllTransports(DaoTest.java:198)
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:585)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:128)
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)
Caused by: org.postgresql.util.PSQLException: The column name id0_ was not found in this ResultSet.

Name and version of the database you are using: PostGreSQL 8.1.x

The generated SQL (show_sql=true):
select r.modeid as ModeID1_,
t.commodityGroupid as Commodit3_0_,
t.containerindicatorid as Containe4_0_,
t.loadindicatorid as LoadIndi5_0_,
sum(volume) / count(distinct year)

from transport t inner join route r on t.routeid = r.id

where r.originzoneid in (select id from zone where emrcode = ?) and
r.destinationzoneid in (select id from zone where emrcode = ?)

group by r.modeid, t.commodityGroupid, t.containerindicatorid, t.loadindicatorid
order by r.modeid, t.commodityGroupid, t.containerindicatorid, t.loadindicatorid

Hi all,

Im trying to perform a native SQL with createSQLQuery(..), the exact generated SQL which I add as a parameter in this method looks like this:

Code:

String avgStr = select r.modeid as {r.mode}, t.commodityGroupid as {t.commodityGroup}, t.containerindicatorid as {t.containderindicator}, t.loadindicatorid as {t.loadindicator}, (sum(volume) / count(distinct year)) 
from transport t inner join route r on t.routeid = r.id 
where r.originzoneid in (select id from zone where emrcode = :origin) and r.destinationzoneid in (select id from zone where emrcode = :dest) 
group by r.modeid, t.commodityGroupid, t.containerindicatorid, t.loadindicatorid 
order by r.modeid, t.commodityGroupid, t.containerindicatorid, t.loadindicatorid

And this is how I call this method:

Code:

query = getSession().createSQLQuery(
                    avgStr, 
                    new String[] {"t", "r"}, 
                    new Class[] {Transport.class, Route.class}); 
.
.
.
return query.setString("origin", origin)
            .setString("dest", destination)
            .list()
            .iterator();

When I run this code, I get an error msg (see above) which I dont understand.
It says that id_0 is not found in the resultset. What does that mean ? I dont see any id_0 variable when I turn on show_sql:true.

Any help would be appreciated.

Thx.

PS: the only reason why Im using native SQL is that in HQL, I couldnt write: "(sum(volume) / count(distinct year))". When you do this, you get a syntax error which says that "/" is not allowed (or something like that).

KakashiSensei · **Joined:** Thu Aug 31, 2006 9:53 am **Posts:** 6

I've even simplified my query to:

Code:

String sql = "select "
             + "t.commodityGroupid as {t.commodityGroup}, "
             + "t.containerindicatorid as {t.containderindicator}, "
             + "t.loadindicatorid as {t.loadindicator} "
             + "from Transport {t}";

session = sf.openSession();
Query query = session.createSQLQuery(
                 sql,
                 new String[] {"t"},
                 new Class[] {Transport.class});
             
return query.list().iterator();

And I still get the same error msg, that is:

Code:

Hibernate: select t.commodityGroupid as Commodit3_0_, t.containerindicatorid as Containe4_0_, t.loadindicatorid as LoadIndi5_0_ from Transport t


12:46:58,350  WARN JDBCExceptionReporter:57 - SQL Error: 0, SQLState: 42703
net.sf.hibernate.exception.SQLGrammarException: error performing findBySQL
   at net.sf.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
   at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:30)
   at net.sf.hibernate.impl.SessionImpl.convert(SessionImpl.java:4110)
   at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3858)
   at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:52)
   at test.DaoTest.testGetAllTransports(DaoTest.java:238)
   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:585)
   at junit.framework.TestCase.runTest(TestCase.java:154)
   at junit.framework.TestCase.runBare(TestCase.java:127)
   at junit.framework.TestResult$1.protect(TestResult.java:106)
   at junit.framework.TestResult.runProtected(TestResult.java:124)
   at junit.framework.TestResult.run(TestResult.java:109)
   at junit.framework.TestCase.run(TestCase.java:118)
   at junit.framework.TestSuite.runTest(TestSuite.java:208)
   at junit.framework.TestSuite.run(TestSuite.java:203)
   at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:128)
   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)
Caused by: org.postgresql.util.PSQLException: The column name id0_ was not found in this ResultSet.

Ow just one another thing: all the columns in the select stmt are enum-types.

KakashiSensei · **Joined:** Thu Aug 31, 2006 9:53 am **Posts:** 6

I've found some more results:

When using native SQL, somehow, the select stmt must contain all the column names otherwise you get error msgs like in my 1st and 2nd post.

But this is strange, since the Hibernate manual contains also some examples of queries where only some columns are selected.

So, the "id0_ not found in the Resultset" error refers to that it cannot find "t.id" in the select stmt. When I add this, then it will say that other columns (which are not in the select stmt) are not found.

Really strange...

hrivik · **Joined:** Tue Sep 26, 2006 6:43 am **Posts:** 1

Today I had same error.
I use Hibernate 2.1.8 and for some optimization issues I wanted to use native SQL.
Also as you, I find that I have to use {alias.*} or name all parameters from object manually.
It is not possible to select just few of them.
So I did the work and I put {alias).column_name for all columns in my tables I used in select with hope that hibernate will not fetch everything.
But it fetched all the related objects also.

The reason of use of native query was to use left outer join between classes with no relations between in mapping xml. but I was not satisfied that hibernate fetches all the objects when I needed just 3 columns from the select.

For now I decided to use direct JDBC call.

Roman

KakashiSensei · **Joined:** Thu Aug 31, 2006 9:53 am **Posts:** 6

I'd also given up using createSQLQuery(..) and switched to JDBC (which made my code really ugly, but it works now).

max · **Posted:** Tue Sep 26, 2006 8:07 am

eh you guys apparently does not read the docs very well ;)

Hibernate have always supported joining on arbitrary properties between classes; you do not have to map them as associations.

Plus Hibernate supports scalar queries allowing you to specificy exactly which small pieces of data you want to get (e..g select p.name, p.age from Person does not fetch Person's, just the two name and age values)

In Hibernate 2 native sql querying is *only* for fetching entities.

In Hibernate 3 native sql querying works for both scalar and entity queries.

In Hibernate 3.1 you are not required to use the {} for most native sql quries.

KakashiSensei · **Joined:** Thu Aug 31, 2006 9:53 am **Posts:** 6

We were both using Hibernate 2.x ;)

But you're right...hibernate 2.1.7 doc states that each property must be stated in your query. I was probably reading the doc of 3.x, and that's why I got confused.

Maybe I'll try to migrate 3.x in the future.

max · **Posted:** Tue Sep 26, 2006 8:18 am

reading the docs that matches the software you are using is definitly recommended ;)