Subject PrepardStatement field truncation
Author J├╝rgen Metzdorf
Hi,

I think I've discovered a bug with PreparedStatement and the attempt to
insert content below the maximal field length. This seems to waste the
PreparedStatement.

Given the following table ...

CREATE TABLE TESTTAB
(
FIELD1 VARCHAR(10) NOT NULL PRIMARY KEY,
FIELD2 VARCHAR(30),
FIELD3 VARCHAR(20),
FIELD4 FLOAT,
FIELD5 CHAR
);

... and the following code:

conn.setAutoCommit( true );
PreparedStatement prep = conn.prepareStatement( "INSERT INTO TESTTAB (
FIELD1, FIELD3, FIELD4, FIELD5 ) VALUES ( ?, ?, ?, ? )");

for( int i = 0; i < 5; i++ ){
try{
if( i == 0 ){
prep.setObject( 1, "0123456789" );
prep.setObject( 2, "01234567890123456789");
prep.setObject( 3, "1259.9" );
prep.setObject( 4, "A" );
}
if( i == 1 ){
prep.setObject( 1, "0123456787" );
prep.setObject( 2, "012345678901234567890");
prep.setObject( 3, "0.9" );
prep.setObject( 4, "B" );
}
if( i == 2 ){
prep.setObject( 1, "0123456788" );
prep.setObject( 2, "Fld3-Rec3");
prep.setObject( 3, "0.9" );
prep.setObject( 4, "B" );
}
if( i == 3 ){
prep.setObject( 1, "0123456780" );
prep.setObject( 2, "Fld3-Rec4");
prep.setObject( 3, "1299.5" );
prep.setObject( 4, "Q" );
}
if( i == 4 ){
prep.setObject( 1, "0123456779" );
prep.setObject( 2, "Fld3-Rec5");
prep.setObject( 3, "1844" );
prep.setObject( 4, "Z" );
}
prep.execute();
}
catch( Exception x ){
x.printStackTrace();
}

What happens: The first Record is propperly inserted. The next insert fails
with exception

org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544794. operation was
cancelled

because FIELD3's max. length is exceeded.

All other inserts are surprisingly failing too: The next one (i==2) with
exception

org.firebirdsql.jdbc.FBSQLException: Resource Exception. Unable to complete
network request to host "".
Reason: Unable to complete network request to host "".

all others with

org.firebirdsql.jdbc.FBSQLException: Resource Exception. Local transaction
active: can't begin another

Seems to me, that the PreparedStatement is somehow unuseable from than on.
With a real life table and the same situation I witnessed messages in the
firbird.log :

linux (Server) Tue Feb 24 19:16:41 2004
SERVER/process_packet: don't understand packet type 4

System: Linux 2.4.29 (SuSE 9.0), Firebird 1.5 Release on localhost, JDK
1.4.1 and JayBird JCA/JDBC Driver for Firebird Version 1.5 Beta4 JDK 1.4.

So far for today

regards

Juergen Metzdorf