Re: 4GL/Engine: FETCH RELATIVE acting flakey

Jacob Salomon wrote: > > Hi family, > > I am having a flaky problem with the FETCH command. I am writing a > classical menu-driven program, with QUERY, NEXT, PREVIOUS menu items. > > When the user calls for QUERY, I initiate the SELECT, declare a cursor > and issue the FETCH FIRST command. When the user presses "NEXT", I > issue the command: FETCH RELATIVE nn cursor_name. Here is a code > snippet: Jacob, if you just execute a fetch, it will do the same thing you're trying to accomplish here. You'll need the fetch relative for reverse traversal of the data, but not for moving forward. This'll get you around half the problem anyway. > > function fetch_libdoc(nfetch) > define nfetch smallint > define nparm smallint # Parameter counter > > if nfetch = 0 > then > fetch first libdoc_pk_curs # Effective fetch first > into rp_libdoc.funcname > else > fetch relative nfetch libdoc_pk_curs # Skip to current + > into rp_libdoc.funcname # nfetch rows > end if > > Problem: The first time I issue the FETCH RELATIVE command, it > retrieves the first row of the query all over again! A "select count" > retrieves 2 yet I can successfully run a total of 3 fetches. The form > comes up with [document 3 of 2], clearly ridiculous. > > Answers I have eliminated: > - nfetch is 0 or null What is nfetch equal to? If it is too large or too small(too negative) the query will fail. If you don't check sqlca.sqlcode, you won't realize this and thus you think you have a bug. (Some people check "status", but I have found instances where it doesn't match what is in sqlca.sqlcode, so I don't trust it.) > No, I have repeatedly checked this in the debugger before the fetch. Try executing this code outside the debugger and use displays or send the data to an errorlog file. The debugger sometimes "fixes" or "creates" bugs that don't otherwise exist in executable code. > > - I have closed the cursor and reopened it somewhere. > Not possible; it would have aborted the next time I did a fetch. > Just in case, I just grep'ed for the cursor name. My code has not > closed the cursor anywhere. > > - The first row of data is really duplicated > No, I ran SQL on the query. Exactly 2 rows in the active set. > > I have also tried using an unqualified FETCH and FETCH ABSOLUTE 1 for my > initial fetch command. The problem persists! > Sounds like the relative value is out of range. > Thinking I have found a silly bug, I wrote a small test program to > demonstrate the problem. SURPRISE! I could not replicate it! > My tester issues FETCH FIRST followed by a loop of FETCH RELATIVE. All > data was retrieved as appropriate. > > OK, I've eliminated the obvious. What could I possibly be overlooking? > I need a brainstorming session! > > Thanks for any help. > -- > -- Jake (With a talent for finding showstoppers) Hey Jake, those most talented at finding showstoppers are usually the most talented and productive, because they're the ones really working. Bret.