Variables in stored procedure

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 ex. 321
fax. 212-428-6806
agerson@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=gcdmg-mysql-2@m.gmane.org

Re: Variables in stored procedure

On 10/4/2011 4:20 PM, Adam Gerson wrote:
> 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?

See the manual page for PREPARE.

PB

-----
>
> 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
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Variables in stored procedure

--bcaec530465756887404ae7fca17
Content-Type: text/plain; charset=ISO-8859-1

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

> 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 ex. 321
> fax. 212-428-6806
> agerson@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
>
>

--bcaec530465756887404ae7fca17--

Re: Variables in stored procedure

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
agerson@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 >
>
> 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 ex. 321
> fax. 212-428-6806
> agerson@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
>
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Variables in stored procedure

--bcaec5314809ed3fc204ae80f6cd
Content-Type: text/plain; charset=ISO-8859-1

yes

2011/10/4 Paul Nickerson

> You need a space before the word VALUES
>
> ------------------------------
> *From: *"Adam Gerson"
> *To: *"luiz rodrigo mottin"
> *Cc: *mysql@lists.mysql.com
> *Sent: *Tuesday, October 4, 2011 6:00:24 PM
> *Subject: *Re: Variables in stored procedure
>
>
> 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
> agerson@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 >
> >
> > 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 ex. 321
> > fax. 212-428-6806
> > agerson@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
> >
> >
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=paul.nickerson@grooveshar k.com
>
>
>

--bcaec5314809ed3fc204ae80f6cd--