RE: SQL Problem -- RPG400-L

Could you omit one?

wSQL = 'Select * from custom where locnbr = ? and fld2 = ? and fld3 = ?

prepare s1 from :wSql;

open C1 using *omit, :WFld2, *Omit

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:54 PM
To: RPG programming on the IBM i / System i
Subject: Re: SQL Problem

Sharon,

The only relationship between the parameter markers and the variables
being passed in the order. The system doesn't care if you pass more
than what you actually use. This compiles fine:

d wSQL s 200a
d wLocNbr s 5a inz('00D26')
d wFld2 s 10a
d wFld3 s 2a

/free
exec SQL
declare C1 cursor for S1;

wSQL = 'select * from customp where locnbr = ?';

exec SQL
prepare S1 from :wSQL;

exec SQL
open C1 using :wLocNbr, :wFld2, :wFld3;

*INLR = *ON;
return;
/end-free

So you're open will need to pass 13 parms, though you may not need them all.

However, you might have issue with the sizes and types of the
parms....you may need to pass everything as text then use appropriate
SQL functions to convert where needed.

The sort fields aren't an issue as you're specify them appropriately
as part of the wSQL text.

Again, some actual code would help.

Charles

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

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.

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