AW: SQL multiple selects

Hi,

Check the SQLCOD or SQLSTT after each SQL-statement.

If the SQLCOD is < *Zeros an error occured. To determine the error message,
look in the message file QSQLMSG. The message-id can be determined as
follows. If the negative SQLCode is between -1 and -9999 the appropriate
message id is 'SQL' + absolute value of the SQLCOD, i.e. -404 results in
SQL0404. If the negative SQLCODE is between -10000 and -99999 the message-id
is SQL + absolute value of the SQLCOD.

Try to move off the '' around the catalog value.

Mit freundlichen Gruessen / Best regards

Birgitta

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les Brown)

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Jeff Crosby
Gesendet: Tuesday, September 05, 2006 15:36
An: 'RPG programming on the AS400 / iSeries'
Betreff: 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.