Обсуждение: prepared statement call fails
I'm hoping someone with more experience can help me find a problem in
calling a function from Java. This is the first time I'm trying this
so I'm guessing it will be straightforward.
The function's signature is:
add_country(bigint, char(2), char(3), varchar(60))
RETURNS INTEGER '
It works fine if I call it from the command line, like so...
select add_country(124,'US', 'USA', 'United States of America');
In java, I call it using the following:
CallableStatement proc = null;
proc = connection.prepareCall("{ ? = call add_country( ? ? ? ? ) }");
proc.registerOutParameter(1, java.sql.Types.INTEGER);
proc.setInt(2, 124);
proc.setString(3, code2); // a two character java String
proc.setString(4, code3); // a three character java String
proc.setString(5, name); // a Java String
proc.execute();
with the result that I get the exception:
ERROR: syntax error at or near "'JA'" at character 33
(where JA is whatever I'm passing in my second in parameter - defined
as char(2).
I've inspected the input Strings just before calling to make sure
they're what I expected.
Is there a mapping published between JDBC statements and Postgresql
types? One possibility seems to be that either the char(x) types
don't like Strings or the setInt(x,y) doesn't work with BIGINTs, but
I'm just guessing.
Any help at all would be greatly appreciated. Thanks.
Larry White wrote:
> I'm hoping someone with more experience can help me find a problem in
> calling a function from Java. This is the first time I'm trying this
> so I'm guessing it will be straightforward.
>
> The function's signature is:
>
> add_country(bigint, char(2), char(3), varchar(60))
> RETURNS INTEGER '
>
> It works fine if I call it from the command line, like so...
>
> select add_country(124,'US', 'USA', 'United States of America');
>
> In java, I call it using the following:
>
> CallableStatement proc = null;
> proc = connection.prepareCall("{ ? = call add_country( ? ? ? ? ) }");
> proc.registerOutParameter(1, java.sql.Types.INTEGER);
> proc.setInt(2, 124);
> proc.setString(3, code2); // a two character java String
> proc.setString(4, code3); // a three character java String
> proc.setString(5, name); // a Java String
> proc.execute();
>
AFAIK, CallableStatement is for stored procedure calls. Stored
procedures are not yet implemented in PostgreSQL. It only has functions.
In order to call a function you need a select statement and a normal
PreparedStatement. Try this:
PreparedStatement stmt = connection.prepareStatement("select
add_country(?,?,?,?)");
stmt.setInt(1, 124);
stmt.setString(2, code2);
stmt.setString(3, code3);
stmt.setString(4, code4);
ResultSet rs = stmt.executeQuery();
if(rs.next())
result = rs.getInt(1);
Kind regards,
Thomas Hallgren
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>
On Sun, 05 Dec 2004 20:08:54 +0100, Thomas Hallgren
<thhal@mailblocks.com> wrote:
> Larry White wrote:
>
>
> > I'm hoping someone with more experience can help me find a problem in
> > calling a function from Java. This is the first time I'm trying this
> > so I'm guessing it will be straightforward.
> >
> > The function's signature is:
> >
> > add_country(bigint, char(2), char(3), varchar(60))
> > RETURNS INTEGER '
> >
> > It works fine if I call it from the command line, like so...
> >
> > select add_country(124,'US', 'USA', 'United States of America');
> >
> > In java, I call it using the following:
> >
> > CallableStatement proc = null;
> > proc = connection.prepareCall("{ ? = call add_country( ? ? ? ? ) }");
> > proc.registerOutParameter(1, java.sql.Types.INTEGER);
> > proc.setInt(2, 124);
> > proc.setString(3, code2); // a two character java String
> > proc.setString(4, code3); // a three character java String
> > proc.setString(5, name); // a Java String
> > proc.execute();
> >
> AFAIK, CallableStatement is for stored procedure calls. Stored
> procedures are not yet implemented in PostgreSQL. It only has functions.
> In order to call a function you need a select statement and a normal
> PreparedStatement. Try this:
>
> PreparedStatement stmt = connection.prepareStatement("select
> add_country(?,?,?,?)");
> stmt.setInt(1, 124);
> stmt.setString(2, code2);
> stmt.setString(3, code3);
> stmt.setString(4, code4);
> ResultSet rs = stmt.executeQuery();
> if(rs.next())
> result = rs.getInt(1);
>
> Kind regards,
> Thomas Hallgren
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
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
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
>
>
Thomas Hallgren wrote:
> AFAIK, CallableStatement is for stored procedure calls. Stored
> procedures are not yet implemented in PostgreSQL. It only has functions. [...]
However the JDBC driver supports the special case of '{ ? = call
f(?,?,?) }' where only the return value is an out parameter; it should
be remapping this to a SELECT automaticallly.
-O
Thomas Hallgren wrote:
> 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.
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.
> Patching it so that it can call functions will cause future problems once the backend really do implement stored
procedurescorrectly.
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.
I believe the current work on SPs is via a new SQL command (PERFORM?),
so I really can't see how this would cause future problems.
-O
Larry White wrote:
> proc = connection.prepareCall("{ ? = call add_country( ? ? ? ? ) }");
You are missing commas between your ? parameter placeholders.
Try:
proc = connection.prepareCall("{ ? = call add_country(?,?,?,?) }");
Otherwise your code looks OK.
It may be useful to turn on statement logging on the server side if you
have more problems -- the JDBC driver is transforming your query and it
can be useful to see exactly what's getting sent to the server.
-O
That did the trick. Thanks much.
On Mon, 06 Dec 2004 10:38:36 +1300, Oliver Jowett <oliver@opencloud.com> wrote:
> Larry White wrote:
>
> > proc = connection.prepareCall("{ ? = call add_country( ? ? ? ? ) }");
>
> You are missing commas between your ? parameter placeholders.
>
> Try:
>
> proc = connection.prepareCall("{ ? = call add_country(?,?,?,?) }");
>
> Otherwise your code looks OK.
>
> It may be useful to turn on statement logging on the server side if you
> have more problems -- the JDBC driver is transforming your query and it
> can be useful to see exactly what's getting sent to the server.
>
> -O
>
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
Thomas Hallgren wrote:
[...]
>> 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.
This is the whole point of using the standard escape syntax: when the
server syntax changes, we can handle it without client applications
needing to change.
Witness support for setFetchSize() evolving through "do nothing" through
"use DECLARE CURSOR and FETCH" through "use V3 portals". And probably
into "(DECLARE CURSOR WITH HOLD and V3 portals) or V4 portals" eventually.
At any rate, {?=call} support is existing functionality that people are
using. Removing support for it because you fear that future code that
hasn't been written yet will break it seems like a bad idea.
As I understand it, the approach being taken for "real" SPs currently is
to extend function behaviour to support the extra bits like OUT
parameters and returned resultsets, probably via extra SQL commands. So
mapping a subset of {call} to what existing functions *do* support seems
fine; we just tweak that mapping to reflect any new features on the
server side. There doesn't seem to be a big model change in the works,
which is all I'd be concerned about. Am I wrong here?
If you have some particular concerns about how the proposed SP syntax,
whatever it is, is going to break the JDBC driver, by all means raise
them.. but this seems like vague handwaving at the moment.
-O
Oliver Jowett wrote: > If you have some particular concerns about how the proposed SP syntax, > whatever it is, is going to break the JDBC driver, by all means raise > them.. but this seems like vague handwaving at the moment. I agree that abandoning the current support is a very bad idea. We all have to live with it now. I was surprised to see that it was implemented this way though. My guess is that it's uncommon. It's certanly doable although you will need to dynamically support both mappings once the real SP's arrive and also thoroughly explain some subtle differences in how auto-commit works one way if the underlying code calls a function and another way if it calls a stored procedure. I for one will stick with PreparedStatement for function calls and reserve CallableStatement for future SP's when using the client driver. This is the approach that I also take on my JDBC driver in PL/Java. Any attempt to use CallableStatement there will yield an exception informing the user that stored procedures are not yet implemented. I do not do it that way because I'm lazy. I do it because I think it's cleaner and closer to the spec. No I'll take down my hand and be silent :-) Regards, Thomas Hallgren
Hi, Thomas, On Mon, 06 Dec 2004 01:04:48 +0100 Thomas Hallgren <thhal@mailblocks.com> wrote: > Oliver Jowett wrote: > > > If you have some particular concerns about how the proposed SP syntax, > > whatever it is, is going to break the JDBC driver, by all means raise > > them.. but this seems like vague handwaving at the moment. > > I agree that abandoning the current support is a very bad idea. We all > have to live with it now. I was surprised to see that it was implemented > this way though. My guess is that it's uncommon. It's certanly doable > although you will need to dynamically support both mappings once the > real SP's arrive and also thoroughly explain some subtle differences in > how auto-commit works one way if the underlying code calls a function > and another way if it calls a stored procedure. The question is whether the new SP interface on the server will work for functions as well (as current functions are semantically a subset of what stored procedures can do). This would allow the drivers to always use the new way of calling. As I heard rumors that the stored procedures will be created by extending the current function capability, this sounds possible. So maybe we should try to influence the server hackers to go into this direction? Thanks, markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:schabios@logi-track.com | www.logi-track.com