RE: Result sets in SQLRPGLE -- RPG400-L

Hi Richard,

I do both of them, but not close cursor before open it (it make a lot of message in job log) : I do an open and test sqlcode = -502 :

EXEC SQL
OPEN Lecture;
if sqlcode = -502;
EXEC SQL CLOSE Lecture;
EXEC SQL OPEN Lecture;
endif;

-----Message d'origine-----
De : RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> De la part de Richard Schoen
Envoyé : samedi 20 juillet 2024 19:36
À : rpg400-l@xxxxxxxxxxxxxxxxxx
Objet : RE: Result sets 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.

Couple options which I was just testing as well :-)

Compile with CLOSQLCSR(*ENDMOD) so it closes the cursor when program exits.

-or-

Once you declare the cursor, issue a close before running your query.

This triggers an SQL. sqlcode=-501 return code, but closes the open cursor before running your query and you can then ignore the close error itself.

I prefer using CLOSQLCSR(*ENDMOD). It's cleaner.

Let us know how the CTE example works out.

Regards,
Richard Schoen
Web: https://urldefense.com/v3/__http://www.richardschoen.net__;!!J76aczzK!2lj0I_MT-8mG6jtqcDZ6SC0KE9HEKyIvqODYlzHXw7nuRMjnWVqU5lvdp15acHs88HNcNbq_a5mBBXQx2imu_AZErIkx$
Email: richard@xxxxxxxxxxxxxxxxx<mailto:richard@xxxxxxxxxxxxxxxxx>

------------------------------
message: 2
date: Sat, 20 Jul 2024 09:53:13 +0200
from: <stefan@xxxxxxxxxx>
subject: RE: Result sets in SQLRPGLE

Daniel wrote:
The more modern approach would be to write a user defined table function (UDTF) - with that you can even use the returned data set inside of CTEs or any SQL construct (aside from updates/deletes).

Thanks a lot for pointing me in this direction - seems to be exactly what I need!

Buck and Richard,

Thanks for pointing out how to return an open cursor. My problem here is that I may end up with an open cursor being left behind. Would like to avoid that.

Thank you for your time and effort!

Best regards

Stefan

--
No trees were killed in the sending of this message, but a large number of electrons were terribly upset.

Stefan Tageson
+46 732 369934
stefan@xxxxxxxxxx

--
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!2lj0I_MT-8mG6jtqcDZ6SC0KE9HEKyIvqODYlzHXw7nuRMjnWVqU5lvdp15acHs88HNcNbq_a5mBBXQx2imu_BTNvTo7$
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!2lj0I_MT-8mG6jtqcDZ6SC0KE9HEKyIvqODYlzHXw7nuRMjnWVqU5lvdp15acHs88HNcNbq_a5mBBXQx2imu_CkLfiN9$ .

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.