Subject Re: [Firebird-Architect] transient data sets and procedures
Author Vlad Horsun
> 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.

Thank you for the trust ;)

> 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)
> as
> declare fatter_cats transient data set
> (name varchar (30), age integer, salary integer);
> begin
> 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
> suspend;
> end
> end
> 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;

There you means "fattest_cats (5) ", right ?

Ok, i understand. You propose to map declared TDS name
to corresponding "real" (defined) TDS name. This is allows to create
and fill TDS outside of procedure and use it inside. This approach needs
run-time check for existence of TDS (this is ok) and structure matching
of declared and defined TDS. I think second requirement is not good.
Developer can wrote procedure with one structure of TDS in mind but
latter he can redefine structure of TDS and get run-time errors in SP.

In your example:

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

give us TDS with the same structure as in SP
but if we do latter

select *
from rdb$database
yielding fat_cats;

we'll got error in latter invocations of procedure fattest_cats.

Since TDS'es is like temporary variables i suspect that its names
often will be like T, T1, T2 etc and scenario above is very probable.

But there is at least one more problem.

I want to use TDS's primarily in my SP's to simplify hard calculations and
reuse partially calculated datasets in subsequent queries. Therefore i have
a big number of SP's which used TDS's. And that TDS's usually local to SP
in which it is declared and defined. Your approach force me to assign a
names to all of my "inner" TDS's in all my SP's :( And there are no help from
compiler at compilation time

> 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.

Ok, i think the same

> 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.

What if i want to declare TDS and define it in the same SP ?
Must i define it before SP compilation ? Or you said about procedure's
request compilation ?