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

Hi Daniel,
I grabbed the code and compiled it.
(I'll say it anyway) Works as advertised. Neat code.
Thanks for sharing.
John

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of daniel@xxxxxxxx
Sent: Thursday, September 14, 2023 8:40 AM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: How to retrieve an SQL statement into a variable in SQLRPGLE

CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.


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__;!!JAIo9NhAvg!r2aFUkAhH7Z4HnEj4fkiRE7E1Jk6_bylhKi4fuk3wSm8gd_9ecGfoeZE91Y9EqCmqZXdjuBzFdZSPWQNQw$

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__;!!JAIo9NhAvg!r2aFUkAhH7Z4HnEj4fkiRE7E1Jk6_bylhKi4fuk3wSm8
gd_9ecGfoeZE91Y9EqCmqZXdjuBzFdYfUfaf8Q$
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__;!!JAIo9NhAvg!r2aFUkAhH7Z4HnEj4fkiRE7E1Jk6_bylhKi4fuk3wSm8gd_9ecGfoeZE91Y9EqCmqZXdjuBzFdaxxRarWg$ .

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__;!!JAIo9NhAvg!r2aFUkAhH7Z4HnEj4fkiRE7E1Jk6_bylhKi4fuk3wSm8gd_9ecGfoeZE91Y9EqCmqZXdjuBzFdYfUfaf8Q$
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__;!!JAIo9NhAvg!r2aFUkAhH7Z4HnEj4fkiRE7E1Jk6_bylhKi4fuk3wSm8gd_9ecGfoeZE91Y9EqCmqZXdjuBzFdaxxRarWg$ .

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.