Re: 4GL/Engine: FETCH RELATIVE acting flakey
Well, assuming you've not made a mistake in your code, I'd go about
making a small test case and show Informix their flakey cursor handling
from Standard Engine days is still around.
Today on a 7.21 Engine on HP-UX 10.20 machine, I got the dreaded -407
error (SQLExec Error Number ZERO ....if the problem recurs, please note
all circumstances and contact Informix Technical Support.")
A "FETCH LAST" on a cursor with no rows returned returns -407, instead
of SQLNOTFOUND....Program AbEnds, developers confused, me pissed off.
The same code does not fail on AIX, but I do remember there was
something very much like that on a Standard Engine database I used to
work on (2.10 and 5.02), and there may have been something flaky with
Fetch Relative, perhapds the one you're describing. And I wrote that
code in ESQL/C, so it's probably the engine and not 4GL's fault.
Here's the test case I will present to Informix
DATABASE system
MAIN
DEFINE f_rec RECORD LIKE recreate_err.*
DECLARE c_bad_cur SCROLL CURSOR FOR
SELECT *
FROM recreate_err
WHERE some_key = 1
OPEN c_bad_cur
FETCH LAST c_bad_cur INTO f_rec.*
DISPLAY SQLCA.SQLCODE
END MAIN
Program stopped at zero.4gl, line number 15.
SQL statement error number -407.
Error number zero received from the sqlexec process.
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:
>
> 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
> No, I have repeatedly checked this in the debugger before the fetch.
>
> - 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!
>
> 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)
> . .
> _..-'( )`-.._
> ./'. '||\\\\. }\\_/{ .//||` .`\\.
> ./'.|'.'||||\\\\|.. )o o( ..|//||||`.`|.`\\.
> ./'..|'.|| |||||\\`````` \\,@,/ ''''''/||||| ||.`|..`\\.
> ./'.||'.|||| ||||||||||||. ||| .|||||||||||| ||||.`||.`\\.
> /'|||'.|||||| ||||||||||||{ | }|||||||||||| ||||||.`|||`\\
> '.|||'.||||||| ||||||||||||{ | }|||||||||||| |||||||.`|||.`
> '.||| ||||||||| |/' ``\\||`` | ''||/'' `\\| ||||||||| |||.`
> |/' \\./' `\\./ \\!|\\ /|!/ \\./' `\\./ `\\|
> V V V }' `\\ /' `{ V V V
> \\ \\ \\ V / / /
> +-----------------------------------------------------------+
> | Impeccable Logic: A thought process which successfully |
> | resists chicken bites |
> +-----------------------------------------------------------+