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)