On 27-10-2013 14:29, Danny García Hernández wrote:

> We have some problems with sql expression width params, jaybird libs
> throw a DataTruncation exception when params's data data overflow the
> field sqllen restriction. Expression width "like" using params don't
> work fine. Example:
>
> create table Bancos (
> entidad varchar(4),
> titulo varchar(100)
> )
>
> select * from Bancos where enticed like ?
>
> Width param's value:
>
> 1: Work OK. (select * from Bancos where enticed like '%1%')
> 11: Work OK. (select * from Bancos where enticed like '%11%')
> 111: ERROR. Params value is '%111%', one more character than field
> definition length.
>
> We are using:
>
> Jaybird. 2.2.3
> Jdk 1.6
> Hib ernate: 2.2.4
>
> We use the Criteria Api to access our Backend, then for us is impossible
> change to use native sql without params.

Unfortunately this is a limitation of Firebird with how it handles
parameters. A parameter is described as the defined length of the column
it is compared with, which is (potentially) problematic with LIKE and
with SIMILAR TO. See http://tracker.firebirdsql.org/browse/CORE-3559 and
http://tracker.firebirdsql.org/browse/CORE-251

Potential workarounds are:
* Casting the field or the parameter to a larger width
* Making the database column wider then actually needed by the application

I am unsure if the first is an option in Hibernate (as far as I can tell
casting is not possible in the criteria API).

Mark
--
Mark Rotteveel