Re: How to save a list of programs

[Robert Rogerson <rrogerson@xxxxxxxxxxx>]

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.