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

Hi All,
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.


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

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

----- Original Message -----

From: [hidden email]

To: [hidden email]

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

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-mailor 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 accuracyor 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.