Re: Stored procedures and out parameters

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.