#467 Parameters are cut off in prepared statement

[Niclas Altenkirch]

Hi,

we noticed a possible problem within the JDBC implementation of JTOpen.
We have a database field(iso code) with a length of 2. If I execute the following statement as a prepared statement, I'll get a resultset with the size of 1. It should be empty, because "Viet Nam" doesn't match with "Vietnam". I tried this in ISeriesNavigator and also DataGrip and I got no results in there. It seems like the parameters of the prepared statement get cut off after 2 characters.
I tried this with JTOpen version 6.1 and 11.0.

SQLStatement.execute("SELECT * FROM **** WHERE UPPER(isocode) = ? OR UPPER(germanCountryName) = ? OR UPPER(englishCountryName) = ?",
                false, (rs)  -> log.info(rs.getString(1) + "|" + rs.getString(4) + "|" + rs.getString(5)), "VIET NAM", "VIET NAM", "VIET NAM");

-> this will return a resultset containing the "Virgin Islands", should be empty.

Here is a code snippet of the execute method:

try {
            if (returnGeneratedKeys) {
                preparedStatement = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            } else {
                preparedStatement = connection.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            }
            int i = 0;
            for (Object param : params) {
                preparedStatement.setObject(++i, param);
            }
            preparedStatement.execute();
            if (returnGeneratedKeys) {
                resultSet = preparedStatement.getGeneratedKeys();
            } else {
                resultSet = preparedStatement.getResultSet();
            }
            if (resultSet != null && processor != null)
                while (resultSet.next()) {
                    processor.process(resultSet);
                }
            return preparedStatement.getUpdateCount();

I tried debugging it the params are definitely "Viet Nam" and not "VI" in all three instances.

Any help would be appreciated.
Thanks in advance,

Niclas Altenkirch

[John Eberhard]

This is working as designed. Since the parameter is a CHAR(2) the driver is only able to send a CHAR(2) as the parameter. The parameter is truncated to Vi which then matches the value in the column.

To avoid this problem, you can use the "query replace truncated parameter" connection property, which does the following.

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 will be silently truncated to 'ABC' and a row will be returned by the query.

This property avoids 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.

---