SAP Adaptive Server Enterprise Update – PB Docs 2019

SAP Adaptive
Server Enterprise Update

Database stored procedures that perform updates only and do not
return result sets are handled in much the same way as procedures that
return result sets. The only difference is that after the EXECUTE
procedure_name statement is executed, no result sets are pending and no
CLOSE statement is required.

Using the SQL Code property

If you know for sure that a particular procedure can never return
result sets, then the EXECUTE statement is all that is needed. 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.

The following table shows all the possible values for SQLCode after
an EXECUTE:

Return code	Means
0	The EXECUTE was successful and at least one 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 sets were returned. The procedure does not require a CLOSE. If a CLOSE is attempted against this procedure an error will be returned.

Return code

Means

The EXECUTE was successful and at least one 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 sets
were returned. The procedure does not require a CLOSE. If a CLOSE
is attempted against this procedure an error will be
returned.

Example 1

Assume the default transaction object (SQLCA) has been assigned
valid values and a successful CONNECT has been executed. Also assume the
description of the Adaptive Server Enterprise procedure good_employee
is:

// Adaptive Server Enterprise good_employee
// stored procedure:
CREATE PROCEDURE good_employee AS
   UPDATE employee 
   SET emp_salary=emp_salary * 1.1
   WHERE emp_status = 'EXC'

// Adaptive Server Enterprise good_employee

// stored procedure:

CREATE PROCEDURE good_employee AS

UPDATE employee

SET emp_salary=emp_salary * 1.1

WHERE emp_status = 'EXC'

This example illustrates how to execute a stored procedure that does
not return any result sets:

// Declare the procedure.
DECLARE good_emp_proc PROCEDURE
FOR good_employee;

// Execute it.
EXECUTE good_emp_proc;

// Test return code. Allow for +100 since you do
// not expect result sets.
if SQLCA.sqlcode = -1 then

// Issue error message since it failed.
   MessageBox("Stored Procedure Error!", &amp;
   SQLCA.sqlerrtext)
end if

// Declare the procedure.

DECLARE good_emp_proc PROCEDURE

FOR good_employee;

// Execute it.

EXECUTE good_emp_proc;

// Test return code. Allow for +100 since you do

// not expect result sets.

if SQLCA.sqlcode = -1 then

// Issue error message since it failed.

MessageBox("Stored Procedure Error!", &

SQLCA.sqlerrtext)

end if

Error checking

Although you should test the SQLCode after every SQL statement,
these examples show statements to test the SQLCode only to illustrate a
specific point.

Example 2

// Adaptive Server Enterprise get_employee
// stored procedure:
   CREATE PROCEDURE get_employee @emp_id_parm
   int AS SELECT emp_name FROM employee
   WHERE emp_id = @emp_id_parm

// Adaptive Server Enterprise get_employee

// stored procedure:

CREATE PROCEDURE get_employee @emp_id_parm

int AS SELECT emp_name FROM employee

WHERE emp_id = @emp_id_parm

This example illustrates how to pass parameters to a database stored
procedure. Emp_id_var has been set elsewhere to 691:

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

// Since we got a row, 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, so close the procedure.
   CLOSE Get_emp_proc;

end if

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

// Since we got a row, 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, so close the procedure.

CLOSE Get_emp_proc;

end if

Document get from Powerbuilder help

Thank you for watching.

Powerbuilder Documentation 2019

August 21, 2023

Was this article helpful?