On Thu, 17 Feb 2005 11:45:11 -0500, Ann W. Harrison wrote:

>
>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)
> as
> declare fatter_cats transient data set
> (name varchar (30), age integer, salary integer);
> begin
> for select first :counter f.name, f.age, f.salary
> from fat_cats f
> join health_records hr
> on (f.name = hr.name 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.name, p.age, p.salary
> from people p
> where p.salary > 100000
> yielding fat_cats;
>
> select fc.name, 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...

create procedure trans_export(counter integer)
as
declare export_trans transient data set
(name varchar (30), age integer, salary integer);
begin
for select e.name, e.date_born, e.salary_incr from
employee e inner join salary_mov s on
s.Id_Emp = e.Id_emp
where (<some condition>)
into :name, :date_born, :salary_incr
do begin
<do some transformations and denormalizations>
insert into export_trans .... values...
<do some more transformations>
insert into for_import ..... values ....
end
end

Could I create such a proc with the transient datasets?
A clarification: for_import is just a waiting table for a external
app to export data to another FB database........ If server-side
multi-db queries there wouldn't be for_import, it will the other
db's table directly.

I cannot expose real metadata - since it's not permitted by
customer.