Re: How to save a list of programs -- RPG400-L

Thanks for the help everyone. Dan's suggestion sank in and I realized all I have to do is double up the quotes (which I did in Notepad++).

I then ran this in Run SQL Scripts

CL: CHGDTAARA DTAARA(T1503BASE/PG0400PGMS *ALL) VALUE('''PU1400'', ''PI1356'', ''FO0580'', ''AL0501'', ''GETUPCA'', ''GETRTL'', ''GETRTL1'', ''GETTFR'', ''RTLARY'', ''FO0520'', ''FO0530'', ''AL0503'', ''FS0008'', ''FS0007'', ''OE1155'', ''ORD0000S'', ''ORD0001M'', ''WHS0000S'', ''GETUPCHRG'', ''WHS0001M'', ''DATE'', ''FS0003'', ''FS0001'', ''EX0013'', ''FS0004'', ''FS0002'', ''FS0005'', ''FS0006'', ''OE1001'', ''AD8130'', ''ADORDER'', ''AD1030'', ''OE7800M'', ''RTV0000S'', ''EX0020'', ''GETDEPT'', ''RTV0000M''');

and the data area is now populated with the list of programs. I can then retrieve the data area in my program to populate the variable myProgramList.

Thanks for all the help everyone,

Rob
On 11/10/2016 9:34 AM, Dan wrote:

Rob,

Not sure if I'm understanding correctly, but it appears that you need to
double up on your single quotes if you want to treat that as one long
string. So, if your end result for storing the string is:
('program1', 'program2', 'program3')
you need to generate it like:
'(''program1'', ''program2'', ''program3'')'
Note that those are all single quotes; there are no double quote characters
used.

If you want to see this in action, copy/paste
('program1', 'program2', 'program3')
into the text parameter of an IBM command like CRTDTAARA, and you'll see
how it gets rendered.

HTH,
- Dan

On Thu, Nov 10, 2016 at 9:17 AM, Robert Rogerson <rrogerson@xxxxxxxxxxx>
wrote:

Charles, it is for an IT tool so dynamic sql should be ok.

I guess I didn't explain the problem correctly. My problem is not with
creating the SQL. My problem is how/where do I save the list of programs
to populate the myProgramList host variable.

I'm thinking that I'll have to use a text editor (Notepad++ in my case) to
convert/replace the single quotes to another character to be able to save
the string/list.

So ('program1', 'program2', 'program3') becomes '_program_, _program2_,
_program3_' which I should be able to save to a data area. Then I'm
thinking I read the data area into myProgramList and then TRANSLATE the '_'
to a quote.

I'm just wondering if anyone has another solution.

Thanks,

Rob

On 11/10/2016 8:55 AM, Charles Wilt wrote:

You can't do it with static SQL...

You'd have to pass each value separately.
WHERE IN (:v1, :v2, :v3)

Otherwise dynamic SQL is an option
wSqlStmt += 'WHERE fld in (' + myProgramList + ')';

exec sql
prepare S1 from :wSqlStmt;

exec sql
execute S1;

Note that the use of dynamic SQL is frowned upon now-a-days; as dynamic
SQL
is open to SQL injection attacks.

Assuming this is a IT tool, not open to your users or the public or that
the program is building the statement without directly concatenating user
input into the statement. You should be able to make the case for using
it.

Charles

On Thu, Nov 10, 2016 at 6:38 AM, Robert Rogerson <rrogerson@xxxxxxxxxxx>
wrote:

Hi all,

I have a list of programs which is generated by a 3rd party software
application. This is in the form of ('program1', 'program2',
'program3').
The list is actually used in a WHERE IN ('program1', 'program2',
'program3') sql statement used by the vendor software.

Basically I want to use that same WHERE IN ('program1', 'program2',
'program3') statement within a custom RPG program. I'm planning to
PREPARE
an sql CURSOR and add the WHERE IN (:myprogramList).

But I can't figure out how to save the list as one big string. Does
anyone
know how I can save ('program1', 'program2', 'program3') as one big
string?

The problem I'm experiencing (I think) is that the quotes are interpreted
as string delimiters. I was trying with a data area and a one column
table
but both had the same issue. Any ideas?

Thanks,

Rob

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.