× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.