Topic
  • 2 replies
  • Latest Post - ‏2019-08-12T14:44:15Z by Carlos_Merida
Carlos_Merida
Carlos_Merida
14 Posts

Pinned topic Stored Procedure decimal output parameter value error in Linux

‏2019-07-09T10:48:33Z |

Hello,

I have this strange error. When I call an Stored Procedure with decimal output parameter, the retuned value is different when use windows provider (the value is the expected) and linux provider (the value is wrong).

This is my code (c#):

DB2Connection connection = (DB2Connection)_context.Database.GetDbConnection();

DB2Command command = new DB2Command("LIBRERIA.CLICHKPAS2", connection);

command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new DB2Parameter("USER", user));
command.Parameters.Add(new DB2Parameter("PASSWORD", password);
DB2Parameter paramOperario = new DB2Parameter();
paramOperario.ParameterName = "OPE";
paramOperario.DB2Type = DB2Type.Decimal;
paramOperario.Precision = 5;
paramOperario.Scale = 0;
paramOperario.Direction = ParameterDirection.Output;
command.Parameters.Add(paramOperario);
DB2Parameter paramError = new DB2Parameter("ERROR", DB2Type.Char, 1);
paramError.Direction = System.Data.ParameterDirection.Output;
command.Parameters.Add(paramError);

connection.Open();

await command.ExecuteNonQueryAsync();

connection.Close();

string error = paramError.Value.ToString();

if (string.IsNullOrWhiteSpace(error))
{
  decimal operario = Convert.ToDecimal(paramOperario.Value);

}

The Stored Procedure call to AS400 CLLE program:

 CREATE PROCEDURE LIBRERIA.CLICHKPAS2(

                                    IN USER CHAR(10),
                                    IN "PASSWORD" CHAR(10),
                                    OUT OPE DECIMAL(5, 0),
                                    OUT "ERROR" CHAR(1))
    LANGUAGE RPGLE
    SPECIFIC LIBRERIA.CLICHKPAS2
    NOT DETERMINISTIC
    CONTAINS SQL
    CALLED ON NULL INPUT
    EXTERNAL NAME 'LIBRERIA/CLICHKPAS2'
    PARAMETER STYLE GENERAL;
 

And the CLLE program has this aspect 

 

PGM        PARM(&USR &PSS &OPE &ERR)

DCL        VAR(&USR) TYPE(*CHAR) LEN(10) 

DCL        VAR(&PSS) TYPE(*CHAR) LEN(10)
DCL        VAR(&OPE) TYPE(*DEC) LEN(5 0) 
DCL        VAR(&ERR) TYPE(*CHAR) LEN(1)  

....    

 

The problem is that the returned value in the decimal variable "operario" in production enviroment (Linux) is incorrect. For example, if "operario" real value is 1875, the returned value is 1075. This is a crazy thing. In my development enviroment (windows) I get the correct value.

I think the problem is with lnx driver:

IBM.Data.DB2.Core-lnx

IBM.EntityFrameworkCore-lnx

Because with the windows version, I don't get this issue:

IBM.Data.DB2.Core

IBM.EntityFrameworkCore

 

Please, can anyone explain this strange behavior???

 

Thanks in advance.

 

Carlos.

Updated on 2019-07-09T10:52:05Z at 2019-07-09T10:52:05Z by Carlos_Merida
  • VishDev2018
    VishDev2018
    65 Posts

    Re: Stored Procedure decimal output parameter value error in Linux

    ‏2019-07-12T10:18:55Z  

    Hi,

     yes, it looks strange and I do not see any reason why our packages do any intermediate conversions. If possible, please validate the values through other mechanisms like directly reading the value or doing some intermediate checks.

    You can also share the schema of the stored procedure so that we can validate it in our environment.

     

    If not resolved, please approach IBM Customer support for us to explore further.

     

    Thanks

    Vishwa

     

  • Carlos_Merida
    Carlos_Merida
    14 Posts

    Re: Stored Procedure decimal output parameter value error in Linux

    ‏2019-08-12T14:44:15Z  

    Hi,

     yes, it looks strange and I do not see any reason why our packages do any intermediate conversions. If possible, please validate the values through other mechanisms like directly reading the value or doing some intermediate checks.

    You can also share the schema of the stored procedure so that we can validate it in our environment.

     

    If not resolved, please approach IBM Customer support for us to explore further.

     

    Thanks

    Vishwa

     

    Hi Vishwa,

     

    I have done all possible intermediate checks, include check values in AS400 communication logs. I'm pretty sure that this is a conversion problem for decimal values, but I don't know if the problem is in the linux provider dll or in some parameter in the linux server configuration,  

     

    What do you mean with "stored procedure schema"?, I set in the original post the script that generate the stored procedure:

     

     CREATE PROCEDURE LIBRERIA.CLICHKPAS2(

                                        IN USER CHAR(10),
                                        IN "PASSWORD" CHAR(10),
                                        OUT OPE DECIMAL(5, 0),
                                        OUT "ERROR" CHAR(1))
        LANGUAGE RPGLE
        SPECIFIC LIBRERIA.CLICHKPAS2
        NOT DETERMINISTIC
        CONTAINS SQL
        CALLED ON NULL INPUT
        EXTERNAL NAME 'LIBRERIA/CLICHKPAS2'
        PARAMETER STYLE GENERAL; 

     

    This stored procedure call to RPGLE program named "LIBRERIA.CLICHKPAS2". Remember that the stored procedure and RPGLE program were the same in windows and linux scenarios.

     

    Regards,

    Carlos.

    Updated on 2019-08-12T14:56:57Z at 2019-08-12T14:56:57Z by Carlos_Merida