Skip site navigation (1) Skip section navigation (2)

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

From: Hery Fanomezantsoa <rhfano(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: A CallableStatement was excecuted with an invalid number of parameters
Date: 2011-11-22 13:42:13
Message-ID: CAG7XqOKctaao_jUZ0qkYJwd0w0PN7ciFwhVF=2bF5jAHmRnYzw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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();
>>         }
>>
>

In response to

pgsql-novice by date

Next:From: Hery FanomezantsoaDate: 2011-11-22 13:48:04
Subject: A CallableStatement was excecuted with an invalid number of parameters
Previous:From: Hery FanomezantsoaDate: 2011-11-22 13:40:36
Subject: A CallableStatement was excecuted with an invalid number of parameters

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group