Re: prepared statement call fails

Oliver Jowett wrote:

> Please explain the difference between a PostgreSQL value-returning
> function and a stored procedure that returns a value and takes no OUT
> parameters.
>
> The {? = call ...} syntax is standard. I don't see any reason not to
> map it to PostgreSQL functions for the cases that make sense.

What will the JDBC driver do when PostgreSQL implements real stored
procedures? If it maintains the current mapping for this syntax, it will
not be able to call real procedures. If the mapping is changed to
accomodate PERFORM, all current code that relies on this behavior will fail.

There are also other differences. A CallableStatement should be prepared
to accept multiple result set's as a return value, a CallableStatement
might have multiple OUT parameters, and the auto commit semantics are
different since stored procedures are allowed to start and end top level
transactions.

> If the server makes changes that are not backwards-compatible, of
> course old drivers are going to break. But a) those sort of changes
> are rare and b) the same argument holds true for *everything* in the
> driver.

In this case, the server will implement something that wasn't there
before so there will not be any compatibility breakage. But since the
JDBC driver use CallableStatement for something that it wasn't intended
for, the special mapping it performs will become a problem now that the
real thing exists.

Then again, perhaps the behavior can be controlled by consulting the
system tables and map according to that? It will take a roundtrip extra
to figure out on the first call but that shouldn't be to big a deal.

Regards,
Thomas Hallgren