PostgreSQL JDBC - CallableStatement problem...?

Author

CallableStatement problem...?

Dave Minter

2005-04-27, 3:24 am

I'm puzzled. I'm trying to invoke a PG/plSQL function as a callable statement.
The function takes one parameter, inserts it into a table, and returns. Since
I'm not selecting anything and I have no (or rather, only a void) return
parameter, I expected to be able to call executeUpdate() on the CS to have this
take effect - but I get this error message:

org.postgresql.util.PSQLException:
A result was returned when none was expected.

If I call executeQuery, then it works perfectly (but that causes problems with a
3rd party tool I'm using which expects a pure-insertion sproc to be comfortable
with a call to executeUpdate).

Here's my schema and test harness; I'm praying fervently that I'm just doing
something stupid here...

-- Table to update
CREATE TABLE FOO (
id int primary key
);

-- Function to do so
CREATE FUNCTION testInsertion(int) RETURNS void AS '
DECLARE
p_id ALIAS FOR $1;
BEGIN
INSERT INTO foo(id) VALUES (p_id);
RETURN;
END;
' LANGUAGE 'plpgsql';

/*
Simple test harness to exercise the above...
*/
public static void main(String[] argv)
throws Exception
{
String driver = "org.postgresql.Driver";
String url = "jdbc:postgresql://localhost/foo";
String username = "foo";
String password = "bar";

Class.forName(driver);
Connection c = DriverManager. getConnection(url,us
ername,password);

c. setAutoCommit(false)
;

String call = "{call testInsertion(?)}";
CallableStatement cs = c.prepareCall(call);
cs.setInt(1,42);

try {
// Works
//cs.executeQuery();

// Fails
cs.executeUpdate();
c.commit();
} catch ( SQLException e ) {
e.printStackTrace();
while( (e = e.getNextException()) != null ) {
e.printStackTrace();
}
c.rollback();
}
}

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Kris Jurka

2005-04-29, 7:25 am

On Tue, 26 Apr 2005, Dave Minter wrote:

>
> I'm puzzled. I'm trying to invoke a PG/plSQL function as a callable
> statement. The function takes one parameter, inserts it into a table,
> and returns. Since I'm not selecting anything and I have no (or rather,
> only a void) return parameter, I expected to be able to call
> executeUpdate() on the CS to have this take effect - but I get this
> error message:
>
> org.postgresql.util.PSQLException:
> A result was returned when none was expected.
>
> If I call executeQuery, then it works perfectly (but that causes
> problems with a 3rd party tool I'm using which expects a pure-insertion
> sproc to be comfortable with a call to executeUpdate).

This is how the driver works, it replaces {call myfunc()} with SELECT *
FROM myfunc, which will always return a ResultSet. It seems we could
special case CallableStatements version to expect and ignore a result when
executeUpdate is called.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Sponsored Links