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

Hi Heikki,
Yes, I figured that out only later . I'm new to CallableStatement so just trying to learn by experimenting.
Thanks for the reply.!
----- Original Message -----
From: Heikki Linnakangas-3 [via PostgreSQL]
To: JavaNoobie
Sent: Friday, November 25, 2011 1:11 AM
Subject: 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

--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

------------------------------------------------------------------------------

If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/CallableStatement-PostgreSQL-Error-A-CallableStatement-was-executed-with-an-invalid-number-of-parames-tp5019196p5021180.html
To unsubscribe from CallableStatement PostgreSQL Error:A CallableStatement was executed with an invalid number of parameters, click here.
NAML
<font Face='Arial' style='font-size:9pt'>This e-mail, and any attachmen
ts are strictly confidential and may also contain legally privileged informa
tion. It is intended for the addressee(s) only. If you are not the intended
recipient, please do not print, copy, store or act in reliance on the e-mail
or any of its attachments. Instead, please notify the sender immediately an
d then delete the e-mail and any attachments.

Unless expressly stated t
o the contrary, the views expressed in this e-mail are not necessarily the v
iews of Enzen Technologies (P) Limited or any of its subsidiaries or affilia
tes (Group Companies), and the Group Companies, their directors, officers an
d employees makes no representation and accept no liability for the accuracy
or completeness of this e-mail. You are responsible for maintaining your ow
n virus protection and the Group Companies do not accept any liability for v
iruses. Enzen reserves the right to monitor and review the content of all me
ssages sent to or from this e-mail address. Messages sent to or from this e-
mail address may be stored on the Enzen e-mail system.</font>

--
View this message in context: http://postgresql.1045698.n5.nabble.com/CallableStatement-PostgreSQL-Error-A-CallableStatement-was-executed-with-an-invalid-number-of-parames-tp5019196p5021828.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.