We have deployed the solution to return variable from a DB2 on iSeries stored procedure using the solution from question text
This is the code we have deployed. The stored procedure being called has 7 Inputs, 1 Input/Output and 2 Outputs
import json
import traceback
from modules.utils import print_message
from modules import secrets_manager
import warnings
import pyodbc
warnings.filterwarnings('ignore')
def lambda_handler(event, context):
result = 0.0
try:
secret = json.loads(secrets_manager.get_secret(tenant,aws_region))
conn = pyodbc.connect(f"DSN=IBM i Access;SYSTEM={secret['ip']};DATABASE={secret['database']};UID={secret['username']};PWD={secret['password']};naming=1;dbq={secret['libraries']}")
crsr = conn.cursor()
crsr.execute("CREATE OR REPLACE VARIABLE AOMO DECIMAL DEFAULT 0")
crsr.execute("CREATE OR REPLACE VARIABLE LSEQ DECIMAL DEFAULT 0")
crsr.execute("CREATE OR REPLACE VARIABLE RETC CHAR DEFAULT NULL")
crsr.execute("{CALL MIE550EC.Z1NS910B(?,?,?,?,?,?,?,AOMO,LSEQ,RETC)}", ('PB', event['orderNo'],'','','','',0,))
while crsr.nextset():
try:
results = crsr.fetchall()
print(results)
break
except:
print('error')
continue
crsr.execute("values(AOMO,LSEQ,RETC)")
results = crsr.fetchall()
print(results)
except Exception as e:
error_str = f"{str(e)}n{traceback.format_exc()}"
finally:
if tenant == "prd" and error_str != "":
print_message(tenant, event, error_str,function_name)
elif tenant != "prd":
print_message(tenant, event, error_str,function_name)
error_str = ""
return result
The results only contain the values the global variables were originally set, they do not contain the output of the stored procedure.
Was hoping someone could point out why we do not get the outputs returned.