Extending INFORMIX-Universal Server: User-Defined Routines
Chapter 4: Debugging User-Defined Routines
Home
Contents
Index
Master Index
New Book
Invoking a Routine
This section describes the following methods that you can use to invoke a routine:
Use the
EXECUTE
statement.
Use the
CALL
statement.
Invoke a routine in an expression.
Invoking a Function with the EXECUTE statement
You can use the
EXECUTE
statement with the
FUNCTION
keyword to execute a function from one of the following:
SPL
An ESQL/C program
DB-Access
For example, suppose
result
is a function variable of type
BOOLEAN
. The following
EXECUTE
statement invokes the
equal()
function:
CREATE FUNCTION equal (arg1 udtype2, arg2 udtype2)
RETURNING BOOLEAN;
SPECIFIC udtype2_lessthan
WITH (HANDLESNULLS,
NOT VARIANT)
EXTERNAL NAME "/usr/lib/udtype2.so(udtype2_lessthan)"
LANGUAGE C
END FUNCTION;
EXECUTE FUNCTION equal (arg1, arg2) INTO result
The
INTO
clause must always be present when you invoke a function using an
EXECUTE
statement.
Important:
You cannot use the
EXECUTE
statement to invoke a function that has an
OUT
parameter.
As another example, suppose you create the following type hierarchy and functions:
CREATE ROW TYPE emp
(name varchar(30),
emp_num int,
salary numeric(10,2));
CREATE ROW TYPE trainee UNDER emp ...
CREATE FUNCTION func1 (trainee) RETURNS row ...
The following
EXECUTE
statement invokes the
func1()
function, which has an argument that is a query that returns a row type:
EXECUTE FUNCTION
func1 ((SELECT * from trainee where emp_num = 1234)) ...
Important:
When you use a query for the argument of a function invoked with the
EXECUTE
statement, ensure that you enclose the query in another set of parentheses.
Invoking a Procedure with the EXECUTE statement
You can use the
EXECUTE
statement with the
PROCEDURE
keyword to execute a procedure from within an SPL or ESQL/C program or DB-Access. The following
EXECUTE
statement invokes the
log_compare()
function:
EXECUTE PROCEDURE log_compare (arg1, arg2)
The
INTO
clause is never present when you invoke a procedure with the
EXECUTE
statement because a procedure does not return a value.
Invoking a Function with the CALL Statement
You can use the
CALL
statement to invoke a function from within an SPL program only. The following statement invokes the
equal()
function:
CALL equal (arg1, arg2)
RETURNING result
When you invoke a function with the
CALL
statement, you must include a
RETURNING
clause and the name of the value or values that the function returns.
You cannot use the
CALL
statement to invoke a function that has an
OUT
parameter.
You cannot execute the
CALL
statement from within an ESQL/C program or the DB-Access utility.
Invoking a Procedure with the CALL Statement
You can use the
CALL
statement to invoke a procedure from within an SPL program only. The following
CALL
statement invokes the
log_compare()
procedure:
CALL log_compare (arg1, arg2)
A
RETURNING
clause is never present when you invoke a procedure with the
CALL
statement because a procedure does not return a value.
Invoking a Function in an Expression
You can invoke a function in an expression either explicitly or implicitly. An external procedure cannot be used in an expression because an external procedure does not return a value. This section describes how you can invoke functions explicitly and implicitly in an expression. The examples in the following sections use the
new_type1
and
new_type2
distinct types and the
tab_1
table.
Figure 4-1
shows the syntax that creates the distinct types and table.
Figure 4-1
CREATE DISTINCT TYPE new_type1 AS DOUBLE PRECISION;
CREATE DISTINCT TYPE new_type2 AS INT;
CREATE TABLE tab_1
(
col_1 new_type1,
col_2 new_type1,
col_3 new_type2
);
Explicitly Invoking a Function in an Expression
You can invoke a function in an expression when the function returns a single value. Suppose an equal() function exists to evaluate the equality of
new_type1
and
new_type2
values. The following query invokes the appropriate
equal()
function in the
WHERE
clause of the
SELECT
statement:
SELECT *
FROM tab_1
WHERE equal (col_1, col_3)
Implicitly Invoking a Function That Is Bound to an Operator
Functions that are bound to specific operators get invoked automatically without explicit invocation. Suppose an
equal()
function exists that takes two arguments of
new_type1
and returns a Boolean. If the equal operator (=) is used for comparisons between
col_1
and
col_2
,
the
equal()
function gets invoked automatically. For example, the following query implicitly invokes
the appropriate
equal()
function to evaluate the
WHERE
clause:
SELECT *
FROM new_table
WHERE col_1 = col_2
The preceding query evaluates as though it had been specified as follows:
SELECT *
FROM new_table
WHERE equal (col_1, col_2)
Implicitly Invoking a Function for Casting
Suppose you create the following external function to cast a value of
newtype2
to
newtype1
:
CREATE FUNCTION ntype2_to_ntype1 (arg1 newtype2)
RETURNING newtype1
EXTERNAL NAME "/usr/lib/btype1/lib/libntype2.so"
LANGUAGE C
END FUNCTION;
Suppose also that you register the function as an implicit cast, as follows:
CREATE IMPLICIT CAST (newtype2 AS newtype1 WITH ntype2_to_ntype1)
The following query automatically invokes the cast
to convert
new_type2
values
to
newtype1
before it invokes the
equal()
function:
SELECT *
FROM tab
WHERE col_1 = col_3
The previous query is equivalent to the following statement:
SELECT *
FROM new_table
WHERE equal (col_1, CAST(col_3 AS newtype1))
Extending INFORMIX-Universal Server: User-Defined Routines
, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.
