IBM i 7.1 brought us the RUNSQL command. This allows me to run SQL statements within the CL.
Prior to IBM i 7.1 I would have to use the RUNSQLSTM command. To use this command I need to have the SQL statement in a source member. The way I knew to run build a SQL statement in CL program was to write it to a source member that could then be used by the RUNSQLSTM command. I always considered it too much work and either used a SQLRPGLE, RPGLE with embedded SQL, or a QMQRY, Query Management Query.
While I do not see the RUNSQL command replacing my use of SQLRPGLE, I do see it eliminating most of my use of QMQRYs.
You cannot execute all SQL statements using the RUNSQL command. Click on the link at the bottom of this post to the IBM website for a list of what statements are supported.
In this first example I want to create an outfile in QTEMP using the CREATE TABLE SQL statement I used in the post Creating a SQL table on the fly:
01 PGM 02 RUNSQL SQL('Drop Table qtemp/temp_table') + COMMIT(*NONE) 03 MONMSG MSGID(SQL9010) 04 RUNSQL SQL('CREATE TABLE QTEMP/TEMP_TABLE AS + (SELECT FLD0,FLD1,FLD2 + FROM TESTPF + WHERE FLD0 = ''1'') + WITH DATA') + COMMIT(*NONE) 05 ENDPGM
The first RUNSQL command, line 2, executes the SQL DROP TABLE command to delete the outfile in QTEMP. This also shows that you can use both lower and upper case in the command. I do not want to use commitment control for any of my SQL statements, therefore, I have *NONE in the COMMIT parameter.
If an error is encounter, i.e. the table does not exist, then it is monitored by the use of the MONMSG for the message SQL9010. SQL9010 simply means “Error found in SQL statement".
The second RUNSQL command, line 4, creates the outfile using the CREATE TABLE statement.
It is possible to do database functions too:
01 PGM 02 DCL VAR(&VALUE) TYPE(*CHAR) LEN(10) VALUE('TEST') 03 DCL VAR(&PARM) TYPE(*CHAR) LEN(100) 03 RUNSQL SQL('INSERT INTO MYLIB/TESTPF + VALUES(''' || &VALUE |< ''', + 123,''ABC'')') + COMMIT(*NONE) 04 CHGVAR VAR(&PARM) + VALUE('DELETE FROM MYLIB/TESTPF + WHERE FLD0 = ''' || &VALUE |< + ''' ') 05 RUNSQL SQL(&PARM) COMMIT(*NONE) 06 ENDPGM
Note: The || and |< are the shorthand equivalent of the *CAT and *BCAT functions in CL.
Line 3 shows how I could integrated a variable into the text string in the SQL parameter.
Line 4 and 5 show how I can use a variable in the SQL parameter.
You can learn more about the RUNSQL command on IBM's website here.