Re: Informix 07003 error

> You certainly get credit for pointing out that I didn't mention +100, and it > can show up in SQLCODE and sqlca.sqlcode. > > Whether +100 (NOTFOUND, SQLNOTFOUND) is an error is a debatable point. > ... These are not errors, though, so I ignored both this and > SQLNOTFOUND for the sake of brevity in my previous answer. As both Jonathan (erroneously identified as John in my earlier post) and Art have pointed out, +100 is not really an error. I mis-read the original message and thought that a claim was being made that positive numbers would never be returned in sqlca.sqlcode. Mark Collins mcollins@us.dhl.com

The 07003 does come from SQLSTATE when I'm using ESQL/C to query the database, the code looks like: EXEC SQL declare chkcursor cursor for select url_title into :cTitle from url where url_path = :cUrl; printf("checkURL - opening cursor.\\n"); EXEC SQL open chkcursor; /*for (;;) {*/ EXEC SQL fetch chkcursor; /*break;*/ /*}*/ if (strncmp(SQLSTATE, "00", 2) != 0) { EXEC SQL close chkcursor; EXEC SQL free chkcursor; printf("Cursor Error: %s\\n", SQLSTATE); return -2; /* Cursor Error */ } EXEC SQL close chkcursor; EXEC SQL free chkcursor; As I realise, this is not a consistent error. There are more than 1 queries during the execution and a few particular query will fail very frequently. Does anyone know why? (Since I'll be on leave for 2 weeks from tomorrow, could you kindly CC your reply to me) Thanks a lot. Regards, Yang Yang Mark Collins wrote: > > You certainly get credit for pointing out that I didn't mention +100, and it > > can show up in SQLCODE and sqlca.sqlcode. > > > > Whether +100 (NOTFOUND, SQLNOTFOUND) is an error is a debatable point. > > ... These are not errors, though, so I ignored both this and > > SQLNOTFOUND for the sake of brevity in my previous answer. > > As both Jonathan (erroneously identified as John in my earlier post) and Art > have pointed out, +100 is not really an error. I mis-read the original message > and thought that a claim was being made that positive numbers would never be > returned in sqlca.sqlcode. > > > Mark Collins > mcollins@us.dhl.com

Yang Yang wrote: > The 07003 does come from SQLSTATE when I'm using ESQL/C to query > the database, the code looks like: > > EXEC SQL declare chkcursor cursor for > select url_title into :cTitle from url > where url_path = :cUrl; > > printf("checkURL - opening cursor.\\n"); > EXEC SQL open chkcursor; > /*for (;;) {*/ > EXEC SQL fetch chkcursor; > /*break;*/ > /*}*/ > if (strncmp(SQLSTATE, "00", 2) != 0) { > EXEC SQL close chkcursor; > EXEC SQL free chkcursor; > printf("Cursor Error: %s\\n", SQLSTATE); > return -2; /* Cursor Error */ > } > EXEC SQL close chkcursor; > EXEC SQL free chkcursor; > > As I realise, this is not a consistent error. There are more than > 1 queries during the execution and a few particular query will > fail very frequently. Does anyone know why? Using the v9.1 manual, 07003 is identified as 'Cursor specification cannot be executed'. You example code doesn't show how errors are handled. Most of my sample code snippets sent to c.d.i includes: EXEC SQL WHENEVER ERROR STOP; This allows me to ignore error handling issues when they are not relevant to the issue under discussion, but the default is to continue. Here, we're discussing error handling, so it is critical to know what error handling you have not shown. I think in terms of sqlca.sqlcode or SQLCODE, so my added error handling uses that (primarily because I learned ESQL/C before there was either SQLCODE or SQLSTATE, and also because you get better diagnostics with SQLCODE); you will probably want to adapt my code to check SQLSTATE instead, for consistency. > EXEC SQL declare chkcursor cursor for > select url_title into :cTitle from url > where url_path = :cUrl; if (SQLCODE < 0) ???? > printf("checkURL - opening cursor.\\n"); > EXEC SQL open chkcursor; Either: if (SQLCODE < 0) ???? Or: > /*for (;;) {*/ while (SQLCODE == 0) { > EXEC SQL fetch chkcursor; if (SQLCODE != 0) break; > /*break;*/ > /*}*/ > if (strncmp(SQLSTATE, "00", 2) != 0) { > EXEC SQL close chkcursor; -- OK to omit error reporting during error recovery -- Note that if the OPEN failed, the CLOSE will too. > EXEC SQL free chkcursor; -- And if the DECLARE failed, this will too. > printf("Cursor Error: %s\\n", SQLSTATE); -- And both the CLOSE and the FREE modified SQLSTATE, -- so your reported error occurs after you've destroyed -- the original error condition. Now you know why global -- variable (such as SQLCODE, SQLSTATE and sqlca) are an -- abomination; you *always* have to know and remember -- which operations might alter the global variables. -- Another nuisance variable is errno; if you're error -- reporting, you have to cache the value before you call -- any function which might modify it, directly or -- indirectly. -- Move the printf() before the CLOSE. > return -2; /* Cursor Error */ > } > EXEC SQL close chkcursor; if (SQLCODE < 0) ???? -- This CLOSE is not on the error handling path > EXEC SQL free chkcursor; if (SQLCODE < 0) ???? -- This FREE is not on the error handling path You are ensuring that no other code in your application ever has the same cursor name open at the same time as this cursor is in use, aren't you? That is, your cursor names are unique throughout the program, or you're sure you're never using a cursor called chkcursor in functionA() where functionA() calls functionB() and functionB() also uses the cursor name chkcursor. You can get into all sorts of fun tracking these problems down, especially if you aren't religiously checking all possible error conditions. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>