Stored procedure

[petar banicevic, modificado hace 15 años.]

Dears,

I need to invoke a store procedure. Anyone has code example for this?

Thx.

[Banji O, modificado hace 15 años.]

public static List getMyStoreProc(String key) throws SystemException{
Session session = null;
try {
//open a new session

session = HibernateUtil.openSession("&myHibernateSessionFactory");

//pull out our query from inference_custom.xml
//TODO fix the reason why its not loading from the xml file.
String sql = "{call myStoredProc(?)}";
//String sql = CustomSQLUtil.get(GET_USERS);
SQLQuery q = session.createSQLQuery(sql);


q.addScalar("mycolumn", Hibernate.STRING);

QueryPos qPos = QueryPos.getInstance(q);

qPos.add(key);

return QueryUtil.list(q, HibernateUtil.getDialect(), -1, -1);


}
catch (Exception e) {
throw new SystemException(e);
}
finally {
//must have this to close the hibernate session.. if you fail to do this.. you will have alot of open session..
HibernateUtil.closeSession(session);
}

}

[petar banicevic, modificado hace 15 años.]

Thanks. This was fast!

[evan cheung, modificado hace 15 años.]

Hi,

I have a stored procedure that returns something, I tried to use your sample code but got some error:

ERROR [JDBCExceptionReporter:78] Cannot perform fetch on a PLSQL statement: next

Can you please tell me what I need to do to get it work?

thanks,
Evan

[Gouse Mohiddin, modificado hace 9 años.]

I am writing stored procedures in liferay using custom queries. how can i call procedure in liferay? i tried in 2 ways
1). i tried to get query from xml file but getting null at " CustomSQLUtil.get("id") ". How can i use this custom query in my finder class?

2) Directly i called procedure in my finder class like
query = session.createSQLQuery("call myprocedure(?,?,?)");
This case its showing syntax error at "call".

How can i resolve this? Can any one please help me how to call procedures in liferay.?

Thanks & Regards
Gouse Mohiddin. M

[meera prince, modificado hace 9 años.]

Hi
Use escape character try as follows

String sql ="call FeedBack_FeedBackSearch(?\,?\,?\,?\,?\,?)\;";


Regards,
Meera Prince

[Gouse Mohiddin, modificado hace 9 años.]

Hi,
In my xml i have written
<?xml version="1.0" encoding="UTF-8"?>
<custom-sql>
<sql
id="com.portaleventmanager.service.persistence.CommentFinderImpl.insertComment">
<![CDATA[
call spcomments(?,?,?,?);
]]>
</sql>
</custom-sql>


But showing " syntax error at or near "call" " error

[Gouse Mohiddin, modificado hace 9 años.]

Hi,

Thanx for your reply, It's working fine, But after calling SP, How can i execute? query.executeUpdate() is not working For SP.

static final String GET_USER_DATA =CommentFinderImpl.class.getName()+".insertComment";
Session session = null;
SQLQuery query = null;
try {
session = openSession();
String sql = CustomSQLUtil.get(GET_USER_DATA);
query = session.createSQLQuery(sql);
QueryPos pos=QueryPos.getInstance(query);
pos.add(description);
pos.add(parentid);
pos.add(eventid);
pos.add(userid);
query.executeUpdate();
Here I have used query.executeUpdate(), but its throwing error,