iSeries stored procedure calling RPG program doesn't return a value to the program

Question

I have a stored procedure that calls an SQLRPGLE program. The program is running fine as I have verified in the debugger and sends the return value as expected. Instead what is returned when looking at cmd.Parameters["@ISMATCH"].Value = {}. I am guessing I am doing something wrong. What could it be?

The stored procedure:

CREATE PROCEDURE "MPRLIB"."CHECKHOURS" (EMPLOYEEID DECIMAL(10 , 0), 
    INOUT ISMATCH CHAR(1))
LANGUAGE RPGLE
PARAMETER STYLE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA 
SPECIFIC CHECKHOURS 
NEW SAVEPOINT LEVEL
EXTERNAL NAME 'MPRLIB/MPRLRCHK';

My method:

    public bool IsValidTimesheet(int id)
    {
        bool isValid = false;

        // Get the data from the iSeries
        using (iDB2Connection conn = new iDB2Connection(ConfigurationManager.ConnectionStrings["IbmIConnectionString"].ConnectionString))
        {
            using (iDB2Command cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "LIB.CHECKHOURS";

                cmd.Parameters.Add("@EMPLOYEEID", iDB2DbType.iDB2Decimal).Value = id;
                cmd.Parameters.Add("@ISMATCH", iDB2DbType.iDB2Char).Direction = ParameterDirection.Output;
                conn.Open();
                cmd.ExecuteNonQuery();
                isValid = (cmd.Parameters["@ISMATCH"].Value.ToString() == "1") ? true : false;
                conn.Close();
            }
        }

        return isValid;
    }

My RPG program parameters:

 D CHECKHOURS      PR                  extpgm('CHECKHOURS')
 D  id                           10P 0
 D  isMatch                       1A

score 1 · Answer 1 · answered Mar 08 '11 at 22:19

1

I've got a vague recollection that despite the fact that you called it an OUT parameter, it really should be an INOUT parm because of the way RPG handles parms. What happens if you change the create proc to be INOUT and make the associated change in the C#?

answered Mar 08 '11 at 22:19

Walden Leverich

4,416
2
21
30

Nope, that didn't see to fix it. I guess INOUT is only good for burgers in this case. :0) – Mike Wills Mar 08 '11 at 22:31

score 1 · Accepted Answer · answered Mar 09 '11 at 16:38

1

The solution should be

CREATE PROCEDURE "MPRLIB"."CHECKHOURS" (EMPLOYEEID DECIMAL(10 , 0), 
    INOUT ISMATCH CHAR(1))
    LANGUAGE RPGLE
    PARAMETER STYLE GENERAL
    NOT DETERMINISTIC
    MODIFIES SQL DATA 
    SPECIFIC CHECKHOURS 
    NEW SAVEPOINT LEVEL
    EXTERNAL NAME 'MPRLIB/MPRLRCHK';

Thanks to Schadd on MIDRANGE-L.

answered Mar 09 '11 at 16:38

Mike Wills

20,959
28
93
149

You can use `PARAMETER STYLE SQL`, but then you need to change your RPG parameters to include the parameters implicitly included by the parameter style. Check out the [iDate](http://www.think400.dk/downloads.htm) service programs by Alan Campin for an example of this. – jmarkmurphy Jun 10 '19 at 21:04

score 0 · Answer 3 · answered Mar 09 '11 at 10:04

0

And what if you change the out parameter to nvarchar(1), and change the sproc. Does the N-type correspond to iDB2Char type?

answered Mar 09 '11 at 10:04

Walter Hesius

79
8

Something similar is what I am going to have to try next. Thanks for the idea. I'll just return Alpha and see what happens. – Mike Wills Mar 09 '11 at 14:28

iSeries stored procedure calling RPG program doesn't return a value to the program

3 Answers3