RUNSQLSTM using QCMDEXC

[nishar]

I am executing a few sql scripts through RPG program using QCMDEXC.


command = 'RUNSQLSTM SRCFILE('+%trim(sqlLibrary)+
'/'+%trim(sqlSource)+') '+'SRCMBR('+%trim(sqlMember)+
') COMMIT(*NONE)' ;


How do I get to know the status of the SQL scripts in program? Is there a way I can do this?

[JonBoy]

I find it hard to understand why you would do this in the first place rather than simply embed the SQL in your RPG program which will give you any number of benefits - not the least being the question you raise here - easy access to status.

That said - you can only get the error(s) that RUNSQLSTM supplies which appears to be SQL9010 no matter whether the error was with the executing statement or if the script file itself cannot be found. I haven't used RUNSQLSTM in eons so this is based on a couple of simple tests.

As long as you have QCMDEXC set up correctly you can trap that error - but that's about it.

[Scott Klement]

Yeah, calling RUNSQLSTM from an RPG program doesn't really make much sense. Use either embedded SQL (super easy to do) or if your company refuses to buy it (which is rare, imho) use the CLI functions -- these are designed for putting SQL code into a program and getting back results (as well as errors) properly.

[Vectorspace]

I had to do this once. I had to run dynamic SQL of the form "insert into mytable select * from otherSystem/lib/mytable".
I had to use RUNSQLSTM because the table names are dynamic, and if it was prepared embedded then it required SQL packages which wouldn't work because it's dynamic.

The only way I could get the results was to retrieve and parse the results spool file that RUNSQLSTM creates.

[nishar]

The only way I could get the results was to retrieve and parse the results spool file that RUNSQLSTM creates. - How did you di that?

[nishar]

I coulnot embed the SQL scripts inside the RPG program because those scripts were created by different set of people and all I have is a configuration file which has the location of the script. So I am reading that file and getting the location and executing those script. Tomorrow the script may change. Those who creating the script doesnot want to change an RPGLE program for that.

[Vectorspace]

After executing RUNSQLSTM, use CPYSPLF to copy the resulting spool file to a table. Then in RPGLE, read that table and parse it.

Does each SQL script contain only one SQL statement? If so then you could read that script file into RPGLE, and execute it in embedded sql using EXECUTE IMMEDIATE, which lets an SQLRPGLE program execute an SQL statement stored in a string variable: https://www.ibm.com/docs/en/i/7.1?to...cute-immediate

[nishar]

Basically I need to read the SQL script from source file as one string in RPGLE. If I get that I can execute it.
The CPYSPLF method I dont understand as I get the spool file only after executing the statements with RUNSQLSTM. So executing the statements in the SPLF will lead to double execution right? Correct me if am wrong .

[B.Hauser]

Basically I need to read the SQL script from source file as one string in RPGLE.

Check the GET_CLOB_FROM_FILE() or IFS_READ Functions in SQL.
GET_CLOB_FROM_FILE

https://www.ibm.com/docs/en/i/7.5?topic=functions-get-clob-from-file

IFS_READ

https://www.ibm.com/docs/en/i/7.5?topic=functions-get-clob-from-file

[Vectorspace]

I meant extracting the results of the SQL execution from the CPYSPLF, not the statement itself.

When you call RUNSQLSTM to run SQL, RUNSQLSTM will generate s spool file. Among other things, that spool file contains the result of executing that statement. E.g. if the SQL statement was an insert, the spool file should contain something like this:

MSG ID  SEV  RECORD  TEXT                                          
SQL7956   0       1  Position 1 1 rows inserted in MYFILE in MYLIB.​

So if you want the number of affected rows for example: after calling RUNSQLSTM, you can call CPYSPLF to get the last generated spool file into a flat file. Then you can search the file for a line starting with SQL7956. Then use regular string search/manipulation/substringing etc. to extract the portion of the line that is the number of affected records.

But if you can import the SQL statement into your RPGLE program somehow, using the functions B.Hauser mentioned if the scripts are on the IFS, or some other method if the scripts are in source file members, then you can execute them with exec sql EXECUTE IMMEDIATE instead of the RUNSQLSTM/CPYSPLF method.

[nishar]

Vectorspace thanks for your inputs. I created a member for the source physical file using CREATE ALIAS inside SQLRPGLE program and used listagg to get the query inside a string variable and then executed it and used SQL diagnostics to get the impacted rows. !!

[Vectorspace]

Coolio

Note that create alias creates a permanent system object (unless you create it in QTEMP)