At 12:01 PM 6/9/00 +1000, Helen Borrie wrote:

>
>How does Jim feel about this complete crock? - the way to get a dataset
>returned from a SP in MSSQL -
>[crock snipped]
>
>

Helen, I do hope that you that you don't find the argument that because
Microsoft found a worse way to do something that anything else is justified?

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

crock?

>

InterBase has two types "stored procedures". One returns scalar
values and is invoked by "execute procedure". The other returns a
pseudo record stream and is invoked by "select ... from ... <proc>".
Other not explicitly forbidden, any give procedure can only give useful
results in one context.

The first problem is how the meta-data is described. The scalar
version is straightforward. The data stream version is syntactically
equivalent to a table -- used in a select, set of named columns,
shares name space with tables -- but isn't described as a table.
A tool that uses the ODBC SQLTables for the JDBC DatabaseMetaData.getTables
isn't going to see those procedures and consequently can't make them
available or avoid trying to create objects with conflicting names.

The stored procedure language is unnecessarily complicated by the
need to handle different things. The select procedures need a
"suspend" verb to function; execute procedures can't contain
"suspend." Worse, a more natural syntax for a select procedure
to return a row is "yield (value1,...,value-n)". But the comingled
languages and documentation constrain select procedures to return
values the same way execute procedures do. By artificially
combining two different objects you get a beast that is not
particularly good for either.

A feature lacking in both forms of stored procedures is the ability
to return an ordered collection of result sets, a feature supported
by JDBC. Neither form of the InterBase stored procedure has the
syntactic ability to support this capability.

InterBase has generally lead the migration towards procedural
encapsulation in databases with triggers, blob filters, user
defined functions, computed fields, and stored procedures. There
is pressure for expanded UDFs, additional trigger types, and security
plug-ins. How many types of user defined procedural objects do
we need? How many sub-languages will be required? How do the
objects interact? There's a lot of work to be done before we
straighten out the mess.

Personally, I think the right solution is to settle on a single
well-defined language with type-specific interface objects.

In any case, I would like to see a comprehensive design encompassing
UDFs, triggers, stored procedures, maybe filters, maybe agents
before we start twiddling with anything.



Jim Starkey