Subject Re: [Firebird-Architect] transient data sets and procedures
Author Ann W. Harrison
Vlad Horsun wrote:
> Can you please give us some examples of proposed syntax and how
> it can be used ? Without it i afraid i have a stupid questions ;-)

Sure, though I doubt your questions would be stupid.

In my application I create a transient data set called fat_cats. The
data source and restriction I use for fat cats will vary over time, but
the result will always be a data set with three fields, name varchar
(30), age integer, and salary integer. I've got a procedure called
fattest_cats that takes the transient data set and returns a subset
selected on health records. From the application, I use that procedure
to refine the definition of fat_cats.

Here's the procedure definition

create procedure fattest_cats (counter integer)
returns (name varchar (30), age integer, salary integer)
declare fatter_cats transient data set
(name varchar (30), age integer, salary integer);
for select first :counter, f.age, f.salary
from fat_cats f
join health_records hr
on ( = and f.age = hr.age)
order by hr.weight descending
into :name, :age, :salary
do begin

Here's a segment of application pseudo code..

select, p.age, p.salary
from people p
where p.salary > 100000
yielding fat_cats;

select, fc.age, fc.salary
from fat_cats (5) yielding fat_cats;


What actually happens here? Normally, when a procedure references
something that ought to be a table, the reference is checked against the
system tables at procedure creation. If there is a "declare <x>
transient data set" in the declarations section of the procedure, that
definition is used in place of a metadata lookup. The blr is generated
as if the declaration were an actual table.

When the procedure is first invoked and compiled, it will return an
error if the invoking connection does not have a transient data set
defined that corresponds to the declaration. If the data set is
defined, the execution structures will reference that definition through
the connection.

Subsequent invocations of the procedure by different connections will
have the same result - error if there is no pre-defined transient data
set, otherwise reference through the connections definition.

I'm sure I've left out, ignored, or overlooked a lot of stuff...