org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error SQL error code = -303 arithmetic exception, numeric overflow, or string truncation string right truncation - problem with like SQL [JDBC355]

[firebird-automations]

Submitted by: Łukasz Bączek (baczek00)

Hello Mark,

Sallis has written a small test in Java showing the problem:

Below you have the entire structure of the table:
__._,_.___

/******************************************************************************/
/* Generated by IBExpert 2013.10.8.1 2014-05-16 00:36:35 */
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES UTF8;

CREATE DATABASE ':C:\TEST.FDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 16384
DEFAULT CHARACTER SET UTF8 COLLATION UNICODE_CI_AI;

/******************************************************************************/
/* Domains */
/******************************************************************************/

CREATE DOMAIN CHECKBOX AS
SMALLINT
DEFAULT 0
CHECK (value is null or value in (0, 1));

/******************************************************************************/
/* Generators */
/******************************************************************************/

CREATE GENERATOR GEN_OSOBY_ID;
SET GENERATOR GEN_OSOBY_ID TO 138;

/******************************************************************************/
/* Tables */
/******************************************************************************/

CREATE TABLE OSOBY (
ID_OSOBY BIGINT NOT NULL,
IMIE VARCHAR(40) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE_CI_AI,
NAZWISKO VARCHAR(120) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE_CI_AI,
ADRES VARCHAR(150) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI,
KOD VARCHAR(6) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI,
MIASTO VARCHAR(30) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI,
TELEFON VARCHAR(12) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI,
EMAIL VARCHAR(60) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI,
DYSTRYBUTOR CHECKBOX,
EKSPERT CHECKBOX,
PRZEDSTAWICIEL CHECKBOX,
WLASCICIEL CHECKBOX,
UWAGI VARCHAR(500) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI
);

INSERT INTO OSOBY (ID_OSOBY, IMIE, NAZWISKO, ADRES, KOD, MIASTO, TELEFON, EMAIL, DYSTRYBUTOR, EKSPERT, PRZEDSTAWICIEL, WLASCICIEL, UWAGI) VALUES (137, 'TESTA', 'TESTb', 'xxx', '00-000', 'TestC', '1234567', 'DEMO@DEMO.DEMO', 0, 0, 0, 0, NULL);
INSERT INTO OSOBY (ID_OSOBY, IMIE, NAZWISKO, ADRES, KOD, MIASTO, TELEFON, EMAIL, DYSTRYBUTOR, EKSPERT, PRZEDSTAWICIEL, WLASCICIEL, UWAGI) VALUES (138, 'Jan', 'Kowalski', 'Aniołów 5', '83-100', 'Warszawa', '0123456', 'jan.kowalski@demo.demo', 0, 0, 0, 0, NULL);

COMMIT WORK;

/******************************************************************************/
/* Primary Keys */
/******************************************************************************/

ALTER TABLE OSOBY ADD CONSTRAINT PK_OSOBY PRIMARY KEY (ID_OSOBY);

/******************************************************************************/
/* Triggers */
/******************************************************************************/

SET TERM ^ ;

/******************************************************************************/
/* Triggers for tables */
/******************************************************************************/

/* Trigger: OSOBY_BI */
CREATE TRIGGER OSOBY_BI FOR OSOBY
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new.id_osoby is null) then
new.id_osoby = gen_id(gen_osoby_id,1);
end
^

SET TERM ; ^

And so reads the data:

*/
public static void main(String[] args) {
try {
FBWrappingDataSource pool = new FBWrappingDataSource();
pool.setMaxPoolSize(5);
pool.setMinPoolSize(2);
pool.setMaxStatements(2);
pool.setMaxIdleTime(30 * 60 * 60);
pool.setDatabase("<//127.0.0.1/C:/TEST.FDB>");
pool.setUserName("SYSDBA");
pool.setPassword("masterkey");
pool.setCharSet("UTF8");
pool.setEncoding("UTF8");
Connection connection = pool.getConnection();
try {
ResultSet result = null;
StringBuilder SBSQL = new StringBuilder();
SBSQL.append("SELECT * FROM (SELECT id_osoby, imie, nazwisko, adres, kod, miasto, telefon ,email, COALESCE(przedstawiciel,0) AS przedstawiciel, COALESCE(wlasciciel,0) AS wlasciciel, COALESCE(ekspert,0) AS ekspert, COALESCE(dystrybutor,0) AS dystrybutor, uwagi FROM osoby ) WHERE imie LIKE ? OR nazwisko LIKE ? OR adres LIKE ? OR kod LIKE ? OR miasto LIKE ? OR telefon LIKE ?");
PreparedStatement ps = connection.prepareStatement(SBSQL.toString());
ps.setString(1, "%TestA%");
ps.setString(2, "%TestA%");
ps.setString(3, "%TestA%");
ps.setString(4, "%TestA%");
ps.setString(5, "%TestA%");
ps.setString(6, "%TestA%");
result = ps.executeQuery();
while (result.next()) {
System.out.println(result.getInt("IMIE"));
}
} finally {
connection.close();
}
} catch (SQLException ex) {
Logger.getLogger(TestConnectionPool.class.getName()).log(Level.SEVERE, null, ex);
}

\}

