Обсуждение: A CallableStatement was excecuted with an invalid number of parameters
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:
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:
CallableStatement cs = con.prepareCall("{ ? = call tmp_insert(?) }");con = DriverManager.getConnection("jdbc:postgresql://" + host + "/" + database,
username, password);
stmt = con.createStatement();
con.setAutoCommit(false);
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();
}