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
