[Closed] Odd "cannot execute query" exceptions

MyEclipse Archived

Database Tools (DB Explorer, Hibernate, etc.)

This topic has 9 replies, 3 voices, and was last updated 18 years, 5 months ago by Peter L. Berghold.

Viewing 10 posts - 1 through 10 (of 10 total)

Author

Posts
April 28, 2006 at 10:50 am #251308 Reply
Peter L. Berghold
Member
Hi folks,

I’ve written a fairly simple HQL query and yet when I fetch the result I get an exception issued:

HTTP Status 500 –

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

java.lang.RuntimeException: org.hibernate.exception.GenericJDBCException: could not execute query
org.agilitystewards.hbm.services.AccountService.getAccountByLogin(AccountService.java:52)

<snip!>

In the course of my trying to pin down what in the world is going on with this I have hardcoded certain things in an attempt to eliminate HQL syntax errors and
such, but here is the code that is being affected:
```
    public Account getAccountByLogin(String uid){
        Account acct = null;
        System.err.println("Entering AccountService::getAccountByLogin");
        System.err.println("Looking for login: " + uid);
        
        
        try {
            String hql="from org.agilitystewards.hbm.tables.Account acct where acct.login = 'peter'";
            
            Session session = SessionFactory.currentSession();
            Query q = session.createQuery(hql);
            
            System.err.println("Setting named parameter");
            // q.setParameter(0,uid);
            
            
            
            System.err.println("Retrieving object.");
            acct = (Account) q.uniqueResult();
            System.err.println("Object retrieved...");
            
        } catch (ObjectNotFoundException e){
            return null;
        } catch (HibernateException e) {
            throw new RuntimeException(e);
        }
        
        if ( acct == null ){
            System.err.println("Could not fetch an object, returning null");
        } else {
            System.err.println("Retrieved account #" + acct.getAccountId());
            
        }
        System.err.println("Exiting AccountService::getAccountByLogin");
        
        return acct;
        
        
    }
```
Normally I would have the parameter “‘peter'” replaced with a “?” and be using the setParameter method for Query to set the placeholer. I’ve hardcoded a known account id just to try and see the code work.

Also in the process of trying to pin this down I’ve turned on logging on my test Posgres server and the syntax of the issued SQL from hibernate is correct. I cut/pest the SQL into an interactive psql session just to be sure and it executed just fine, returning the unique result that I expected.

The Account.hbm.xml file was autogenerated by the database explorer tool and looks like this:
```
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
    <class name="org.agilitystewards.hbm.tables.Person" table="person" schema="public">
        <id name="personId" type="integer">
            <column name="person_id" />
            <generator class="native" />
        </id>
        <property name="emailAddr" type="string">
            <column name="email_addr" length="80" not-null="true" />
        </property>
        <property name="givenName" type="string">
            <column name="given_name" length="50" not-null="true" />
        </property>
        <property name="surname" type="string">
            <column name="surname" length="50" not-null="true" />
        </property>
        <property name="showEmail" type="byte">
            <column name="show_email" not-null="true" />
        </property>
        <property name="showPhone" type="byte">
            <column name="show_phone" not-null="true" />
        </property>
        <set name="accounts" inverse="true">
            <key>
                <column name="person_id" not-null="true" />
            </key>
            <one-to-many class="org.agilitystewards.hbm.tables.Account" />
        </set>
        <set name="telephoneNumberses" inverse="true">
            <key>
                <column name="person_id" not-null="true" />
            </key>
            <one-to-many class="org.agilitystewards.hbm.tables.TelephoneNumbers" />
        </set>
        <set name="stewards" inverse="true">
            <key>
                <column name="person_id" not-null="true" />
            </key>
            <one-to-many class="org.agilitystewards.hbm.tables.Steward" />
        </set>
    </class>
</hibernate-mapping>
```
At this point I’m baffled and running out of ideas as to why this rather simple Hibernate code isn’t working. Everything to my eye looks like it should run but its erroring out. Any thoughts? Am I missing something obvous?
April 28, 2006 at 1:35 pm #251316 Reply

Riyad Kalla
Member

Peter,
Post the entire stack trace for the exception, those things tend to be in the format of:
EXCEPTION
<tons of stuff>
CASUED BY: OTHER EXCEPTION
<tons of stuff>

and so on, and it’s normally the one near the bottom we want, so just post the whole thing. I do appreciate the detailed post for starters though.

April 29, 2006 at 4:52 am #251363 Reply

