Procedure without return

sen.chi.jin · **Joined:** Thu Nov 01, 2007 4:17 pm **Posts:** 3

Hi,

I m try use procedure for insert in a N:N with a PK and XML Document but the code report a error ... becouse the named query don´t has a return value.

My nhibernate map file is:

Code:

<sql-query name="SQL.Procedure.Permissao.Menu">
<![CDATA[
   exec up_atualiza_permissao :menu :xml
]]>
</sql-query>

my code :

Code:

public void SetPermissao(XmlDocument xmlDoc)
      {
         ISession session = null;
         try
         {
            session = this.SessionFactory.OpenSession();
            IQuery spPermissao = session.GetNamedQuery("SQL.Procedure.Permissao.Menu");
            spPermissao.SetInt32("menu",this.Codigo);
            spPermissao.SetString("xml", xmlDoc.InnerXml);
         }
         catch(Exception)
         {
            throw;
         }
         finally 
         {
            session.Flush();
            session.Close();
         }
      }

procedure code:

Code:

EXEC sp_xml_preparedocument @idoc OUTPUT, @xml
   
   begin try
      begin tran
      delete usu_grupo where gru_id_grupo = @grupo     
      insert into permissao (gru_id_grupo, men_id_menu)
      SELECT    GRUPOID, USUARIOID
      FROM       OPENXML (@idoc, '/USUARIOS_GRUPO/USUARIO_GRUPO',1)
               WITH (GRUPOID  int,
                    USUARIOID int)
      commit tran
   end try
   begin catch
      if @@trancount > 0 rollback tran
      raiserror('Erro na atualização da tabela de grupos de usuários. Transação não-concluída.',16,1)
   end catch

what s wrong?

thanks

rmuti · **Joined:** Fri Nov 02, 2007 11:08 am **Posts:** 6

Your stored procedure has to return a result set. NHibernate will expect one and will try to open an IDataReader.

sen.chi.jin · **Joined:** Thu Nov 01, 2007 4:17 pm **Posts:** 3

And <return-scalar> don t work for this case? I m try use this but report a generic exception.

My named query:

Code:

<sql-query name="SQL.Procedure.Permissao.Menu">
    <return-scalar column="signal" type="Int32"/>
    <![CDATA[
      exec up_atualiza_permissao :menu :xml
        ]]>
</sql-query>

Stored Procedure:

Code:

EXEC sp_xml_preparedocument @idoc OUTPUT, @xml
   
   begin try
      begin tran
      delete usu_grupo where gru_id_grupo = @grupo     
      insert into permissao (gru_id_grupo, men_id_menu)
      SELECT    GRUPOID, USUARIOID
      FROM       OPENXML (@idoc, '/USUARIOS_GRUPO/USUARIO_GRUPO',1)
               WITH (GRUPOID  int,
                    USUARIOID int)
      commit tran
      [b]SELECT 1 AS signal[/b]
   end try
   begin catch
      if @@trancount > 0 rollback tran
      raiserror('Erro na atualização da tabela de grupos de usuários. Transação não-concluída.',16,1)
   end catch 

But don t work too ... :(

Thanks for help

karlchu · **Posted:** Tue Nov 06, 2007 12:54 pm

If you only need to call a stored procedure and won't need or care what it returns, then just call the stored proc with plain ADO.NET:

Code:

ITransaction tx = session.BeginTransaction()
IDbCommand cmd = session.Connection.CreateCommand;
// Set up the command here
tx.Enlist(cmd);
cmd.Execute();
tx.Commit();