On 21 Dec 2004 at 21:06, Dmitry Yemanov wrote:

>2) Throw an error from SELECT if it's called for a non-selectable
>procedure.
>
>It will break a lot of existing databases and it's very wrong decision
>from the design POV.

Consider this SP:

CREATE PROCEDURE BOTH_WAY(X INTEGER) RETURNS (Y INTEGER) AS
BEGIN
Y = X+1;
IF (Y>1000) THEN
SUSPEND;
END;

SELECT from this procedure will return either empty result set or
one record, depending on input parameter. EXECUTE PROCEDURE will
always return a result, but it will be not the result the developer
may expect.

>3) Add an optional keyword (e.g. RETURNING ROWS) to the DDL syntax,
>store it in RDB$PROCEDURE_TYPE, change behaviour of EXECUTE PROCEDURE
>to be described as either select or execute depending on this value.
>100% compatibility + everything is controlled by the developer. But a
>bit complex to handle reliably.

And what if I want to call the procedure like above via both ways?

>4) Add CALL statement (BTW, this is a standard syntax for procedures,
>see SQL-99) which cares about output parameters and return either a
>single set of outputs or a cursor and hence will be described as
>either select or execute. But what should be a PSQL syntax to retrieve
>a cursor? Or will this be a client-only thingy?

If I'm not mistaken, you implemented full cursors' support for PSQL
in 2.0 including DECLARE CURSOR, FETCH and so on. May CALL return a
CURSOR?

>Did I miss anything?

What should return the procedure above if it is called with CALL?
Should a coder use isc_dsql_free_statement() with DSQL_close after
executing CALL statement? Should data in buffers be filled right
after call isc_dsql_execute() or isc_dsql_fetch() is required?
Jim said that current DSQL API is crap (probably he was in a hurry
designing it) but we have to live with it.
--
SY, Dimitry Sibiryakov.