Informix Guide to SQL: Syntax SPL Statements

Use the SYSTEM statement to make an operating-system command run from within an SPL routine.

If the supplied expression is not a character expression, expression is converted to a character expression before the operating-system command is made. The complete character expression passes to the operating system and executes as an operating-system command.

The operating-system command that the SYSTEM statement specifies cannot run in the background. The database server waits for the operating system to complete execution of the command before it continues to the next statement in the SPL routine.

Your SPL routine cannot use a value or values that the command returns.

If the operating-system command fails (that is, if the operating system returns a nonzero status for the command), an exception is raised that contains the returned operating-system status as the ISAM error code and an appropriate SQL error code.

In DBA- and owner-privileged SPL routines that contain SYSTEM statements, the operating-system command runs with the permissions of the user who is executing the routine.

When the operating-system command that SYSTEM specifies is executed, no guarantee exists that the environment variables that the user application set are passed to the operating system. To ensure that the environment variables that the application set are carried forward to the operating system, enter a SYSTEM command that sets the environment variables before you enter the SYSTEM command that causes the operating-system command to execute.

For information on the operating-system commands that set environment variables, see the Informix Guide to SQL: Reference.

The following example shows a SYSTEM statement in an SPL routine. The SYSTEM statement in this example causes the UNIX operating system to send a mail message to the system administrator.

CREATE PROCEDURE sensitive_update()

	.

	.

	.

	LET mailcall = 'mail headhoncho < alert';

	-- code that evaluates if operator tries to execute a

	-- certain command, then sends email to system

	-- administrator

	SYSTEM mailcall;

	.

	.

	.

END PROCEDURE; -- sensitive_update

You can use a double-pipe symbol (||) to concatenate expressions with a SYSTEM statement, as the following example shows:

CREATE PROCEDURE sensitive_update2()

	DEFINE user1 char(15);

	DEFINE user2 char(15);

	LET user1 = 'joe';

	LET user2 = 'mary';

	.

	.

	.

	-- code that evaluates if operator tries to execute a

	-- certain command, then sends email to system

	-- administrator

	SYSTEM 'mail -s violation' ||user1 || ' ' || user2

					 || '< violation_file';

	.

	.

	.

END PROCEDURE; --sensitive_update2 

The following example shows a SYSTEM statement in an SPL routine. The first SYSTEM statement in this routine causes the Windows NT operating system to send an error message to a temporary file and to put the message in a system log that is sorted alphabetically. The second SYSTEM statement causes the operating system to delete the temporary file.

CREATE PROCEDURE test_proc()

	.

	.

	.

	SYSTEM 'type errormess101 > %tmp%tmpfile.txt |

			sort >> %SystemRoot%systemlog.txt';

	SYSTEM 'del %tmp%tmpfile.txt';

	.

	.

	.

END PROCEDURE; --test_proc

The expressions that follow the SYSTEM statements in this example contain two variables, %tmp% and %SystemRoot%. Both of these variables are defined by the Windows NT operating system.