New Posts New Posts RSS Feed: Stored Procedures - OUT parameters
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Stored Procedures - OUT parameters
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
jsobell View Drop Down
Groupie
Groupie
Avatar

Joined: 02-Apr-2009
Location: Australia
Posts: 80 		Post Options Post Options   Quote jsobell Quote  Post ReplyReply Direct Link To This Post Topic: Stored Procedures - OUT parameters
    Posted: 03-Aug-2010 at 12:41am
There doesn't seem to be proper support for OUT parameters on stored procedures.
EF4 generates templates with ObjectParameter types for parameters flagged as OUT, but DF creates these as Nullable<> types, meaning they can't be modified by the underlying code, and as such are effectively non-output!

Compare the context code for a stored procedure with parameter
    @retval INT OUTPUT
between EF4 native and DF and you'll see the problem.
We're currently converting all our SPs to return a row with the integers in, so it's not a show-stopper for us, but it's a bug that needs fixing :)
Back to Top
ting View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 27-Mar-2009
Location: San Francisco
Posts: 427 		Post Options Post Options   Quote ting Quote  Post ReplyReply Direct Link To This Post Posted: 03-Aug-2010 at 2:41pm
Thanks!  We'll take a look.
Back to Top
pk55 View Drop Down
Senior Member
Senior Member


Joined: 22-Jul-2009
Location: CA
Posts: 105 		Post Options Post Options   Quote pk55 Quote  Post ReplyReply Direct Link To This Post Posted: 08-Jun-2011 at 5:54pm
Any update on this problem?  
 
We're using 6.0.9 and output parms aren't being populated.  I can execute a function mapped from the stored procedure directly from LinqPad (we build a separate EF context project so that we can use our domain models in LinqPad) and the results show up but when executing the function from the IB generated code on the BOS (so it's just executed synchronously), the function executes, hits the db, returns but no values are in the parms. 
 
I've tried having the procedure both return any result sets and not return a result and and it doesn't make any difference.
I've also tried making the output parms nullable but that doesn't help either.
 
I'm not sure the fact that DevForce is making the parms NULLable is really the problem.  I changed the generated designer file to switch the 2 output parms to plain Int32  and Int64 (for my case) non-nullable and they still don't get populated.
 
I've had to switch to returning these as a result set row and just map them to a complex type which works just fine. And even with a result set coming back and me iterating over it, the output parms still aren't set.
Back to Top
pk55 View Drop Down
Senior Member
Senior Member


Joined: 22-Jul-2009
Location: CA
Posts: 105 		Post Options Post Options   Quote pk55 Quote  Post ReplyReply Direct Link To This Post Posted: 10-Jun-2011 at 9:20am

Any chance we can get an update on this?  I've got a lot of stored procedures to change if this bug isn't corrected.

:-(
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 10-Jun-2011 at 12:24pm
Hi pk55,
 
This isn't really a bug, we just don't yet support out parameters from stored procedures.   We've got a new release coming next week so won't be getting this working by then, but we can take a look at this for the following release, which would be 6.1.2 in late July.  Can you contact your accout executive about this, so we can determine the priority and timing?
Back to Top
pk55 View Drop Down
Senior Member
Senior Member


Joined: 22-Jul-2009
Location: CA
Posts: 105 		Post Options Post Options   Quote pk55 Quote  Post ReplyReply Direct Link To This Post Posted: 10-Jun-2011 at 12:29pm
Will this working in 6.1.2 depend in any way on EF 4.1 or would it work with EF4.0?
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 10-Jun-2011 at 12:35pm
There shouldn't be any dependency on EF 4.1, this is just something we haven't implemented.  We will be supporting EF 4.1 in a summer release though.
Back to Top
pk55 View Drop Down
Senior Member
Senior Member


Joined: 22-Jul-2009
Location: CA
Posts: 105 		Post Options Post Options   Quote pk55 Quote  Post ReplyReply Direct Link To This Post Posted: 10-Jun-2011 at 12:50pm
Thanks Kim. 
 
One of the main reasons to use an output parm is that EF doesn't send back the Stored Procedure's return code which is a huge failure on their part (at least in my opinion).  Since we've got an existing Windows-based product that has to integrate with the SL/WPF-browser product, we can't change the way those procedures work so either our .net code would have to ignore the SP return code (not really an option for error handling) or we'll write wrapper SPs that we call from the .net side that in turn call the real SP and were planning on stuffing the actual return code from the real stored procedures into the output parm of the wrapper.  If the output parms don't' bubble back up through DevForce, we'd have to create a complex type that encompasses both the return code from the SP and whatever result set it returns and always return a row even if there's a faillure so we can access the return code.  A kludge at best and doesn't really work if the SP maps instead to an entity set.
 
If DevForce supports the output parms, then at least the return code can be handled in a common way regardless of whether the returned set is mapped to an entity or a complex type.
 
So we'll proceed with using output parms and 'assume' you'll have this in sometime this summer.
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 10-Jun-2011 at 1:02pm
OK, but get in touch with Derick or Michael, since we need to get this prioritized with some other things planned for the next few releases.
Back to Top
pk55 View Drop Down
Senior Member
Senior Member


Joined: 22-Jul-2009
Location: CA
Posts: 105 		Post Options Post Options   Quote pk55 Quote  Post ReplyReply Direct Link To This Post Posted: 10-Jun-2011 at 1:04pm
Will do.
Back to Top
daw451 View Drop Down
Newbie
Newbie


Joined: 23-May-2011
Posts: 3 		Post Options Post Options   Quote daw451 Quote  Post ReplyReply Direct Link To This Post Posted: 29-Aug-2011 at 7:47am
Are output parameters supported yet? Are they support on async calls?
Back to Top
pk55 View Drop Down
Senior Member
Senior Member


Joined: 22-Jul-2009
Location: CA
Posts: 105 		Post Options Post Options   Quote pk55 Quote  Post ReplyReply Direct Link To This Post Posted: 29-Aug-2011 at 8:49am
Yes. 6.1.2.0 has support for output parms and they work on async calls (I've tested both with SQL Server 2008 r2).
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down