SQL query which I used here works without a problem in IBExpert. I think that the problem lies in the driver Javabird and not as previously thought on the length of the field.

maj 16, 2014 12:51:02 AM testconnectionpool.TestConnectionPool main
SEVERE: null
org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -303
arithmetic exception, numeric overflow, or string truncation
string right truncation
at org.firebirdsql.jdbc.AbstractPreparedStatement.internalExecute(AbstractPreparedStatement.java:801)
at org.firebirdsql.jdbc.AbstractPreparedStatement.executeQuery(AbstractPreparedStatement.java:182)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.firebirdsql.pool.PooledPreparedStatementHandler.invoke(PooledPreparedStatementHandler.java:171)
at org.firebirdsql.pool.$Proxy1.executeQuery(Unknown Source)
at testconnectionpool.TestConnectionPool.main(TestConnectionPool.java:49)
Caused by: org.firebirdsql.gds.GDSException: Dynamic SQL Error
SQL error code = -303
arithmetic exception, numeric overflow, or string truncation
string right truncation
at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.readStatusVector(AbstractJavaGDSImpl.java:2092)
at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.receiveResponse(AbstractJavaGDSImpl.java:2042)
at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.iscDsqlExecute2(AbstractJavaGDSImpl.java:1149)
at org.firebirdsql.gds.impl.GDSHelper.executeStatement(GDSHelper.java:227)
at org.firebirdsql.jdbc.AbstractPreparedStatement.internalExecute(AbstractPreparedStatement.java:797)
... 8 more

My Firebird is: WI-6.3.0.26074 Firebird 2.5
My Javabird are: Jaybird-2.2.5-JDK_1.7

Regards

[firebird-automations]

Commented by: @mrotteveel

The problem is the column KOD which is defined as VARCHAR(6). In Firebird columns are described as their declared length, but the parameter "%TestA%" is 7 characters long, which is too long for the parameter field. It works in a literal query, because then the length of the literal itself is used.

See CORE251 and CORE3559.

