Обсуждение: A CallableStatement was excecuted with an invalid number of parameters

Поиск
Список
Период
Сортировка

A CallableStatement was excecuted with an invalid number of parameters

От
Hery Fanomezantsoa
Дата:
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@mybiz.ch");   
        try {
            cs.execute();
            System.out.println(cs.getObject(1));
        } catch (PSQLException psqlex) {
            psqlex.printStackTrace();
        }