Ching Liang Chin

Hi Will Way,
You must include the <return-property name="Id" column="dept_id"/> in between the <return> tag

For Example
<sql-query name="testCallableQuery" callable="true">
<return alias="proc" class="vo.GtJobs">
<return-property name="Id" column="dept_id"/>
<return-property name="name" column="dept_name"/>
<return/>
{ call erp.GetJob(?) }
</sql-query>

Hope it helps.

Thank you Mark, the sample code help alot and I am able to call the Oracle 9i stored procedule and function already.
Unfortunately, when I tried to call the stored procedure/function for MYSQL, it always give me the error as below, anyone got any idea what is wrong with the coding.

exception

org.apache.jasper.JasperException: could not execute query
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:370)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:362)


root cause

org.hibernate.exception.GenericJDBCException: could not execute query
org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
org.hibernate.loader.Loader.doList(Loader.java:2148)
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
org.hibernate.loader.Loader.list(Loader.java:2024)
org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:118)
org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1684)
org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:164)
mypackage.DepartmentDAO.departmentName(DepartmentDAO.java:40)
org.apache.jsp.index_jsp._jspService(index_jsp.java:65)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:362)

Below is the mysql stored procedure that I am trying to call

CREATE PROCEDURE `myDepartName`()
BEGIN
SELECT dept_id, dept_name FROM t_department;
END

Below is the setting in the Department.hbm.xml

...
<sql-query name="departmentName_SP" callable="true">
<return alias="dept" class="mypackage.Department">
<return-property name="Id" column="dept_id"/>
<return-property name="strDeptName" column="dept_name"/>
</return>
{ call myDepartName() }
</sql-query>

Below is the function used to call the stored procedure

public List departmentName() {
Session session = HibernateUtil.getSession();
List lstDept;

lstDept = session.getNamedQuery("mypackage.Department.departmentName_SP").list();

return lstDept;
}

Thanks in advanced.

I am also facing the same problem and don't know how actually it works. Will very appreciate if anyone can share any sample coding on how to call the stored procedure with the out parameter.