RE: Using host variables in SQLRPGLE doesn't seem to be working.... INclause

Martin,

Have you considered using some work tables? Insert the values for the selection as rows in the table then use an EXISTS clause in the where statement to select rows from the primary table that are in the work table. Clear and insert values in the work tables before running the SQL instead of preparing a new SQL statement.

Paul

Principal Programmer Analyst
IT Supply Chain/Replenishment


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Bryce Martin
Sent: Monday, January 10, 2011 1:30 PM
To: RPG programming on the IBM i / System i
Subject: RE: Using host variables in SQLRPGLE doesn't seem to be working.... INclause

I definitely understand the concern you raise, but this statement is
behind a few layers of logic and doesn't actually return results a calling
program. The results are used as park of a ranking algorithm with other
criteria and goes through a weighting algorithm that returns only the top
20 results. So SQL injection isn't a worry.


Thanks
Bryce Martin
Programmer/Analyst I
570-546-4777



"Mark Murphy/STAR BASE Consulting Inc." <mmurphy@xxxxxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
01/10/2011 12:56 PM
Please respond to
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>


To
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
cc

Subject
RE: Using host variables in SQLRPGLE doesn't seem to be working.... IN
clause






Beware of SQL Injection attacks when you do this. If NameString were to
contain something like "space' and ('1' = '1" you would get every record
in the database. May or may not be an issue in this application since the

user may be authorized to all the records, but just saying that using
prepared statements with appropriate parameter markers is far safer than
allowing the user to enter SQL source.

Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx



From: "Schutte, Michael D" <Michael_Schutte@xxxxxxxxxxxx>
To: RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
Date: 01/10/2011 08:23 AM
Subject: RE: Using host variables in SQLRPGLE doesn't seem to be
working.... IN clause
Sent by: rpg400-l-bounces@xxxxxxxxxxxx



Another option is to build the entire select statement in a string
variable. Prepare it and then declare your cursor over the prepared
statement.

For example.

STRSQL = 'SELECT ' + ColumnsSQL + ' FROM DCR_PIVOT +
WHERE RNWORD1 in (' + %Trim(NameString) + ') +
ORDER BY ' + %TRIM(SortBy);
Exec SQL PREPARE S1 FROM :STRSQL;

Exec SQL DECLARE C1 CURSOR FOR S1;
Exec SQL OPEN C1;

Exec SQL Fetch C1 Into :ColumnsDS
DoW SQLCOD <> 100 And SQLCOD >= 0;
...
Exec SQL Fetch C1 Into :ColumnsDS
EndDo;

I'm assuming RNWORD1 is character so you will need to make sure that you
have the single quotes around each name in your string.



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]

On Behalf Of Bryce Martin
Sent: Friday, January 07, 2011 5:08 PM
To: RPG programming on the IBM i / System i
Subject: Using host variables in SQLRPGLE doesn't seem to be working....
IN clause

I might be trying to be too fancy here, but I thought that maybe this
would work.

I have an array of values (words) that I need to search a particular field


for in a file.... So I thought that this might work...

Exec SQL
Declare NameSet Cursor For
Select Cast(rncust As Numeric(6,0)),
Cast(rnseq As Numeric(4,0)),
Cast(rntype As Numeric(2,0)),
rnword1, rnword2, rnword3, rnword4, rnword5
From rcm002pf
Where rnword1 in(:NameString)
and rntype = (case when :type<>'' then :NumType else rntype


End)
Order by rncust, rnseq;

I build NameString from the array values. Its a character string. Its
value is like... 'word1','word2','word3'

My thinking is that NameString would just plop right inside the IN()
clause and act as the list to check. But I keep getting and sql 100 error


(no results). If I put this into my favorite sql tool it works fine. So
I know that its something to do with the SQL. I thought this would be
cleaner than doing a Prepare, but maybe I'll have to do it anyway.

Any thoughts????


Thanks
Bryce Martin
Programmer/Analyst I
570-546-4777
--- This message (including any attachments) is intended only for the use
of the individual or entity to which it is addressed and may contain
information that is non-public, proprietary, privileged, confidential, and

exempt from disclosure under applicable law. If you are not the intended
recipient, you are hereby notified that any use, dissemination,
distribution, or copying of this communication is strictly prohibited. If
you have received this communication in error, please notify us and
destroy this message immediately. ---