Database stored procedures that only perform updates and do not return a result set are handled in much the same way as procedures that return a result set. The only difference is that after the EXECUTE procedure_name statement executes, no result set is pending and no CLOSE statement is required.
Using the SQLCode property
If you know that a particular procedure can never return a result set, only the EXECUTE statement is required. If there is a procedure that may or may not return a result set, you can test the SQLCode property of the referenced transaction object for +100 (the code for not found) after the EXECUTE.
This table shows all possible values for SQLCode after an EXECUTE:
|
Return code |
Means |
|---|---|
|
0 |
The EXECUTE PROCEDURE was successful and a result set is pending. Regardless of the number of FETCH statements executed, the procedure must be explicitly closed with a CLOSE statement. This code is returned even if the result set is empty. |
|
+100 |
Fetched row not found |
|
-1 |
The EXECUTE was not successful and no result set was returned. |
Example 1
This example illustrates how to execute a database stored procedure that does not return a result set:
// good_employee is an Informix stored procedure.
// Declare the procedure.
DECLARE good_emp_p 1roc PROCEDURE FOR good_employee;
EXECUTE good_emp_proc;
// Test return code. Allow for +100 since you do
// not expect a result set.
if SQLCA.sqlcode = -1 then
// Issue error message since it failed.
MessageBox("Stored Procedure Error!", &
SQLCA.sqlerrtext)
end if Example 2
This example illustrates how to pass parameters to a database stored procedure that returns a result set. Emp_id_var has been set elsewhere to 691:
// Get_employee is an Informix stored procedure.
// Declare the procedure.
DECLARE get_emp_proc PROCEDURE FOR
get_employee @emp_id_parm = :emp_id_var;
// Declare a destination variable for emp_name
string emp_name_var
// Execute the stored procedure using the
// current value for emp_id_var.
EXECUTE get_emp_proc;
// Test return code to see if it worked.
if SQLCA.sqlcode = 0 then
// We got a row, so fetch it and display it.
FETCH get_emp_proc INTO :emp_name_var;
// Display the employee name.
MessageBox("Got my employee!",emp_name_var)
// You are all done, close the procedure.
CLOSE Get_emp_proc;
end if
