如果SQL:parameter在DB2JDBC中為“ALL”，如何匹配所有行？

這只是重現錯誤的示例代碼

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

上面的陳述句應該回傳第一行 if'A' 分配給PARAM引數，第二行 if'B'等等......否則，如果'ALL'分配給PARAM那么應該回傳所有行。

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

令人驚訝的是，它不起作用。

這是應用程式輸出：

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)

JDBC 跟蹤檔案在此處上傳

我將完整的示例專案代碼推送到https://github.com/noureldin-eg/db2-sql-error并在README 中添加了構建和運行它所需的所有步驟。您還可以在https://hub.docker.com/r/noureldin/db2-sql-error上找到預先構建的 docker 鏡像

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.

uj5u.com熱心網友回復：

我將其稱為“功能”，而不是“錯誤”。

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

COL2有VARCHAR(1)資料型別。因此，當您使用引數WHERE T.COL2 = :PARAM的資料型別和長度在編譯時是未知的，并且 Db2 嘗試從背景關系中派生它 - 它假定它的資料型別和長度等于T2.COL2= = VARCHAR(1)。但是您提供具有資料型別的引數值“ALL” VARCHAR(3)，并在 OPEN 呼叫中獲得 SQLCODE=-302。
對于:PARAM = 'ALL'. 如果您提供長度 <=3 的實際引數值，它會起作用，但否則會失敗（例如，在 'ALL1' 值上）。

解決方案是明確指定所有引數標記的資料型別和長度。例如，如果您要提供長度不超過 3 個位元組的值，則：

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

如果沒有，那么您應該使用以下內容，其中x是您要提供的引數值的最大長度：

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

標籤：sql jdbc db2 jcc

上一篇：如何獲取date_from和date_to之間的日期集

下一篇：無法讀取CSV資料（預計欄位數不同）