Can a prepared statement in MySQL contain PREPARE and EXECUTE?

Question

I wanted to know if prepared statements can be used in the following way:

 public static function GetCategoryItems($categoryId,$pageNum, &$rnum_pages)
    {
    $sql = 'SELECT DISTINCT COUNT(*) AS items_count
                FROM item I
                JOIN sub_category SC
                ON I.sub_category_id = SC.sub_category_id
                JOIN category C 
                ON C.category_id = SC.category_id
                WHERE (I.location = 2 OR I.location = 3)
                AND C.category_id =' . (int)$categoryId ;
        $params = array (':category_id'=>$categoryId);
        $rnum_pages = Catalog::CalculatePages($sql,$params);
        $first_item = ($pageNum - 1) * ITEMS_PER_PAGE;
            $sql =
         "PREPARE stmt_name FROM 
        'SELECT I.item_id, I.name , I.price, I.discounted_price, I.thumbnail_photo
        FROM item I
        JOIN sub_category SC
        ON I.sub_category_id = SC.sub_category_id
        JOIN category C
        ON C.category_id = SC.category_id
        WHERE C.category_id = ?
        LIMIT ?,? ' ; 

        SET @p1 = categoryId;
        SET @p2 = firstItem;
        SET @p3 = items_per_page;

        EXECUTE stmt_name USING @p1,@p2,@p3; "
        return DatabaseConnection::GetAll($sql,$params);
        }

I am actually getting an error :

 ERRNO: 256 TEXT: SQLSTATE[HY000]: General error

Can anyone help please?

Answer 1

You prepare stmt_name but execute stmt - is this just a typo?

What about the categoryId, firstName and items_per_page - where are those coming from?

Answer 2

In the user comments in the PHP Manual, from "Brian at diamondsea":

SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. ...

After spending hours trying to track down why we were getting this error on a new server, after the same code ran fine on other servers, we found the problem to be an old MySQL client library running on our web server, and a latest-version MySQL server running on the database server's box.

Upgraded the MySQL client on the web server to the current revision and the problem went away.

Answer 3

Generally, the PREPARE and EXECUTE statements are 'client-side' operations that the `server-side' of the DBMS does not normally see. This used to be a hard-and-fast rule; things are now blurred with stored procedures and the like allowing dynamic SQL. Consequently, what you're attempting may be possible in MySQL, but it looks dubious (at least).

However, given that you have problems, I recommend splitting up the second chunk of SQL into two separate operations - and omit the PREPARE blurb and the EXECUTE blurb. You will need to pass the parameters to the statement that executes the split SQL (to the execute phase of the split SQL, in fact).