Haris Peco
Member

Peter,

Please, send Account.hbm.xml – you send Person.bm.xml and stack trace what Riyad said

I suggest that you try test standalone (out of tomcat) and then transfer to tomcat

Thanks

April 29, 2006 at 8:32 pm #251382 Reply
Peter L. Berghold
Member
Here is everything I am seeing. Nothing is showing up in the Tomcat logs.

HTTP Status 500 –

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

java.lang.RuntimeException: org.hibernate.exception.GenericJDBCException: could not execute query
org.agilitystewards.hbm.services.AccountService.getAccountByLogin(AccountService.java:52)
org.agilitystewards.servlets.LoginServlet.processInput(LoginServlet.java:56)
org.agilitystewards.servlets.LoginServlet.doGet(LoginServlet.java:95)
javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

note The full stack trace of the root cause is available in the Apache Tomcat/5.5.16 logs.
Apache Tomcat/5.5.16

And here is Account.hbm.xml:
```
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
    <class name="org.agilitystewards.hbm.tables.Account" table="account" schema="public">
        <id name="accountId" type="integer">
            <column name="account_id" />
            <generator class="native" />
        </id>
        <many-to-one name="person" class="org.agilitystewards.hbm.tables.Person" fetch="select">
            <column name="person_id" not-null="true" />
        </many-to-one>
        <property name="login" type="string">
            <column name="login" length="15" not-null="true" />
        </property>
        <property name="passwd" type="string">
            <column name="passwd" length="50" not-null="true" />
        </property>
        <property name="active" type="byte">
            <column name="active" not-null="true" />
        </property>
        <property name="locked" type="byte">
            <column name="locked" not-null="true" />
        </property>
        <set name="roles" inverse="true">
            <key>
                <column name="account_id" not-null="true" />
            </key>
            <one-to-many class="org.agilitystewards.hbm.tables.Role" />
        </set>
    </class>
</hibernate-mapping>
```
The fact that I can’t seem to figure out how to get tomcat to be more loquatious about its errors is another topic for another thread…
April 29, 2006 at 8:42 pm #251386 Reply

Riyad Kalla
Member

Check your Console view in MyEclipse, output is generally redirected there.

April 29, 2006 at 9:20 pm #251387 Reply
Peter L. Berghold
Member
actually, there isn’t anything else in the console other than:
```
 Entering AccountService::getAccountByLogin
Looking for login: peter
Setting named parameter
Retrieving object.
Hibernate: select account0_.account_id as account1_, account0_.person_id as person2_0_, account0_.login as login0_, account0_.passwd as passwd0_, account0_.active as active0_, account0_.locked as locked0_ from public.account account0_ where account0_.login='peter'
```
Remember that I have (currently) the parameter “peter” hardcoded just trying to get “something” back from the database.
April 29, 2006 at 9:25 pm #251388 Reply

Peter L. Berghold
Member

What gets me is it looks like the query is being executed, but Hibernate/JDBC doesn’t think that it was.

April 29, 2006 at 11:09 pm #251390 Reply
Haris Peco
Member
Peter,

Hibernate translate hql to sql and print it – it doesn’t mean that query is good .
You can paste query to MyEclipse SQL editor and try execute

However, hibernate can’t execute hql and it is much easier test in standalone

You can enhance logging with log4j.properties, too

This is example (add it in src directory in / )
```
### direct log messages to stdout ###
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

### direct messages to file hibernate.log ###
#log4j.appender.file=org.apache.log4j.FileAppender
#log4j.appender.file.File=hibernate.log
#log4j.appender.file.layout=org.apache.log4j.PatternLayout
#log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

### set log levels - for more verbose logging change 'info' to 'debug' ###

log4j.rootLogger=warn, stdout

#log4j.logger.org.hibernate=info
log4j.logger.org.hibernate=debug

### log HQL query parser activity
#log4j.logger.org.hibernate.hql.ast.AST=debug

### log just the SQL
log4j.logger.org.hibernate.SQL=debug

### log JDBC bind parameters ###
#log4j.logger.org.hibernate.type=info
log4j.logger.org.hibernate.type=debug

### log schema export/update ###
log4j.logger.org.hibernate.tool.hbm2ddl=debug

### log HQL parse trees
#log4j.logger.org.hibernate.hql=debug

### log cache activity ###
#log4j.logger.org.hibernate.cache=debug

### log transaction activity
log4j.logger.org.hibernate.transaction=debug

### log JDBC resource acquisition
log4j.logger.org.hibernate.jdbc=debug

### enable the following line if you want to track down connection ###
### leakages when using DriverManagerConnectionProvider ###
log4j.logger.org.hibernate.connection.DriverManagerConnectionProvider=trace
```
April 30, 2006 at 7:44 am #251397 Reply

