(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????
---------------------------
Bryce - have you run in debug and pulled the sql statement being built
then place that in your sql tool
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
rpg400-l-request@xxxxxxxxxxxx
Sent: Monday, January 10, 2011 2:15 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: RPG400-L Digest, Vol 10, Issue 21
Send RPG400-L mailing list submissions to
rpg400-l@xxxxxxxxxxxx
To subscribe or unsubscribe via the World Wide Web, visit
http://lists.midrange.com/mailman/listinfo/rpg400-l
or, via email, send a message with subject or body 'help' to
rpg400-l-request@xxxxxxxxxxxx
You can reach the person managing the list at
rpg400-l-owner@xxxxxxxxxxxx
When replying, please edit your Subject line so it is more specific than
"Re: Contents of RPG400-L digest..."
*** NOTE: When replying to this digest message, PLEASE remove all text
unrelated to your reply and change the subject line so it is meaningful.
Today's Topics:
1. RE: Using host variables in SQLRPGLE doesn't seem to be
working.... IN clause (Bryce Martin)
2. RE: Using host variables in SQLRPGLE doesn't seem to be
working.... INclause (Morgan, Paul)
3. RE: Using host variables in SQLRPGLE doesn't seem to be
working.... INclause (Bryce Martin)
----------------------------------------------------------------------
message: 1
date: Mon, 10 Jan 2011 13:30:23 -0500
from: Bryce Martin <BMartin@xxxxxxxxxxxx>
subject: RE: Using host variables in SQLRPGLE doesn't seem to be
working.... IN clause
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. ---
midrange.com
