db2_execute返回“描述参数失败”和“绑定错误” [英] db2_execute returns "Describe Param Failed" and "Binding Error"

问题描述

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

  $ U $（
 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？年龄（STRT_DT）和年（END_DT）
 AND（LOWER（CNTRCT_NM）LIKE？）
 ORDER BY CNTRCT_NM ASC 
  

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

将问题缩小到这一行： CL.SLS_CTR_CD IN（？，？，？，？）。如果我使用实际值而不是参数，则查询运行正常。

环境是IBM i，PHP 5.3，DB2（在i）V6R1上的Zend Server。从工作日志（QEZJOBLOG）获得的唯一帮助是SQL0313（主机变量的数量无效）。

同样，我已经确认我提供正确数量的变量。 描述参数失败错误让我想知道PHP是否正在努力确定这四个参数的类型。它们应该键入 string（2），传递给db2_execute的参数数组的var_dump证实它们已被转换。

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

谢谢

乍得

解决方案

我在db2_prepare和db2_execute中遇到了一些问题，在具有此PTF级别的复杂查询中绑定参数：

  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 
  

问题消失，我恢复了一个容许的疯狂级别：）

I'm running the following query using PHP's db2_prepare and db2_execute (schema names have been changed to protect the innocent):

    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

I've confirmed that my parameters are correct in number and in order. When I execute this query, php returns two errors: Describe Param Failed and Binding Error.

I've narrowed the problem down to this line: CL.SLS_CTR_CD IN (?,?,?,?). If I use actual values here instead of parameters, the query runs fine.

The environment is Zend Server for IBM i, PHP 5.3, DB2 (on i) V6R1. The only help I get from the job log (QEZJOBLOG) is an SQL0313 (Number of host variables not valid).

Again, I've confirmed that I'm providing the correct number of variables. The Describe Param Failed error makes me wonder if PHP is struggling to determine the type of those four parameters. They should be type string(2), and a var_dump of the parameter array passed to db2_execute confirms that they have been cast as such.

I'm going to switch to using the values instead of parameters for that specific line, but it's going to drive me crazy until I figure out what's going on.

Thanks

Chad

解决方案

I had a few issues with db2_prepare and db2_execute whith bind params in complex queries with this PTFs level:

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

Upgrading to levels

20 DB2
11 TCP/IP
20 IBM HTTP

the issues disappear and I was return to a permisive crazy level :)

这篇关于db2_execute返回“描述参数失败”和“绑定错误”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！