Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

WWWXXXAPC, docmd.close 2585, WWWXXXDOCO, nu vot, dhcpd lease file "binding state", WWWXXXDOCO, how to setup procmail to process html2text, how to setup procmail html2text, WWWXXXAPC., XXXCNZZZ

Links

XODOX
Impressum

#1: Variables in stored procedure

Posted on 2011-10-04 23:20:58 by 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=gcdmg-mysql-2@m.gmane.org

Report this message

#2: Re: Variables in stored procedure

Posted on 2011-10-04 23:27:26 by Peter Brawley

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

Report this message

#3: Re: Variables in stored procedure

Posted on 2011-10-04 23:29:45 by luiz rodrigo mottin

--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 <agersonl@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 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<http://lists.mysql.com/mysql?unsub=luizrodrigomottin@gmail.com>
>
>

--bcaec530465756887404ae7fca17--

Report this message

#4: Re: Variables in stored procedure

Posted on 2011-10-05 00:00:24 by Adam Gerson

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 <agersonl@cgps.org <mailto:agersonl@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>
> agerson@cgps.org <mailto: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
> <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

Report this message

#5: Re: Variables in stored procedure

Posted on 2011-10-05 00:53:48 by luiz rodrigo mottin

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

yes

2011/10/4 Paul Nickerson <paul.nickerson@escapemg.com>

> You need a space before the word VALUES
>
> ------------------------------
> *From: *"Adam Gerson" <agersonl@cgps.org>
> *To: *"luiz rodrigo mottin" <luizrodrigomottin@gmail.com>
> *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 <agersonl@cgps.org <mailto:agersonl@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>
> > agerson@cgps.org <mailto: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
> > <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--

Report this message