CREATE PROCEDURE FROM statement

Use the CREATE PROCEDURE FROM statement to access a user-defined procedure. The actual text of the CREATE PROCEDURE statement resides in a separate file.

This statement is an extension to the ANSI/ISO standard for SQL. You can use this statement with Informix® ESQL/C.

Syntax

CREATE PROCEDURE FROM [IF NOT EXISTS] { 'file' | file_var }
Element Description Restrictions Syntax
file Pathname and filename of file that contains full text of a CREATE PROCEDURE statement. Default pathname is the current directory. Must exist, and can contain only one CREATE PROCEDURE statement. See also Default Directory That Holds the File. Operating-system specific
file_var Name of a program variable that contains file specification Must be of a character data type; its contents have same restrictions as file Language specific

Usage

You cannot create a user-defined procedure directly in Informix® ESQL/C programs. That is, the program cannot contain the CREATE PROCEDURE statement.

To use a user-defined procedure in an ESQL/C program:

  1. Create a source file with the CREATE PROCEDURE statement.
  2. Use the CREATE PROCEDURE FROM statement to send the contents of this source file to the database server for execution.

    The file can contain only one CREATE PROCEDURE statement.

For example, suppose that the following CREATE PROCEDURE statement is in a separate file, called raise_pr.sql: 
CREATE PROCEDURE raise_prices( per_cent INT )
   UPDATE stock -- increase by percentage;
   SET unit_price = unit_price + 
      ( unit_price * (per_cent / 100) );
END PROCEDURE;
In the Informix® ESQL/C program, you can access the raise_prices( ) SPL procedure with the following CREATE PROCEDURE FROM statement: 
EXEC SQL create procedure from 'raise_pr.sql';

If you are not sure whether the UDR in the file returns a value, use the CREATE ROUTINE FROM statement.

When the IFX_EXTEND_ROLE configuration parameter is set to ON, only users who have the built-in EXTEND role can create external routines.

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.)

User-defined procedures, like user-defined functions, use the collating order that was in effect when they were created. See SET COLLATION statement for information about using non-default collation.