Subject Re: [Firebird-Java] Jaybird problems using hibernate
Author Danny García Hernández
Post date 2013-10-27T21:29:17Z
Hi Mark, 

I change this code in FBWorkaroundStringField.java, if data is greater than sqllen but minor or equal than sqllen  + 2, and have a begining or ending '%', i consider the value as good.

public void setString(String value) throws SQLException {
        byte[] data = setStringForced(value);

        if (value == STRING_NULL_VALUE)
            return;
        
        if (data.length > field.sqllen && !isSystemTable(field.relname)) {
            // special handling for the LIKE ? queries with CHAR(1) fields
            if (!(value.length() <= field.sqllen + 2 && (value.charAt(0) == '%' || value.charAt(value.length() - 1) == '%'))) 
                throw new DataTruncation(-1, true, false, data.length, field.sqllen);
        }
 }

But this only disallow the exception, select don't return any result, maybe because server can´t read the correct value from parameters structure passed from jaybird.

My initial idea was extract % character from de data value if value length was greater than field legnth:

Field Length: varchar(4).
Search for: 11 (%11%). OK. (Containing)
Search for: 111 (%111%). ERROR. Extract  first % (111%). OK. (Starting)
Search for: 1111 (%1111%). ERROR. Extract first and last % (1111). OK. (Equal)

I know that search logic change as a function of the length of search string, but for us, is better than only search for two characters (is this case).

Thanks 
Danny









Documento sin título
DANNY GARCÍA HERNÁNDEZ
Director de Proyectos
 Parque Científico de la Universidad de Valencia
C/ Agustín Escardino,9
Sector B. Despachos 1.11
46980 Paterna - Valencia
E-mail:danny@...
Tel.: 963 634 317

www.imasdsolutions.com www.g2k.es
Rss Youtube Facebook Twitter Flicker Google+ Mail Web

Este mensaje y los archivos adjuntos son confidenciales. Los mismos contienen información reservada y que no puede ser difundida. Si usted ha recibido este correo por error, por favor avísenos inmediatamente vía correo electrónico (lopd@...) o por teléfono (96 363 43 17) y tenga la amabilidad de eliminarlo de su sistema; no deberá copiar el mensaje ni divulgar su contenido a ninguna persona. Muchas gracias.

Asimismo, le advertimos de que este mensaje o los archivos adjuntos podrían contener algún virus, y es su responsabilidad comprobar si los hay y eliminarlos.

This message and its attached files are confidential. They contain information that is privileged and legally exempt from disclosure. If you have received this e-mail by mistake, please let us know immediately by e-mail (lopd@...) or phone (34) 96 363 43 17 and delete it from your system; you should also not copy the message nor disclose its contents to anyone. Thanks.

Please, also note that this message or its attached files could possibly contain viruses and its your responsibility to check and delete them.
-------------------------------------------------------------------------------------------------------------------------------------------
De acuerdo con la nueva ley de Servicios de la Sociedad de la información y Comercio Electrónico aprobada por el parlamento español y de la vigente ley Orgánica 15 13/12/1999 de Protección de datos española, le comunicamos que su dirección de correo electrónico forma parte de un fichero automatizado, teniendo usted derecho de oposición, acceso, rectificación y cancelación de sus datos. Para ELIMINAR su dirección de correo electrónico de nuestra base de datos tiene que escribirnos a lopd@... indique en asunto CANCELACION DE DATOS, y nombre fiscal de la empresa. Gracias por su atención.

 


El 27/10/2013, a las 21:52, Mark Rotteveel <mark@...> escribió:

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


------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
   http://groups.yahoo.com/group/Firebird-Java/

<*> Your email settings:
   Individual Email | Traditional

<*> To change settings online go to:
   http://groups.yahoo.com/group/Firebird-Java/join
   (Yahoo! ID required)

<*> To change settings via email:
   Firebird-Java-digest@yahoogroups.com
   Firebird-Java-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
   Firebird-Java-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
   http://info.yahoo.com/legal/us/yahoo/utos/terms/