Peter L. Berghold
Member

OK… I think I now understand the problem, now I just got to figure out how to fix it. There’s a complaint about byte vs. boolean types.

Here’s the stack trace:

08:41:38,819 DEBUG HqlSqlBaseWalker:128 – query() >> end, level = 1
08:41:38,831 DEBUG AST:193 – — SQL AST —
\-[SELECT] QueryNode: ‘SELECT’ querySpaces (public.account)
+-[SELECT_CLAUSE] SelectClause: ‘{derived select clause}’
| +-[SELECT_EXPR] SelectExpressionImpl: ‘account0_.account_id as account1_’ {FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=acct,role=null,tableName=public.account,tableAlias=account0_,colums={,className=org.agilitystewards.hbm.tables.Account}}}
| \-[SQL_TOKEN] SqlFragment: ‘account0_.person_id as person2_0_, account0_.login as login0_, account0_.passwd as passwd0_, account0_.active as active0_, account0_.locked as locked0_’
+-[FROM] FromClause: ‘from’ FromClause{level=1, fromElementCounter=1, fromElements=1, fromElementByClassAlias=[acct], fromElementByTableAlias=[account0_], fromElementsByPath=[], collectionJoinFromElementsByPath=[], impliedElements=[]}
| \-[FROM_FRAGMENT] FromElement: ‘public.account account0_’ FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=acct,role=null,tableName=public.account,tableAlias=account0_,colums={,className=org.agilitystewards.hbm.tables.Account}}
\-[WHERE] SqlNode: ‘where’
\-[EQ] SqlNode: ‘=’
+-[DOT] DotNode: ‘account0_.login’ {propertyName=login,dereferenceType=4,propertyPath=login,path=acct.login,tableAlias=account0_,className=org.agilitystewards.hbm.tables.Account,classAlias=acct}
| +-[ALIAS_REF] IdentNode: ‘account0_.account_id’ {alias=acct, className=org.agilitystewards.hbm.tables.Account, tableAlias=account0_}
| \-[IDENT] IdentNode: ‘login’ {originalText=login}
\-[QUOTED_STRING] LiteralNode: ”peter”

