HP 3000 Manuals HP 3000 Manuals
Using Host Variables to Pass Parameter Values
You can specify up to 255 parameters for passing data between an
application and a procedure. You can pass values into a procedure using
any SQL expression except a subquery, an aggregate function, or a LONG
column, string, or TID function. Refer to the ALLBASE/SQL Reference
Manual "Expressions" chapter for more information. (Built-in variables
cannot be used as parameters, since they are only available within the
procedure.) Input parameters can be passed using host variables or
literal values. For any OUTPUT parameters, you must use host variables.
(In the EXECUTE PROCEDURE clause of a CREATE RULE statement, you can pass
a column name as an input parameter, but column names are not permitted
in procedures invoked through the EXECUTE PROCEDURE statement.)
The following example shows a procedure definition, followed by an
excerpt from an application program that executes the procedure
programmatically:
CREATE PROCEDURE ManufDB.Process12
(Operator CHAR(20),
Shift CHAR(20),
FailureType CHAR(10) NOT NULL) AS
BEGIN
INSERT INTO ManufDB.TestMonitor
VALUES (:Operator, CURRENT_DATETIME,
:Shift, :FailureType);
IF ::sqlcode = 0 THEN
COMMIT WORK;
RETURN 0;
ELSE
RETURN 1;
ENDIF;
END;
The following shows the execution of procedure ManufDB.Process12 using
host variables to pass in the values for Operator, Shift, and
FailureType:
First, declare host variables. Note that the same names are used
for host variables in the application program that were used in the
parameter definitions of the CREATE PROCEDURE statement. While
this is not required, it helps in seeing the relationship between the
procedure and the application program.
C Declarations: COBOL Declarations:
EXEC SQL BEGIN DECLARE SECTION; EXEC SQL BEGIN DECLARE SECTION END-EXEC.
char Operator[21]; 01 OPERATOR PIC X(20).
sqlind OperatorInd; 01 OPERATORIND SQLIND.
char Shift[21]; 01 SHIFT PIC X(20).
sqlind ShiftInd; 01 SHIFTIND SQLIND.
char FailureType[11]; 01 FAILURETYPE PIC X(10).
EXEC SQL END DECLARE SECTION; EXEC SQL END DECLARE SECTION END-EXEC.
A routine within the application program obtains values for
Operator, Shift, and FailureType from a user who enters the data.
If either Shift or Operator is null, the corresponding indicator
variable is set to -1.
Next, call the procedure to enter the failure information into the
database:
In C: In COBOL:
EXEC SQL EXECUTE PROCEDURE EXEC SQL EXECUTE PROCEDURE
ManufDB.Process12(:Operator ManufDB.Process12 (:OPERATOR
:OperatorInd, :Shift :OPERATORIND, :SHIFT
:ShiftInd, :FailureType); :SHIFTIND, :FAILURETYPE) END-EXEC.
Each host variable name in the EXECUTE PROCEDURE statement maps in
sequential order to a parameter definition in the CREATE PROCEDURE
statement. Inside the procedure, values are referenced by parameter
name, not by host variable name. Thus, parameter names and host variable
names need not be the same.
As in the example, you use indicator variables in the parameter list of
the EXECUTE PROCEDURE statement to indicate null values. But inside the
procedure, the parameter itself contains the null value. In order to
pass a null operator name to the procedure, you set the indicator
variable OperatorInd to -1 and the content of the host variable Operator
is undefined. Inside the procedure, the parameter Operator is set to
NULL, and this fact can be determined through a test:
IF :Operator IS NULL THEN
PRINT 'Parameter is null';
ELSE
PRINT :Operator;
ENDIF;