Re: [Hsqldb-user] Unable to invoke a procedure within for loop

[Fred T. <fr...@us...>]

Please download and use the latest snapshot. Read the message I posted
two days ago about new support for local table variables in functions.
The snapshot also supports the features you needed for recusrive
routines. The next snapshot will be released tomorrow and will fix some
minor issues.

Your routine compiles OK if you correct it as below:

create PROCEDURE proc_y() MODIFIES SQL DATA
BEGIN ATOMIC
declare id_1 Integer;
declare id_2 integer;
declare result integer;
set id_1 = 1;
for_label_1: for select id from mdl do
    set id_2 = id;
    call isValid(id_1 , id_2, result);
    if(result = 1) then
        insert into temp_tbl values id_2, result;
    end if;
end for for_label_1;
end;


On Wed, 13 Oct 2010 15:29 +0000, "Buwan" <bu...@gm...> wrote:
> I would like to invoke a procedure with in a for loop as in the example. 
> I would like to know the result for each row in 'mdl' table. So I am
> traversing
> all the rows in a table and trying to get the corresponding result 
> for each entry. 
> I can invoke isValid() procedure without for loop, but I couldn't 
> invoke withina for loop. I am getting 'user lacks privilege 
> or object not found: ID_2 / Error Code: -5501 / State: 42501'
> 
> Could you help me to overcome this issue.
> 
> create PROCEDURE isValid(
> INOUT id_1      Integer,
> INOUT id_2      Integer,
> INOUT result    Integer
> )BEGIN ATOMIC
> 	if (id_2 = 1) then 
> 		set result = 1;
> 	end if;
> end;
> --------------------
> create PROCEDURE proc_y() MODIFIES SQL DATA BEGIN ATOMIC
> declare id_1 Integer;
> declare id_2 integer;
> declare result integer;
> set id_1 = 1;
> for_label_1: for select id from mdl do
> 	set id_2 = id;
> 	call isValid(id_1 , id_2, result);
> 	if(result == 1) then
> 		insert into temp_tbl(id_2, result);
> 	end if;
> end for for_label_1;
> end;
> 
> -------------------
> Even the following procedure failed as 'dynamic parameter or variable 
> required as INOUT or OUT argument / Error Code: -5603 / State: 42603'
> 
> create PROCEDURE proc_y() MODIFIES SQL DATA BEGIN ATOMIC
> declare id_1 Integer;
> declare result integer;
> set id_1 = 1;
> for_label_1: for select id from mdl do
> 	call isValid(id_1 , id, result);
> 	if(result == 1) then
> 		insert into temp_tbl(id, result);
> 	end if;
> end for for_label_1;
> end;
> 
> Thanks
> Buwan
> 
> 
> ------------------------------------------------------------------------------
> Beautiful is writing same markup. Internet Explorer 9 supports
> standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
> Spend less time writing and  rewriting code and more time creating great
> experiences on the web. Be a part of the beta today.
> http://p.sf.net/sfu/beautyoftheweb
> _______________________________________________
> Hsqldb-user mailing list
> Hsq...@li...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>