Stored procedure output parameter with format dec(5, 0) is not returned properly by ibm_db.callproc

[colin-grierson]

System details

Operating system: IBMi V7R3M0
db2level output from clidriver if in use:
Target Db2 Server Version: IBMi V7R3M0
Python Version: 3.9.16
ibm_db version:

For non-Windows, output of below commands:

• uname: OS400
• uname -m: 00680001187X

Value of below environment variables if set:

• IBM_DB_HOME: not set
• PATH: /QOpenSys/pkgs/bin:/QOpenSys/usr/bin:/usr/ccs/bin:/QOpenSys/usr/bin/X11:/usr/sbin:.:/usr/bin
• LIB/LD_LIBRARY_PATH/DYLD_LIBRARY_PATH: not set

Problem

The problem is with a stored procedure output parameter with format dec(5, 0) not returned properly by ibm_db.callproc. Other parameters (VarChar, Char, Date and Integer) return correctly
Everything is on the IBMi

Thanks for your help. Regards, Colin

Stored procedure definition

create or replace procedure *LIB/tststpout(
i_Count in Dec(5,0),
i_Text in VarChar(20),
i_Date in Date,
o_Count_d out Dec(5,0),
o_Count_i out Int,
o_Text out VarChar(20),
o_Date out Date)

Language RPGLE
Parameter style general WITH NULLS
Deterministic
No SQL
specific *LIB/tststpout
External name tststpout

Here is the RPG program behind it

d tststpout pi
d i_count 5p 0
d i_text 20a varying
d i_date 10a
d o_count_d 5p 0
d o_count_i 10i 0
d o_text 20a varying
d o_date 10a
d Up_Case c 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
d Lo_Case c 'abcdefghijklmnopqrstuvwxyz'

// Change the input parameters a little and we are done
o_count_d = i_count + 1;
o_count_i = i_count + 2;
o_text = %xlate(Lo_Case: Up_Case: i_text);
o_date = %char(%date(i_date: *iso) + %days(1): *iso);
return;

Here is what I see when testing using the ACS SQL tool

call tststpout ( cast(12345 as dec(5,0)), cast('Hello there' as varchar(20) ccsid 37), curdate(), 0, 0, 'x', 'x')
Return Code = 0
Output Parameter ibmdb#4 (O_COUNT_D) = 12346
Output Parameter ibmdb#5 (O_COUNT_I) = 12347
Output Parameter ibmdb#6 (O_TEXT) = HELLO THERE
Output Parameter ibmdb#7 (O_DATE) = 2023-08-16
Statement ran successfully (11 ms)

Here is my Python test script

import ibm_db
try:
i_Num = 798
i_Char = "The boy and his dog"
i_Date = "2023-08-15"
o_Num_d = 1
o_Num_i = 2
o_Char = "a"
o_Date = "b"
print("i_Num is: " + str(i_Num) + " i_Char is: " + i_Char + " i_Date is: " + i_Date)
print("o_Num_d is: " + str(o_Num_d) + " o_Num_i is: " + str(o_Num_i) + " o_Char is: " + o_Char + " o_Date is: " + o_Date)
conn = ibm_db.connect("*LOCAL","sascpg","bould3rbk")
stmt, i_Num, i_Char, i_Date, o_Num_d, o_Num_i, o_Char, o_Date =
ibm_db.callproc(conn, 'sasgen.tststpout', (i_Num, i_Char, i_Date, o_Num_d, o_Num_i, o_Char, o_Date))
print("i_Num is: " + str(i_Num) + " i_Char is: " + i_Char + " i_Date is: " + i_Date)
print("o_Num_d is: " + str(o_Num_d) + " o_Num_i is: " + str(o_Num_i) + " o_Char is: " + o_Char + " o_Date is: " + o_Date)
ibm_db.close(conn)
except:
print("failed to connect")

This is the output from the test script:

i_Num is: 798 i_Char is: The boy and his dog i_Date is: 2023-08-15
o_Num_d is: 1 o_Num_i is: 2 o_Char is: a o_Date is: b
i_Num is: 798 i_Char is: The boy and his dog i_Date is: 2023-08-15
o_Num_d is: 7 o_Num_i is: 800 o_Char is: THE BOY AND HIS DOG o_Date is: 2023-08-16

Comments on test script output:
o_Num_d should be i_Num + 1 - 799 in this case, not 7.
o_Num_i is i_Num + 2 - 800 as expected. o_Char is i_Char in upper case and o_Date is i_Date plus a day. Both are correct.

If I try to display o_Num_d in hex I get "TypeError: 'str' object cannot be interpreted as an integer" - a character string has been returned, not a number.
Hex(o_Num_i) returns '0x320' - as expected
Setting o_Num_d and o_Num_i to 123456789 before the call... after the call I get

o_Num_d = '79956789', again a string - not a number... 799 is present but concatenated with some of the original value???
o_Num_i = 800 - as expected and this is a number