Overview

The examples in this document are intended to extend the information provided with our Online Documentation and manuals.

How do I get the output parameters back from the server if my stored procedure is also returning a resultset?

There are a couple of different ways to get the RESULTSET and OUTPUT parameter from a stored procedure.

Code Sample 1: DECLARE, EXECUTE and FETCH ResultSet and Output parameters:

The first is to declare, execute and fetch the resultset and then when the sqlcode = 100 get out of loop and fetch the procedure a second time.  The code would look like this:

//declare local variable
LONG l_parm1
LONG l_out_parm

STRING s_message

CONNECT USING SQLCA;

//Initialize the input parameter - this could be hard coded.
l_parm1 = 35

DECLARE testproc PROCEDURE FOR dbo.testproc @Parm1 = :l_parm1, @OutParm = :l_out_parm OUTPUT USING SQLCA;

EXECUTE testproc;
//First, fetch the RESULTSET
do while sqlca.sqlcode = 0
FETCH testproc INTO :s_message;
if sqlca.sqlcode = 0 then
MessageBox( "s_message", s_message)
end if

loop
//Now fetch the OUTPUT PARM
FETCH testproc INTO :l_out_parm;
MessageBox( "l_out_parm", String(l_out_parm))
CLOSE testproc;
DISCONNECT USING SQLCA;

The second way this can be accomplished is by using the RPC method.  This is discussed in the "Application Techniques" manual.

Code sample 2:   Dynamic SQL Format 4 declaring a stored procedure

The sample in our help file illustrates ways to use format 4 using a declared cursor.  This script uses Format 4 embedded SQL statements and a declared stored procedure. This example assumes you know that there will be only one output descriptor and that it will be an integer. You can expand this example to support any number of output descriptors and any data type by wrapping the CHOOSE CASE statement in a loop and expanding the CASE statements.

string ls_procname, ls_sql, ls_Temp
int li_job_id, li_Ctr, li_Temp
int li_rtn

li_job_id = dw_emp.getitemNumber(1, "job_id")
setNull(li_job_id)

ls_procname = 'pr_405237'
ls_sql = 'execute ' + ls_procname + ' @job_id=' + '?'

PREPARE SQLSA FROM :ls_sql using sqlca;
DESCRIBE SQLSA INTO SQLDA ;

DECLARE my_procudure DYNAMIC PROCEDURE FOR SQLSA ;
li_rtn = SQLDA.SetDynamicParm(1, li_job_id)
sle_1.Text = String(li_rtn)
EXECUTE DYNAMIC my_procudure USING DESCRIPTOR SQLDA ;
FETCH my_procudure USING DESCRIPTOR SQLDA ;
If Sqlca.Sqlcode <> 0 then
    Messagebox("Error ", String(Sqlca.Sqlcode) + sqlca.sqlerrtext)
 else
       for li_Ctr = 1 to sqlda.NumOutputs
          CHOOSE CASE SQLDA.OutParmType[li_Ctr]
            CASE TypeString!
              ls_Temp = GetDynamicString(SQLDA, li_Ctr)
            CASE TypeInteger!
              li_Temp = GetDynamicNumber(SQLDA, li_Ctr)
          END CHOOSE
       next
  end if

CLOSE my_procedure ;