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. Please note that the result of standard output and standard error is limited to 100,000 characters.

For both Windows and Linux operating systems, the path to the script can be specified using a slash. On the Windows operating system, 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 with 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 than 0, the job will fail, and its state can be used to decide the execution of dependent schedules.

Please be advised that 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 the newly created users/roles.

SYSADMIN.executeCli

This procedure executes an arbitrary JBoss CLI script passed with the script parameter. Each command in the script is on a different line (divided by LF char).

SQL

SYSADMIN.executeCli(IN script string NOT NULL)

In this example, we use it 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