This is unfortunately not something we can fix in Jaybird. The only workaround is to add a cast to the parameter to have it described as a longer parameter (eg KOD LIKE CAST(? AS VARCHAR(10)).

However I would have expected Jaybird to throw an exception when the value was set and not on execute, so I will investigate what is happening.

PS: Did you have a specific reason for setting the security level to Developers, or is it ok if I set it so the ticket is visible to all?

[firebird-automations]

Commented by: @mrotteveel

Looking closely at the code, it is sent to the server because it is UTF-8, is it is related to JDBC354.

[firebird-automations]

Commented by: Łukasz Bączek (baczek00)

OK , but it means that Javabird take different sized boxes as IBExpert . IBExpert in the case of UTF -8 is multiplied by 4 multiplicity of fields , hence it distinguishes between the action and it can be confusing for the programmer

More information about this is here:
http://ibexpert.net/ibe/index.php?n=Doc.ConvertYourFirebirdApplicationsToUnicode

I have no special reason I set this flag because I thought that it will be fine. This is my first application bug . How to fix it would be ok ? ( How to set it as a ticket)

[firebird-automations]

Modified by: Łukasz Bączek (baczek00)

security: Developers [ 10012 ] =>

[firebird-automations]

Commented by: Łukasz Bączek (baczek00)

Hello,

I did another test and when SQL does not have a parameter that is all good ..

public static void main(String[] args) {
try {
FBWrappingDataSource pool = new FBWrappingDataSource();
pool.setMaxPoolSize(5);
pool.setMinPoolSize(2);
pool.setMaxStatements(2);
pool.setMaxIdleTime(30 * 60 * 60);
pool.setDatabase("<//127.0.0.1/C:/TEST.FDB>");
pool.setUserName("SYSDBA");
pool.setPassword("masterkey");
pool.setCharSet("UTF8");
pool.setEncoding("UTF8");
pool.setSqlDialect("3");
Connection connection = pool.getConnection();
try {
ResultSet result = null;
StringBuilder SBSQL = new StringBuilder();
SBSQL.append("SELECT * FROM (SELECT id_osoby, imie, nazwisko, adres, kod, miasto, telefon ,email, COALESCE(przedstawiciel,0) AS przedstawiciel, COALESCE(wlasciciel,0) AS wlasciciel, COALESCE(ekspert,0) AS ekspert, COALESCE(dystrybutor,0) AS dystrybutor, uwagi FROM osoby ) WHERE imie LIKE '%TestA%' OR nazwisko LIKE '%TestA%' OR adres LIKE '%TestA%' OR kod LIKE '%TestA%' OR miasto LIKE '%TestA%' OR telefon LIKE '%TestA%' ");
Statement s = connection.createStatement();
result = s.executeQuery(SBSQL.toString());
while (result.next()) {
System.out.println(result.getString("IMIE"));
}
} finally {
connection.close();
}
} catch (SQLException ex) {
Logger.getLogger(TestConnectionPool.class.getName()).log(Level.SEVERE, null, ex);
}
}
As you can see if there are parameters in the SQL query works.

There is a problem with the operation parameters in Javabird. Please Mark if you can fix it quickly.

[firebird-automations]

Commented by: @mrotteveel

This is a limitation with parameters in Firebird that cannot be fixed in Jaybird. Parameters have the maximum length of the field itself. Longer texts are not accepted. So as KOD is VARCHAR(6) and %TestA% is 7 characters it fails. It works if in a query with literal instead of parameters, because then Firebird allocates enough space to accommodate the literal.

The only problem in Jaybird is JDBC354 where it doesn't take into account the bytes per character leading it to send the value to Firebird instead of throwing a DataTruncation exception immediately.

I don't understand what you mean with "IBExpert in the case of UTF -8 is multiplied by 4 multiplicity of fields". This is not really true, except that the storage (and protocol) requirements of a UTF-8 field is 4 bytes per character, but it is still only 1 character, not 4 (unless you use connection character set NONE, then things get complicated).

[firebird-automations]

Commented by: Łukasz Bączek (baczek00)

Writing about "IBExpert in the case of UTF -8 is multiplied by 4 multiplicity of fields" I mean this sentence: "CREATE TABLE TEST5 (TXT VARCHAR (32000))
is not a problem, even though it's not necessarily a good idea is to define your VARCHAR (32000) That big. April but you have a problem in the Unicode database, because Firebird That has to consider the information That is stored here is could be, for example, and complete lists of Chinese characters. As every single character needs up to 4 bytes, it must have the room to store up to 32000 * 4 So the actual maximum length VARCHAR with UTF8 is 8191 and not 32000. "Quote comes from this site: http://ibexpert.net/ibe/index.php?n=Doc.ConvertYourFirebirdApplicationsToUnicode

This SQL query:

select
b.rdb$field_name as field,
c.rdb$field_length as length,
a.rdb$relation_name as table_name
from rdb$relations a
inner join rdb$relation_fields b on a.rdb$relation_name = b.rdb$relation_name
inner join rdb$fields c on b.rdb$field_source = c.rdb$field_name
where a.rdb$system_flag = 0 and a.rdb$relation_name='OSOBY' and b.rdb$field_name='KOD'

This confirms what I mean (see for yourself) will come out as 24 bytes of information on the results in the case of UTF-8 VARCHAR field size is multiplied * 4 (6 * 4 = 24) which gives 24 bytes so "%TestA%)" having a size 7 bytes should be fit

As I mentioned a SQL query that I have given in the examples earlier works provided that no parameters.

[firebird-automations]

Commented by: @mrotteveel

Unfortunately it will not fit, because even though Firebird will need up to 24 bytes for a VARCHAR(6) CHARACTER SET UTF8, it allows only 6 characters. Send more characters (even if their total length is less or equal to 24 bytes and you get the exact exception specified).

As I posted on Firebird-support:
"My impression is that you think that as a VARCHAR(6) CHARACTER SET UTF8 takes up to 6 * 4 = 24 bytes, that you will be able to fit 24 ASCII characters in it: that isn't the case."

This is entirely clear in this case, because Jaybird actually does send those 7 bytes to the server due to a bug (the exception you receive is sent by Firebird and not by Jaybird).

[firebird-automations]

Commented by: Łukasz Bączek (baczek00)

"Warszawa is 8 characters, so would at minimum require a VARCHAR(8). And
as they are all in ASCII, with UTF-8 that is 8 bytes, however to store
UTF8 internally, Firebird has to reserve 4*8 = 32 bytes (because
Firebird has to assume the worst case byte requirement), however that
should be entirely transparent to you."

This description allowed me to understand. Thank you

[firebird-automations]

Modified by: @mrotteveel

status: Open [ 1 ] => Closed [ 6 ]

resolution: Won't Fix [ 2 ]