callable statement cannot work when call stored procedure @postgres84

Поиск
Список
Период
Сортировка
От Ying-Wen Chen
Тема callable statement cannot work when call stored procedure @postgres84
Дата
Msg-id 272d36040910060529n7325a1ces68cb3a3d025d9149@mail.gmail.com
обсуждение исходный текст
Список 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

В списке pgsql-jdbc по дате отправления:

Предыдущее
От: Robert Nicholson
Дата:
Сообщение: How do you batch insert and still obtain identity?
Следующее
От: David Langton
Дата:
Сообщение: Connection Properties for FetchSize and Autocommit