Very strange iSeries Provider behavior

Question

We've been given a "stored procedure" from our RPG folks that returns six data tables. Attempting to call it from .NET (C#, 3.5) using the iSeries Provider for .NET (tried using both V5R4 and V6R1), we are seeing different results based on how we call the stored proc. Here's way that we'd prefer to do it:

using (var dbConnection = new iDB2Connection("connectionString"))
{
    dbConnection.Open();
    using(var cmd = dbConnection.CreateCommand())
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "StoredProcName";
        cmd.Parameters.Add(new iDB2Parameter("InParm1", 
            iDB2DbType.Varchar).Value = thing;
        var ds = new DataSet();
        var da = new iDB2DataAdapter(cmd);
        da.Fill(ds);
    }
}

Doing it this way, we get FIVE tables back in the result set. However, if we do this:

cmd.CommandType = CommandType.Text;
cmd.CommandText = "CALL StoredProcName('" + thing + "')";

We get back the expected SIX tables.

I realize that there aren't many of us sorry .NET-to-DB2 folks out here, but I'm hoping someone has seen this before.

TIA.

Answer 1

Look into the LibraryList (and maybe the Naming) property of your connection string. When you use CommandType.StoredProcedure it could be executing the stored procedure right from the SQL database library. When you use CommandType.Text it searches the library list to find the stored procedure. You end up running different versions of the stored procedure from different libraries which gives you different results.