Mapeando Stored Procedure Hibernate

Front-end
#1

Olá,

Estou precisando realizar o mapeamento de um Stored Procedure através do Hibernate.

Procedure.hbm.xml

<hibernate-mapping>

    <sql-query name="sp_hall_login" callable="true">
        <return alias="usuarioBanco" class="br.bean.UsuarioBanco">
            <return-property name="nome" column="name"/>
            <return-property name="senha" column="password"/>
	</return>
	{ call sp_hall_login( ?,? ) }
    </sql-query>

</hibernate-mapping>

DAO

            List list = sessao.getNamedQuery("sp_hall_login").
            setParameter(0,pUsuario.getNome()).
            setParameter(1,pUsuario.getSenha()).list();
            
            Iterator it = list.iterator();
            UsuarioBanco user;
            while(it.hasNext()) {
                user = (UsuarioBanco) it.next();
                System.out.println("Nome: "+user.getNome());
                System.out.println("Senha: "+user.getSenha());
            }

Dá o seguinte erro

Erro: java.lang.UnsupportedOperationException: org.hibernate.dialect.HSQLDialect does not support resultsets via stored procedures
Exception in thread "main" java.lang.UnsupportedOperationException: org.hibernate.dialect.HSQLDialect does not support resultsets via stored procedures
	at org.hibernate.dialect.Dialect.registerResultSetOutParameter(Dialect.java:897)
	at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1574)
	at org.hibernate.loader.Loader.doQuery(Loader.java:661)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
	at org.hibernate.loader.Loader.doList(Loader.java:2150)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
	at org.hibernate.loader.Loader.list(Loader.java:2024)
	at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:117)
	at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1607)
	at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:121)
	at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:169)
	at br.dao.UsuarioDAO.logar(UsuarioDAO.java:47)
	at br.dao.UsuarioDAO.main(UsuarioDAO.java:123)

Alguem sabe como devo proceder?

Att.

#2 
org.hibernate.dialect.HSQLDialect does not support resultsets via stored procedures

dialeto errado, procure o dialeto correto para seu banco de dados.

#3

Mudei o dialeto para:

<property name="dialect">
            org.hibernate.dialect.SybaseDialect
</property>

E está ocorrendo o erro abaixo:

Hibernate: { call sp_hal_login( ?, ? ) }
Erro: org.hibernate.exception.SQLGrammarException: could not execute query
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
	at org.hibernate.loader.Loader.doList(Loader.java:2153)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
	at org.hibernate.loader.Loader.list(Loader.java:2024)
	at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:117)
	at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1607)
	at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:121)
	at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:169)
	at br.dao.UsuarioDAO.logar_2(UsuarioDAO.java:81)
	at br.dao.UsuarioDAO.main(UsuarioDAO.java:164)
Caused by: java.sql.SQLException: S0022: Invalid column name 'name'.
	at com.sybase.jdbc3.jdbc.ErrorMessage.raiseError(Unknown Source)
	at com.sybase.jdbc3.tds.TdsResultSet.findColumnByLabel(Unknown Source)
	at com.sybase.jdbc3.jdbc.SybResultSet.findColumn(Unknown Source)
	at com.sybase.jdbc3.jdbc.SybResultSet.getString(Unknown Source)
	at org.hibernate.type.StringType.get(StringType.java:18)
	at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:113)
	at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:102)
	at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:1088)
	at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:554)
	at org.hibernate.loader.Loader.doQuery(Loader.java:689)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
	at org.hibernate.loader.Loader.doList(Loader.java:2150)
	... 8 more

Agradecido

#4

No stack temos

Caused by: java.sql.SQLException: S0022: Invalid column name 'name'.

ou seja, o nome que voce mapeou no xml para o parametro da procedure não existe na definição dela no banco de dados :wink:

#5

Poderiam me explicar como faço para mapear uma procedure via xml??

Obrigado

#6