callable statement cannot work when call stored procedure @postgres84

From: Ying-Wen Chen <lightlycat(at)gmail(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: callable statement cannot work when call stored procedure @postgres84
Date: 2009-10-06 12:29:29
Message-ID: 272d36040910060529n7325a1ces68cb3a3d025d9149@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

hi postgres team,

I try to call stored function with Postgres84 doc and jdbc but always
fails! *

First of ALL ,my stored procedure code's as follow:*
(It returns recordset when I run "select t.* from fn_getmsgobj(98); at
pgAdmin tool, so I think procedure is fine)
CREATE OR REPLACE FUNCTION fn_getmsgobj(user_id integer)
RETURNS setof dandelion_vermouth AS
$BODY$
declare rs record;
BEGIN
create temp table temp_follower as
select *
from dblink('host=192.168.2.143 dbname=GmmyI_Angler01 user=postgres
password=111111', 'select following_user_id , user_id from angler_playmate')
as t1(following_user_id integer, host_user_id integer)
where host_user_id = user_id;

FOR rs IN select b.ser_id, b.host_user_id,
b.to_user_id,b.content_type,b.content,b.create_time
from dandelion_vermouth as b,
(select a.host_user_id , max(a.ser_id) as ser_id ,max(a.create_time)
from dandelion_vermouth as a, temp_follower as b where a.host_user_id in (
b.following_user_id, b.host_user_id) and a.to_user_id in(0,
b.host_user_id) group by a.host_user_id ) as a
where a.ser_id = b.ser_id ;
LOOP
RETURN NEXT refcursor;
END LOOP;
DROP table temp_follower;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION fn_getmsgobj(integer) OWNER TO postgres;

*Second, i use java code to call procedure*.
In fact, I tried using hibernate to call stored procedure but it got error
all the time, so then alternative way to use JDBC...

session = HibernateUtil.getSessionFactoryS2().getCurrentSession();
tx = session.beginTransaction();
Connection connection = session.connection();
connection.setAutoCommit(false);
CallableStatement proc = connection.prepareCall("{ ? = call
fn_getmsgobj(?) }");
proc.setInt(2,98);
proc.registerOutParameter(1, Types.OTHER);
proc.execute();
ResultSet results = (ResultSet) proc.getObject(1);
while(results.next()){
System.out.println("okok");
}

After running programmer, error accours ...
org.postgresql.util.PSQLException: A CallableStatement was executed with an
invalid number of parameters
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:387)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:360)
at com.gmmyi.dao.DandelionDAO.getMsgObj(DandelionDAO.java:389)
at com.gmmyi.test.TestMessage.main(TestMessage.java:15)
[FATAL] 2009-10-06 20:09:52 com.gmmyi.dao.DandelionDAO - A CallableStatement
was executed with an invalid number of parameters
2009/10/6 下午 08:09:52 org.hibernate.impl.SessionFactoryImpl close

I am so preciate someone could help me solve this.

By the way, It is ok if I code this
ResultSet rs;
Connection connection = session.connection();
connection.setAutoCommit(false);
CallableStatement callableStatement =
connection.prepareCall("{call fn_getmsgobj(?) }");
callableStatement.setInt(1,user_id);
rs = callableStatement.executeQuery();
while(rs.next()){
Dandelion_vermouth dvm = new Dandelion_vermouth();

dvm.setSer_id(Integer.valueOf(rs.getString("ser_id")));

dvm.setHost_user_id(Integer.valueOf(rs.getString("host_user_id")));

dvm.setTo_user_id(Integer.valueOf(rs.getString("to_user_id")));

dvm.setContent_type(Short.valueOf(rs.getString("content_type")));
dvm.setContent(rs.getString("content"));
dvm.setCreate_time(rs.getTimestamp("create_time"));
}

It really confuses me why getObject(1) cannot work but the doc commends this
usage.

--
Best regard,

Olivia.Chen

Browse pgsql-jdbc by date

  From Date Subject
Next Message David Langton 2009-10-08 20:02:27 Connection Properties for FetchSize and Autocommit
Previous Message Robert Nicholson 2009-10-03 21:28:46 How do you batch insert and still obtain identity?