Subject Re: [Firebird-Architect] RFC: Please unify stored procedure execution
Author Martijn Tonies
Hello Nando,

> R> There is no flag in RDB$PROCEDURES system table telling whether
> R> procedure is selectable or not. Connectivity dirvers that are required
> R> to translate abstract procedure call syntax (for example {call ...}
> R> syntax in JDBC) cannot automatically generate appropriate SQL.
> FWIW you could look for output parameters in the SP declaration and
> use the "select" form if you find any. That's what FlameRobin does.

Huh? How should that work?

btw, I'm looking for a SUSPEND in Database Workbench and use
SELECT to "run" the procedure then.

> R> I see the need to unifying the way stored procedures are handled.
> That wouldn't hurt, but it should be made in a way that's consistent
> with the current syntax and semantics.
> R> a) provide a new SQL statement, for example CALL, that when applied to
> R> selectable procedures, returns complete result set (is equivalent to
> R> SELECT * FROM), when applied to non-selectable procedures returns a
> R> result set with single row corresponding to the RETURNS declaration.
> I don't think an additional verb is needed. Better "fix" execute
> procedure or select IMHO.
> R> b) Fix the PSQL compiler to add SUSPEND before exiting the
> R> non-selectable procedure if SUSPEND is not already contained in the
> R> procedure body (in other words, automatically convert non-selectable
> R> procedure into selectable). This allows using SELECT * FROM syntax for
> R> all types of procedures.
> That makes sense, also in Martijn's variation.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Upscene Productions