Re: How to retrieve SQL results into CL pgm

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Nice! And I don't mean the seaport in France!

:)

Vern

On 6/4/2022 4:25 AM, Birgitta Hauser wrote:
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
> Modernization – Education – Consulting on IBM i
>
> Unfortunately you cannot handle SQL Cursors/SELECT Statements
>
> What you can do:
> 1. Write a RPG Function that will execute the SQL command and return the
> Result 2. If you want to do it only with SQL:
> The easisest way is to create a View in the QTEMP, Declare the view as file
> and read the file with RCVF
>
> Something linke this (corrected):
>          DCLF       FILE(YourView)
>
>          RUNSQL     SQL('Drop View QTEMP/YourView') COMMIT(*NONE)
>          MONMSG     MSGID(SQL0000)
>                                                                             
>          RUNSQL     SQL('Create View QTEMP/YourView                       +
>                          as (Select . . .                                 +
>                                FROM . . .                                 +
>                                . . .)                                     +
>                          RcdFmt YourViewF')                               +
>                     COMMIT(*NONE)
>   
>
>          RCVF       RCDFMT(YourViewF)
>          MONMSG     MSGID(CPF0864) EXEC(DO)
>                     /* Not Found - Do whatever you want */
>          ENDDO
>
>          /* Found: Do whatever you want */
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
> Modernization – Education – Consulting on IBM i
>
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown) "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training them
> and keeping them!"
> „Train people well enough so they can leave, treat them well enough so they
> don't want to.“ (Richard Branson)
>
>
> -----Original Message-----
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Steve
> McKay
> Sent: Freitag, 3. Juni 2022 23:31
> To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
> Subject: How to retrieve SQL results into CL pgm
>
> How can I run an SQL statement and retrieve the results into a CL pgm?
>
> For example, I want to SELECT COUNT(*) FROM QSYS2.ASP_INFO: but I want to
> run it using RUNSQL in a CL program and get the COUNT into a variable in the
> CL program.
>
> Something like this:
> DCL &COUNT *DEC LEN(1)
> RUNSQL SQL( SELECT COUNT(*) FROM QSYS2.ASP_INFO) and get the SQL statement
> result in the CL &COUNT field.
>
> Ultimately, I want to do different things depending on whether there is 1
> ASP or more than 1 ASP so I'm really after the count of ASPs on the system.
> If there is a better way to do this within CL, I'm open to suggestions.
>
> Thanks,
>
> Steve McKay
> (205) 585-8424
> mailto:samckay1@xxxxxxxxx
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
> unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives at
> https://archive.midrange.com/midrange-l.
>
> Please contact mailto:support@xxxxxxxxxxxxxxxxxxxx for any subscription
> related questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate link:
> https://amazon.midrange.com