CODEWITHSUNDEEP
phpsql-serverpdo
tyrone 1988
tyrone 1988

Reputation: 321

PDOException: SQLSTATE[IMSSP]: Tried to bind parameter number 65536. SQL Server supports a maximum of 2100 parameters

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}

And here is the code it refers to:

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

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?

Upvotes: 5

Views: 10706

Answers (1)

tyrone 1988
tyrone 1988

Reputation: 321

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.

Upvotes: 7

Related Questions