Writing an iterator function in SPL
Stephen wanted an SPL iterator function to walk an id/supervisor_id org-chart tree and be callable as a table in a SELECT (table(function ...)), but got error 675, "Illegal SQL statement in SPL routine". Marco Greco explained the cause: a procedure invoked from a SELECT cannot perform DML, which Stephen's temp-table build/insert/update/drop logic does; he suggested running the procedure via a cursor or EXECUTE PROCEDURE instead, or using SQSL (the SELECT form was needed for ACE reports). Stephen meanwhile found a recursive SPL solution in the group's archives that works, though slowly; others suggested a node datatype/DataBlade or moving the logic to Java. No tuning fix was recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Howdy,
I'm getting this error, "675: Illegal SQL statement in SPL routine",
when trying to make
this iterator function work.
This is how I call the iterator function (like a table):
select a, b from table(function org_chart(1234)) AS foo (a, b);
This is the essence of the function:
--------------------
create procedure org_chart (
param_id integer)
returning
integer,
integer; define i, k integer;
select id, supervisor_id, "0" flg
from employees
where id = param_id
into temp emps_0 with no log;
while ((select count(*) from emps_0 where flg = "0") > 0)
select employees.id,
employees.supervisor_id
from employees,
emps_0
where employees.supervisor_id = emps_0.id
and emps_0.flg = 0
into temp save_emps with no log;
update emps_0 set flg = "1";
insert into emps_0
(id, supervisor_id, flg)
select id, supervisor_id, "0"
from save_emps;
drop table save_emps; end while
foreach select id, supervisor_id into i, k from emps_0
return i, k with resume;
end foreach
end procedure
----------------------------------
The end goal of this is to output a table which represents a branch
of the id/supervisor_id
tree (that's why it's called org_chart -- organizational chart). I
would vastly prefer a solution
that works in SPL and works in SELECT statements, though 1) I do not
want to have a
permanent table for this and 2)
Thanks for any help or suggestions!
Sincerely,
Stephen
Sounds like a node datatype/datablade solution assuming you are on 9+
Paul Watson
Tel: +44 1414161772
Mob: +44 7818003457
Web: www.oninit.com
Failure is not as frightening as regret.
Attend IDUG 2007 San Jose, North America
May 6-10, 2007
Visit http://www.iiug.org/conf for more information.
> -----Original Message-----
> From: Stephen [mailto:swaters@luy.info]
> Posted At: 06 February 2007 11:43
> Posted To: comp.databases.informix
> Conversation: Writing an iterator function in SPL
> Subject: Writing an iterator function in SPL
>
>
> Howdy,
>
> I'm getting this error, "675: Illegal SQL statement in SPL
> routine", when trying to make this iterator function work.
>
> This is how I call the iterator function (like a table):
> select a, b from table(function org_chart(1234)) AS foo (a, b);>
> This is the essence of the function:
> --------------------
> create procedure org_chart (
> param_id integer)
> returning
> integer,
> integer;> define i, k integer;
>
> select id, supervisor_id, "0" flg
> from employees
> where id = param_id
> into temp emps_0 with no log;>
> while ((select count(*) from emps_0 where flg = "0") > 0)
>
> select employees.id,
> employees.supervisor_id
> from employees,
> emps_0
> where employees.supervisor_id = emps_0.id
> and emps_0.flg = 0
> into temp save_emps with no log;>
> update emps_0 set flg = "1";>
> insert into emps_0
> (id, supervisor_id, flg)
> select id, supervisor_id, "0"
> from save_emps;>
> drop table save_emps;> end while
>
> foreach select id, supervisor_id into i, k from emps_0
> return i, k with resume;
> end foreach
> end procedure
> ----------------------------------
>
> The end goal of this is to output a table which represents a
> branch of the id/supervisor_id tree (that's why it's called
> org_chart -- organizational chart). I would vastly prefer a
> solution that works in SPL and works in SELECT statements,
> though 1) I do not want to have a permanent table for this and 2)
>
> Thanks for any help or suggestions!
>
> Sincerely,
> Stephen
>
On Feb 6, 11:42 am, "Stephen" <swat...@luy.info> wrote: > Howdy, > > I'm getting this error, "675: Illegal SQL statement in SPL routine", > when trying to make > this iterator function work. Ha! I eventually found a solution in the archives. My only issue is it's a little sluggish, but tuning is better than not working at all. :) http://groups.google.com/group/comp.databases.informix/browse_thread/thread/4d72253d159a6acc/106c4bacc45d881a#106c4bacc45d881a Cheers, Stephen
Stephen wrote:
> Howdy,
>
> I'm getting this error, "675: Illegal SQL statement in SPL routine",
> when trying to make
> this iterator function work.
>
> This is how I call the iterator function (like a table):
> select a, b from table(function org_chart(1234)) AS foo (a, b);>
> This is the essence of the function:
> --------------------
> create procedure org_chart (
> param_id integer)
> returning
> integer,
> integer;> define i, k integer;
>
> select id, supervisor_id, "0" flg
> from employees
> where id = param_id
> into temp emps_0 with no log;>
> while ((select count(*) from emps_0 where flg = "0") > 0)
>
> select employees.id,
> employees.supervisor_id
> from employees,
> emps_0
> where employees.supervisor_id = emps_0.id
> and emps_0.flg = 0
> into temp save_emps with no log;>
> update emps_0 set flg = "1";>
> insert into emps_0
> (id, supervisor_id, flg)
> select id, supervisor_id, "0"
> from save_emps;>
> drop table save_emps;> end while
>
> foreach select id, supervisor_id into i, k from emps_0
> return i, k with resume;
> end foreach
> end procedure
> ----------------------------------
>
> The end goal of this is to output a table which represents a branch
> of the id/supervisor_id
> tree (that's why it's called org_chart -- organizational chart). I
> would vastly prefer a solution
> that works in SPL and works in SELECT statements, though 1) I do not
> want to have a
> permanent table for this and 2)
>
> Thanks for any help or suggestions!
>
> Sincerely,
> Stephen
The problem is that you are calling the SP from a select statement - and this
requires that your SP doesn't do any DML. What's the problem with forgetting
about the select statement and just using a cursor to execute the stored
procedure? Or, from dbaccess, a straight execute procedure org_chart(1234)?
--
Ciao,
Marco
______________________________________________________________________________
Marco Greco /UK /IBM Standard disclaimers apply!
Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm
On Feb 6, 2:20 pm, Marco Greco <m...@4glworks.com> wrote:
> The problem is that you are calling the SP from a select statement - and this
> requires that your SP doesn't do any DML. What's the problem with forgetting
> about the select statement and just using a cursor to execute the stored
> procedure? Or, from dbaccess, a straight execute procedure org_chart(1234)?
I hate to admit this, but it's so we can use it in ACE reports. Ugh.
Cheers,
Stephen
Stephen wrote:
> On Feb 6, 2:20 pm, Marco Greco <m...@4glworks.com> wrote:
>> The problem is that you are calling the SP from a select statement - and this
>> requires that your SP doesn't do any DML. What's the problem with forgetting
>> about the select statement and just using a cursor to execute the stored
>> procedure? Or, from dbaccess, a straight execute procedure org_chart(1234)?
>
> I hate to admit this, but it's so we can use it in ACE reports. Ugh.
>
> Cheers,
> Stephen
Aha! have you considered using SQSL instead?
--
Ciao,
Marco
______________________________________________________________________________
Marco Greco /UK /IBM Standard disclaimers apply!
Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm
On Feb 6, 4:32 pm, Marco Greco <m...@4glworks.com> wrote: > Stephen wrote: > > I hate to admit this, but it's so we can use it in ACE reports. Ugh. > > Aha! have you considered using SQSL instead? Actually, our upstream vendor is going Java (JBoss stack) so we'll be slowly converting all our zillion reports to Jasper Reports format. But in the meantime... I need something generic enough to work in SPL, Perl DBI, ACE, (and Java). The recursive SPL is fine for this purpose, though I need to play with the recursion to see if I can get the results a little faster (it's definitely slow). Cheers, Stephen
>From: "Stephen" <swaters@luy.info> >Actually, our upstream vendor is going Java (JBoss stack) so we'll be > slowly converting all our zillion reports to Jasper Reports format. >But in >the meantime... I need something generic enough to work in SPL, >Perl DBI, ACE, (and Java). The recursive SPL is fine for this >purpose, >though I need to play with the recursion to see if I can get the >results a >little faster (it's definitely slow). > >Cheers, >Stephen > Recursion shouldn't be slow... But I'd consider doing this in java , sooner than later. Consider building your result set within a list and returning the list. That may make things easier. HTH, -G _________________________________________________________________ FREE online classifieds from Windows Live Expo ' buy and sell with people you know http://clk.atdmt.com/MSN/go/msnnkwex0010000001msn/direct/01/?href=http://expo.live.com?s_cid=Hotmail_tagline_12/06