Re: ACS Run SQL Scripts - define a variable

[Niels Liisberg <nli@xxxxxxxxxxxxxxxxx>]

You can use compound statement in ACS that is stred procdure with out the
parameters and only the begin end:

https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/db2/rbafzcompoundstmt.htm

Like this:

begin

    declare rows int;

    -- do you stuff
     GET DIAGNOSTICS Rows = ROW_COUNT ;  --to get the number of rows
end;


On Thu, Jun 18, 2020 at 10:42 AM Tim Fathers <X700-IX2J@xxxxxxxxxxx> wrote:

> Unfortunately, according to the manual, get diagnostics can only be used
> in an embedded program, not interactively:
>
> "This statement can only be embedded in an application program, SQL
> function,
> SQL procedure, or trigger. It cannot be issued interactively. It is an
> executable
> statement that cannot be dynamically prepared."
>
> Tim.
>
> ________________________________
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Don
> Brown via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx>
> Sent: 18 June 2020 00:27
> To: midrange-l@xxxxxxxxxxxxxxxxxx <midrange-l@xxxxxxxxxxxxxxxxxx>
> Cc: Don Brown <DBrown@xxxxxxxxxx>
> Subject: ACS Run SQL Scripts - define a variable
>
> In a post from Jon Paris on a different thread Jon suggested using
>
> exec sql GET DIAGNOSTICS :Rows = ROW_COUNT ;  to get the number of rows
> returned by the previous select.
>
> Is it possible to do this in the ACS run SQL Scrips - How can I define a
> variable to receive the row count into ?
>
>
> Thanks
>
> Don
>
>
>
>
> ______________________________________________________________________
> This email has been scanned for computer viruses. Although MSD has taken
> reasonable precautions to ensure no viruses are present in this email, MSD
> cannot accept responsibility for any loss or damage arising from the use of
> this email or attachments.
> ______________________________________________________________________
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit:
> https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&amp;data=02%7C01%7C%7Cf7c0e1818e6046bbf3f708d8130d9e81%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637280296493560955&amp;sdata=7bPjVk1LBoor4sWP4RflzIaslZKx2QV%2B4YXVOAaK%2Fbk%3D&amp;reserved=0
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at
> https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&amp;data=02%7C01%7C%7Cf7c0e1818e6046bbf3f708d8130d9e81%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637280296493560955&amp;sdata=pgU2%2Bit6uIHLc86bv6A9a2%2Ftz%2Bofr0SzZFO4hMdf6TQ%3D&amp;reserved=0
> .
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link:
> https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com%2F&amp;data=02%7C01%7C%7Cf7c0e1818e6046bbf3f708d8130d9e81%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637280296493560955&amp;sdata=ablakW424k5Kvsibq5FGPZ%2FJFPqyRwLHkOG%2BeHHxQQE%3D&amp;reserved=0
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com