Re: CallableStatement.getParameterMetaData() throws exception for valid {call ...} statement

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);
       }
    }