I have a datasource on my page, the select query of which creates a pivot table. The complexity is that the columns in the pivot are determined by records in another table (the users table). So I have to use a prepared statement to build and execute the pivot query, as beow;
SelectCommand="set @q = CONCAT('SELECT Question,', (SELECT GROUP_CONCAT(CONCAT('NULLIF(COUNT(NULLIF(t.user=',ID,',0)),0) AS `',Firstname,' ',Lastname,'`')) FROM users), ' FROM archiveresponses LEFT JOIN archivetransactions t ON t.TransactionID=archiveresponses.TransactionID GROUP BY Question,Compliance HAVING Compliance=0'); PREPARE smt FROM @q; EXECUTE smt;"
The actual select query that runs will look something like this (obviously the number of user-named columns is dynamic)
SELECT Question,NULLIF(COUNT(NULLIF(t.user=2,0)),0) AS `John Smith`,NULLIF(COUNT(NULLIF(t.user=3,0)),0) AS `Jane Doe` FROM archiveresponses LEFT JOIN archivetransactions t ON t.TransactionID=archiveresponses.TransactionID GROUP BY Question,Compliance HAVING Compliance=0
This works perfectly when I run it manually against the database. However, in the aspx page it throws an exception stating
MySql.Data.MySqlClient.MySqlException: Parameter '@q' must be defined.
I am positive that this worked a couple days ago, but no longer. Only some minor changes have been made to the query and page layout which should not have affected the workflow of the PREPARE-EXECUTE structure of the query.
Is there any way to tell the datasource that
@q is not in fact a parameter, but just part of the query that needs to be executed?
does anyone know how to use the Prepared statement construct without requiring the interum variable?
I know you can use
PREPARE stmt FROM 'string constant query' but it doesn't seem to work with the
CONCAT function or a
SELECT query that returns the query string.
I'm trying to avoid having to manually execute and build the required query in code, due to the additional calls to the database this would require, but at this stage it looks like my only option. Any advice greatly appreciated.