WITH LISTING IN Clause

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.

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

On UNIX platforms, 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 systems, 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 Functions

SPL functions are UDRs written in SPL that return one or more values. To write and register an SPL function, use a CREATE FUNCTION statement. Embed appropriate SQL and SPL statements between the CREATE FUNCTION and END FUNCTION keywords. You can also follow the function with the DOCUMENT and WITH FILE IN options.

SPL functions are parsed, optimized (as far as possible), and stored in the system catalog tables in executable format. The body of an SPL function is stored in the sysprocbody system catalog table. Other information about the function is stored in other system catalog tables, including sysprocedures, sysprocplan, and sysprocauth. For more information about these system catalog tables, see the IBM Informix Guide to SQL: Reference.

The END FUNCTION keywords are required in every SPL function, and a semicolon ( ; ) must follow the clause that immediately precedes the statement block. The following code example creates an SPL function:

CREATE FUNCTION update_by_pct ( pct INT, pid CHAR(10))
   RETURNING INT;
   DEFINE n INT;
   UPDATE inventory SET price = price + price * (pct/100)
      WHERE part_id = pid;
   LET n = price;
   RETURN price;
END FUNCTION
   DOCUMENT "USAGE: Update a price by a percentage",
         "Enter an integer percentage from 1 - 100",
         "and a part id number"
   WITH LISTING IN '/tmp/warn_file'

For more information on how to write SPL functions, see the chapter about SPL routines in IBM Informix Guide to SQL: Tutorial.

See also the section Transactions in SPL Routines.

You can include valid SQL or SPL language statements in SPL functions. See, however, the following sections in Chapter 5 that describe restrictions on SQL and SPL statements within SPL routines: Subset of SPL Statements Valid in the Statement Block; SQL Statements Not Valid in an SPL Statement Block; and Restrictions on SPL Routines in Data-Manipulation Statements.

Internal representation

External functions are functions you write in an external language (that is, a programming language other than SPL) that Dynamic Server supports.

To create a C user-defined function

Write the C function.
Compile the function and store the compiled code in a shared library (the shared-object file for C).
Register the function in the database server with the CREATE FUNCTION statement.

To create a user-defined function 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 file and create a .jar file (the shared-object file for Java).
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 map between SQL data types and Java classes. Use the setUDTExtName( ) procedure that is explained in EXECUTE PROCEDURE.
Register the UDR with the CREATE FUNCTION statement.

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. When it executes the external routine, the database server invokes the external object code.

The database server stores information about an external function in system catalog tables, including sysprocbody and sysprocauth. For more information on the system catalog, see the IBM Informix Guide to SQL: Reference.