it seems none of you hava answered my question.....
On Tue, Mar 27, 2012 at 7:01 AM, Walter Tross <walter@stripped>wrote:
> 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 :-(
> 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>
> >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
> >> 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 <<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