Subject Re: [IB-Architect] Select procedures
Author Helen Borrie
At 01:30 PM 08-06-00 -0400, you wrote:
> > >At 10:32 AM 08-06-00 -0400, Greg Deatz wrote:
> > >
> > > (Quoting Jim..)
> > >>The design (not the concept) of the select form of the InterBase
> > >>stored procedures is, in my ever humble opinion, is complete crock.
> > >>It is, in fact, a computational form of a view and should have been
> > >>implemented under the aegis of the view mechanism. With named arguments
> > >>and appearing in rdb$relations, rdb$fields, and rdb$relation_fields
> > >>(and the respective ODBC/JDBC meta data calls) it would have been
> > >>easier to use, easier to implement, and much easier to wrap with
> > >>intelligent code.
> > >>
>I really *like* the select form of the IB stored proc. While I'm not sure what
>you would do for a "parameterized-view", I'm particularly fond of the ability
>to use a stored procedure to push and pull data simultaneously, and that I can
>"softly" compensate for bad optimizer plans--in other words, I don't have to
>explicitly say "plan", I can just write the proc as a series of
>*well-"planned"* queries.
>Of course, views, just like SPs, hide the mechanics of the underlying database
>which is nice, but getting rid of "select-procedures", in my mind, would be a
>terrible thing. Am I wrong? Aside from pure elegance, are there compelling
>reasons to leave select-procedures behind?

How does Jim feel about this complete crock? - the way to get a dataset
returned from a SP in MSSQL -
1. Create a temp table on disk to receive the output.
2. Declare variables to hold the fields for the output and any
intermediate values.
2. Create a cursor for the query that gets the fodder for the output.
3. Step through the cursor set, fetching a row at a time, process a row,
insert row into temp table
4. If needful of a UNION set, repeat 2 and 3

End of SP

Post-operative treatment:
Client traps errors (if any)
Client uses temp table (select * from temp table ORDER BY...)
Client deletes temp table.

There are crocks and then there are crocks.
After a year working around MSSQL crocks, I threw in the towel. I missed
FOR...SELECT...DO....SUSPEND like you couldn't believe.

Now, I'm amenable to some "new thing" that lets me do everything I can do
now with FSDS, like f.i.

Anything I can do now in an FSDS proc

plus, possibly
Anything reasonable I want to do in DDL
"Commit work" and carry on (nested transactions....)<g> nothing and leave well alone...

For the obtuse (such as I) could Jim give some clues as to why FSDS is a crock?


"Ask not what your free, open-source database can do for you,
but what you can do for your free, open-source database."