Subject RE: [firebird-support] Re: Incompatible column/host variable data type.
Author Svein Erling Tysvær
> if I remove the "AND (:NOMBRE <> '')" parts the query runs without problem.
> As this simple query is for cheking if a customer already exist in the
> database, I don't want to evaluate the blank fields, thats why I added the
> forementioned part.

Huh? I'd say your query does the opposite:

((NOMBRE = :NOMBRE) AND (:NOMBRE <> '')) OR
((DIRECCION = :DIRECCION) AND (:DIRECCION <> '')) OR
((TELEFONOS = :TELEFONOS) AND (:TELEFONOS <> ''))

Suppose you have these records:

ID NOMBRE DIRECCION TELEFONOS
1 HELLO EAST
2 HELLO EAST YES

and you have the following parameters:

:NOMBRE='HI'
:DIRECCION='NORTH'
:TELEFONOS='YES'

then the row with ID 2 would be returned. If you changed the last parameter so that it was an empty string ('') then no row would be returned. Basically, your query is identical to checking the field name rather than the parameter, i.e.

((NOMBRE = :NOMBRE) AND (NOMBRE <> '')) OR
((DIRECCION = :DIRECCION) AND (DIRECCION <> '')) OR
((TELEFONOS = :TELEFONOS) AND (TELEFONOS <> ''))

If the query you really wanted was the opposite:

((NOMBRE = :NOMBRE) OR (:NOMBRE <> '')) AND
((DIRECCION = :DIRECCION) OR (:DIRECCION <> '')) AND
((TELEFONOS = :TELEFONOS) OR (:TELEFONOS <> ''))

so that

:NOMBRE='HI'
:DIRECCION='NORTH'
:TELEFONOS='YES'

didn't return any rows, whereas

:NOMBRE='HELLO'
:DIRECCION='EAST'
:TELEFONOS=''

would return both, then you can change your query to using coalesce and supply <NULL> as a parameter rather than an empty string:

SELECT
ID,
NOMBRE,
DIRECCION,
TELEFONOS
FROM
CLIENTES_ASEGURADOS
WHERE ID <> :ID
AND ID_EMPRESAS = :ID_EMPRESAS
AND NOMBRE = coalesce(:NOMBRE, NOMBRE)
AND DIRECCION = coalesce(:DIRECCION, DIRECCION)
AND TELEFONOS = coalesce(:TELEFONOS, TELEFONOS)

HTH,
Set