Re: Stored procedures and out parameters

On 16/08/2018 00:50, Shay Rojansky wrote:
> >> Also another request by Vladimir and myself to consider allowing
> >> functions to be invoked with CALL, in order to provide a single way to
> >> call both procedures and functions - this is important as language
> >> database APIs typically have a single, database-independent way to
> >> invoke server-side code that does not distinguish between functions and
> >> procedures.
>
> > I am familiar with the Java {call} escape.  But I think it's pretty
> > useless.
>
>
> It would be good to understand why you think it's useless (am not
> familiar at all with JDBC, am genuinely interested).

I think this is all coming from Microsoft. The JDBC driver API was
modeled after the ODBC API, and the ODBC specification also contains the
{call} escape. Microsoft SQL Server is also the only SQL implementation
to handle this stored function/procedure stuff totally differently: They
only have procedures, but they return values, and they are invoked by an
EXEC command. (They don't support transaction control AFAIK.) The .NET
stuff is obviously also from Microsoft.

So from Microsoft's perspective, this makes some sense: They only have
one invokable object type, and their invocation syntax is different from
everyone else's. So they made a compatibility wrapper in their client
libraries.

Everyone else, however, has two invokable object types and standard ways
to invoke them. And they all seemingly faced this problem of how to jam
these two into this one hole provided by the JDBC spec and ended up with
slightly different, and incompatible, solutions.

I think, if you want to write a portable-sans-Microsoft JDBC
application, you can just run CALL or SELECT directly. If you want to
write something that is compatible with Microsoft, you can map {call} to
a function invocation as before, which is actually more similar to a
procedure in MS SQL Server.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services