From: Peter Brawley
Date: April 14 2007 2:49pm
Subject: Re: problem with Procedure and Cursor
List-Archive: http://lists.mysql.com/mysql/206166
Message-Id: <4620EA00.3010307@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------020500060305070003070100"

--------------020500060305070003070100
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Balaraju,

When the line
  set pack=''
is removed in favour of
  declare pack text default '';
MySQL 5.0.37 accepts the procedure. That looks like a MySQL bug. Will 
you report it, or shall I?

When the sproc runs, the first NULL passed to CONCAT() sets pack to 
NULL. CONCAT_WS() ignores NULLs. That gives us...

drop procedure if exists activity1;
delimiter |
create procedure activity1()
begin
  declare done int default 0;
  declare pack text default ''; 
  declare name, logout, login varchar(50);
  declare cur1 cursor for
  select
    user_name,
    concat(logindate, ' ', logintime),
    concat(logoutdate, ' ',logouttime)
  from playersloginfo;
  declare continue handler for sqlstate '02000' set done = 1;
  open cur1;
  repeat
    begin
      fetch cur1 into name, login, logout;
      set pack = concat_ws(pack, name, ',', login, ',', logout,';');
    end;
  until done end repeat;
  close cur1;
  select pack;
end;
|
delimiter ;

HTH

PB

-----

balaraju mandala wrote:
> Do you have the procedure, i am pastin it here.
>  
> create procedure activity1()
> begin
> declare done int default 0;
> declare pack text;
> declare name, logout, login varchar(50);
> set pack='';
> declare cur1 cursor for select user_name, concat(logindate, ' ', 
> logintime), concat(logoutdate, ' ',logouttime) from playersloginfo;
> declare continue handler for sqlstate '02000' set done = 1;
> open cur1;
> repeat
> fetch cur1 into name, login, logout;
> set pack = concat(pack, name, ',', login, ',', logout,';');
> until done end repeat;
> select pack;
> close cur1;
> end;|
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.5.446 / Virus Database: 269.4.0/759 - Release Date: 4/12/2007 7:58 PM
>   

--------------020500060305070003070100--