Re: Fwd: why must user variable in EXECUTE USING clause ?

[Cifer Lee]

it seems none of you hava answered my question.

On Tue, Mar 27, 2012 at 7:01 AM, Walter Tross wrote:

> Harald,
> keep in mind that
> a) this mailing list is badly set up: unless you do a "reply all", the
> reply goes only to the poster (happened to me too)
> b) Microsoft products have made inline (bottom) answering almost
> impossible. I, like many others, am forced to used Outlook at the office,
> and therefore I had to give up, and now I stick to top-posting :-(
> ciao
> W.
> P.S. the only thing I have not given up yet is not using empty lines to
> avoid the "feature" (?) of Outlook that glues lines together. This
> "feature" can be disabled/reverted (although this is not widely known)
>
> At 21.38 26/03/2012 +0800, Cifer Lee wrote:
>
>
> >-- Forwarded message --
> >From: Reindl Harald <
> h.rei...@thelounge.net>
> >Date: Mon, Mar 26, 2012 at 9:17 PM
> >Subject: Re: why must user variable in EXECUTE USING clause ?
> >To: Cifer Lee <mantia...@gmail.com>
> >
> >
> >would you PLEASE send to the list instead off-list
> >and put your answer BELOW instead to-posting?
> >
> >is it really so difficult to use mailing-lists?
> >
> >
> >Am 26.03.2012 14:34, schrieb Cifer Lee:
> >> thanks for reply
> >> and .sorry for my poor English ...
> >> I wrote a procedure program which contains prepare clause , please see
> below
> >>
> >> CREATE PROCEDURE `iter_table`(IN type int)
> >> BEGIN
> >> DECLARE tablename VARCHAR(24) DEFAULT '';
> >> DECLARE shop_id int DEFAULT 0;
> >> DECLARE count int DEFAULT 0;
> >> DECLARE row_count int DEFAULT 0;
> >> DECLARE x varchar(24);
> >> DECLARE cur1 CURSOR FOR SELECT `id` FROM shop;
> >>
> >> SELECT COUNT(*) INTO row_count FROM shop;
> >>
> >> OPEN cur1;
> >> REPEAT
> >> FETCH cur1 INTO shop_id;
> >>   SET @shop_id := shop_id;
> >>   SET @type := type;
> >>   SET tablename= CONCAT('shop',@shop_id);
> >> SET @sqlstr = CONCAT('SELECT
> `id`,`name`,`repertory`,`photo`,`type`,`price`,@shop_id AS shop_id FROM
> >> ',tablename,' WHERE `type`=?;');
> >> PREPARE stat FROM @sqlstr;
> >> EXECUTE stat USING type; - Must be EXECUTE stat USING
> @type ;
> >> SET count=count+1;
> >> UNTIL count >= row_count
> >> END REPEAT;
> >> CLOSE cur1;
> >> END
> >>
> >> I got an error at the red line when I creating this procedure
> >> and the solution is replace the 'type'  with  '@type'
> >>
> >> I found  here  
> http://dev.mysql.com/doc/refman/5.5/en/execute.html
> >> and knows that
> >> "/you must supply a |USING| clause that lists user variables containing
> the values to be bound to the parameters.
> >> Parameter values can be supplied only by user variables/,"
> >>
> >> but, I don't know why .   why must parameter be user variables ? why
> can't be  local variables ,  as declared in
> >> DECLARE clause..
> >>
> >> thanks!
> >>
> >> On Mon, Mar 26, 2012 at 8:15 PM, Reindl Harald < h.rei...@thelounge.net>h.rei...@thelounge.net  h.rei...@thelounge.net>> wrote:
> >>
> >>
> >>
> >> Am 26.03.2012 14:13, schrieb Cifer Lee:
> >> > why can't be local variable which  declared in  DECLARE  clause?
> >>
> >> what are you speaking about?
> >> keep in mind that we can not read your thoughts
> >
> >
> >Content-Type: application/pgp-signature; name="signature.asc"
> >Content-Disposition: attachment; filename="signature.asc"
> >X-Attachment-Id: 2caae85bfd1f082d_0.1
> >
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe:http://lists.mysql.com/mysql
>

Re: Fwd: why must user variable in EXECUTE USING clause ?

[Walter Tross]

Harald,
keep in mind that
a) this mailing list is badly set up: unless you do a "reply all", the reply 
goes only to the poster (happened to me too)
b) Microsoft products have made inline (bottom) answering almost impossible. I, 
like many others, am forced to used Outlook at the office, and therefore I had 
to give up, and now I stick to top-posting :-(
ciao
W.
P.S. the only thing I have not given up yet is not using empty lines to avoid 
the "feature" (?) of Outlook that glues lines together. This "feature" can be 
disabled/reverted (although this is not widely known)

At 21.38 26/03/2012 +0800, Cifer Lee wrote:


>-- Forwarded message --
>From: Reindl Harald <h.rei...@thelounge.net>
>Date: Mon, Mar 26, 2012 at 9:17 PM
>Subject: Re: why must user variable in EXECUTE USING clause ?
>To: Cifer Lee <mantia...@gmail.com>
>
>
>would you PLEASE send to the list instead off-list
>and put your answer BELOW instead to-posting?
>
>is it really so difficult to use mailing-lists?
>
>
>Am 26.03.2012 14:34, schrieb Cifer Lee:
>> thanks for reply
>> and .sorry for my poor English ...
>> I wrote a procedure program which contains prepare clause , please see below
>>
>> CREATE PROCEDURE `iter_table`(IN type int)
>> BEGIN
>> DECLARE tablename VARCHAR(24) DEFAULT '';
>> DECLARE shop_id int DEFAULT 0;
>> DECLARE count int DEFAULT 0;
>> DECLARE row_count int DEFAULT 0;
>> DECLARE x varchar(24);
>> DECLARE cur1 CURSOR FOR SELECT `id` FROM shop;
>>
>> SELECT COUNT(*) INTO row_count FROM shop;
>>
>> OPEN cur1;
>> REPEAT
>> FETCH cur1 INTO shop_id;
>>   SET @shop_id := shop_id;
>>   SET @type := type;
>>   SET tablename= CONCAT('shop',@shop_id);
>> SET @sqlstr = CONCAT('SELECT 
>> `id`,`name`,`repertory`,`photo`,`type`,`price`,@shop_id AS shop_id FROM
>> ',tablename,' WHERE `type`=?;');
>> PREPARE stat FROM @sqlstr;
>> EXECUTE stat USING type; - Must be EXECUTE stat USING @type ;
>> SET count=count+1;
>> UNTIL count >= row_count
>> END REPEAT;
>> CLOSE cur1;
>> END
>>
>> I got an error at the red line when I creating this procedure
>> and the solution is replace the 'type'  with  '@type'
>>
>> I found  here  
>> http://dev.mysql.com/doc/refman/5.5/en/execute.html
>> and knows that
>> "/you must supply a |USING| clause that lists user variables containing the 
>> values to be bound to the parameters.
>> Parameter values can be supplied only by user variables/,"
>>
>> but, I don't know why .   why must parameter be user variables ? why can't 
>> be  local variables ,  as declared in
>> DECLARE clause..
>>
>> thanks!
>>
>> On Mon, Mar 26, 2012 at 8:15 PM, Reindl Harald 
>> <h.rei...@thelounge.net 
>> > wrote:
>>
>>
>>
>> Am 26.03.2012 14:13, schrieb Cifer Lee:
>> > why can't be local variable which  declared in  DECLARE  clause?
>>
>> what are you speaking about?
>> keep in mind that we can not read your thoughts
>
>
>Content-Type: application/pgp-signature; name="signature.asc"
>Content-Disposition: attachment; filename="signature.asc"
>X-Attachment-Id: 2caae85bfd1f082d_0.1
>
>
>-- 
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:http://lists.mysql.com/mysql

Fwd: why must user variable in EXECUTE USING clause ?

[Cifer Lee]

-- Forwarded message --
From: Reindl Harald 
Date: Mon, Mar 26, 2012 at 9:17 PM
Subject: Re: why must user variable in EXECUTE USING clause ?
To: Cifer Lee 


would you PLEASE send to the list instead off-list
and put your answer BELOW instead to-posting?

is it really so difficult to use mailing-lists?


Am 26.03.2012 14:34, schrieb Cifer Lee:
> thanks for reply
> and .sorry for my poor English ...
> I wrote a procedure program which contains prepare clause , please see
below
>
> CREATE PROCEDURE `iter_table`(IN type int)
> BEGIN
> DECLARE tablename VARCHAR(24) DEFAULT '';
> DECLARE shop_id int DEFAULT 0;
> DECLARE count int DEFAULT 0;
> DECLARE row_count int DEFAULT 0;
> DECLARE x varchar(24);
> DECLARE cur1 CURSOR FOR SELECT `id` FROM shop;
>
> SELECT COUNT(*) INTO row_count FROM shop;
>
> OPEN cur1;
> REPEAT
> FETCH cur1 INTO shop_id;
>   SET @shop_id := shop_id;
>   SET @type := type;
>   SET tablename= CONCAT('shop',@shop_id);
> SET @sqlstr = CONCAT('SELECT
`id`,`name`,`repertory`,`photo`,`type`,`price`,@shop_id AS shop_id FROM
> ',tablename,' WHERE `type`=?;');
> PREPARE stat FROM @sqlstr;
> EXECUTE stat USING type; - Must be EXECUTE stat USING
@type ;
> SET count=count+1;
> UNTIL count >= row_count
> END REPEAT;
> CLOSE cur1;
> END
>
> I got an error at the red line when I creating this procedure
> and the solution is replace the 'type'  with  '@type'
>
> I found  here  http://dev.mysql.com/doc/refman/5.5/en/execute.html
> and knows that
> "/you must supply a |USING| clause that lists user variables containing
the values to be bound to the parameters.
> Parameter values can be supplied only by user variables/,"
>
> but, I don't know why .   why must parameter be user variables ? why
can't be  local variables ,  as declared in
> DECLARE clause..
>
> thanks!
>
> On Mon, Mar 26, 2012 at 8:15 PM, Reindl Harald > wrote:
>
>
>
> Am 26.03.2012 14:13, schrieb Cifer Lee:
> > why can't be local variable which  declared in  DECLARE  clause?
>
> what are you speaking about?
> keep in mind that we can not read your thoughts


signature.asc
Description: PGP signature

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql