Re: How to retrieve an SQL statement into a variable in SQLRPGLE

Hi Jean-Marc,

I recently encountered the same problem in our application - after an unsuccessful SQL statement I wanted to have the statement text in the error message file.

All solutions which use SQL services like GET_JOB_INFO() or ACTIVE_JOB_INFO() won’t work, because if you use these, you always end up with the query to that service. This will only work, if another job looks from the outside.

So in short - you cannot use SQL to retrieve the last SQL statement of your program - but you can use the system API „QUSRJOBI“.

I created a small GIST: https://gist.github.com/qpgmr-de/4e31d8033d58a39d63c69b09f54caba9

The small program executes a SQL SELECT statement, and then uses a small procedure to call QUSRJOBI to retrieve the last SQL statement. Of course you should pack such a procedure in a service program and inside your SQL-error-logging-procedure which should call QUSRJOBI first, before using GET DIAGNOSTICS or any other SQL statement, because the API retrieve the LAST, and only the LAST SQL statement executed in the job.

I hope you will find the code readable and useful - and hopefully it will help others too.

Kind regards,
Daniel


P.S.: @Seán Courtney - I also thought, that the SQL table function should return a good value - but they don’t - so you can’t read into the past with a do-loop, because there is only one row per job, and if you execute this for the current job, it will always return the statement, which calls the SQL table function.

Am 12.09.2023 um 15:31 schrieb Jean-Marc DUVAL via RPG400-L <rpg400-l@xxxxxxxxxxxxxxxxxx>:

Hi,

With a view to creating logs for my SQLRPGLE processing, I would like to know if there is a possibility of recovering the text of the query submitted into a character variable.
Thanks for ideas;

Cordialement,

Jean-Marc DUVAL
Pôle développement IT
Développeur IBM i consultant

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.