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