Informix Guide to SQL: Tutorial Creating and Using SPL Routines

After you successfully create and run an SPL routine, you can encounter logic errors. If the routine has logic errors, use the TRACE statement to help find them. You can trace the values of the following items:

• Variables
• Arguments
• Return values
• SQL error codes
• ISAM error codes

To generate a listing of traced values, first use the SQL statement SET DEBUG FILE to name the file that is to contain the traced output. When you create the SPL routine, include a TRACE statement.

The following methods specify the form of TRACE output.

Figure 10-107 demonstrates how you can use the TRACE statement to monitor how an SPL function executes.

CREATE FUNCTION read_many  (lastname CHAR(15))

	RETURNING CHAR(15), CHAR(15), CHAR(20), CHAR(15),CHAR(2),

		CHAR(5);



	DEFINE p_lname,p_fname, p_city CHAR(15);

	DEFINE p_add CHAR(20);

	DEFINE p_state CHAR(2);

	DEFINE p_zip CHAR(5);

	DEFINE lcount, i INT;



	LET lcount = 1;



	TRACE ON; -- Every expression will be traced from here on

	TRACE 'Foreach starts';										
		-- A trace statement with a literal


	FOREACH

	SELECT fname, lname, address1, city, state, zipcode

        INTO p_fname, p_lname, p_add, p_city, p_state, p_zip

        FROM customer

        WHERE lname = lastname

	RETURN p_fname, p_lname, p_add, p_city, p_state, p_zip

		WITH RESUME;

	LET lcount = lcount + 1;		 	 -- count of returned addresses

	END FOREACH



	TRACE 'Loop starts'; -- Another literal 

	FOR i IN (1 TO 5)

        BEGIN

          RETURN i , i+1, i*i, i/i, i-1,i with resume; 

        END

	END FOR;



END FUNCTION; 

With the TRACE ON statement, each time you execute the traced routine, entries are added to the file you specified in the SET DEBUG FILE statement. To see the debug entries, view the output file with any text editor.

The following list contains some of the output that the function in Figure 10-107 generates. Next to each traced statement is an explanation of its contents.