On Thu, 16 Aug 2018 at 13:54, Shay Rojansky <r...@roji.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