SQLCODE as 100 while OPENING/CLOSING the cursor ?

gragha · New User Joined: 25 Jul 2006 Posts: 23

Hi,

Is it possible to get a SQLCODE as 100 while OPENING/CLOSING the cursor ? This is not a question for a specific cursor but a general one.

Thanks, Ragha

socker_dad · Posted: Tue May 01, 2007 2:24 am

Yes, you can get a -100 on an Open Cursor. Bear in mind that is on the actual OPEN, not the DECLARE.

This is because you have to supply some sort of key value for the cursor, regardless of whether the table entries are expected to be less than, equal to, or greater than the supplied value.

If nothing satisfies the condition, a -100 is the result.

You cannot get a -100 on a close cursor, as you are not supplying any information with which to read the table: you are simply releasing your DB2 thread.

Hope this helps.

gragha · New User Joined: 25 Jul 2006 Posts: 23

Hi,

I think the OPEN cursor just prepares the data for the FETCH Statement. So when there is no rows matching the values that we gave in DECLARE cursor, it will prepare the data but there will not be any value in it. Since the preparation was successfull I think it will still give a SQLCODE of 0 and while we fetch, then only it tries to retrieve the data that it prepared and since there were no data it will give an SQLCODE of 100.

If we go with what you suggest, then we can easily identify if there is data available after the OPEN CURSOR statement only and not even execute the FETCH.

I tried a condition with no matching rows and it still gave an SQLCODE of 0 while opening and 100 while fetching which looks contrary to what you said. Please let me know if I am wrong.

William Thompson · Posted: Tue May 01, 2007 2:32 am

I looked around, and I can't find any indication that a +100 wil be rasied by a cursor open.....

wanderer · Posted: Tue May 01, 2007 11:37 am

As far as I remember, although FETCH is the one which actually gets data, if your SQL has things like Order-By etc then it is OPEN that gets the data and then prepares for subsequent FETCH. So I would think a +100 in OPEN is possible. Don't understand how a CLOSE will give +100.

Again, I haven't verified it or seen it written in a manual, so can't be 100% sure.

MFRASHEED · Active User Joined: 14 Jun 2005 Posts: 186 Location: USA

Here is text from manual:

And deriving conclusion from statements in BOLD looks like +100 is not possible on OPEN.

The result table of the cursor is derived by evaluating the SELECT statement. The evaluation uses the current values of any special
| registers or PREVIOUS VALUE expressions specified in the SELECT statement and the current values of any host variables specified in the SELECT statement or the USING clause of the OPEN statement. The rows of the result table can be derived during the execution of the OPEN statement and a temporary copy of a result table can be created to hold them. They can be derived during the execution of later FETCH statements. In either case, the cursor is placed in the open state and positioned before the first row of its result table. If the table is empty the position of the cursor is effectively "after the last row." DB2 does not indicate an empty table when the OPEN statement is executed. But it does indicate that condition, on the first execution of FETCH, by returning values of +100 for SQLCODE and '02000' for SQLSTATE.

and link to manual : publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/dsnsqj10/5.77?ACTION=MATCHES&REQUEST=CURSOR&TYPE=FUZZY&SHELF=&DT=20040216135741&CASE=&searchTopic=TOPIC&searchText=TEXT&searchIndex=INDEX&rank=RANK&ScrollTOP=FIRSTHIT#FIRSTHIT