[hibernate-issues] [Hibernate-JIRA] Created: (HHH-3147) Reading resultsets from tables and stored procedures.

[Krashan Brahmanjara (JIRA)]

Reading resultsets from tables and stored procedures.
-----------------------------------------------------

                 Key: HHH-3147
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3147
             Project: Hibernate3
          Issue Type: Improvement
         Environment: 3.2.6ga
            Reporter: Krashan Brahmanjara


Hi

I see that Hibernate can't read a results from procedure if  results field names are not known.
For example on Informix  when procedure return results WITH RESUME all fields got name "(expression)"
and finally result is list of empty Objects.

I found that Hibernate try read all results by name  f.e.  rs.getInt("name")  but resultsets can be also read column byId
which is safe and probably faster. I think that reading byId can be great  improvement
for example  return-property  "columnId" would be solution
  <return-property name="idGrub" columnId="1"/>

BTW
InformixDialect.java need few lines of code to support stored procedures like below. It work.

   public int registerResultSetOutParameter(CallableStatement statement, int col) throws SQLException {
     return col;
  }
  public ResultSet getResultSet(CallableStatement ps) throws SQLException {
    boolean isResultSet = ps.execute();
     while (!isResultSet && ps.getUpdateCount() != -1) {
      isResultSet = ps.getMoreResults();
    }
  return ps.getResultSet();
} 


Test case for stored procedures

java code:
  es =  session.getNamedQuery("aa3_SP");
 result = es.list();
  for (int idx = 0; idx < result.size(); idx++) {
     Object  resultList =  result.get(idx);           // result return a list of empty Objects
 }
      		
named query :
<sql-query name="aa2_SP" callable="true">
     <![CDATA[{call aa2() }]]>
 </sql-query>

spl:
CREATE PROCEDURE aa2() RETURNING INTEGER, CHAR(10), CHAR(60), SMALLINT;
  DEFINE a_id INTEGER;
  DEFINE a_id2 CHAR(10);
  DEFINE a_id3 CHAR(60);
  DEFINE a_id4 SMALLINT;

  FOREACH                                                                      
    SELECT id, id2,id3,id4
    INTO a_id, a_id2, a_id3, a_id4
    FROM table
    RETURN a_id, a_id2, a_id3, a_id4 WITH RESUME;
  END FOREACH;
        
END PROCEDURE;
  
result:
set of records of [(exprssion),(exprssion),(exprssion),(exprssion)]

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira