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

The problem is that "{call <procedure name> [param]}" works for every
other JDBC driver, except for Firebird, because Firebird must use
SELECT. So Firebird is not fully Java compliant. The driver transforms
"{call}" to EXEC or SELECT, but because there is no way to actually
know if it should be either one, THAT is the problem.

Now, IF the driver gets an empty resultset, how do you know that if this
is correct (as in: the procedure really returns an empty resultset) or if
this is an executable procedure??

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

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com