Informix DECLARE and EXECUTE – PB Docs 126


PowerBuilder requires a declarative statement to identify
the database stored procedure that is being used and specify a logical
name. The logical name is used to reference the procedure in subsequent
SQL statements. The general syntax for declaring a procedure is:

DECLARE logical_procedure_name PROCEDURE FOR
({:arg1,:arg2 , ...})
{USING transaction_object};

where logical_procedure_name can
be any valid PowerScript identifier and Informix_procedure_name is
the name of the stored procedure in the Informix database. The parentheses
after Informix_procedure_name are
required even if the procedure has no parameters.

Creating a stored procedure

The default SQL terminator character for the Database painter
is a semicolon (;). Informix also uses a semicolon in its stored
procedure syntax. Therefore, to create a stored procedure in the
Database painter, you must change the SQL terminator character to
something other than a semicolon, such as a backquote (`).

To change the Database painter’s SQL terminator character,
type the character you want in the SQL Terminator Character box
in the Database Preferences dialog box.

The parameter references can take the form of any valid parameter
string that Informix accepts. PowerBuilder does not inspect the
parameter list format except for purposes of variable substitution.
The USING clause is required only if you are using a transaction
object other than the default transaction object (SQLCA).


Assume a stored procedure proc1 is defined as:

SELECT emp_name FROM employee

To declare that procedure for processing within PowerBuilder,

DECLARE emp_proc PROCEDURE FOR proc1; 

Note that this declaration is a nonexecutable statement, just
like a cursor declaration. Where cursors have an OPEN statement,
procedures have an EXECUTE statement.

When an EXECUTE statement is executed, the procedure is invoked.
The EXECUTE refers to the logical procedure name:

EXECUTE emp_proc; 
note.png 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.

Issuing EXECUTE statements

Use PowerBuilder embedded SQL syntax when you enter an embedded EXECUTE
statement in a script; do not enter the PROCEDURE keyword. Use this

EXECUTE procedure_name; 

Specify the EXECUTE statement the same way whether or not
a stored procedure takes arguments. The arguments used in the DECLARE
statement get passed automatically, without your having to state
them in the EXECUTE statement.

Document get from Powerbuilder help
Thank you for watching.
Was this article helpful?
Notify of
Inline Feedbacks
View all comments
Would love your thoughts, please comment.x