RE: SQL Problem

Basically the fields are all subfile fields with a position to header
row in the subfile control.

Based on where the screen cursor is located when they hit F23 (sort) it
determines the sort order.


Sharon Wintermute


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of jdavis@xxxxxxxx
Sent: Monday, October 19, 2009 3:49 PM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Problem

How do your users select the data? I know it is a screen, but does it
look like

Value Sort
FLD1
FLD2
FLD3
FLD4
FLD5

or is it done differently?

Jeff Davis





"Wintermute, Sharon" <Sharon.Wintermute@xxxxxxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
10/19/2009 03:35 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: SQL Problem






I wanted to use parameterized versions but how are you going to build a
where statement with from 1 to 13 different fields and 13 different sort

fields with one dynamic select?

I need to be able to dynamically build and recreate the sql on the fly:
Select * from XYZ where fld2 = 'DD' order by fld4
Select * from XYZ where fld3 = 'D' and fld6 = 99 order by fld2
Select * from XYZ where fld1 = 'A' and fld2 = 'DD' and fld3='D' and
fld9=0 order by fld5

I can dynamically change the 'VALUES' but how do I dynamically change
the
'FIELDS'?

Every time they press enter it can change the select dramatically.

Sharon Wintermute


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Charles Wilt
Sent: Monday, October 19, 2009 3:17 PM
To: RPG programming on the IBM i / System i
Subject: Re: SQL Problem

Sharon,

You're doing something wrong. Dynamic SQL will let you change
anything you want.
Post some code along with the error messages.

Note however, if you have something like so

wSQL = 'select * from XYZ where fld2 = ' + QUOTE + inFld2Value + QUOTE;

you are opening yourself up to SQL injection attacks. Instead, you
should use parametrized statements:

wSQL = 'select * from XYZ where fld2 = ?';

/exec SQL prepare C1 from :wSQL;

/exec SQL open C1 using :inFld2Value;

Lastly, you may not even need dynamic statements; you'd be surprised
what you can do statically...

/exec-sql
select * from XYZ
where
( :inFld2Value = ' ' or fld2 = :inFld2Value )
and ( :inFld3Value = ' ' or fld3 = :inFld3Value )
order by
case
when :inFld2Value <> ' ' then fld4
else fld2
end

HTH,
Charles

On Mon, Oct 19, 2009 at 3:52 PM, Wintermute, Sharon
<Sharon.Wintermute@xxxxxxxxxxxxxxxx> wrote:

That doesn't work when they change the select.

Basically it comes in the first time as 'Select * from XYZ where fld1

=

'2' order by fld3

Then if they change the screen value it could be:
Select * from XYZ where fld2 = 'DD' order by fld4
Or: Select * from XYZ where fld3 = 'D' and fld6 = 99 order by fld2

The where clause changes and the order by clause changes.

It doesn't allow the second prepare. Do I just not use prepare?


Sharon Wintermute

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of jdavis@xxxxxxxx
Sent: Monday, October 19, 2009 2:38 PM
To: RPG programming on the IBM i / System i
Subject: Re: SQL Problem

I would use dynamic SQL and not static. You will build your statement
before executing it.

Something like this

Sql_Statment = 'Select * From XYZ';

If Cond1 <> Blanks;
Sql_Statement = %Trim(Sql_Statement) + 'where XXXX'
EndIf;

and so on and so on.

The use Exec sql prepare statement and everything else the same.


Jeff Davis




"Wintermute, Sharon" <Sharon.Wintermute@xxxxxxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
10/19/2009 02:29 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
SQL Problem






I have a new situation I have not encountered before. I have a work

with

display that can have up to 13 different position to values with 13
different sorts. Seems like the perfect candidate for sql.



All my other sql routines use a standard prepare, declare, open, fetch
close with one cursor. I thought if I closed the cursor I might be

able

to re-open it with a different prepare but no luck. It doesn't allow
that. (Figures).



Basically I can use 1 to 13 different position-to fields and one sort

at

a time.



So how do I go about this?





Sharon Wintermute



--
This is the RPG programming on the IBM i / System i (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.




HCSC Company Disclaimer

The information contained in this communication is confidential,
private,
proprietary, or otherwise privileged and is intended only for the use

of

the addressee. Unauthorized use, disclosure, distribution or copying

is

strictly prohibited and may be unlawful. If you have received this
communication in error, please notify the sender immediately at (312)
653-6000 in Illinois; (800)835-8699 in New Mexico; (918)560-3500 in
Oklahoma; or (972)766-6900 in Texas.
--
This is the RPG programming on the IBM i / System i (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.

--
This is the RPG programming on the IBM i / System i (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.