08:41:38,834 DEBUG ErrorCounter:72 – throwQueryException() : no errors
08:41:38,906 DEBUG QueryTranslatorImpl:177 – HQL: from org.agilitystewards.hbm.tables.Account acct where acct.login = ‘peter’
08:41:38,912 DEBUG QueryTranslatorImpl:178 – SQL: select account0_.account_id as account1_, account0_.person_id as person2_0_, account0_.login as login0_, account0_.passwd as passwd0_, account0_.active as active0_, account0_.locked as locked0_ from public.account account0_ where account0_.login=’peter’
08:41:38,916 DEBUG ErrorCounter:72 – throwQueryException() : no errors
08:41:38,958 DEBUG AbstractBatcher:290 – about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
08:41:38,962 DEBUG ConnectionManager:296 – opening JDBC connection
08:41:39,011 DEBUG DriverManagerConnectionProvider:93 – total checked-out connections: 0
08:41:39,014 DEBUG DriverManagerConnectionProvider:99 – using pooled JDBC connection, pool size: 0
08:41:39,034 DEBUG SQL:324 – select account0_.account_id as account1_, account0_.person_id as person2_0_, account0_.login as login0_, account0_.passwd as passwd0_, account0_.active as active0_, account0_.locked as locked0_ from public.account account0_ where account0_.login=’peter’
Hibernate: select account0_.account_id as account1_, account0_.person_id as person2_0_, account0_.login as login0_, account0_.passwd as passwd0_, account0_.active as active0_, account0_.locked as locked0_ from public.account account0_ where account0_.login=’peter’
08:41:39,038 DEBUG AbstractBatcher:378 – preparing statement
08:41:41,048 DEBUG AbstractBatcher:306 – about to open ResultSet (open ResultSets: 0, globally: 0)
08:41:41,054 DEBUG Loader:405 – processing result set
08:41:41,063 DEBUG Loader:410 – result set row: 0
08:41:41,066 DEBUG IntegerType:86 – returning ‘1’ as column: account1_
08:41:41,086 DEBUG Loader:828 – result row: EntityKey[org.agilitystewards.hbm.tables.Account#1]
08:41:41,107 DEBUG Loader:978 – Initializing object from ResultSet: [org.agilitystewards.hbm.tables.Account#1]
08:41:41,143 DEBUG BasicEntityPersister:1651 – Hydrating entity: [org.agilitystewards.hbm.tables.Account#1]
08:41:41,146 DEBUG IntegerType:86 – returning ‘1’ as column: person2_0_
08:41:41,149 DEBUG StringType:86 – returning ‘peter’ as column: login0_
08:41:41,168 DEBUG StringType:86 – returning ‘letmein’ as column: passwd0_
08:41:41,321 DEBUG AbstractBatcher:313 – about to close ResultSet (open ResultSets: 1, globally: 1)
08:41:41,350 DEBUG AbstractBatcher:298 – about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
08:41:41,353 DEBUG AbstractBatcher:416 – closing statement
08:41:41,446 DEBUG JDBCExceptionReporter:63 – could not execute query [select account0_.account_id as account1_, account0_.person_id as person2_0_, account0_.login as login0_, account0_.passwd as passwd0_, account0_.active as active0_, account0_.locked as locked0_ from public.account account0_ where account0_.login=’peter’]
org.postgresql.util.PSQLException: Bad value for type byte : t
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getByte(AbstractJdbc2ResultSet.java:1906)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getByte(AbstractJdbc2ResultSet.java:2161)
at org.hibernate.type.ByteType.get(ByteType.java:26)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:68)
at org.hibernate.type.AbstractType.hydrate(AbstractType.java:80)
at org.hibernate.persister.entity.BasicEntityPersister.hydrate(BasicEntityPersister.java:1690)
at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:991)
at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:942)
at org.hibernate.loader.Loader.getRow(Loader.java:855)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:305)
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)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:395)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:844)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:603)
at org.agilitystewards.hbm.services.AccountService.getAccountByLogin(AccountService.java:47)
at org.agilitystewards.servlets.LoginServlet.processInput(LoginServlet.java:67)
at org.agilitystewards.servlets.LoginServlet.doPost(LoginServlet.java:139)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:595)
08:41:41,472 WARN JDBCExceptionReporter:71 – SQL Error: 0, SQLState: 22003
08:41:41,475 ERROR JDBCExceptionReporter:72 – Bad value for type byte : t
08:41:41,485 DEBUG JDBCContext:322 – after autocommit
Hibernate is blowing chunks.
org.hibernate.exception.GenericJDBCException: could not execute query
08:41:41,535 ERROR [LoginServlet]:260 – Servlet.service() for servlet LoginServlet threw exception
java.lang.RuntimeException: org.hibernate.exception.GenericJDBCException: could not execute query
at org.agilitystewards.hbm.services.AccountService.getAccountByLogin(AccountService.java:57)
at org.agilitystewards.servlets.LoginServlet.processInput(LoginServlet.java:67)
at org.agilitystewards.servlets.LoginServlet.doPost(LoginServlet.java:139)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:595)
Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:82)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.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.hql.QueryLoader.list(QueryLoader.java:395)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:844)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:603)
at org.agilitystewards.hbm.services.AccountService.getAccountByLogin(AccountService.java:47)
… 18 more
Caused by: org.postgresql.util.PSQLException: Bad value for type byte : t
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getByte(AbstractJdbc2ResultSet.java:1906)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getByte(AbstractJdbc2ResultSet.java:2161)
at org.hibernate.type.ByteType.get(ByteType.java:26)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:68)
at org.hibernate.type.AbstractType.hydrate(AbstractType.java:80)
at org.hibernate.persister.entity.BasicEntityPersister.hydrate(BasicEntityPersister.java:1690)
at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:991)
at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:942)
at org.hibernate.loader.Loader.getRow(Loader.java:855)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:305)
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)
… 25 more

Now I gotta figure out how to fix it…

April 30, 2006 at 8:43 pm #251417 Reply

Peter L. Berghold
Member

OK:

Thanks for the pointers gang. I was able to fix this eventually by using the data type mapper tool within the wizard for creating the Hibernate mappings. Once I told it to map the boolean columns to Hibernate type “boolean” instead of Hibernate type “byte” things went better.

I have a new problem now, but pending my inablity to resolve it I may open a new subject thread for that.

Thanks again for all the pointers.
Author

Posts

Viewing 10 posts - 1 through 10 (of 10 total)