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

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: A CallableStatement was excecuted with an invalid number of parameters
Date: 2011-11-22 13:40:36
Message-ID: CAG7XqOK2Mkgzhhm7fxKmNL_=+shmYDBN+jZ6sGSiX1+OAQ6q1A@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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 ttt()
> }");
>         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();
>         }
>

Responses

pgsql-novice by date

Next:From: Hery FanomezantsoaDate: 2011-11-22 13:42:13
Subject: Re: A CallableStatement was excecuted with an invalid number of parameters
Previous:From: Jaime CasanovaDate: 2011-11-22 05:17:31
Subject: Re: Grant privs on entire database, not table-by-table-by-table

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