CODEWITHSUNDEEP
mysqlsql
Mr A
Mr A

Reputation: 1385

MySQL #1243 Unknown prepared statement handler (stmt) given to EXECUTE

I am following this tutorial on my installed version of MySQL, but it's throwing me an error:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(property_name = ''',
      property_name,
      ''', value, NULL)) AS ',
      property_name
    )
  ) INTO @sql
FROM
  properties;
SET @sql = CONCAT('SELECT item_id, ', @sql, ' FROM properties GROUP BY item_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

I am pasting it on SQL editor in phpMyAdmin.

I followed the suggestion. No errors shown but here's the result:

SELECT item_id
,MAX(IF(property_name = 'color', value, NULL)) AS color
,MAX(IF(property_name = 'size', value, NULL)) AS size
,MAX(IF(property_name = 'weight', value, NULL)) AS weight 
FROM properties GROUP BY item_id

Upvotes: 8

Views: 35675

Answers (3)

Dustin
Dustin

Reputation: 63

You need to remove the DEALLOCATE PREPARE stmt; from your query until after the query runs.

DEALLOCATE cancels the statement before it has a chance to run.

Upvotes: 6

Jis Jose
Jis Jose

Reputation: 644

Try this in MySQL Workbench. I had the same issue in phpMyAdmin and I tried in Workbench and it works fine.

Upvotes: 3

udog
udog

Reputation: 1536

If you have access to a MySQL command-line, I think you'll find your SQL code is fine (as long as @sql doesn't equal NULL) and the issue is with phpMyAdmin. Another idea is to wrap the code in a stored procedure and then CALL the procedure.

Upvotes: 3

Related Questions