jt400 Trunc my where condition as like clause

Question

I'm using jt400-9.3.jar to connect DB2/AS400.

My table BAND with this records:

+-----|------------------+
| MAT |     NAME         |
+-----|------------------+
|100  | Paul McCartney   |
|101  | John Lennon      |
|102  | Ringo Starr      |
|103  | George Harrison  |

And my table MUSICIAN with:

+------|------------------+
|MAT   |  NAME            |
+------|------------------+
|1001  | Pete Best        |
|1002  | Stuart Sutcliffe |
|1003  | Jimmy Nicol      |
|1004  | Tommy Moore      |
|1005  | Norman Chapman   |

When I run this select

SELECT t.mt, t.name
FROM (      
    SELECT 
        trim(b.mat) AS mat, 
        trim(b.name) AS name
    FROM band b
    WHERE trim(b.mat) = '1001'
    UNION 
    SELECT 
        trim(m.mat) AS mat, 
        trim(m.name) AS name
    FROM MUSICIAN m
    WHERE trim(m.mat) = '1001'
) AS t
FETCH FIRST 1 ROWS ONLY

I retrieve:

+-----|----------------+
|MAT  |NAME            |
+-----|----------------+
|100  | Paul McCartney |

The first query is working as like clause. The 1001 is matching with 100, but the sentence is = no like.

When I execute the query in dbeaver works, but in java (using PreparedStatement) I got the wrong, there are some configuration to the driver?

Answer 1

John Eberhard answer so fast about the problem for me.

https://sourceforge.net/p/jt400/bugs/121/

Registering your answer.

The problem is that = is being used in the query. Because = is used in the query, then the database tells the driver that the type is CHAR(5). The driver then truncates to char 5.

There are two possible solutions.

1. Add a cast to the parameter marker so that the larger character will fit and miscompare. i.e. SELECT * FROM MYTABLE WHERE MAT = CAST( ? AS VARCHAR(80))

2. Use the "query replace truncated parameter" JDBC property. See https://static.javadoc.io/net.sf.jt400/jt400/9.7/com/ibm/as400/access/doc-files/JDBCProperties.html

Here is how that property is defined.

"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 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.