Subject Re: [IB-Architect] Select procedures
Author Helen Borrie
At 10:58 AM 09-06-00 -0400, you wrote:

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

Jim, you KNOW we can't compete with Microsoft in contests like that!

>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

I understand that bit...
>or avoid trying to create objects with conflicting names.

... I don't understand that bit.

>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

According to a poster in a recent thread, he always uses suspend in FSD
constructs in exec stored procedures because he believes it's a way to
guarantee he gets a hit for every row processed. I can't see the point in
it, but apparently the compiler doesn't mind. Not a significant point,
just FYI.

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

Is this so? I don't declare output parameters for select procedures. I
use Delphi and I'm not familiar with the way you collect output from SPs in C.

>By artificially
>combining two different objects you get a beast that is not
>particularly good for either.

By that, do you mean the beast is not particularly good for interfacing to
in a standard way? Because an object interface needs to see a completely
unequivocal row of hooks?

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

Agreed; though it's not relevant to the exec form, is it?

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

So do I. It would be brilliant if, some day, we had a totally abstract
data store that could be surfaced as objects with base classes and
properties to replace all the RDB$Thingies, each object knowing how to get
into referential relationships with other objects of other classes; from
these classes you build the database with descendants carrying properties
and methods for the business rules, etc. etc. Hide the milk and cookies
stuff like generators, many of the insert and update triggers that we
laboriously copy and paste in scripts now, cascades, repetitive metadata
changes like altering constraints and enabling/disabling indexes,
heck! hide indexes and UDFs too! Your drivers wouldn't need to give a
damn about the internals of the database, would they? The whole API would
just be properties. Could you do that by June 30?

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

Yes, a design would be quite a good idea, really. <g>

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