Thanks Luiz,
That got me closer. I was able to save the stored proc. It should be
execute stm; not execute @sql; right?
I get this when I try to execute it:
You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near ''309',
'0', 'statpress_mincap', 'edit_posts', 'yes'), ('306', '0',
'statpress_co' at line 1
begin
declare v_max int unsigned default 21;
declare v_counter int unsigned default 21;
declare TABLENAME text;
start transaction;
while v_counter <= v_max do
SET TABLENAME = CONCAT('wp_',v_counter, '_options');
set @sql = concat( "INSERT INTO ", TABLENAME, "VALUES ('309', '0',
'statpress_mincap', 'edit_posts', 'yes'), ('306', '0',
'statpress_collectloggeduser', 'checked', 'yes'), ('307', '0',
'statpress_autodelete', '1 year', 'yes'), ('308', '0',
'statpress_daysinoverviewgraph', '31', 'yes'), ('310', '0',
'statpress_donotcollectspider', 'checked', 'yes'), ('311', '0',
'statpress_autodelete_spider', '1 day', 'yes'), ('312', '0',
'statpress_number_display_post_and_page', '20', 'yes'), ('313', '0',
'statpress_number_display_ip_spy_visitor', '20', 'yes'), ('314', '0',
'statpress_number-display_visit_spy_visitor', '20', 'yes');");
prepare stm from @sql;
execute stm;
set v_counter=v_counter+1;
end while;
commit;
END
--
Adam Gerson
Assistant Director of Technology
Columbia Grammar and Prep School
phone. 212-749-6200 ex. 321
fax. 212-428-6806
ager...@cgps.org
http://www.cgps.org
On 10/4/11 5:29 PM, luiz rodrigo mottin wrote:
you can use:
set @sql = concat( "INSERT INTO ", TABLENAME, "VALUES ('309', '0',
'statpress_mincap', 'edit_posts', 'yes')");
prepare stm from @sql;
execute @sql;
2011/10/4 Adam Gerson <agers...@cgps.org <mailto:agers...@cgps.org>>
I am getting the error that "TABLENAME" does not exist. How do I get
it to substitute the value stored in TABLENAME, and not the literal
string?
begin
declare v_max int unsigned default 1;
declare v_counter int unsigned default 21;
declare TABLENAME text;
start transaction;
while v_counter <= v_max do
SET TABLENAME = CONCAT('wp_',v_counter, '_options');
INSERT INTO TABLENAME VALUES ('309', '0',
'statpress_mincap', 'edit_posts', 'yes');
set v_counter=v_counter+1;
end while;
commit;
END
Thanks,
Adam
--
Adam Gerson
Co-Director of Technology
Columbia Grammar and Prep School
phone. 212-749-6200 <tel:212-749-6200> ex. 321
fax. 212-428-6806 <tel:212-428-6806>
ager...@cgps.org <mailto:ager...@cgps.org>
http://www.cgps.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?__unsub=luizrodrigomottin@gmail.__com
<http://lists.mysql.com/mysql?unsub=luizrodrigomot...@gmail.com>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
