CallableStatement problem...? - Mailing list pgsql-jdbc

From Dave Minter
Subject CallableStatement problem...?
Date
Msg-id 20050426190737.mcn8ksk404cwwk4w@webmail.spamcop.net
Whole thread Raw
Responses Re: CallableStatement problem...?
List pgsql-jdbc
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,username,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();
      }
   }

pgsql-jdbc by date:

Previous
From: Mauricio Hernández Durán
Date:
Subject: ERROR: could not convert UTF-8 character 0x00ef to ISO8859-1 possiblesolution
Next
From: Dave Cramer
Date:
Subject: Re: JDBC Driver Modifies Calendar passed into setTimestamp()