Обсуждение: CallableStatement.getParameterMetaData() throws exception for valid {call ...} statement
CallableStatement.getParameterMetaData() throws exception for valid {call ...} statement
От
Thomas Kellerer
Дата:
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); } }
Re: CallableStatement.getParameterMetaData() throws exception for valid {call ...} statement
От
Thomas Kellerer
Дата:
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.
Re: CallableStatement.getParameterMetaData() throws exception for valid {call ...} statement
От
Thomas Kellerer
Дата:
> 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