Обсуждение: CallableStatement.getParameterMetaData() throws exception for valid {call ...} statement

Hello,

I just noticed the following:

Using this sample function from the manual:

   CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
   BEGIN
       sum := x + y;
       prod := x * y;
   END;
   $$ LANGUAGE plpgsql;


When calling this through JDBC, the following works without problems:

     cstmt = connection.prepareCall("{call sum_n_product(?,?,?,?)}");
     cstmt.registerOutParameter(3, Types.INTEGER);
     cstmt.registerOutParameter(4, Types.INTEGER);
     cstmt.setInt(1, 2);
     cstmt.setInt(2, 5);
     cstmt.execute();
     int x1 = cstmt.getInt(3);
     int x2 = cstmt.getInt(4);

     System.out.println("x1: " + x1);
     System.out.println("x2: " + x2);

However when calling

      ParameterMetaData meta = cstmt.getParameterMetaData();

after prepareCall() the following exception is thrown:

    org.postgresql.util.PSQLException: ERROR: function sum_n_product(unknown, unknown, unknown, unknown) does not exist
       Hint: No function matches the given name and argument types. You might need to add explicit type casts.
       Position: 15

Casting everything to integer ( "{call sum_n_product(?::int,?::int,?::int,?::int)}" ) produces the same error (except
thatthe parameter list shows integer in the exception message). 

This looks like a bug to me. Why should getParameterMetaData() throw an exception if the call is valid and working?

Driver version is 9.4-1200
Postgres version is 9.4.2
Java version is 1.7.0_55

Regards
Thomas

Thomas Kellerer wrote:
> Hello,
>
> I just noticed the following:
>
> Using this sample function from the manual:
>
>    CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
>    BEGIN
>        sum := x + y;
>        prod := x * y;
>    END;
>    $$ LANGUAGE plpgsql;
>
>
> When calling this through JDBC, the following works without problems:
>
>      cstmt = connection.prepareCall("{call sum_n_product(?,?,?,?)}");
>      cstmt.registerOutParameter(3, Types.INTEGER);
>      cstmt.registerOutParameter(4, Types.INTEGER);
>      cstmt.setInt(1, 2);
>      cstmt.setInt(2, 5);
>      cstmt.execute();
>      int x1 = cstmt.getInt(3);
>      int x2 = cstmt.getInt(4);
>
>      System.out.println("x1: " + x1);
>      System.out.println("x2: " + x2);
>
> However when calling
>
>       ParameterMetaData meta = cstmt.getParameterMetaData();
>
> after prepareCall() the following exception is thrown:
>
>     org.postgresql.util.PSQLException: ERROR: function sum_n_product(unknown,
> unknown, unknown, unknown) does not exist
>        Hint: No function matches the given name and argument types. You might
> need to add explicit type casts.
>        Position: 15
>
> Casting everything to integer ( "{call
> sum_n_product(?::int,?::int,?::int,?::int)}" ) produces the same error (except
> that the parameter list shows integer in the exception message).
>
> This looks like a bug to me. Why should getParameterMetaData() throw an
> exception if the call is valid and working?
>
> Driver version is 9.4-1200
> Postgres version is 9.4.2
> Java version is 1.7.0_55
>
> Regards
> Thomas


Hello,

Seems to work fine, maybe I have it wrong?

danap.

private void testCallableStatement2(Connection connection)
    {
       // Method Instances
       String sqlStatementString;
       Statement sqlStatement;
       CallableStatement cstmt;
       ParameterMetaData meta;

       try
       {
          // Setup a connection statement.
          sqlStatement = connection.createStatement();

          // Create Function.

          //sqlStatementString = "DROP FUNCTION sum_n_product(int, int, OUT int,
OUT int)";
          //sqlStatement.execute(sqlStatementString);

          sqlStatementString = "CREATE OR REPLACE FUNCTION sum_n_product(x int,
y int, OUT sum int, OUT prod int) AS $$"
                + " SELECT (x + y) AS sum, (x * y) AS prod; $$ LANGUAGE SQL;";
          System.out.println(sqlStatementString);
          sqlStatement.execute(sqlStatementString);

          // Execute Function.

          cstmt = connection.prepareCall("{call sum_n_product(?,?,?,?)}");

          cstmt.registerOutParameter(3, Types.INTEGER);
          cstmt.registerOutParameter(4, Types.INTEGER);
          cstmt.setInt(1, 2);
          cstmt.setInt(2, 5);
          cstmt.execute();

          meta = cstmt.getParameterMetaData();
          System.out.println("Parameter Count: " + meta.getParameterCount());

          int x1 = cstmt.getInt(3);
          int x2 = cstmt.getInt(4);

          System.out.println("x1: " + x1);
          System.out.println("x2: " + x2);


          // Drop Function.
          sqlStatementString = "DROP FUNCTION sum_n_product(int, int, OUT int,
