Re: How to save a list of programs

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.