Well, you seem to be right after all. :-) I tried your approach and
it works fine.
I'm using functions in part for security reasons and I'm not sure if
there are any implications. I guess I can still lock down the tables
and grant only execute rights on the functions. The fact that they
get called in a query probably doesn't matter.
In any case this seems like a bug in the driver, or in the
documentation at a minimum.
Thanks very much for your help.
On Sun, 05 Dec 2004 20:49:15 +0100, Thomas Hallgren
<thhal@mailblocks.com> wrote:
> Larry White wrote:
>
>
>
> >Thanks Thomas. I'll try it your way to see what happens, but
> >according to the Postgresql documentation, it should support callable
> >statements. I posted the relevent text from the JDBC section of the
> >online docs below:
> >
> ><quote>
> > PostgreSQL's JDBC driver fully supports calling PostgreSQL
> >stored functions.
> >
> > Example 31-4. Calling a built in stored function
> >
> > This example shows how to call a PostgreSQL built in
> >function, upper, which simply converts the supplied
> >string argument to uppercase.
> >
> > // Turn transactions off.
> > con.setAutoCommit(false);
> > // Procedure call.
> > CallableStatement upperProc = con.prepareCall("{ ? = call
> >upper( ? ) }");
> > upperProc.registerOutParameter(1, Types.VARCHAR);
> > upperProc.setString(2, "lowercase to uppercase");
> > upperProc.execute();
> > String upperCased = upperProc.getString(1);
> > upperProc.close();
> ><end quote>
> >
> >
> Ok, I did write AFAIK. Apparently my knowlegde didn't extend far enough :-)
>
> The JDBC driver must do some trick then, and make an attempt to convert
> what you wrote into what I suggested. With some risk of getting flogged,
> I must admin that I disagree with that implementation since the
> interface doc's for JDBC mandates that the CallableStatement is for
> Stored Procedures only. Patching it so that it can call functions will
> cause future problems once the backend really do implement stored
> procedures correctly.
>
> My advice would be to stick with prepared statements until that happens.
> I know for certain that it works.
>
> Regards,
> Thomas Hallgren
>
>