Re: A CallableStatement was excecuted with an invalid number of parameters

On Tue, Nov 22, 2011 at 4:40 PM, Hery Fanomezantsoa <rhfano(at)gmail(dot)com>wrote:

> 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();
>> }
>>
>