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