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?

user330315 · Accepted Answer · 2011-11-24 10:13:41Z

4

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

answered Nov 24, 2011 at 10:13

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

seeker Over a year ago

Hey, thanks for the reply,I've done that with preparedstatement before . Was just wondering how to use CallableStatement..

user330315 Over a year ago

I don't think this is possible. With a CallableStatement you would need an OUT parameter (e.g. a REF CURSOR) in order to process the result. But the way you create your function it does not define a refcursor. I think using a SELECT is the only option you have.

norbitheeviljester · Accepted Answer · 2011-11-24 10:52:38Z

2

You are trying to call a SETOFF function via a Callable Statement. That's not going to happen! You'll always get an error.

PostgreSQL's stored functions can return results in two different ways. The function may return either a refcursor value or a SETOF some datatype. Depending on which of these return methods are used determines how the function should be called.
Functions that return data as a set should not be called via the CallableStatement interface, but instead should use the normal Statement or PreparedStatement interfaces.

answered Nov 24, 2011 at 10:52

norbitheeviljester

9611 gold badge6 silver badges17 bronze badges

Collectives™ on Stack Overflow

CallableStatement PostgreSQL :Invalid number of parameters error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related