hi,
I get problem when call store procedure at postgresql8.4, and try to solve for several days.
Pleas help me if you have any idea.
****** Dandelion_vermouth.hbm.xml ********
Code:
<sql-query name="getMsg_SP" callable="true">
<return alias="msg" class="com.gmmyi.pojo.Dandelion_vermouth">
<return-property name="ser_id" column="ser_id"/>
<return-property name="host_user_id" column="host_user_id"/>
<return-property name="to_user_id" column="to_user_id"/>
<return-property name="content_type" column="content_type"/>
<return-property name="content" column="content"/>
<return-property name="create_time" column="create_time"/>
</return>
{? = call fn_getmsgobj(?)}
</sql-query>
****** Dandelion_vermouth.java ********
Code:
public class Dandelion_vermouth {
public static final String SELECT_MESSAGE_OBJECTS = "getMsg_SP";
public Dandelion_vermouth(){}
private int ser_id;
private int host_user_id;
private int to_user_id;
private short content_type;
private Timestamp create_time;
private String content;
.......
}
******* DandelionDAO.java ***************
Code:
List<Dandelion_vermouth> dvmList = session.getNamedQuery(Dandelion_vermouth.SELECT_MESSAGE_OBJECTS )
.setParameter(0, user_id)
.setResultTransformer(Transformers.aliasToBean(Dandelion_vermouth.class))
.setReadOnly(true).list();
****** store procedure @ postgres84 **********
Code:
CREATE OR REPLACE FUNCTION fn_getmsgobj(user_id integer)
RETURNS SETOF dandelion_vermouth AS
$BODY$
DECLARE rs RECORD;
BEGIN
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 rs;
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION fn_getmsgobj(integer) OWNER TO postgres;
*******************************************************************
I get the error as follow,
Exception in thread "main" java.lang.IllegalArgumentException: callable not yet supported for native queries
at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:171)
at org.hibernate.impl.SessionImpl.executeNativeUpdate(SessionImpl.java:1190)
at org.hibernate.impl.SQLQueryImpl.executeUpdate(SQLQueryImpl.java:357)
at com.gmmyi.dao.DandelionDAO.getMessageObj(DandelionDAO.java:250)
at com.gmmyi.test.TestMessage.main(TestMessage.java:29)
Could anyone help me?
Login
FAQ
Search