PDOException:SQLSTATE [IMSSP]:试图绑定参数号65536.SQLServer支持最多2100个参数 [英] PDOException: SQLSTATE[IMSSP]: Tried to bind parameter number 65536. SQL Server supports a maximum of 2100 parameters

问题描述

好吧，所以我遇到了一个非常奇怪的PDOException，似乎无法理解.这是生成的异常:

Ok so I've come across a really odd PDOException that I cannot seem to get my head around. Here is the exception generated:

PDOException: SQLSTATE[IMSSP]: Tried to bind parameter number 65536.  SQL Server supports a maximum of 2100 parameters. in D:\Work\CEUR16-004\Project\www_root\includes\scripts\php\libCore\products.php:169
Stack trace:
#0 D:\Work\CEUR16-004\Project\www_root\includes\scripts\php\libCore\products.php(169): PDOStatement->execute()
#1 D:\Work\CEUR16-004\Project\www_root\includes\pages\products\products.php(5): ProductCore->build_product_list()
#2 D:\Work\CEUR16-004\Project\www_root\index.php(27): include('D:\\Work\\CEUR16-...')
#3 {main}

这是它所引用的代码:

public function build_product_list()
    {
        // This function builds the product list visible on the main site (guests only)
        try
        {
            if($this->product_type_id == "999")
            {
                $query = 'SELECT sp.product_id, sp.product_name, sp.product_price, pt.product_type_name FROM shop_products AS sp LEFT JOIN product_types AS pt ON sp.product_type_id = pt.product_type_id ORDER BY sp.product_type_id ASC'; // Line 161
            }
            else
            {
                $query = 'SELECT sp.product_id, sp.product_name, sp.product_price, pt.product_type_name FROM shop_products AS sp LEFT JOIN product_types AS pt ON sp.product_type_id = pt.product_type_id WHERE sp.product_type_id = :product_type_id ORDER BY sp.product_id ASC';
            }
            $stmt = $this->dbcore_prod_core->dbc->prepare($query);
            $stmt->bindParam(':product_type_id', $this->product_type_id, PDO::PARAM_INT);
            $stmt->execute(); // Line 169
            // Function continues below...

现在，仅当$this->product_type_id等于999并在第161行(上面的代码中注释)上运行查询时，才会生成此异常.我直接在服务器上运行了查询，它返回了预期的结果，那么PDO为什么会引发异常?

Now, this exception is only generated when $this->product_type_id is equal to 999 which runs a query on line 161 (annotated in above code). I have run the query directly on the server and it returns the expected results, so why does PDO throw an exception?

推荐答案

我花了几分钟查看我做错了什么，但是很快我单击了我试图将product_type_id绑定到一个不占位的占位符. t在第161行被调用的查询中存在，但在第166行的查询中存在.因此，如果$this->product_type_id等于999，PDO将由于尝试绑定到第161行查询而抛出异常，但在任何其他时间都可以正常运行，因为它将尝试在第166行运行查询.这需要对代码进行一些细微调整，如下所示:

It took me a few minutes to see what I was doing wrong, however it soon clicked that I was trying to bind product_type_id to a placeholder that didn't exist in the query that was being called on line 161, yet exists in the query on line 166. So if $this->product_type_id is equal to 999, PDO would throw an exception due to the attempted bind to the query on line 161, yet any other time it would work fine because it would be trying to run the query on line 166. This called for a slight code adjustment as follows:

public function build_product_list()
    {
        // This function builds the product list visible on the main site (guests only)
        try
        {
            if($this->product_type_id == "999")
            {
                $query = 'SELECT sp.product_id, sp.product_name, sp.product_price, pt.product_type_name FROM shop_products AS sp LEFT JOIN product_types AS pt ON sp.product_type_id = pt.product_type_id ORDER BY sp.product_type_id ASC'; // Line 161
                $stmt = $this->dbcore_prod_core->dbc->prepare($query);
            }
            else
            {
                $query = 'SELECT sp.product_id, sp.product_name, sp.product_price, pt.product_type_name FROM shop_products AS sp LEFT JOIN product_types AS pt ON sp.product_type_id = pt.product_type_id WHERE sp.product_type_id = :product_type_id ORDER BY sp.product_id ASC';
                $stmt = $this->dbcore_prod_core->dbc->prepare($query);
                $stmt->bindParam(':product_type_id', $this->product_type_id, PDO::PARAM_INT);
            }
            $stmt->execute(); // Line 169
            // Function continues below...

对于每种条件，查询均已准备就绪.然后，如果调用第二个查询而不是第一个查询，它将仅在该点绑定参数.

For each condition, the query gets prepared. Then if the second query is called instead of the first, it will bind the parameters at that point only.

这篇关于PDOException:SQLSTATE [IMSSP]:试图绑定参数号65536.SQLServer支持最多2100个参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！