RE: SQL Problem

["Wintermute, Sharon" <Sharon.Wintermute@xxxxxxxxxxxxxxxx>]

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.