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.