-->
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
  [ 7 posts ] 
  Previous topic | Next topic 
Author Message
scott69taylor
 Post subject: DataException when querying with a string > than column s
PostPosted: Tue Jan 20, 2009 9:24 am 
Newbie

Joined: Sun May 23, 2004 2:03 pm
Posts: 7
Hibernate version:
hibernate-core-3.3.1.GA
hibernate-commons-annotations-3.1.0.GA
hibernate-annotations-3.4.0.GA

Mapping documents:
None. Using annotations.

Full stack trace of any exception that occurs:
2009-01-20 13:29:26,473 ERROR pool-1-thread-1 util.JDBCExceptionReporter DB2 SQL error: SQLCODE: -302, SQLSTATE: 22001, SQLERRMC: null
2009-01-20 13:29:26,473 ERROR pool-1-thread-1 occ.DemarcateTransaction An exception occurred. Rolling back database transaction.
org.hibernate.exception.DataException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:100)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2231)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
at org.hibernate.loader.Loader.list(Loader.java:2120)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:118)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1596)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:306)
at com.netgiro.occ.common.avs.AvsAnswerCodeDaoHib.getAvsAnswerCode(AvsAnswerCodeDaoHib.java:40)
<snip>
Caused by: com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -302, SQLSTATE: 22001, SQLERRMC: null
at com.ibm.db2.jcc.c.kh.c(kh.java:1660)
at com.ibm.db2.jcc.c.kh.a(kh.java:1224)
at com.ibm.db2.jcc.b.db.n(db.java:737)
at com.ibm.db2.jcc.b.db.i(db.java:257)
at com.ibm.db2.jcc.b.db.c(db.java:53)
at com.ibm.db2.jcc.b.t.c(t.java:46)
at com.ibm.db2.jcc.b.sb.g(sb.java:154)
at com.ibm.db2.jcc.c.kh.o(kh.java:1219)
at com.ibm.db2.jcc.c.lh.d(lh.java:2436)
at com.ibm.db2.jcc.c.lh.d(lh.java:2507)
at com.ibm.db2.jcc.c.lh.S(lh.java:432)
at com.ibm.db2.jcc.c.lh.executeQuery(lh.java:415)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
at org.hibernate.loader.Loader.doQuery(Loader.java:697)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2228)
... 103 more


Name and version of the database you are using:
DB2 8.1.6

The generated SQL (show_sql=true):
Hibernate: select this_.AACM_RESPONSE_CODE as AACM1_4_0_, this_.AACM_ANSWER_CODE as AACM2_4_0_, this_.AACM_DESCRIPTION as AACM3_4_0_ from AVS_ANSWER_CODE_MAP this_ where this_.AACM_RESPONSE_CODE=?

Problem description
I have a DB2 database table which contains a primary key column defined as VARCHAR(2). When I try to run an hibernate query using a string larger than two I get the exception found above.

I've tested using hsqldb and it works correctly and returns an empty result.

I've also tested using hql and criteria and always get the exception when using DB2.

Code:
  final Criteria crit = getSession().createCriteria(AvsAnswerCode.class);
  crit.add(Restrictions.eq("iResponseCode", "123"));
  return (AvsAnswerCode) crit.uniqueResult();


If I alter the column size from two to three then the criteria above works correctly and returns null.

This seems like a bug to me, but may be this is by design? Shouldn't I be able to perform an hibernate query with a string whose length is greater than the size of the db column and simply receive an empty result set back?

If this seems like a bug, let me know and I'll create the minimum code required to reproduce it.

/Scott
Top
 Profile  
 
nordborg
 Post subject:
PostPosted: Tue Jan 20, 2009 10:41 am 
Expert
Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden
Seems like it is your database that doesn't like this. I don't think Hibernate has any problem with it.
Top
 Profile  
 
scott69taylor
 Post subject:
PostPosted: Tue Jan 20, 2009 10:55 am 
Newbie

Joined: Sun May 23, 2004 2:03 pm
Posts: 7
nordborg wrote:
Seems like it is your database that doesn't like this. I don't think Hibernate has any problem with it.


That was my first though as well, so I executed the following sql using DbVisualizer configured with the same DB2 jdbc drivers:

Code:
select * from cpg.avs_answer_code_map where aacm_response_code = '123';

15:45:12  [SELECT - 0 row(s), 0.000 secs]  Empty result set fetched
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.188/0.000 sec [0 successful, 1 warnings, 0 errors]


It seems strange that another JDBC based application can query with a string greater than the column size but not hibernate. Right now my leading theory is it has something to do with the sql statement Hibernate generates and passes onto DB2.

/Scott
Top
 Profile  
 
nordborg
 Post subject:
PostPosted: Tue Jan 20, 2009 1:56 pm 
Expert
Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden
The major difference is that the query that works has the value '123' hardcoded into it, but Hibernate uses a parametrized query. I have no experience with DB2 but after quick search on the internet I found that the error code -302 means (from http://theamericanprogrammer.com/progra ... odes.shtml):

Quote:
THE VALUE OF INPUT VARIABLE OR PARAMETER NUMBER IS INVALID OR TOO LARGE FOR THE TARGET COLUMN OR THE TARGET VALUE


Try using a Criteria.sqlRestriction() and see if the error goes away. Eg.

Code:
crit.add(Restrictions.sqlRestriction("{alias}.iResponseCode = '123'"));


Top
 Profile  
 
scott69taylor
 Post subject:
PostPosted: Wed Jan 21, 2009 5:16 am 
Newbie

Joined: Sun May 23, 2004 2:03 pm
Posts: 7
nordborg wrote:
Try using a Criteria.sqlRestriction() and see if the error goes away. Eg.

Code:
crit.add(Restrictions.sqlRestriction("{alias}.iResponseCode = '123'"));


That worked. So it looks like it has something to do with the parameterized query. I can probably use the following:

Code:
crit.add(Restrictions.sqlRestriction("{alias}.iResponseCode = '" + responseCode + "'"));


But that would have two draw backs:

- I need to handle a null responseCode separately
- Vulnerable to SQL injection.

Is there a better way around this?
Top
 Profile  
 
scott69taylor
 Post subject:
PostPosted: Wed Jan 21, 2009 6:01 am 
Newbie

Joined: Sun May 23, 2004 2:03 pm
Posts: 7
After a little thinking I 'll probably use the following code:

Code:
        try
        {
            final Criteria crit = getSession().createCriteria(AvsAnswerCode.class);
            crit.add(Restrictions.eq("iResponseCode", aResponseCode));
            return (AvsAnswerCode) crit.uniqueResult();
        }
        catch (DataException e)
        {
            /*
             * This is a work around for a DB2 problem (bug?) which results in a
             * SQL exception when querying using a responseCode with a length
             * greater than the corresponding DB column size.
             */
            if (e.getErrorCode() == -302)
            {
                return null;
            }
            throw e;
        }

It introduces DB2 specific code, but doesn't have the drawbacks that I believe sqlRestriction has.
Top
 Profile  
 
hueoogle
 Post subject: Re: DataException when querying with a string > than column s
PostPosted: Tue Sep 08, 2009 9:55 pm 
Newbie

Joined: Tue Sep 08, 2009 8:41 pm
Posts: 2
OMG Sott?

Still getting other people to write your Hibernate code? You would think after 8 years of trying to get it into production in the company you would figure out the skill set just wasn't there and move on, but o well, you fired me, so that speaks to how smart you were too....
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
  [ 7 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.