RE: How Stored Procedure should be called?

[roberto]

Hi Alexey.    >  In the iBATIS tests SP are called via QueryForObject method. Why? This > method will create unnecessary object, isn't it?   The 2 tests that use QueryForObject are passing in an object as a method parameter to have some property or hashtable value of that object be set to the new/updated db value as a result of the stored procedure.   >  If I call this procedure (and procedure raises an error) via > SqlMapper.Insert or QueryForObject methods it fails with exception "Cannot > widen from target type to primitive type" or something like that. I > couldn't > see "native" exception :(   I'm also experiencing this with the Oracle SP tests for OUT parameters.  Gilles just introduced a new resultMap result type attribute to specify a CLR type.  I think the same may be needed for parameter since a stored proc out parameter can be a "result." Oh, about the sp_executesql, I was oblivious to MS's SqlCommand.ExecuteReader change in .NET 1.1!  LOL!  Makes sense to me now.    From http://msdn.microsoft.com/library/default.asp?url="">   In the .NET Framework version 1.0, SqlCommand.ExecuteReader executes all commands in the context of the sp_executesql stored procedure. As a result, commands that affect the state of the connection (for example, SET NOCOUNT ON), only apply to the execution of the current command. The state of the connection is not modified for any subsequent commands executed while the connection is open.   In the .NET Framework version 1.1, SqlCommand.ExecuteReader only executes a command in the context of the sp_executesql stored procedure if the command contains parameters, as this provides a performance benefit. As a result, commands that affect the state of the connection, that are included in a non-parameterized command, modify the state of the connection for all subsequent commands executed while the connection is open.   In the .NET Framework version 1.1, NOCOUNT will remain ON for any subsequent commands executed while the connection is open. In the .NET Framework version 1.0, NOCOUNT is only ON for the current command execution.   This change can affect both the forward and backward compatibility of your application if you depend on the behavior of SqlCommand.ExecuteReader for either version of the .NET Framework.   Roberto     > -----Original Message----- > From: Alexey Boroday [mailto:[EMAIL PROTECTED] > Sent: Thursday, January 06, 2005 2:36 AM > To: ibatis-user-cs@incubator.apache.org > Subject: How Stored Procedure should be called? > > Hello > >  Explain me please how MS SQL Server Stored Procedures should be called: > via > QueryForObject or Update methods? > >  I'm creating object in runtime, fill its properties and want to save it > to > the database. To save I should call stored procedure that will inserts > object and returns object ID as output parameters. This procedure could > raise error if something goes wrong. > >  If I call this procedure (and procedure raises an error) via > SqlMapper.Insert or QueryForObject methods it fails with exception "Cannot > widen from target type to primitive type" or something like that. I > couldn't > see "native" exception :( >  If I call this procedure via SqlMapper.Update statement - all works fine > and I could see error from the procedure. > >  In the iBATIS tests SP are called via QueryForObject method. Why? This > method will create unnecessary object, isn't it? > > -- >  Good bye. > > > __________________________________________________________________________ > _ > > This communication is confidential and may be legally privileged. > If you believe you are not an intended recipient, please inform the > sender, > delete the e-mail and do not copy, print or use it or disclose it to > others. > You can inform the sender by replying to this e-mail or by telephone (+380 > 44 4906080). > >