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

Hi Daniel,

Many thanks for this information.
I will look at this quickly and very carefully.

-----Message d'origine-----
De : RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> De la part de daniel@xxxxxxxx
Envoyé : jeudi 14 septembre 2023 15:40
À : RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Objet : Re: How to retrieve an SQL statement into a variable in SQLRPGLE



ATTENTION : Ce message provient d'une source EXTERNE. Veuillez n'ouvrir les liens ou pièces jointes que si vous êtes sûr de l'expéditeur.



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://urldefense.com/v3/__https://gist.github.com/qpgmr-de/4e31d8033d58a39d63c69b09f54caba9__;!!J76aczzK!yhyQbrId4iPF3lbq4kERgSRK7_p8fApd94-RqkN_-WBvhXmaVpLqNPTmGEldwb7GGmldD7TvWCg9HxkdgtEWny8$

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://urldefense.com/v3/__https://lists.midrange.com/mailman/listinf
o/rpg400-l__;!!J76aczzK!yhyQbrId4iPF3lbq4kERgSRK7_p8fApd94-RqkN_-WBvhX
maVpLqNPTmGEldwb7GGmldD7TvWCg9Hxkd9i1QH8k$
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.com/v3/__https://archive.midrange.com/rpg400-l__;!!J76aczzK!yhyQbrId4iPF3lbq4kERgSRK7_p8fApd94-RqkN_-WBvhXmaVpLqNPTmGEldwb7GGmldD7TvWCg9Hxkd0KhMl4I$ .

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://urldefense.com/v3/__https://lists.midrange.com/mailman/listinfo/rpg400-l__;!!J76aczzK!yhyQbrId4iPF3lbq4kERgSRK7_p8fApd94-RqkN_-WBvhXmaVpLqNPTmGEldwb7GGmldD7TvWCg9Hxkd9i1QH8k$
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://urldefense.com/v3/__https://archive.midrange.com/rpg400-l__;!!J76aczzK!yhyQbrId4iPF3lbq4kERgSRK7_p8fApd94-RqkN_-WBvhXmaVpLqNPTmGEldwb7GGmldD7TvWCg9Hxkd0KhMl4I$ .

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.