Midrange News for the IBM i Community


Posted by: GL Slone
Stored Procedure Call from .NET
has no ratings.
Published: 29 Jul 2014
Revised: 17 Sep 2014 - 951 days ago
Last viewed on: 24 Apr 2017 (2186 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Stored Procedure Call from .NET Published by: GL Slone on 29 Jul 2014 view comments(5)

I am having a problem returning data back to a .Net application from a ISeries (V7R1) stored procedure that calls a SQLRPGLE program. There is one "IN" parm and 2 "OUT" parms. When run native on the I, the procedure returns the proper data upon exit.

When invoked from the .NET app, blanks are returned to .NET.

The biggest problem is not beig able to view what comes into the SP from .NET, hen it comes in,  and how it interacts with the server going back.

when a valueis hard coded as LR is turned on, then it sends that value to .NET, leading me to believe the problem is in the IN variable, which is key to the return of a valid value.

Anyone aware of a way to verify parms coming in from .NET?

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Stored Procedure Call from .NET
Posted: 2 years 8 months 27 days 22 hours 12 minutes ago
Edited: Tue, 29 Jul, 2014 at 17:05:00 (1001 days ago)

You may want to have a look at how the PARAMETERS are defined in your stored procedure. If they're being called by native IBM i SQL, that's one thing, but if they're being called by .NET you may need to have some SQL standard parameter convention specified.  Typically this means "PARAMETER STYLE GENERAL"

I'm no Stored Procedure expert and in fact, I've only written SQL functions using RPG or SQL, never a Stored Procedure, but it sounds like a familiar story.

Posted by: Ringer
Premium member *
Comment on: Stored Procedure Call from .NET
Posted: 2 years 8 months 27 days 2 hours 37 minutes ago

Show us your CREATE PROCEDURE statement. Bob is right, the parmeter style affects the parms required. Are you able to successfully call the SP from iSeries Navigator? 

Chris Ringer

Posted by: Midnite1
Premium member *
Comment on: Stored Procedure Call from .NET
Posted: 2 years 7 months 11 days 6 hours 21 minutes ago

Sorry i did not circle back. We found that the problem was with the inherited library list  when the .NET program. We were not seeing it and the SP was blowing up and returning zeros/blanks.

Bob/Chris, thanks for your responses. the Create Proc stmt was solid. Thats why it was so confusing. And I got no help from my .NET programmer. His standard response was, and is, "the problem must be on the AS400, because my program is fine". The problem was i the fuzzy MS server area between .NET and AS400. It was actually a poster on another forum that pointed me to the resolution.

 

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Stored Procedure Call from .NET
Posted: 2 years 7 months 11 days 6 hours 4 minutes ago

You don't mean this do you? Library List and SQL Functions

The situation might be slightly different from yours but was the solutions the one you're talking about?

Posted by: Midnite1
Premium member *
Comment on: Stored Procedure Call from .NET
Posted: 2 years 7 months 9 days 5 hours 44 minutes ago

Yes. This is the case. Your linked post hit the nail on the head. Thanks. Now we know what to do when we encounter this error with other remote systems, not just .NET