#307 PreparedStatement return unexpect result fixed length field

[Chun Ko]

I find that the PreparedStatement is acting incorrect on a fixed length field.

let say I have a table like this

SCHEMA ON SLP05 Table

CONO05 CHAR(2)
CUSN05 CHAR(8)
DSEQ05 CHAR(3)

and having data like

AA, C1234567, 000
AA, B1234567, 000

if I use normal SQL as "SELECT * FROM SLP05 WHERE CONO05 = '77' and CUSN05 = 'C12345678' and DSEQ='000'"
this SQL will return nothing to me as expect, because I have CUSN05 value incorrect with 9 CHARs

However, if I use PreparedStatement as "SELECT * FROM SLP05 WHERE CONO05 = ? and CUSN05 = ? and DSEQ=?"
with statement.setString(1, "77"), statement.setString(2, "C12345678") and statement.setString(3, "000")
it UNEXPECTLY return 1 row with CUSN05 = C1234567

I have tried this situation on other database, such as Derby, it does not return any result as expected.

Please let me know if it can be fixed, because it impacts on other ORM technology, such as Hibernate.

ON PLATFORM:
CONNECT TO ISERISE V5R3
CODE RUNNING ON WINDOWS XP
JTOPEN 6.5.1 (tested on 6.2 too)

CODE:

public void testSql() throws SQLException {
String lvAccountID = "C5AAINAD1";
Connection lvConnection = dataSource.getConnection();

PreparedStatement lvStatement = lvConnection.prepareStatement("select * from slp05 where cono05 = ? and cusn05 = ? and dseq05 = ?");

lvStatement.setString(1, "772");
lvStatement.setString(2, lvAccountID);
lvStatement.setString(3, "200");

ResultSet lvResultSet = lvStatement.executeQuery();

while (lvResultSet.next()) {
System.out.println("cusn" + lvResultSet.getString("CUSN05"));
}
System.out.println(lvAccountID);
}

With Best Regards,

Chun

[Paul D]

Yes, I see what you are describing... In this case, we are following the JDBC specification for the PreparedStatement. Unfortunately, with the non-prepared Statement, the sql is sent directly to the DB without any checking by JDBC, and so the its behavior is a bit different.

****************
8.3 DataTruncation
The DataTruncation class, a sub-class of SQLWarning, provides information
when data is truncated. When data truncation occurs on a write to the data source, a
DataTruncation object is thrown. The data value that has been truncated may
have been written to the data source even if a warning has been generated. When
data truncation occurs on a read from the data source, a SQLWarning is reported.
A DataTruncation object contains the following information:
■ the descriptive String "Data truncation"
■ the SQLState "01004" when data truncation occurs on a read from the data source
■ the SQLState "22001" when data truncation occurs on a write to the data source

****************
Since you are doing a select, that is considered a read from the db, so a DataTruncation SQLWarning is issued.

The solution to this is to always check for warnings after setting data that may be too long:

ps.setString(1,"1234567890123");
SQLWarning w = ps.getWarnings();
System.out.println(w);
System.out.println(w.getSQLState());

This behavior is also the same on our Native IBM i JDBC driver.

[Chun Ko]

So that, is that mean the SQL Statement should behave the same as PreparedStatement? meaning that the query parameter value would be trim and return a result to the client?

[Paul D]

It would be nice if they had the same behavior, but they are different by design. So they are both working as designed.

[Nobody/Anonymous]

Many thanks for your reply and I have been going through those specifications and other driver implementations. The implementation in the JTOpen, I am totally agree this implementation is completely fulfilled the JDBC Specification.

Now what I am hoping that is it possible to have a property setting in JTOpen that to ignore this specification, which allows parameter value not to be truncated automatically and return no value to the result set?

The reason behind this is because:
1. As "Client" point of view, "C12345678" is definitely not equal to "C1234567", the reason they are equal because it is truncated from "C12345678" to "C1234567"
2. By doing this property, could have reduce any client code to do SQLWarning lookup every time dealing with PreparedStatement, as well as the performance issue on checking.

Again Many Thanks for your help.

[Paul D]

I will discuss your request for a property with the team. I can't guarantee 100% that we will add it, but we will at least discuss the possibility of adding it.

As I think about this, I think this proposed property would cause any data that is too long to not be truncated in JDBC. The data would then be send as-is to the DBMS and allow the DBMS to issue any errors or not...

[Paul D]

In doing some tests, I found that this proposed property would also force other non-JDBC code to be updated. This is because the code path on the DBMS for prepared statement parms is different from the code for a statement with embedded parms. Currently the DBMS will throw back an error similar to "truncation" if the parm is too long.

I will discuss this with the team still, but if we are able to do this, it would not be really soon.

[Chun Ko]

Highly appreciate the consideration adding this as a future feature, because as I read more bug request from Hibernate Group, users from Oracle space also has similar situation.

Just to say thank again.

[John Eberhard]

How does the following proposal seem? The driver would add the property "query replace truncated parameter" which defines the data to be used for a truncated parameter on a query. This should be set to a value that should never appear. If this property is set to XXXXXXXX, when the long parameter, like 1234567890123 is set and truncation occurs and the statement is a query, the parameter would be set to XXXXXXXX, instead. Thus the query would run and produce no results, since XXXXXXXX should not be seen in the column.

[Brendan]

This solution would be great

[John Eberhard]

The query replace truncated parameter has been added to JTOpen.

"query replace truncated parameter" Specifies the value that should be used in place of a truncated parameter of an SQL query. By default, the parameter is silently truncated to the length for the parameter. Consider the following scenario.

Table T1 has a CHAR(3) column with the name of C1, and a row where C1='ABC'.
An application prepares a statement using SELECT * FROM TABLE_X where C1=?
If the parameter is set to 'ABCD', it wil be silently truncated to 'ABC' and a row will be returned by the query.

This property avoid this problem by allowing the application to set the string to something that doesn't exist in the application - i.e. @@@@@@@. A blank value means that the property will be ignored