List:General Discussion« Previous MessageNext Message »
From:Walter Tross Date:March 26 2012 11:01pm
Subject:Re: Fwd: why must user variable in EXECUTE USING clause ?
View as plain text  
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
> <<mailto:h.reindl@stripped>h.reindl@stripped>
>Date: Mon, Mar 26, 2012 at 9:17 PM
>Subject: Re: why must user variable in EXECUTE USING clause ?
>To: Cifer Lee <<mailto:mantianyu@stripped>mantianyu@stripped>
>
>
>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>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
> <<mailto:h.reindl@stripped>h.reindl@stripped
> <mailto:h.reindl@stripped>> 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

Thread
Fwd: why must user variable in EXECUTE USING clause ?Cifer Lee26 Mar
  • Re: Fwd: why must user variable in EXECUTE USING clause ?Walter Tross26 Mar
Re: Fwd: why must user variable in EXECUTE USING clause ?Cifer Lee27 Mar
  • Re: why must user variable in EXECUTE USING clause ?Johan De Meersman27 Mar