Re: Stored procedures and out parameters

On Thu, 16 Aug 2018 at 13:54, Shay Rojansky <roji(at)roji(dot)org> wrote:

> Peter,
>
> 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.
>>
>
> Am going to repeat some of Vladimir's responses here...
>
> I don't really know (or care much) about the history of how language
> database APIs evolved to where they are, I'm more concerned with what the
> introduction of stored procedures will do... The problem we're describing
> seems to go beyond JDBC or .NET. Looking at psycopg, for example, there's a
> callproc() function that internally translates to SELECT * FROM (
> http://www.postgresqltutorial.com/postgresql-python/call-stored-procedures/)
> - at the very least there are going to be some very confused users when
> callproc() becomes a way to only invoke functions, whereas calling
> procedures requires something else. I don't think there's anything really
> Microsoft-specific about any of this (except maybe in the history) - just
> like JDBC and psycopg, there's simply a single standard way in the database
> API for invoking server-side things, and not two ways.
>
> It's true that users will always be able to simply avoid the standard API
> altogether and do SELECT * FROM func() or CALL proc(), but it really isn't
> ideal to force users down this road, which once again, hurts portability
> and general adoption.
>
> Andres,
>
> > Are you actually suggesting we effectively drop procedure soupport?
>
> The ideal solution here is to allow functions to be invoked with CALL,
> rather than rolling back the entire feature (which obviously nobody wants).
> This would allow drivers to simply change their API implementation to
> translate to CALL instead of SELECT * FROM. I have no idea what the risk of
> that is, what it would entail etc. - I'm just expressing the driver writer
> perspective here with Vladimir. Hopefully some satisfactory solution can be
> found here.
>

I think this is the best solution. Without looking too deeply at the
details it seems to me that calling functions would require some wrapper
code around the function?

Regards,

Dave