How to view a prepared statement OR should I abandon dynamic table names?

Question

I'm working on updating some PHP/MySQL reporting code someone wrote a few years back. There are over 50 SQL statements in a single PHP page. Some of them create temporary tables, all of them refer to at least one dynamic table (e.g. Report_201701), which is simple in PHP using a variable.

But as I adapt all these statements to MySQL Workbench to troubleshoot and enhance, this is causing issues because I can't view the the output of the prepared statements since I'm using variables for the dynamic table name.

I'm not familiar with dynamic SQL. Is there some way I can view/output the query results? According to this post it appears I can't.

A few of the statements return a single value, which I have been able to view by assigning to a variable like this:

set @sqlQuery = 
    Concat(
        'SELECT 1 as "A"
        into @sqlOut;'
    );
    #$opentickets = mysql_result($sqlQuery, 0);
PREPARE stmt1 from @sqlQuery;
EXECUTE stmt1;
deallocate prepare stmt1;
select @sqlOut;

# @sqlOut
-----------
1

But if I try it with results that return multiple columns or multiple rows I get errors like this:

set @sqlQuery = 
    Concat(
        'SELECT 1 as "A", 2 as "B", 3 as "C"
        into @sqlOut;'
    );
    #$opentickets = mysql_result($sqlQuery, 0);
PREPARE stmt1 from @sqlQuery;
EXECUTE stmt1;
deallocate prepare stmt1;
select @sqlOut;

Error Code: 1222. The used SELECT statements have a different number of columns


set @sqlQuery = 
    Concat(
        'SELECT 1 as "A"
        UNION SELECT 2 as "A"
        UNION SELECT 3 as "A"
        into @sqlOut;'
    );
    #$opentickets = mysql_result($sqlQuery, 0);
PREPARE stmt1 from @sqlQuery;
EXECUTE stmt1;
deallocate prepare stmt1;
select @sqlOut;

Error Code: 1172. Result consisted of more than one row

What's my best approach? Replace the dynamic table name with a static name for now? (I'm thinking of doing that permanently since temp tables in this situation don't need to have names that indicate date.)

fyi, If you get really stuck then you can use the Mysql 'General Log' to record everything that goes to the database engine. It is really useful. As you have seen a lot of the libraries that build SQL will also tell you what they did. All this is very useful for debugging SQL stuff. — Ryan Vincent, Feb 09 '17 at 23:29
Thanks for the tip @RyanVincent. I'll have to check where to turn it on in case I need it. — MJA, Feb 09 '17 at 23:47

score 0 · Accepted Answer · answered Feb 09 '17 at 23:24

Well, I stumbled upon the finding that MySQL Workbench DOES output prepared SELECT statements. No need to assign to a variable. Not sure if this will help someone, or if it should be deleted for stating the obvious.

set @sqlQuery = 
    Concat(
        'SELECT 1 as "A", 2 as "B", 3 as "C"
        UNION SELECT 10 as "A", 20 as "B", 30 as "C"
        UNION SELECT 100 as "A", 200 as "B", 300 as "C";'
    );
PREPARE stmt1 from @sqlQuery;
EXECUTE stmt1;
deallocate prepare stmt1;

Returns:

A       B       C
'1'     '2'     '3'
'10'    '20'    '30'
'100'   '200'   '300'

How to view a prepared statement OR should I abandon dynamic table names?

1 Answers1