HELP: SPL WHILE loop problem
Hi Guys,
I thought I'd ask the SPL experts personally on this one... ;-)
Here's my problem:
I have a table called io_group that works like a tree, as follows:
create table io_group
(
clientnr varchar(10) not null,
{primary key1 - as user}
groupnumber integer not null,
{primary key2 - unique num}
groupparent integer,
{the parent for this group}
grouppasstf varchar(1),
{t or f for password protection}
grouppasswd varchar(10),
{the actual password if any}
primary key (clientnr, groupname)
);
The group tree works as follows:
Every group has a number and parent, and the parent of a group points to
another group closer to the root of the tree.
No matter which record of io_group I request, I need to be able to find the
top of the tree - which is groupnumber 0, or, until a group has grouppasstf
set to 't'.
Here's what I thought would work:
=========
create function haspasswd
(client varchar(10), {passed clientnr}
startgroup integer {passed group to start from}
)
returns varchar(1), varchar(10); {returns t/f and the password}
define passwdtf varchar(1); {holds passwdtf of group}
define password varchar(10); {holds the password of group}
define parent integer; {holds the parent}
define currentgroup integer; {holds the current group for while}
let passwdtf='f';
let password='';
let currentgroup=startgroup;
while EXISTS
(SELECT grouppasstf, grouppasswd, groupparent INTO
passwdtf, password, parent FROM io_group WHERE
groupnumber=currentgroup and clientnr=client) and passwdtf = 'f'
let currentgroup=parent;
end while;
return passwdtf, password;
end function;
===========
I'd call the procedure using:
execute function haspasswd('theclient',5);
The compile error is just after the INTO in the select statement; it says
syntax error.
I've read the Informix SPL manual thoroughly on WHILE loops and have found
even their examples don't even compile into function/procedures because of
the same reason. I've tried both Universal Server v9.12 and v9.14.
Is my above logic correct? The idea is that it would "climb" the tree with
the currentgroup variable being changed every iteration of the while
statement until no more records are found (not exists) or a 't' is found in
passwdtf (hence the AND in the while loop).
Any assistance or hints are greatly appreciated.
Thanks,
---
Steven Livanes
steven@diggy.com
Diggy Internet Services
>while EXISTS
> (SELECT grouppasstf, grouppasswd, groupparent INTO
> passwdtf, password, parent FROM io_group WHERE
> groupnumber=currentgroup and clientnr=client) and passwdtf = 'f'
> let currentgroup=parent;
>end while;
I've hacked something together that got this working:
=============
for counter = 1 to 50
select grouppasstf, grouppasswd, groupparent into
passwdtf, password, parent from io_group where
groupname=currentgroup and clientnr=client; let currentgroup=parentgroup;
if passwdtf = 't' or currentgroup = 0 then
exit for;
end if;
end for;
=============
Pretty dodgy as it assumes that it is going down the tree a maximum of 50
times. However, it does work.
Anyone got a WHILE loop going?
Regards,
---
Steven Livanes
steven@diggy.com
Diggy Internet Services