db2_execute返回"Describe Param Failed"和"Binding Error"

Question

我正在使用PHP的db2_prepare和db2_execute运行以下查询(模式名称已更改以保护无辜者(：

    WITH U AS (
            SELECT *
            FROM FOO.USR
            WHERE USR_ID = ?
        ), UC AS (
            SELECT UC.*
            FROM FOO.USR_CNTRCT UC
                JOIN U ON U.USR_ID = UC.USR_ID
        ) , LC AS (
            SELECT DISTINCT CNTRCT_ID
            FROM FOO.CNTRCT_LOC CL  
                JOIN FOO.USR_LOC UL ON UL.SLS_CTR_CD = CL.SLS_CTR_CD
                JOIN U ON U.USR_ID = UL.USR_ID
            WHERE CL.SLS_CTR_CD IN (?,?,?,?)
        ) 
    SELECT C.*, COALESCE(P.PGM_NM, CAST('' AS CHAR(80) CCSID 37)) AS PGM_NM,
        COALESCE(ADT.ACTN_TM, TIMESTAMP('2000001', '00.00.00')) AS TIME_ORDER
    FROM U, FOO.CNTRCT AS C
        LEFT JOIN FOO.CNTRCT_PGM CP ON CP.CNTRCT_ID = C.CNTRCT_ID
        LEFT JOIN FOO.PGM P ON P.PGM_ID = CP.PGM_ID 
        LEFT JOIN UC ON UC.CNTRCT_ID = C.CNTRCT_ID
        LEFT JOIN (
            SELECT ENTY_ID AS CNTRCT_ID, MAX(ACTN_TM) AS ACTN_TM
            FROM FOO.ADT A  JOIN U ON U.USR_ID = A.USR_ID
            WHERE ENTY_TP = 'CT'
            GROUP BY ENTY_ID
        ) AS ADT ON ADT.CNTRCT_ID = C.CNTRCT_ID
    WHERE C.APP = ? 
            AND (
                ((SELECT COUNT(*) FROM UC) > 0 AND UC.CNTRCT_ID IS NOT NULL)
                OR
                ((SELECT COUNT(*) FROM UC) = 0 AND UC.CNTRCT_ID IS NULL)
            ) 
            AND ? BETWEEN YEAR(STRT_DT) AND YEAR(END_DT) 
            AND (LOWER(CNTRCT_NM) LIKE ?)
    ORDER BY CNTRCT_NM ASC

我已经确认我的参数在数量和顺序上都是正确的。当我执行此查询时，php 返回两个错误：描述参数失败和绑定错误。

我将问题范围缩小到这一行：CL.SLS_CTR_CD IN (?,?,?,?).如果我在这里使用实际值而不是参数，查询运行良好。

环境是 Zend Server for IBM i、PHP 5.3、DB2(在 i( V6R1 上。我从作业日志(QEZJOBLOG(中获得的唯一帮助是SQL0313(主机变量数量无效(。

同样，我已经确认我提供了正确数量的变量。描述参数失败的错误让我想知道 PHP 是否正在努力确定这四个参数的类型。它们应该是类型 string(2) ，并且传递给 db2_execute 的参数数组的var_dump确认它们已被强制转换为 。

我将切换到使用该特定行的值而不是参数，但这会让我发疯，直到我弄清楚发生了什么。

谢谢

乍得

Answer 1

我在db2_prepare方面遇到了一些问题，db2_execute在这个 PTFs 级别的复杂查询中绑定参数：

SF99601   15   DB2 FOR IBM i
SF99354    8   TCP/IP GROUP PTF
SF99115   14   IBM HTTP SERVER FOR i

升级到级别

20 DB2
11 TCP/IP
20 IBM HTTP

问题消失了，我又回到了一个令人厌恶的疯狂水平:)