CREATE ROUTINE FROM statement

Use the CREATE ROUTINE FROM statement to register a UDR by referencing the text of a CREATE FUNCTION statement or CREATE PROCEDURE statement that resides in a separate file.

This statement is an extension to the ANSI/ISO standard for SQL.

You can use this statement with ESQL/C.

Syntax

CREATE ROUTINE FROM [IF NOT EXISTS] { 'file' | file_var }


Element	Description	Restrictions	Syntax
file	Pathname and filename for the text of a CREATE PROCEDURE or CREATE FUNCTION statement. Default path is the current directory.	Must exist and can contain only one CREATE FUNCTION or CREATE PROCEDURE statement.	Operating-system dependent
file_var	Name of a program variable that contains file specification	Must be a character data type; contents must satisfy file restrictions	Language specific

Usage

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 Informix® 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 1 or to ON, only users to whom the Database Server Administrator (DBSA) has granted the built-in EXTEND role can create external routines. In addition, you must hold at least the Resource access privilege on the database in which the routine will be registered. You must also hold the Usage privilege on the programming language in which the routine is written. (For the syntax of granting Usage privileges on the C language to a user or to a role, see Language-Level Privileges.)

Routines use the collating order that was in effect when they were created. See SET COLLATION statement for information about using non-default collation.

Examples

The following statement registers at UDR by referencing the text in the del_ord.sql file.

EXEC SQL CREATE ROUTINE FROM 'del_ord.sql';

ESQL/C source code example:

#include <stdio.h>

main()
{

    printf( "CREATE ROUTINE FROM ESQL Program running.\n\n");
    EXEC SQL WHENEVER ERROR STOP;
    EXEC SQL connect to 'stores_demo';

    EXEC SQL CREATE ROUTINE FROM 'del_ord.sql';

    EXEC SQL disconnect current;
    printf("\nCREATE ROUTINE Sample Program over.\n\n");

   exit(0);
}

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;