Informix Guide to SQL: Syntax SPL Statements

Use the TRACE statement to control the generation of debugging output.

The TRACE statement generates output that is sent to the file that the SET DEBUG FILE TO statement specifies.

Tracing prints the current values of the following items:

• Variables
• Routine arguments
• Return values
• SQL error codes
• ISAM error codes

The output of each executed TRACE statement appears on a separate line.

If you use the TRACE statement before you specify a DEBUG file to contain the output, an error is generated.

Any routine that the SPL routine calls inherits the trace state. That is, a called routine assumes the same trace state (ON, OFF, or PROCEDURE) as the calling routine. The called routine can set its own trace state, but that state is not passed back to the calling routine.

A routine that is executed on a remote database server does not inherit the trace state.

If you specify the keyword ON, all statements are traced. The values of variables (in expressions or otherwise) are printed before they are used. To turn tracing ON implies tracing both routine calls and statements in the body of the routine.

If you specify the keyword OFF, all tracing is turned off.

If you specify the keyword PROCEDURE, only the routine calls and return values, but not the body of the routine, are traced.

The TRACE statement does not have ROUTINE or FUNCTION keywords. Therefore, use the TRACE PROCEDURE keywords even if the SPL routine you want to trace is a function.

You can use the TRACE statement with a quoted string or an expression to display values or comments in the output file. If the expression is not a literal expression, the expression is evaluated before it is written to the output file.

You can use the TRACE statement with an expression even if you used a TRACE OFF statement earlier in a routine. However, you must first use the SET DEBUG statement to establish a trace-output file.

The following example uses a TRACE statement with an expression after using a TRACE OFF statement. The example uses UNIX file-naming conventions.

CREATE PROCEDURE tracing ()

	DEFINE i INT;

BEGIN

	ON EXCEPTION IN (1)

	END EXCEPTION; -- do nothing

	SET DEBUG FILE TO '/tmp/foo.trace';

	TRACE OFF;

	TRACE 'Forloop starts';

	FOR i IN (1 TO 1000)

		BEGIN

			TRACE 'FOREACH starts';

			FOREACH SELECT...INTO a FROM t

				IF <some condition> THEN

					RAISE EXCEPTION 1    -- emergency exit

				END IF

			END FOREACH



			-- return some value

		END

	END FOR

	

	-- do something

END; 

END PROCEDURE

The following example shows several different forms of the TRACE statement. The example uses Windows NT file-naming conventions.

CREATE PROCEDURE testproc()

	DEFINE i INT;



	SET DEBUG FILE TO 'C:\tmp\test.trace';

	TRACE OFF;

	TRACE 'Entering foo';



	TRACE PROCEDURE;

	LET i = test2();



	TRACE ON;

	LET i = i + 1;



	TRACE OFF;

	TRACE 'i+1 = ' || i+1;

	TRACE 'Exiting testproc';

	

	SET DEBUG FILE TO 'C:\tmp\test2.trace';



END PROCEDURE

To see the traced output, use an editor or utility to display or read the contents of the file.