From: | dmp <danap(at)ttc-cmc(dot)net> |
---|---|
To: | Thomas Kellerer <spam_eater(at)gmx(dot)net>, PostgreSQL JDBC <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | Re: CallableStatement.getParameterMetaData() throws exception for valid {call ...} statement |
Date: | 2015-05-30 19:50:15 |
Message-ID: | 556A1477.2050506@ttc-cmc.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
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);
}
}
From | Date | Subject | |
---|---|---|---|
Next Message | Rahul Khandelwal | 2015-06-01 10:05:40 | ERROR-org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend. |
Previous Message | Thomas Kellerer | 2015-05-30 08:21:52 | CallableStatement.getParameterMetaData() throws exception for valid {call ...} statement |