该服务器支持最多2100个参数,php – PDOException：SQLSTATE [IMSSP]：尝试绑定参数号65536.SQL Server最多支持2100个参数...

好的,所以我遇到了一个非常奇怪的PDO异常,我似乎无法理解.这是生成的异常：

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会抛出异常？

解决方法:

我花了几分钟才看到我做错了什么,但是很快就点击了我试图将product_type_id绑定到在第161行调用的查询中不存在的占位符,但是在查询中存在因此,如果$this-> product_type_id等于999,由于尝试绑定到第161行的查询,PDO会抛出异常,但是任何其他时间它都可以正常工作,因为它会尝试运行查询在第166行.这要求进行如下的轻微代码调整：

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...

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

标签：php,sql-server,pdo

来源： https://codeday.me/bug/20191008/1871267.html