Re: Inconsistency with SQL in ACS vs RPG

Hi Birgitta,

yes - prompting the values is already available, but it has some quirks. 

a) if you use the same :named-variable multiple times in you statement, it prompts the same variable multiple times 

b) it doesn't remember values from the last execution - so if you execute the same statement multiple times, you have to re-enter the values every time

There are already 2 ideas in the portal, to fix a) and b) - but I have seen that nice "bind:" solution in the VScode/Db2i extension, which seems to work nice. It doesn't fix a) right now, but that wouldn't be so hard. 

So adding this "bind:" to RSS would also bring both tools en par.

Regards,
Daniel


> Am 11.10.2025 um 04:43 schrieb Birgitta Hauser <[email protected]>:
> 
> Isn't that not available in Run SQL Script in ACS?
> 
> Just copy your SELECT (or whatever Statement) into ACS (incl. the Host variables, i.e. :HostVar1, :HostVar2 ...
> ... and then run it.
> A window will be open which lists all Host Variables (incl. Variable Names) ... and you just enter the values with which you want to test.
> 
> Mit freundlichen Grüßen / Best regards
> 
> Birgitta Hauser
> Modernization – Education – Consulting on IBM i
> Database and Software Architect  
> IBM Champion since 2020
> 
> "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)
> "Learning is experience … everything else is only information!" (Albert Einstein)
> 
> 
> -----Original Message-----
> From: RPG400-L <[email protected]> On Behalf Of Daniel Gross
> Sent: Saturday, 11 October 2025 02:42
> To: [email protected]
> Subject: Re: Inconsistency with SQL in ACS vs RPG
> 
> Hi,
> 
> this week at TechXchange in Orlando, I have seen a nice feature in the VScode/Db2i extension:
> 
> select * from table where col1 = ? and col2 = ?;
> bind: 'ABC' 123;
> bind: 'DEF' 456;
> 
> The "bind:" lines bind the values to the parameters. And of course, when they do that, it should also work with "named variables":
> 
> select * from table where col1 = :var1 or col3 = :var1;
> bind: var1='ABC';
> bind: var1='DEF';
> 
> Every "bind:" line stands for an individual execution of the SQL statement above.
> 
> This would make copying SQL statements out of a program source so much easier - and it would give us the opportunity to have "persistant" values for variables.
> The only difference is that you always have to type in the values.
> So just keeping the previous values and show them as default could make life easier.
> 
> I created an idea, to add that to iACS/RSS too: https://ibm-power-systems.ideas.ibm.com/ideas/IBMI-I-4655
> 
> If you like it, please vote - thank you.
> 
> Kind regards,
> Daniel
> 
> 
>> Am 09.10.2025 um 09:52 schrieb Vern Hamberg via RPG400-L <[email protected]>:
>> That's the one, Dan - thanks for finding it. I voted.
>> 
>> *Regards*
>> 
>> *Vern Hamberg*
>> 
>> IBM Champion 2025 <cid:[email protected]> CAAC
>> (COMMON Americas Advisory Council) IBM Influencer 2023
>> 
>> 
>>> On 10/8/2025 7:43 PM, Dan Bale wrote:
>>> You mean, like this one?
>>> 
>>> https://ibm-power-systems.ideas.ibm.com/ideas/IBMI-I-4640
>>> 
>>> - Dan Bale
>>> 
>>> -----Original Message-----
>>> From: RPG400-L<[email protected]> On Behalf Of Vern
>>> Hamberg via RPG400-L
>>> Sent: Wednesday, October 8, 2025 5:51 PM
>>> To:[email protected]
>>> Cc: Vern Hamberg<[email protected]>
>>> Subject: Re: Inconsistency with SQL in ACS vs RPG
>>> 
>>> Hi all
>>> 
>>> We can also save the SQL that contains host variables or parameter markers - it's just a text file.
>>> 
>>> Daniel, I'm also not happy that I have to enter a value for each instance of a host variable. I go back to how it works in QMQRY, the same substitution string needs the value entered only one time. I know, different interface.
>>> 
>>> But one of us should submit an Idea for this to change. I'll look to
>>> see if there might already be one, maybe submit it myself - what a
>>> concept, eh?  :)
>>> 
>>> *Regards*
>>> 
>>> *Vern Hamberg*
>>> 
>>> IBM Champion 2025<cid:[email protected]> CAAC
>>> (COMMON Americas Advisory Council) IBM Influencer 2023
>>> 
>>> 
>>> On 10/8/2025 9:09 AM,[email protected] wrote:
>>>> Daniel
>>>> In RSS try creating variables first and then populate the variables with a SET statement.
>>>> You can then save the SQL for later use and you will not have to re-enter any values.
>>>> Darryl Freinkel
>>>> A4G
>>>> Telephone: 770.321.8562
>>>> -----Original Message-----
>>>> From: RPG400-L<[email protected]> On Behalf Of
>>>> Daniel Gross
>>>> Sent: Wednesday, October 8, 2025 8:49 AM
>>>> To:[email protected]
>>>> Subject: Re: Inconsistency with SQL in ACS vs RPG I do that
>>>> occasionally - but sadly the pseudo-host-variables in iACs are ...
>>>> a) not persistent - so I have to re-enter them at every execution
>>>> again and again - VScode DB2 for I has a new "bind:" prefix - I have
>>>> to look into this
>>>> b) if you have the same host-variable multiple times, you have to
>>>> enter it multiple times - which is also less than ideal But all in
>>>> all I use that feature here and there.
>>>> Regards,
>>>> Daniel
>>> *** CONFIDENTIALITY NOTICE: The information contained in this
>>> communication may be confidential, and is intended only for the use
>>> of the recipients named above. If the reader of this message is not
>>> the intended recipient, you are hereby notified that any
>>> dissemination, distribution, or copying of this communication, or any
>>> of its contents, is strictly prohibited. If you have received this
>>> communication in error, please return it to the sender immediately
>>> and delete the original message and any copy of it from your computer
>>> system. If you have any questions concerning this message, please
>>> contact the sender. ***
>> --
>> This is the RPG programming on IBM i (RPG400-L) mailing list To post a
>> message email: [email protected] To subscribe, unsubscribe,
>> or change list options,
>> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
>> or email: [email protected]
>> Before posting, please take a moment to review the archives at
>> https://archive.midrange.com/rpg400-l.
>> 
>> Please contact [email protected] for any subscription related questions.
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: [email protected] To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: [email protected]
> Before posting, please take a moment to review the archives at https://archive.midrange.com/rpg400-l.
> 
> Please contact [email protected] for any subscription related questions.
> 
> 
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: [email protected]
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: [email protected]
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
> 
> Please contact [email protected] for any subscription related questions.
> 
-- 
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: [email protected]
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: [email protected]
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact [email protected] for any subscription related questions.