OUT int);";
          System.out.println(sqlStatementString);
          sqlStatement.execute(sqlStatementString);

          cstmt.close();
          sqlStatement.close();
       }
       catch (SQLException sqle)
       {
          System.out.println("SQL Exeception" + sqle);
       }
    }

dmp wrote on 30.05.2015 21:50:
> Seems to work fine, maybe I have it wrong?
>

interesting, your code indeed works.

After playing around with it, the difference is that I call getParameterMetaData() *before* calling
registerOutParameter().

This works:

       cstmt = connection.prepareCall("{call sum_n_product(?,?,?,?)}");

       cstmt.registerOutParameter(3, Types.INTEGER);
       cstmt.registerOutParameter(4, Types.INTEGER);
       cstmt.setInt(1, 2);
       cstmt.setInt(2, 5);

       cstmt.execute();

       meta = cstmt.getParameterMetaData();

This also works:

       cstmt = connection.prepareCall("{call sum_n_product(?,?,?,?)}");

       cstmt.registerOutParameter(3, Types.INTEGER);
       cstmt.registerOutParameter(4, Types.INTEGER);

       meta = cstmt.getParameterMetaData();

       cstmt.setInt(1, 2);
       cstmt.setInt(2, 5);

       cstmt.execute();


This does NOT work:

       cstmt = connection.prepareCall("{call sum_n_product(?,?,?,?)}");

       meta = cstmt.getParameterMetaData(); -- right after preparing the statement!

       cstmt.registerOutParameter(3, Types.INTEGER);
       cstmt.registerOutParameter(4, Types.INTEGER);
       cstmt.setInt(1, 2);
       cstmt.setInt(2, 5);

       cstmt.execute();

So getParameterMetaData() seems to only work after all OUT parameters have been registered (I also tried calling it
afterthe first registerOutParameter() - failed as well). 

So how can I retrieve the information about the parameters if I can't call getParameterMetaData() before registering
theparameters (which I try to detect by calling that method). The JDBC JavaDocs do not mention such a restriction. 


Thomas

Thomas Kellerer wrote:
> dmp wrote on 30.05.2015 21:50:
>> Seems to work fine, maybe I have it wrong?
>>
> ~
 > ~
 > ~
> interesting, your code indeed works.
>
> After playing around with it, the difference is that I call
> getParameterMetaData() *before* calling registerOutParameter().
>
>
> So getParameterMetaData() seems to only work after all OUT parameters have been
> registered (I also tried calling it after the first registerOutParameter() -
> failed as well).
>
> So how can I retrieve the information about the parameters if I can't call
> getParameterMetaData() before registering the parameters (which I try to detect
> by calling that method).The JDBC JavaDocs do not mention such a restriction.
 >
 >
 > Thomas

According to the Java 7 API it seems this interface is mainly associated with
PreparedStatements and lucky to get any information from a CallableStatement.

---------------
public interface ParameterMetaData
extends Wrapper

An object that can be used to get information about the types and properties
for each parameter marker in a PreparedStatement object. For some queries
and driver implementations, the data that would be returned by a
ParameterMetaData object may not be available until the PreparedStatement
has been executed.

Some driver implementations may not be able to provide information about
the types and properties for each parameter marker in a CallableStatement object.
---------------

 From the context of your question, it seems what you are looking for is the
information about the function, parameters, etc. When I first looked at this
I searched the database for the newly created sum_n_product() function to
find information about it. It seemed to be abscured. Perhaps that information
stored in the database is the answer to your question rather then this interface.

danap.

> According to the Java 7 API it seems this interface is mainly associated with
> PreparedStatements and lucky to get any information from a CallableStatement.

Well a CallableStatement *is* a PreparedStatement

> From the context of your question, it seems what you are looking for is the
> information about the function, parameters, etc. When I first looked at this
> I searched the database for the newly created sum_n_product() function to
> find information about it. It seemed to be abscured. Perhaps that information
> stored in the database is the answer to your question rather then this interface.

Yes, that's what I'm doing when getParameterMetaData() fails. But it makes things a bit more complicated
This is part of my SQL client that supports multiple DBMS and offers a DBMS independent way to call procedures that
have OUT parameters. 

I was just curious. Many JDBC drivers don't even implement getParameterMetaData() but the error message in Postgres'
exceptionis misleading because there is such a function - and the fact that it works _after_ registering an OUT
parametermakes it even more confusing. 

Thomas