This document describes a sample PowerBuilder application that demonstrates the use of a Adaptive Server Enterprise Stored Procedure. The sample procedure contains a result set of two columns, an input parameter, an output parameter, and a return value.

Following is a general overview of the commands covered in this example:

Create a Stored Procedure
Use the CREATE command to create the procedure in the database. You must be connected to the database, and a procedure with the same name can not already exist in the database. If the procedure already exists and you wish to re-create it, it must first be dropped from the database.

Declare a Stored Procedure
Use the DECLARE command to define the variables to be used for input and output parameters. Note that the procedure must already exist in the database before it can be declared successfully. The procedure must be declared in PowerBuilder before it is executed.

Execute a Stored Procedure
Use the EXECUTE command to execute the procedure on the database server.

Fetch the Stored Procedure results
Use the FETCH command to obtain the result set, output parameter value(s), and the return value. All rows from the result set are first fetched in a loop, then an additional fetch is used to obtain the output parameter(s) and the return value.

Drop the Stored Procedure
Use the DROP command, if desired, to delete the stored procedure form the database.
 

Sample PBL Description
 

In this example, a stored procedure is used to obtain a list of employees in a specific department. The procedure uses an input parameter containing the desired department id, and sets an output parameter to the count of employees in the selected department. In addition, a listbox is populated with the employee names from the procedure result set.

The following sections describes the objects and scripts used to CREATE, DECLARE, EXECUTE, FETCH, and DROP the stored procedure.

Error checking is included. If any of the scripts fail for any reason, a MessageBox is displayed containing error codes and the error message text.

Window instance variables

string is_msg, is_command
int input1
DECLARE myproc PROCEDURE FOR @rv = test_proc
       @inparm1 = :input1,
       @outparm1 = 0 OUTPUT;

The procedure DECLARE is done as a window instance variable in this example.

Command buttons
 

These are the command buttons defined on the window. See the next section for the scripts for each of these buttons.

cb_connect Connect to the database
cb_create Create the stored procedure in the database
cb_execute Execute the stored procedure
cb_fetch Fetch the result set, return code, and output parameter
cb_drop Drop the stored procedure from the database

Window Objects

lb_resultset List box that is populated with the data from the result set
 

Application Script

a_stored_proc

// Open the window
Open(w_syb10_sp)
 

Window command button Scripts

cb_connect
 

// Note: Please put appropriate values in these variables
SQLCA.DBMS="syc"
SQLCA.Database="dbname"
SQLCA.LogPass="password"
SQLCA.ServerName="server"
SQLCA.LogId="loginid"
SQLCA.AutoCommit = TRUE

// Connect to the database
CONNECT USING SQLCA;

// Display an error if the connect fails
IF SQLCA.SQLCode <> 0 THEN
  is_msg = "SQLCode: " + string(SQLCA.SQLCode)
  MessageBox("Connect Error", is_msg)
END IF
 

cb_create
 

// Populate the string variable with the SQL command to create the procedure.
// Note that in this example, the procedure will always return a 3 for the
// return value, since it is hardcoded in the procedure definition.

is_command = "create procedure test_proc @inparm1 int, @outparm1 int " + &
 "OUTPUT as " + &
 "BEGIN " + &
 "select @outparm1 = COUNT(emp_id) from employee where dept_id = @inparm1 " + &
 "select emp_fname, emp_lname from employee where dept_id = @inparm1 " + &
 "return 3 " + &
 "END"

// Use dynamic SQL to execute the Create command
EXECUTE IMMEDIATE :is_command;

// Display error information if the create failed
If SQLCA.SQLCode <> 0 THEN
is_msg = "SQLCode: " + String(SQLCA.SQLCode) + " DBCode: " + &
String(SQLCA.SQLDBCode) + " ReturnData: " + SQLCA.SQLReturnData + &
" DBErrText: " + String(SQLCA.SQLErrText)
MessageBox("CREATE failed", is_msg)
END IF
 

cb_execute
 

// Populate the input parameter with the desired value
input1 = 300            // Use Department ID 300 as selection criteria

// Execute the procedure
EXECUTE myproc;

  // Display error information if the execute failed
  If SQLCA.SQLCode <> 0 THEN
    is_msg = "SQLCode: " + String(SQLCA.SQLCode) + " DBCode: " + &
      String(SQLCA.SQLDBCode) + " ReturnData: " + SQLCA.SQLReturnData + &
      " DBErrText: " + String(SQLCA.SQLErrText)
      MessageBox("Execute failed", is_msg)
  END IF
 

cb_fetch
 

  // Define variables to hold the return value and output parameter
  int li_ret_value, output1

  // Define variables to hold the result set data from the fetch
  string ls_lname, ls_fname

  // Loop to Fetch the result set, one row at a time
  DO WHILE SQLCA.SQLCode = 0
     FETCH myproc INTO :ls_lname, :ls_fname;
     IF SQLCA.SQLCode = 0 THEN
         // Add the employee name to the listbox
         lb_ResultSet.AddItem(ls_fname + ls_lname)
     ELSE
         IF SQLCA.SQLCode <> 100 THEN
             MessageBox("FETCH error", SQLCA.SQLErrText)
             Return
         END IF
     END IF
  LOOP

// Fetch the return value and output parameter into the variables
FETCH myproc INTO :li_ret_value, :output1;

// Check to ensure the return value and output parameter were obtained
IF SQLCA.SQLCode = 0 THEN
// Display the return value and output parameter in a message box
     is_msg = "Return value: " + string(li_ret_value) + &
         " Output parameter value: " + string(li_ret_value)
     MessageBox("Results", is_msg)
  ELSE
// Display error
     is_msg = "SQLCode: " + String(SQLCA.SQLCode) + " DBCode: " + &
         String(SQLCA.SQLDBCode) + " ReturnData: " + SQLCA.SQLReturnData + &
         " DBErrText: " + String(SQLCA.SQLErrText)
     MessageBox("Execute of FETCH failed", is_msg)
  END IF
 

cb_drop
 

// populate the string variable with the SQL command to drop the procedure
  is_command = "DROP PROCEDURE test_proc"

// Use dynamic SQL to execute the drop command
  EXECUTE IMMEDIATE :is_command;

// Display error information if the drop failed
  IF SQLCA.SQLCode <> 0 THEN
     is_msg = "SQLCode: " + String(SQLCA.SQLCode) + " DBCode: " + &
         String(SQLCA.SQLDBCode) + " ReturnData: " + SQLCA.SQLReturnData + &
         " DBErrText: " + String(SQLCA.SQLErrText)
     MessageBox("DROP failed", is_msg)
  END IF