-->
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
  [ 4 posts ] 
  Previous topic | Next topic 
Author Message
antonia
 Post subject: DataTruncation exception when using like and %
PostPosted: Wed Sep 06, 2006 11:15 am 
Newbie

Joined: Wed Aug 30, 2006 3:09 am
Posts: 7
Hibernate version:3.1.3

Name and version of the database you are using: Firebird 1.5

Hi,

I have a Criteria like this:
Code:

    criteria.add(Restrictions.like("fieldName",
      "%" + partialText + "%"));


When partialText has the maximum field length (or the maximum length - 1) I get a DataTruncationException. I think that hibernate is adding 2 to partialText length, one for every %.

Is it correct to query for %partialText% when partialText has the maximum field length - 1 or the maximum length? A workaround is to query for %partialText or partialText% when it reachs the maximum length - 1, and to query with eq when it reachs the maximum length, but it is quite tedious.

The exception I get is:

Code:
org.hibernate.type.NullableType nullSafeSet
INFO: could not bind value '%1122334455%' to parameter: 4; Data truncation
06-sep-2006 16:40:34 org.hibernate.util.JDBCExceptionReporter logExceptions
ADVERTENCIA: SQL Error: 0, SQLState: 01004
06-sep-2006 16:40:34 org.hibernate.util.JDBCExceptionReporter logExceptions

Caused by: 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:2148)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
        at org.hibernate.loader.Loader.list(Loader.java:2024)
        at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
        at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1533)
        at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
        at com.psleuropa.paco.models.common.handlers.decla.DeclaracionDECLAHandler.listDeclaracionFilterCriteria(DeclaracionDECLAHandler.java:473)
        at com.psleuropa.paco.models.common.handlers.decla.DeclaracionDECLAHandler.listDeclaraciones(DeclaracionDECLAHandler.java:277)
        ... 9 more
Caused by: java.sql.DataTruncation: Data truncation
        at org.firebirdsql.jdbc.field.FBWorkaroundStringField.setString(FBWorkaroundStringField.java:95)
        at org.firebirdsql.jdbc.AbstractPreparedStatement.setString(AbstractPreparedStatement.java:383)
        at org.hibernate.type.StringType.set(StringType.java:26)
        at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:83)
        at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:65)
        at org.hibernate.loader.Loader.bindPositionalParameters(Loader.java:1514)
        at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1576)
        at org.hibernate.loader.Loader.doQuery(Loader.java:661)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
        at org.hibernate.loader.Loader.doList(Loader.java:2145)
        ... 16 more
Top
 Profile  
 
Ananasi
 Post subject:
PostPosted: Wed Sep 06, 2006 11:25 am 
Expert
Expert

Joined: Tue Jul 11, 2006 10:21 am
Posts: 457
Location: Columbus, Ohio
As far as I know, you do not need to add the "%"'s to the second parameter in the like(), Hibernate wil do that for you. Use the MatchMode as a third parameter if you need more fine grained control over the pattern matching method.

So this will work for you:
Code:
criteria.add(Restrictions.like("fieldName", partialText, MatchMode.ANYWHERE));
Top
 Profile  
 
Ananasi
 Post subject:
PostPosted: Wed Sep 06, 2006 11:27 am 
Expert
Expert

Joined: Tue Jul 11, 2006 10:21 am
Posts: 457
Location: Columbus, Ohio
By the way, the odd looking data truncation error is probably because you are trying to bind a 12 character string (%1122334455%) to a parameterized statement where the referenced column is char(10).
Top
 Profile  
 
antonia
 Post subject:
PostPosted: Thu Sep 07, 2006 4:30 am 
Newbie

Joined: Wed Aug 30, 2006 3:09 am
Posts: 7
Hi,

You are right, I don't need to add %.

I'm building the criteria like you say, but I get the same exception.

Code:
  criteria.add(Restrictions.like("fieldName", partialText, MatchMode.ANYWHERE));


Like you say, my field is char(10), then if I use a partialText with the maximum length (10), Hibernate will add %. Now, the partialText has the maximum length + 2.

How can I do it?

I'm using Firebird, maybe is it a problem related to firebird itself (or the firebird dialect)?
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
  [ 4 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.