Calling AS400 SP from ASP .NET webservice

Question

I have an external stored procedure (SP) in AS400 with IN and OUT prameters.When my SP is called from webservice with IN and OUT parameters, it shows me an error (Attempted to read or write protected memory).But when the webservice has all the parm as IN mode and calls my SP it works properly.

Answer 1

Just for arguments sake, it should not be important that your stored procedure runs on an AS400. From the point of view of your ASP.NET webservice it is using a regular DB2 database. BTW how do you connect from the Webservice to the database, ADO.NET, ODBC or something else.

Have you tried to call the stored procedure from the AS400 SQL command shell (STRSQL)? Have you tried to call the stored procedure from the Client Access Navigator SQL script? Especially if the last one works, your SP is technically correct. Notice that you can retrieve the definition of the SP in CA Navigator. Study your IN and OUT parameter very good because DB2 is not very forgiving about the type of these parameters.

Notice that your external program on the as400 (ILE or not ILE?) must support the type of parameters that you have defined for your stored procedure. Maybe your IN parameters are correct, but your OUT parameter is not correct. Therefore, your program can fail if it want to return a value. Also, you can have defined a different set of parameters (e.g. SQL instead of JAVA). I can image that defining the wrong set can give strange effects.

For now, test your SP with CA Navigator (available with every AS400). If that works, your SP works. Then you can narrow your problem down to your Webservice.

Also, in DB2 you can define your parameters als INOUT. That will be a nice experiment too.