× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.




(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. ---

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.