Execute Procedures

SYSADMIN.execExternalProcess

This procedure executes shell scripts or external programs:

SQL

SYSADMIN.execExternalProcess(IN command string NOT NULL, IN args object, OUT exitCode integer RESULT, OUT stdOut clob, OUT stdErr clob)

It accepts a command (the absolute name of the script to be executed) and a list of arguments (possibly null), and returns an integer representing the exit code of the call to the external process and the results of the command sent to stdout and stderr. Please note that standard output and standard error results are limited to 100,000 characters.

For both Windows and Linux operating systems, the path to the script can be specified using a slash. On Windows, the path can also be specified using a backslash, escaped with a second backslash.

Examples

1. Example using slashes

SQL

SELECT * FROM (CALL execExternalProcess(command => '/tmp/shellScripts/shellScript.sh'), args => null) a

2. Example using escaped backslashes

SQL

SELECT * FROM (CALL execExternalProcess(command => 'D:\\tmp\\shellScripts\\shellScript.bat', args => ARRAY('c:'))) a

If the stored procedure fails during its execution (e.g. if the file name does not exist), an exception is thrown.

Using execExternalProcess to Control Execution of Dependent Schedules

To use this stored procedure to control the execution of dependent schedules, you can create a job using the following SQL script:

SQL

BEGIN
IF (NOT EXISTS (SELECT * FROM (CALL execExternalProcess(command => 'D:/tmp/shellScripts/shellScript.bat', args => ARRAY('c:'))) a WHERE exitCode = 0))
ERROR 'failed';
END

The previous code supposes that the script D:/tmp/shellScripts/shellScript.bat returns, for example, 0 as a successful exit code. If the exit code is different from 0, the job will fail, and its state can be used to determine the execution of dependent schedules.

This stored procedure can be potentially dangerous since any kind of script could be executed by the system.

By default, this stored procedure (as all the other system procedures in SYSADMIN) is visible to the user admin, but is not available to newly created users/roles.

SYSADMIN.executeCli

This procedure executes an arbitrary JBoss CLI script passed with the script parameter. It can be used, for example, for logging tasks.

Each command in the script is on a different line (divided by LF char):

SQL

SYSADMIN.executeCli(IN script string NOT NULL, IN maskInLogs boolean DEFAULT 'false')

The maskInLogs parameter can be used to mask the script in the server log tables and files if it contains any sensitive data.

maskInLogs parameter of the SYSADMIN.executeCli procedure available since v4.7

Example

In this example, the procedure is used to get a configured SMTP server:

SQL

SELECT 
  xpathvalue(x.reply, '/r/result/host') AS host, 
  CAST(xpathvalue(x.reply, '/r/result/port') AS integer) AS port 
FROM (
   SELECT 
     jsontoxml('r', replace(reply, '=>', ':')) AS reply 
   FROM
   (
     CALL executeCli(
       script => E'/socket-binding-group=standard-sockets/remote-destination-outbound-socket-binding=mail-smtp:read-resource'
     )
   ) a
) x