How to match all rows if SQL :parameter is 'ALL' in DB2 JDBC?

This is just a sample code to reproduce the error

SELECT *
FROM ( VALUES (10,'A'),(20,'B'),(30,'C'),(40,'D') ) AS T(COL1,COL2)
WHERE T.COL2 = :PARAM OR :PARAM = 'ALL'

The above statement should return the first row if 'A' is assigned to PARAM parameter, second row if 'B', etc... Otherwise, if 'ALL' is assigned to PARAM then all rows should be returned.

String PARAM = "ALL";

// SQL = SQL.replaceAll(":PARAM", "'" + PARAM + "'"); // Uncomment me

try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
        DB2PreparedStatement statement = (DB2PreparedStatement) connection.prepareStatement(SQL)) {

    print(connection.getMetaData());

    statement.setJccStringAtName("PARAM", PARAM); // Comment me
    try (ResultSet resultSet = statement.executeQuery()) {
        print(resultSet);
    }
} catch (SQLException exception) {
    print(exception);
}

Surprisingly, it does not work.

Here is the application output:

Database Product Name: DB2/LINUXX8664
Database Product Version: SQL110551
Database Version: 11.5
Driver Name: IBM Data Server Driver for JDBC and SQLJ
Driver Version: 4.29.24
JDBC Version: 4.1
SQLException information:
Error msg: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null, DRIVER=4.29.24
SQLSTATE: 22001
Error code: -302
com.ibm.db2.jcc.am.SqlDataException: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null, DRIVER=4.29.24
    at com.ibm.db2.jcc.am.b7.a(b7.java:802)
    at com.ibm.db2.jcc.am.b7.a(b7.java:66)
    at com.ibm.db2.jcc.am.b7.a(b7.java:140)
    at com.ibm.db2.jcc.am.k9.c(k9.java:2844)
    at com.ibm.db2.jcc.am.k9.a(k9.java:2281)
    at com.ibm.db2.jcc.t4.ab.r(ab.java:1670)
    at com.ibm.db2.jcc.t4.ab.l(ab.java:754)
    at com.ibm.db2.jcc.t4.ab.d(ab.java:110)
    at com.ibm.db2.jcc.t4.p.c(p.java:44)
    at com.ibm.db2.jcc.t4.av.j(av.java:162)
    at com.ibm.db2.jcc.am.k9.an(k9.java:2276)
    at com.ibm.db2.jcc.am.k_.a(k_.java:4699)
    at com.ibm.db2.jcc.am.k_.b(k_.java:4215)
    at com.ibm.db2.jcc.am.k_.a(k_.java:4860)
    at com.ibm.db2.jcc.am.k_.b(k_.java:4215)
    at com.ibm.db2.jcc.am.k_.bd(k_.java:785)
    at com.ibm.db2.jcc.am.k_.executeQuery(k_.java:750)
    at com.ibm.db2.jcc.am.d0.executeQuery(d0.java:297)
    at com.example.App.main(App.java:38)

The JDBC trace file is uploaded here

I pushed the full sample project code to https://github.com/noureldin-eg/db2-sql-error and added all required steps to build and run it in the README. You can also find a pre-built docker image on https://hub.docker.com/r/noureldin/db2-sql-error

I know there are many workarounds (for example it works as expected if the parameter is substituted in java as shown in the comments) but I want to understand what I am missing here.

---

Update on 2021-10-22 1:30 PM (UTC)

I found that the ParameterMetaData API is very useful in debugging this issue.

int parameterCount = parameterMetaData.getParameterCount();
System.out.println("Number of statement parameters: " + parameterCount);
for (int i = 1; i <= parameterCount; i++) {
    String sqlType = parameterMetaData.getParameterTypeName(i);
    int precision = parameterMetaData.getPrecision(i);
    System.out.printf("SQL type of parameter %d is %s(%d)%n", i, sqlType, precision);
}

The above code shows that my named parameter is converted behind the scenes to 2 question marks (?) in parameter marker style. I have already noticed that from the trace file but now it is clear that each one has its own type and length.

Number of statement parameters: 2
SQL type of parameter 1 is VARCHAR(1)
SQL type of parameter 2 is VARCHAR(3)

And this is why I get SqlDataException if my parameter's length of characters exceeds any of them. I hope this may help anyone facing similar error.

I'd call it as a "feature", not as a "bug".

CALL ADMIN_CMD ('DESCRIBE SELECT * FROM ( VALUES (10,''A''),(20,''B''),(30,''C''),(40,''D'') ) AS T(COL1,COL2)');

COL2 has VARCHAR(1) data type. So, when you use WHERE T.COL2 = :PARAM the data type and length of the parameter is not known at the compilation time, and Db2 tries to derive it from the context - it assumes, that its data type and length is equal to the data type and length of the T2.COL2 == VARCHAR(1). But you provide the parameter value 'ALL' having data type VARCHAR(3), and get SQLCODE=-302 on the OPEN call.
The same is for :PARAM = 'ALL'. It works if you provide an actual parameter value of length <=3, but fails otherwise (say, on 'ALL1' value).

The solution is to explicitly specify the data type and length of all parameter markers. For example, if you are going to provide the value of not more than 3 bytes in length, then:

WHERE T.COL2 = CAST (:PARAM AS VARCHAR(3)) OR :PARAM = 'ALL'

If not, then you should use the following, where x is a maximum length of the parameter value you are going to provide:

WHERE T.COL2 = CAST (:PARAM AS VARCHAR(x)) OR CAST (:PARAM AS VARCHAR(x)) = 'ALL'