AW: How to save a list of programs

A single quote within a string simply must be doubled.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"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)


-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Robert
Rogerson
Gesendet: Thursday, 10.11 2016 15:18
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: Re: How to save a list of programs

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.