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.

--
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.