I have been looking for an answer to this for a long time, but I have not found a solution without RPG/COBOL yet.
Is there any way or approach that I use to make a call to the SQL stored procedure from within a CL program?
Sorry, you do not have permission to ask a question, You must login to ask a question.
Sorry, you do not have permission to ask a question.
I have been looking for an answer to this for a long time, but I have not found a solution without RPG/COBOL yet.
Is there any way or approach that I use to make a call to the SQL stored procedure from within a CL program?
I have tried to
call
theSQL stored procedure with output parameter
within aCL program
usingQZDFMDB2
to run SQL interactively in a CL program and I have succeeded a lot in that. Now, I can call the SQL stored procedure with the output parameter in the CL program, the only drawback that I face is I am not getting the output parameter value returned in my CL program.To call the SQL stored procedure with output parameter in the CL program I have used the, so I created a
SQL variable
and passed that as areturn parameter
in the form of a command string to programQZDFMDB2
that allows to run SQL statements in CL program. This makes my first work done i.e.calling a SQL stored procedure in a CL program with output parameters
which earlier seemed very difficult or almost impossible. So congrats for this success that we have achieved so far.Now, the turn is for receiving the return value in the output parameter which is
SQL variable
here. So, it was sad but we are not getting return value from SQL stored procedure in our CL program. So, I decided to use some tricks and tweaks so that I would anyway receive the return value or value returned by the output parameterSQL stored procedure
in my CL program. Let me show you the code and steps that I followed to achieve this:SQL variable
using theCREATE VARIABLE
statement from STRSQL.In the SQL stored procedure code, a few lines are written intentionally such as we are writing
create or replace SQL variables
and setting their values as 1 or 0 according to SQL code value. Here, the SQL variable works as a return value for my CL program. Please note that SQL variable value is accessible to the same session in which its value is set by default during the create variable statement orSET
statement.RUNSQLSTM
command.CREATE VIEW
statement.Please, note that in
step 1
we created aSQL variable
namedOUT
in theTESTLIB
library so that we can later create a viewRTNVAL
inqtemp
library using that atstep 4
. For your knowledge as we have declared the viewrtnval
in my CL program so that I can populate that view with the value that was set by SQL stored procedure in SQL variable (SQL global variable
).At the beginning of the program, we create an SQL variable
out
intestlib
with a default blank value by passing the create or replace variable statement to theQZDFMDB2
program as a parameter in the string format.Then, we call the SQL stored procedure from within the CL program with input and output parameters by first building the
&cmd
string, and in, we pass theSQL variable
instead of any CL program variable. Then we pass it to theQZDFMDB2
program as a parameter to run the SQL stored procedure. Now till this point, we have not received any return value from the SQL stored procedure call usingQZDFMDB2
. However, within the SQL stored procedure script, we did set the respective success/failure value in the SQL variable globally and now I am ready to receive that in my CL program which would be the actual return value by my SQL stored procedure call.So, I selected the global SQL variable value and output it in a view named
rtnval
, and after that, I just read the view using theRCVF
command and evaluated the value from the view column&rtn
to the cl program variable&sqlout
. Therefore,&sqlout
would not contain the return value or the output parameter value that should be returned by the actual SQL stored procedure call within the CL program with the output parameterHi, I have tried calling your SQL stored procedure example case in a CL program at my end everything works fine however, there is one observation that I came across that I am mentioning here, You are using the
QZDFMDB2
program to run SQL stored procedure in a CL program. So, when I am calling your CL program that internally calls QZDFMDB2 prompts toPress ENTER to end the terminal session.
after executing theQZDFMDB2
call statement.DB20000I THE SQL COMMAND COMPLETED SUCCESSFULLY.
Press ENTER to end terminal session.
Can you suggest what I should change to remove the prompt in an interactive job?
Hi, Yes you can override the output to a file. Use the below code in the CL program
1-CRTPF FILE(QTEMP/CALLOUTPUT) RCDLEN(200) + MBR(CALLOUTPUT) MAXMBRS(1) SIZE(1 0 0)
2-OVRDBF FILE(STDOUT) TOFILE(QTEMP/CALLOUTPUT) + EXPCHK(*YES) OVRSCOPE(*CALLLVL)
3-Call to QZDFMDB2(command string) as in your CL program
4-DLTOVR FILE(STDOUT)
5-DLTF FILE(QTEMP/CALLOUTPUT)
The following highlighted modifications done to the existing CL program.
Hi, I have written a SQL stored procedure with output parameters as follows:
This SQL stored procedure STORED33A takes 2 input parameters and one output parameter. It inserts data in PF8_D and returns a success or failure in the output parameter.
I have compiled it using the RUNSQLSTM command with the following compile options.
I then wrote a CL program
stored33cl
to call this SQL stored procedureSTORED33A
with output parameter using datalink file manager DB2 CLP program (QZDFMDB2
) present in the QSYS library.Compiled CL program with Create bound CL program (CRTBNDCL) command invoked using option 14 on CL source member
Then I called my CL program stored33cl and then I got an error.
Parameter 3 is the output parameter in the SQL stored procedure. Therefore, calling a SQL stored procedure containing an OUT parameter returns
SQL0469
i.e.sqlcode: -469
,sqlstate: 42886
.I am not getting how can I call SQL stored procedure in CL program with the output parameter. Can anyone help here and please let me know what I am missing here?
Hi, I used your SQL stored procedure example and just modified your CL program and tried to call a SQL stored procedure the same way as we call any RPGLE or CL program, i.e. Using the CALL command as below:
I compiled using
option 14
and called the CL program from the command line and this time I was getting an errorMCH3601 received by procedure STORED33CL. (C D I R)
.I took
F1
on the error and below is the additional message information that I can see. So, this makes me aware that we cannot call the SQL stored procedure using the CALL command in CL program.Yes,
MCH3601
is a known error for the IBM i SQL stored procedure. Please note that the SQL stored procedure cannot be called from the command line interface using the CALL command. That’s why you are getting anMCH3601
error.You will encounter this
MCH3601
error when you do not qualify the SQL stored procedure CALL statement through an SQL interface. If the call is not qualified and the SQL path is not set to resolve the SQL stored procedure, thisMCH3601
error will occur.You can only call the SQL stored procedure with the SQL CALL statement.