RE: SQL multiple selects

Jeff,

Change this line:

D                        CATCD1 = ''?'' +

To This:

D                        CATCD1 = ? +


With the original code, you where looking for records where CATCD1
contained a ? Surrounded in single quotes.

When using parameter markers, the character or numeric values passed on
the open are handled correctly assuming that the columns in the prepared
statement are corresponding character or numeric.

In other words, if you have

 numfld1 = ?  And charfld2 = ?

Then the RPG code would need to use a numeric variable followed by a
character variable in the OPEN CURSOR.

HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx 
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Crosby
Sent: Tuesday, September 05, 2006 9:36 AM
To: 'RPG programming on the AS400 / iSeries'
Subject: SQL multiple selects

I've got a strange one in embedded SQL that debug is not helping with.
Being the only tech person here, there's no one else to show 
it to.  I've
got to be doing something dumb, spent all Friday afternoon till I was
pulling my hair out.  Today has been no better.

Basically, when requesting this list, the user is allowed to 
either include
all items, or limit the selection to the items from a 
particular catalog.  A
blank in field "Catalog" means include everything.  To 
accommodate this in
RPGSQL, I have the following 2 constants defined (shortened 
to, I hope,
prevent wrapping):

D @SQLByCat       C     'Select * from dmitmmst +
D                        Where (WHSLC between +
D                        ? and ?) and +
D                        CATCD1 = ''?'' +
D                        Order By SAQYR desc'
D @SQLNotByCat    C     'Select * from dmitmmst +
D                        Where (WHSLC between +
D                        ? and ?) +
D                        Order By SAQYR desc'

Note that the only difference is "and CATCD1 = ''?''"

In my Prepare subproc, I have this:

Select;
  When Catalog = *Blank;
    ActSQL = @SQLNotByCat;
  Other;
    ActSQL = @SQLByCat;
Endsl;
Exec SQL  Prepare Stmt1 from :ActSQL;

In my Declare subproc I have this:

Exec SQL  Declare MyCursor cursor
            for Stmt1;

In my OpenCursor subproc I have this:

Select;
  When Catalog = *Blank;
    Exec SQL  Open MyCursor Using :LoSlot, :HiSlot;
  Other;
    Exec SQL  Open MyCursor Using :LoSlot, :HiSlot, :Catalog;
Endsl;

And my FetchNext subproc looks like this:

Exec SQL  Fetch next from MyCursor into :dmitmmstDS;

If the user puts in a blank (meaning include everything), it 
works fine.  If
the user puts in any valid Catalog whatsoever, I get nothing.  A
step-by-step debug exits the read loop immediately because 
there is no more
data.  i.e., no records were selected.  An RPG dump shows 
variable ActSQL
has:

Select * from dmitmmst Where (WHSLC between ? and ?) and 
CATCD1 = '?' Order
By SAQYR desc

Which is what I expect.  Variable Catalog has the correct 
value also.  A
step-by-step debug also shows all the variables in the 
"Using" clause have
the correct values.  I did a copy/paste of ActSQL from the 
program dump into
STRSQL, replaced the 3 parameter markers with the 
corresponding values from
the program dump (also via copy/paste), and it worked just 
fine.        

What am I missing?

Thanks.

-- 
Jeff Crosby
Dilgard Frozen Foods, Inc.
P.O. Box 13369
Ft. Wayne, IN 46868-3369
260-422-7531

The opinions expressed are my own and not necessarily the 
opinion of my
company.  Unless I say so.


-- 
This is the RPG programming on the AS400 / iSeries (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.