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

Question

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.

If you think that this might be a bug, let me know where can I report it. — Mohamed M NourElDin, Oct 22 '21 at 01:28
Error [SQL0301N](https://www.ibm.com/docs/en/db2/11.5?topic=messages-sql0250-sql0499#sql0302n) in your case means probaly `ALL` is too long for `:PARAM`, db2 fails to detect `:PARAM` can be equal to `ALL` according your query, maybe try `WHERE :PARAM in ('ALL', T.COL2)` — nfgl, Oct 22 '21 at 08:49
Thank you @nfgl , I will try this and let you know if it works — Mohamed M NourElDin, Oct 22 '21 at 09:08
Hi @nfgl, I confirmed that your solution works and this would have been the first accepted answer (8 mins before Mark) if you didn't write it in a comment. However, I still won't use this approach as I found that I should not use parameter in the > [Left-hand side operand of an IN predicate](https://www.ibm.com/docs/en/db2/11.5?topic=design-parameters-markers) I don't understand how it works if this parameter usage is restricted so, I will use the `CAST` approach suggested by @Mark — Mohamed M NourElDin, Oct 22 '21 at 13:37

score 0 · Accepted Answer · answered Oct 22 '21 at 08:57

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)');

SQLTYPE_ID	SQLTYPE	SQLLENGTH	SQLSCALE	SQLNAME_DATA	SQLNAME_LENGTH	SQLDATATYPE_NAME_DATA	SQLDATATYPE_NAME_LENGTH
496	INTEGER	4	0	COL1	4		0
448	VARCHAR	1	0	COL2	4		0

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'

Thank you for this explanation. This makes a lot of sense now. I will try your suggestions and will accept your answer once I confirm that it works in my case. — Mohamed M NourElDin, Oct 22 '21 at 09:12

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

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

1 Answers1