Informix
DECLARE and EXECUTE
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:
|
1 2 3 4 |
DECLARE logical_procedure_name PROCEDURE FOR Informix_procedure_name ({: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).
Example
Assume a stored procedure proc1 is defined as:
|
1 2 |
CREATE PROCEDURE proc1 AS SELECT emp_name FROM employee |
To declare that procedure for processing within PowerBuilder,
enter:
|
1 |
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:
|
1 |
EXECUTE emp_proc; |
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 syntax:
|
1 |
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.