Use the CREATE ROUTINE FROM statement to register a UDR by referencing
the text of a CREATE FUNCTION or CREATE PROCEDURE statement that resides in
a separate file. Only Dynamic Server supports this statement, which is an
extension to the ANSI/ISO standard for SQL. You can use this statement with
ESQL/C.
ESQL/C programs cannot use the CREATE FUNCTION or CREATE PROCEDURE statement
directly to define a UDR. You must instead do this:
- Create a source file with the CREATE FUNCTION or CREATE PROCEDURE statement.
- Execute the CREATE ROUTINE FROM statement from an ESQL/C program to send
the contents of this source file to the database server for execution. The
file that you specify can contain only one CREATE FUNCTION or CREATE PROCEDURE
statement.
The file specification that you provide is relative. If you include no
pathname, the client application looks for the file in the current directory.
If you do not know at compile time whether the UDR in the file is a function
or a procedure, use the CREATE ROUTINE FROM statement in the ESQL/C program.
If you know whether the UDR is a function or a procedure, you can improve
the readability of your code by using the matching SQL statement to access
the source file:
- To access user-defined functions, use CREATE FUNCTION FROM.
- To access user-defined procedures, use CREATE PROCEDURE FROM.
When the IFX_EXTEND_ROLE configuration parameter is
set to ON, only users to whom the Database System Administrator (DBSA) has
granted the built-in EXTEND role can create external routines.
Routines use the collating order that was in effect when they were created.
See SET COLLATION for information about using
non-default collation.
>>-CREATE ROUTINE FROM--+-'file'---+--------------------------->< '-file_var-'