Informix update using database stored procedures – PB Docs 2018

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 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.

Return code

Means

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!", &amp;
   SQLCA.sqlerrtext)
end if

// 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

// 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

Document get from Powerbuilder help

Thank you for watching.

Powerbuilder Documentation 2018

August 20, 2023

Was this article helpful?