If you've ever wanted to quickly run a Python, PHP, Node, bash script or other PASE based CLI command or program you've been able to use my QShell on i utilities to run them via the QSHEXEC or QSHBASH commands and then consume the results in a CL or RPG program by reading the OUTFILE results.
There's also an RPGLE service program that can be used to call QSHEXEC and QSHBASH that makes things a bit simpler buy putting a sub procedure interface around the commands.
IBM has recently been promoting the use of SQL table functions and I hadn't had a chance to play with them until today. This morning I was inspired by an example of running PASE commands via SQL from Jesse G to create a table function over the QSHEXEC and QSHBASH commands. The result of this effort is the ability to call QSHEXEC and QSHBASH as SQL table functions from an SQLRPGLE program.
Here's an example of running a PASE directory list with QSHEXEC or QSHBASH and returning the results as an SQL table:
SELECT * FROM TABLE(QSHONI.QSHEXEC_TABLE('ls -l /tmp')) AS X;
SELECT * FROM TABLE(QSHONI.QSHBASH_TABLE('ls -l /tmp')) AS X;
Here's an example of running a Python application and returning the output results:
SELECT * FROM TABLE(QSHONI.QSHEXEC_TABLE('python3 /pythonapps/hello.py')) AS X;
SELECT * FROM TABLE(QSHONI.QSHBASH_TABLE('python3 /pythonapps/hello.py')) AS X;
LINE_NO - Line number in the OUTFILE
OUTPUT_LINE - 1000 character line data
CMD_SUCCESS - This field shows is the command was successful or not. If any record has a value or 0 in the CMD_SUCCESS, the PASE command ran successfully. If any record has a value of -1 it means the PASE command didn't run successfully. The OUTPUT_LINE data will either contain valid STDOUT return data on success or any relevant error messages on failure. The table functions should not hard halt on errors so always make sure to check the CMD_SUCCESS result field at least for LINE_NO 1.
Since this is an SQL table the contents can also be filtered:
This example lists and filters a directory result list for anything containing "tmp":
SELECT * FROM TABLE(QSHONI.QSHEXEC_TABLE('ls -l /tmp')) AS X where output_line like '%tmp%';
The SQL functions can be created using ACS Run SQL scripts in any library although the example SQL scripts create them in QSHONI.
The SQL table function source is located here on the QSHONI site:
https://github.com/richardschoen/QshOni/tree/master/sqlsamples
Have fun and let me know how you're using these with your SQL RPG programs.
