A CallableStatement was excecuted with an invalid number of parameters

From: Hery Fanomezantsoa <rhfano(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: A CallableStatement was excecuted with an invalid number of parameters
Date: 2011-11-23 06:08:21
Message-ID: CAG7XqOKuqdmdD0iyz2AjoPhHRvRDdBtPc=9on8aOZnyjPZk=Tg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

When I'm trying to call a postgres function inside java application, i got
this exception:

A CallableStatement was excecuted with an invalid number of parameters
>
I'm using postgresql-driver-8.3-607 and jdk1.7

Here's the postgres function:

CREATE OR REPLACE FUNCTION tmp_insert (text) RETURNS SETOF restype AS 'DECLARE
> adrs restype%rowtype;
> adrs_id bigint;
> mess_id record;
> adress text[];
> adrs_id_array bigint[];
> exp text;
> dest text[];
> sbj text;
> dt timestamp;
> adrs_dest record;
> adrs_id_tmp record;
> BEGIN
> adrs_id := id FROM adresses WHERE valeur = $1;
> FOR mess_id IN SELECT message FROM correspondants WHERE adresse = adrs_id
> LOOP
> adrs.exp := adresses.valeur FROM adresses WHERE adresses.id =
> (SELECT adresse FROM correspondants WHERE message = mess_id.message AND type = 1);
> adrs.sujet := valeur FROM contenu WHERE message = mess_id.message AND type = 1;
> adrs.date := m_date FROM messages WHERE id = mess_id.message;
> FOR adrs_id_tmp IN SELECT adresse FROM correspondants WHERE message = mess_id.message AND type = 2
> LOOP
> FOR adrs_dest IN SELECT adresses.valeur FROM adresses WHERE adresses.id = adrs_id_tmp.adresse
> LOOP
> adrs.dest := adrs_dest.valeur;
> RETURN NEXT adrs;
> END LOOP;
> END LOOP;
> END LOOP;
>
> END;' LANGUAGE "plpgsql"
>
>

and restype is CREATE TYPE restype AS (exp text, dest text, sujet text,
> date timestamp)
>

My calling java application is as following:

con = DriverManager.getConnection("jdbc:postgresql://" + host + "/"
> + database,
> username, password);
> stmt = con.createStatement();
> con.setAutoCommit(false);
> CallableStatement cs = con.prepareCall("{ ? = call tmp_insert(?)
> }");
>
cs.registerOutParameter(1, Types.OTHER);
>
cs.setString(2, "lool(at)mybiz(dot)ch");
> try {
> cs.execute();
> System.out.println(cs.getObject(1));
> } catch (PSQLException psqlex) {
> psqlex.printStackTrace();
> }
>

Browse pgsql-admin by date

  From Date Subject
Next Message Hariraman Jayaraj 2011-11-23 09:20:50 Reg: unexpected EOF on client connection
Previous Message PresleyDias 2011-11-23 04:28:53 Re: Postgres database creation using batch files