On Fri, 20 Jan 2012 10:36:05 +0100, Carsten Schäfer <ca_schaefer@...>
wrote:

> We are using PreparedStatement for Selects.
> We are getting DataTruncation Exceptions when using Strings in a Select
> that are longer than the defined varchar column.
> For example
> SELECT t_apos.ID_APOS From t_apos WHERE t_apos.f_best_nr = ?
> where f_best_nr is a varchar(30) column.
> When i use SELECT t_apos.ID_APOS from t_apos WHERE t_apos.f_best_nr =
> 'xxx';
> (without the ?) i can write strings with any length without getting an
> exception.
> So it seems to be a problem with the driver and not with the database.
> Is this an already known problem?

It is a problem with using parameterized queries in Firebird and more
specific the size reserved in the data structures in Firebird to accept the
parameter; if the parameter is longer then this reserved size, Firebird
will raise this error. It does not occur when you put the literal text in
the query itself as then Firebird processes the condition differently.

It is unfortunately not a problem that can be solved in Jaybird. The same
error can be reproduced using a parameterized EXECUTE STATEMENT in - for
example - ISQL.
See the following tickets:
http://tracker.firebirdsql.org/browse/JDBC-132 (closed because Jaybird
cannot work around this)
http://tracker.firebirdsql.org/browse/CORE-251
http://tracker.firebirdsql.org/browse/CORE-3559
(there are probably some other tickets as well describing this problem)

There are several workarounds you can do yourself if you know that the
length will be longer:
* Cast the parameter to an appropriate length:
eg SELECT t_apos.ID_APOS From t_apos WHERE t_apos.f_best_nr = CAST(? AS
VARCHAR(500))
* Cast the parameter to a BLOB SUB_TYPE 1
eg SELECT t_apos.ID_APOS From t_apos WHERE t_apos.f_best_nr = CAST(? AS
BLOB SUB_TYPE 1)
* Concatenate the literal in the query string (not advisable as it exposes
you to SQL injection etc)

Also please leave a comment on ticket CORE-251.

Mark