Re: CallableStatement PostgreSQL Error:A CallableStatement was executed with an invalid number of parameters

On 24.11.2011 06:13, JavaNoobie wrote:
> Im trying to write sample stored functions in postgresql and call them using
> the CallableStatement  offered by JDBC.
>
> Here's some my test code
> [code=java]
> 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());
> .....
> [/code]
> And my Stored function is of the form .
> [code=sql]
> CREATE FUNCTION getData(int) RETURNS SETOF db_consumer AS $$
>     SELECT * FROM db_consumer WHERE consumer_id = $1;
>   $$ LANGUAGE SQL;
> [/code]
>
> However, I'm getting the following error when I try to run the code .
>
> [code=java]
> org.postgresql.util.PSQLException: A CallableStatement was executed with an
> invalid number of parameters
>     at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:408)
>     at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:381)
>     at com.enzen.cis.dao.Updatetest.main(Updatetest.java:44)
>
> [/code]
>   Any idea on why this is happening would be appreciated.

Since it's a set-returning function, I think you need to execute it like
a query, instead of using CallableStatement. Something like:

pstmt = conn.prepareStatement("SELECT getData(?)");
pstmt.setInt(1, 5);
rs = pstmt.executeQuery();

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com