Using the WITH LISTING IN Option

The WITH LISTING IN clause specifies a filename where compile time warnings are sent. After you compile a UDR, this file holds one or more warning messages. This listing file is created on the computer where the database resides.

If you do not use the WITH LISTING IN clause, the compiler does not generate a list of warnings.

On UNIX, if you specify a filename but not a directory, this listing file is created in your home directory on the computer where the database resides. If you do not have a home directory on this computer, the file is created in the root directory (the directory named "/").

On Windows, if you specify a filename but not a directory, this listing file is created in your current working directory if the database is on the local computer. Otherwise, the default directory is %INFORMIXDIR%\bin.

SPL Procedures

SPL procedures are UDRs written in Stored Procedure Language (SPL) that do not return a value. To write and register an SPL routine, use the CREATE PROCEDURE statement. Embed appropriate SQL and SPL statements between the CREATE PROCEDURE and END PROCEDURE keywords. You can also follow the UDR definition with the DOCUMENT and WITH FILE IN options.

SPL routines are parsed, optimized (as far as possible), and stored in the system catalog tables in executable format. The body of an SPL routine is stored in the sysprocbody system catalog table. Other information about the routine is stored in other system catalog tables, including sysprocedures, sysprocplan, and sysprocauth.

If the Statement Block portion of the CREATE PROCEDURE statement is empty, no operation takes place when you call the procedure. You might use such a "dummy" procedure in the development stage when you intend to establish the existence of a procedure but have not yet coded it.

If you specify an optional clause after the parameter list, you must place a semicolon after the clause that immediately precedes the Statement Block.

The following example creates an SPL procedure:

CREATE PROCEDURE raise_prices ( per_cent INT )
   UPDATE stock SET unit_price =
      unit_price + (unit_price * (per_cent/100) );
END PROCEDURE
   DOCUMENT "USAGE: EXECUTE PROCEDURE raise_prices( xxx )",
   "xxx = percentage from 1 - 100 "
   WITH LISTING IN '/tmp/warn_file'

External Procedures (IDS)

External procedures are procedures you write in an external language that the database server supports.

To create a C user-defined procedure

Write a C function that does not return a value.
Compile the C function and store the compiled code in a shared library (the shared-object file for C).
Register the C function in the database server with the CREATE PROCEDURE statement.

To create a user-defined procedure written in the Java language

Write a Java static method, which can use the JDBC functions to interact with the database server.
Compile the Java source and create a jar file (the shared-object file).
Execute the install_jar( ) procedure with the EXECUTE PROCEDURE statement to install the jar file in the current database.
If the UDR uses user-defined types, create a mapping between SQL data types and Java classes, using the setUDTExtName( ) procedure that is explained in EXECUTE PROCEDURE.
Register the UDR with the CREATE PROCEDURE statement. (If an external routine returns a value, you must register it with the CREATE FUNCTION statement, rather than CREATE PROCEDURE.)

Rather than storing the body of an external routine directly in the database, the database server stores only the pathname of the shared-object file that contains the compiled version of the routine. The database server executes an external routine by invoking the external object code.

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