Informix Guide to SQL: Tutorial Creating and Using SPL Routines

When you use CREATE PROCEDURE or CREATE FUNCTION to write an SPL routine with DB-Access or the Relational Object Manager, the statement fails when you select Run from the menu, if a syntax error occurs in the body of the routine.

If you are creating the routine in DB-Access or the Relational Object Manager, when you choose the Modify option from the menu, the cursor moves to the line that contains the syntax error. You can select Run and Modify again to check subsequent lines.

If the database server detects a potential problem, but the syntax of the SPL routine is correct, the database server generates a warning and places it in a listing file. You can examine this file to check for potential problems before you execute the routine.

The filename and pathname of the listing file are specified in the WITH LISTING IN clause of the CREATE PROCEDURE or CREATE FUNCTION statement. For information about how to specify the pathname of the listing file, see Specifying a Document Clause.

If you are working on a network, the listing file is created on the system where the database resides. If you provide an absolute pathname and filename for the file, the file is created at the location you specify.

If you provide a relative pathname for the listing file, the file is created in your home directory on the computer where the database resides. (If you do not have a home directory, the file is created in the root directory.)

If you provide a relative pathname for the listing file, the default directory is your current working directory if the database is on the local computer. Otherwise the default directory is %INFORMIXDIR%\bin.

After you create the routine, you can view the file that is specified in the WITH LISTING IN clause to see the warnings that it contains.

After you create an SPL routine, it is stored in the sysprocbody system catalog table. The sysprocbody system catalog table contains the executable routine, as well as its text.

To retrieve the text of the routine, select the data column from the sysprocbody system catalog table. The datakey column for a text entry has the code T.

The SELECT statement in Figure 10-106 reads the text of the SPL routine read_address.

SELECT data FROM informix.sysprocbody

	WHERE datakey = 'T'				 		-- find text lines

	AND procid = 
		( SELECT procid 
		FROM informix.sysprocedures

		WHERE informix.sysprocedures.procname =
			'read_address' )