CallableStatement PostgreSQL :Invalid number of parameters error

Question

Im trying to write sample stored functions in postgresql and call them using the CallableStatement offered by JDBC.

Here's some my test code

Consumer bean =new Consumer();
CallableStatement pstmt = null;
try {
con.setAutoCommit(false);
String  query = "{ ? = call getData( ? ) }";
pstmt = con.prepareCall(query); 
 pstmt.registerOutParameter(1, Types.OTHER);
      pstmt.setInt(2,5);
      pstmt.execute(); // execute update statement
      bean=(Consumer)pstmt.getObject(1);
       System.out.println("bean"+bean.getConsumer_name());

And my Stored function is of the form .

CREATE FUNCTION getData(int) RETURNS SETOF db_consumer AS $$
 SELECT * FROM db_consumer WHERE consumer_id = $1;
$$ LANGUAGE SQL;

However, I'm getting the following error when I try to run the code .

org.postgresql.util.PSQLException: A CallableStatement was executed with an invalid    number of  parameters .

Any idea why this could be happening?

Answer 1

I don't think you need a CallableStatement as you should be able to run select * from getData(5) directly:

PreparedStatement pstmt = con.prepareStatement("select * from getData(?)")
pstmt.setInt(1,5);
ResultSet rs = pstmt.execute(); 
while (rs.next()) {
  System.out.println(rs.getString(1));
}