Subject Re: [Firebird-Architect] RFC: Please unify stored procedure execution
Author Helen Borrie
At 01:28 PM 21/12/2004 +0000, you wrote:

> > > Because there's no way to KNOW if a procedure is select-able. Is
> > > it?
> >
> > There is way - existence of outputs.
>Please use then example posted by Martijn - SELECT with procedure with
>outputs without SUSPEND in its body. You get empty result set.

What is the problem here? Is it that Jaybird cannot accept a SELECT
statement for CallableStatement? Or that it can, but it can't be taught
that if you get both SELECT and a FROM clause consisting of an identifier
and a bracketed tuple of arguments, then it is a call to a selectable
stored procedure?

If the programmer submits a SELECT statement for an executable SP, then
that is a programmer error.

> > Roman propose always return resultset, even if SP has no
> > SUSPEND. But this can break existing applications. Therefore there
> > are another proposition - use new special syntax for such call's.
> > Ok... what else from JDBC\ADO\OLEDB\etc don't fit in FB's SQL
> > dialect ? Will we make new syntax for every new client access
> > layer ?
> >
> > If JDBC will follow simple rule about how to call our SP's and
> > documented it i see no problem, sorry.
>The problem is that people have to change their applications to
>provide a hint to a driver to use SELECT.

Why would they have to change their applications? The driver should make
the proper decision according to the SQL statement supplied by the
application. It can mollycoddle the programmers by silently handling the
exceptions, e.g. 335544374 when it attempts to fetch after getting SQLCODE
100 and no tuple, and reparse and fix the statement itself; or it can
simply return the exception and force them to correct the statement, as
other interfaces do.