Only Dynamic Server supports this statement, which is an extension to the ANSI/ISO standard for SQL. Use this statement with ESQL/C.
>>-CREATE FUNCTION FROM--+-'file'---+-------------------------->< '-file_var-'
| Element | Description | Restrictions | Syntax |
|---|---|---|---|
| file | Path and filename of a file that contains the full CREATE FUNCTION statement text. Default pathname is current directory. | Must exist and contain exactly one CREATE FUNCTION statement | Must conform to operating-system rules. |
| file_var | Variable storing value of file | Same as for file | Language specific |
Functions written in the C or Java language are called external functions. When the IFX_EXTEND_ROLE configuration parameter is set to ON, only users who have been granted the built-in EXTEND role can create external functions.
An ESQL/C program cannot directly create a user-defined function. That is, it cannot contain the CREATE FUNCTION statement.
The file that you specify in the file parameter can contain only one CREATE FUNCTION statement.
For example, suppose that the following CREATE FUNCTION statement is in a separate file, called del_ord.sql:
CREATE FUNCTION delete_order( p_order_num int) RETURNING int, int;
DEFINE item_count int;
SELECT count(*) INTO item_count FROM items
WHERE order_num = p_order_num;
DELETE FROM orders WHERE order_num = p_order_num;
RETURN p_order_num, item_count;
END FUNCTION;
In the ESQL/C program, you can access the delete_order( ) SPL function with the following CREATE FUNCTION FROM statement:
EXEC SQL create function from 'del_ord.sql';
If you are not sure whether the UDR in the file is a user-defined function or a user-defined procedure, use the CREATE ROUTINE FROM statement.
The filename that you provide is relative. If you provide a simple filename with no pathname (as in the preceding example), the client application looks for the file in the current directory.
Related statements: CREATE FUNCTION, CREATE PROCEDURE, CREATE PROCEDURE FROM, and CREATE ROUTINE FROM