30
05
2007
Use parameters for field and table names in a MySQL Prepare statement to create truly dynamic SQL
Posted by: Ori Pekelman in sqlThe mysql prepare statement sucks heavy time. You can not (!?!!!) use it to template field and table names. So it is mostly useless for any heavy duty stored procedures that want to address different columns.
Here is a small function that will take care of that 
SQL:
-
DELIMITER //
-
DROP FUNCTION IF EXISTS substrCount//
-
CREATE FUNCTION substrCount(x varchar(255), delim varchar(12)) returns int
-
RETURN (length(x)-length(REPLACE(x, delim, '')))/length(delim);//
-
-
DROP FUNCTION IF EXISTS charsplit//
-
CREATE FUNCTION charsplit(x varchar(255), delim varchar(12), pos int) returns varchar(255)
-
RETURN REPLACE(substring(substring_index(x, delim, pos), length(substring_index(x, delim, pos - 1)) + 1), delim, '');//
-
-
DROP FUNCTION IF EXISTS replacefirst//
-
CREATE FUNCTION replacefirst(haystack varchar(255), needle varchar(255),replacestr varchar(255)) returns varchar(255)
-
BEGIN
-
IF LOCATE(needle,haystack)>0 THEN
-
SET @replaced=concat(LEFT(haystack,LOCATE(needle,haystack)-LENGTH(needle)),replacestr,RIGHT(haystack,LENGTH(haystack)-LOCATE(needle,haystack)));
-
ELSE
-
SET @replaced=haystack;
-
END IF;
-
RETURN @replaced;
-
END;//
-
-
DROP FUNCTION IF EXISTS properprepare//
-
CREATE FUNCTION properprepare(template varchar(255), args varchar(255)) returns varchar(255)
-
BEGIN
-
SET @i=0;
-
SET @numargs = substrCount(args,',');
-
WHILE @i <= @numargs DO
-
SET @i= @i+ 1;
-
SET template=replacefirst(template,'?',charsplit(args,',',@i));
-
END WHILE;
-
RETURN template;
-
END;//
-
-
DELIMITER ;
Test with
SQL:
-
SELECT properprepare('SELECT ? FROM ? ', '*,user');
-
SELECT properprepare('SELECT ? FROM ? ??', '*,user');
-
SELECT properprepare('SELECT ? FROM ?', '*,user,test,test1');
-
SELECT properprepare('SELECT ? FROM ? WHERE ?=?', '*,user,user_id,25');
-- So now you can in your stored procedures (at last!) do:
SQL:
-
PREPARE update_query FROM properprepare('SELECT ? FROM ?', '*,mytable');
-
EXECUTE update_query;
-
PREPARE update_query FROM properprepare('SELECT ? FROM ?', 'myfield,mytable');
-
EXECUTE update_query;
The first argument is the template with the question mark '?' as placeholder, the second is a comma separated string with the variables to replace. If you pass too few variables, the remaining placeholders will not be modified so they can be treated with the mysql PREPARE FROM, EXECUTE USING afterwards...
SQL:
-
SET @a=25;
-
SET @b=properprepare('SELECT ? FROM ? WHERE ?=?', '*,user,id_user');
-
PREPARE stmnt FROM @b;
-
EXECUTE stmnt USING @a;
CAVEAT: Hardly tested, do not use in production without really testing this one
Entries (RSS)
June 21st, 2007 at 8:44 pm
“The mysql prepare statement sucks heavy time. You can not (!?!!!) use it to template field and table names.”
To my knowledge, no stored procedure language supports this for prepared statements. The entire idea of *preparing* a statement is that the database engine can parse a *statement*, optimize it, create a query plan for it and then afterwards reuse the handle to that statement. A statement template with place holders for structural elements (tables, columns etc) is syntactically not a statement, can thus not be parsed, and thus no plan can be created for it.
A few comments on your code:
#1: length() returns the number of *bytes* not the number of characters. There are multiple places in you code where this difference matters.
#2: Inside your functions you are using user-defined variables (@variables) instead of proper local variables. This is unnecessary, and can have unwanted side effects.
#3: You do not DEALLOCATE your prepared statements. This might not seem a big deal, but can be killing if you have a high load on your server. That is because the total number of prepared statements is limited.
kind regards,
Roland.