Re: Call Parameterize iSeries Store Procedure from .NET C#

On 05-Mar-2015 23:52 -0600, Guo, Fuwang wrote:

I have a stored procedure created on the AS400 that takes in 2
parameters and returns either 000 if found and 001 if not found.

The SQL DDL for a CREATE PROCEDURE that exhibits the error would be best for a reviewer of the scenario. If not LANGUAGE SQL, then the parameter-list definitions of the invoked program would also best be included.

I don't think I have the correct syntax for the commandtext.

Coded as "CALL DKDEXE.DKSLI ('?','?')", the request is not properly coded using _parameter markers_; instead, that coded request is passing two arguments, each as one-character strings, each string being the sole question-mark character. Parameter markers are just the question-mark character, without delimiters; the second attempt described as failing coded correctly for parameter markers, but to match more closely, apparently was intended to have been coded as?:
"CALL DKDEXE.DKSLI ( ? , ? )"

<<SNIP what presumably describes the garbage-in passing '?'s>>

I can hardcode the parameters "CALL DKDEXE.DKSLICCHK1
('AAAAAA1','*43 spaces*')" and the AS400 gets the correct data and
returns a valid 001 but I can not get that 001 back to my program.

Difficult [for me anyway] to understand what that implies. If the second parameter is INOUT, then passing a literal is not legitimate, and of course for the lack of a variable passed as an argument, a returned value can not be made available to the invoker.

Also, that the routine name is not the same as coded in the example(s), is at a minimum, confusing.

I'm out of ideas as to how to call the store procedure and get the
return code. What is wrong with my code?

using (OdbcConnection cn = new OdbcConnection(
"Driver=Client Access ODBC Driver (32-bit);System=X;Uid=U;Pwd=P"))

{
cn.Open();
using (OdbcCommand cm = cn.CreateCommand())
{
cm.CommandText = "CALL DKDEXE.DKSLI ('?','?')";
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.Add("P1", OdbcType.Char).Value = "AAAAAAA1";
cm.Parameters["P1"].Size = 8;
cm.Parameters["P1"].Direction = ParameterDirection.Input;
cm.Parameters.Add("P2", OdbcType.Char);
cm.Parameters["P2"].Size = 43;
cm.Parameters["P2"].Direction = ParameterDirection.InputOutput;
cm.Prepare();
cm.ExecuteNonQuery();
string result = cm.Parameters["P2"].Value.ToString();
}

Try in the above:
cm.CommandText = "CALL DKDEXE.DKSLI (?,?)";

I also tried {CALL DKDEXE.DKSLICCHK1 (?,?)} but I get an error
ERROR [42S02] [IBM][System i Access ODBC Driver][DB2 for i5/OS]
SQL0204 - DKSLI in DKDEXE type *N not found.

That alternate name apparently defines an external stored procedure that refers to the EXTERNAL NAME 'DKDEXE/DKSLI' or similar? Sorry, again, the CREATE PROCEDURE statement could help someone to diagnose difficulties; as well, the clarification on the [changing] routine-name specified on the CALLs could help someone to understand better.

FWiW: A difficulty with INOUT or OUT parameters in a PROCEDURE often can be circumvented either by encapsulating the CALL to the PROCEDURE inside a UDF and then instead obtaining the value from the UDF or by coding the PROCEDURE to return the information as a result-set rather than via a parameter. I recall I had to resort to returning a value in a result set once, using an interface that had not [yet] provided any support for anything other than input variables; I no longer recall what the interface was, but REXX has always had that restriction.