Hi All,

Can table procedures (or row producing procedures) use the execute procedure statement?

Unless I'm doing something wrong (which is odds on) it appears that the procedure can be defined with an execute procedure call but the attempt to execute it will fail with:
E_QE0310 Unsupported procedure statement type: CALL

drop procedure trial_date_cast;
\p\g
create procedure trial_date_cast(
a_string varchar(40) not null not default
)
as declare
int_date ingresdate not null not default;
begin
int_date = date(:a_string);
return iierrornumber;
end;
\p\g
drop procedure is_date;
\p\g
create procedure is_date(
a_string varchar(40) not null not default
)
result row myrow (is_date integer not null)
as declare
int_string varchar(40) not null not default;
a_date integer not null not default;
begin
for select :a_string into :int_string
do
execute procedure trial_date_cast(a_string = :a_string) into :a_date;
return row (:a_date);
endfor;
end;
\p\g
declare global temporary table x_date(
a_string varchar(40) not null not default
) on commit preserve rows with norecovery;
insert into x_date(a_string) values('not a date');
insert into x_date(a_string) values('1/1/2011 11:13:45');
insert into x_date(a_string) values('1/11/2010');
insert into x_date(a_string) values('31/1/2002');
\p\g
select x.a_string,
select x.a_string, y.is_date
from x_date x, is_date(a_string = x.a_string) y
\p\g

Aha, just found it in the documentation. Row producing procedures (and hence Table Procedures) cannot call a procedure.

Marty

From: Martin Bowes [mailto:martin.bowes (AT) ctsu (DOT) ox.ac.uk]
Sent: 20 January 2012 09:39
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] Table procedures and execute procedure

Whoops, sent some partially edited code...

It should have been:

drop procedure trial_date_cast;
\p\g
create procedure trial_date_cast(
a_string varchar(40) not null not default
)
as declare
int_date ingresdate not null not default;
begin
int_date = date(:a_string);
return iierrornumber;
end;
\p\g
drop procedure is_date;
\p\g
create procedure is_date(
a_string varchar(40) not null not default
)
result row myrow (is_date integer not null)
as declare
int_string varchar(40) not null not default;
a_date integer not null not default;
begin
for select :a_string into :int_string
do
execute procedure trial_date_cast(:int_string) into :a_date;
return row (:a_date);
endfor;
end;
\p\g
declare global temporary table x_date(
a_string varchar(40) not null not default
) on commit preserve rows with norecovery;
insert into x_date(a_string) values('not a date');
insert into x_date(a_string) values('1/1/2011 11:13:45');
insert into x_date(a_string) values('1/11/2010');
insert into x_date(a_string) values('31/1/2002');
\p\g
select x.a_string, y.is_date
from x_date x, is_date(a_string = x.a_string) y
\p\g

Martin Bowes wrote:

Irksome, but better to know about it than not.

--
Roy

UK Ingres User Association Conference 2012 will be on Tuesday June 19 2012.
The latest information is available from www.uk-iua.org.uk.

On Fri, 20 Jan 2012 12:26:08 +0000 (UTC)
Roy Hann <specially (AT) processed (DOT) almost.meat> wrote:

Actually, it's a Good Thing (TM).

First, execution of a procedure can entail any number of actions. The
caller must be prepared to deal with any kind of return status.
Where in the SELECT syntax could you accept, much less handle that
status?

Second, SELECT shouldn't have side effects. To avoid them would
be to restrict the procedures that a table-producing procedure might
call to other table-producing procedures.

If table-producing procedures can call other table-producing
procedures, then the system is forced to keep track of which procedures
may call which others, and prevent updates to a called procedure that
change it from being SELECT-only. There's a forest of complexity in
there, and not much benefit, if any, to the user.

Better to follow Nancy Reagan's advice, and Just Say No. :-)

--jkl

James K. Lowden wrote:

Yeah, I'll buy that.

--
Roy

UK Ingres User Association Conference 2012 will be on Tuesday June 19 2012.
The latest information is available from www.uk-iua.org.uk.

There is a 'New York Asian Escort' (http://www.nyorientalescort.com)
wholeness about the person who has come to terms with his limitations,
who 'New York Escort' (http://www.nyorientalescort.com) has been brave
enough to let go of his unrealistic dreams and not feel like a failure
for doing so. There is a wholeness 'New York Asian Escorts'
(http://www.nyorientalescort.com) about the man or woman who has learned
that he or she is 'New York Escorts' (http://www.nyorientalescort.com)
to go through a tragedy and survive, he or she can lose someone and
still


--
zxc123