I have problem with mapping store procedure which has a cursor or loop in it. Result list has always only one element. Do I need something else in my configuration in order to make it work?
Database is MySql
Let's say we have this simple stored procedure


DROP PROCEDURE IF EXISTS .abc;
CREATE PROCEDURE abc()
BEGIN
DECLARE a date DEFAULT now();
DECLARE b date DEFAULT Date(DATE_ADD(now(),INTERVAL 4 DAY));
test_while: WHILE (a<b) do
Select a,b;
Set a=Date(DATE_ADD(a,INTERVAL 1 DAY));
END WHILE test_while;

END;

if you map it in hibernate

<sql-query name="test_st_proc" callable="true">
<return-scalar column="a" />
<return-scalar column="b" />
{call abc() }
</sql-query>

and you have a method in dao like this

public void getAbcDates(){

Session session = null;
List<Abc> data = null;
try {
SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
session = sessionFactory.openSession();
Query query = session.getNamedQuery("test_st_proc");
query.setResultTransformer(Transformers.aliasToBean(domain.Abc.class));
data = query.list();
} catch (Exception e) {
System.out.println(e.getMessage());
} finally {
// Actual contact insertion will happen at this step
}
System.out.println("Done List Size:" + data.size());

with JDBC it looks like this
CallableStatement cstmt =conn.prepareCall("call abc()");
boolean hadResults = cstmt .execute();
while (hadResults) {
rs = cs.getResultSet();
data.add(element)
hadResults = cs.getMoreResults();
}

this will return list correctly. Question is how I can make it work with hibernate?