Informix update using database stored procedures
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 |
Means |
|---|---|
|
0 |
The EXECUTE PROCEDURE was successful This code is returned even if the result set is empty. |
|
+100 |
Fetched row not found |
|
-1 |
The EXECUTE was not successful and no |
Example 1
This example illustrates how to execute a database stored
procedure that does not return a result set:
|
1 |
// good_employee is an Informix stored procedure. <br>// Declare the procedure.<br>DECLARE good_emp_p 1roc PROCEDURE FOR good_employee;<br>EXECUTE good_emp_proc;<br> <br>// Test return code. Allow for +100 since you do <br>// not expect a result set.<br>if SQLCA.sqlcode = -1 then<br> <br>// Issue error message since it failed.<br> MessageBox("Stored Procedure Error!", &<br> SQLCA.sqlerrtext)<br>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:
|
1 |
// Get_employee is an Informix stored procedure.<br>// Declare the procedure.<br>DECLARE get_emp_proc PROCEDURE FOR <br> get_employee @emp_id_parm = :emp_id_var;<br> <br>// Declare a destination variable for emp_name<br>string emp_name_var<br> <br>// Execute the stored procedure using the <br>// current value for emp_id_var. <br>EXECUTE get_emp_proc;<br> <br>// Test return code to see if it worked.<br>if SQLCA.sqlcode = 0 then<br> <br>// We got a row, so fetch it and display it.<br> FETCH get_emp_proc INTO :emp_name_var;<br> <br>// Display the employee name.<br> MessageBox("Got my employee!",emp_name_var)<br> <br>// You are all done, close the procedure. <br> CLOSE Get_emp_proc; <br>end if |