[SQL] sqlstate 02000 while declaring cursor/freeing prepared statements
I'm new to the PostgreSQL community so please pardon what is probably a silly question. Also, this is my first attempt at posting so you might have seen this already (Sorry!) ... I'm in the process of porting Informix ESQL to PostgreSQL. I occasionally get sqlcode = 100 and sqlstate = 02000 when declaring cursors or freeing prepared statements. Is this normal? For example: $declare loop1 cursor with hold for select distinct ( tabname ) from meta ; results in sqlca.sqlcode = 100 and sqlca.sqlstate = '02000' -- Andrew Rost National Operational Hydrologic Remote Sensing Center (NOHRSC) National Weather Service, NOAA 1735 Lake Dr. West, Chanhassen, MN 55317-8582 Voice: (952)361-6610 x 234 Fax: (952)361-6634 [EMAIL PROTECTED] http://www.nohrsc.nws.gov ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] sqlstate 02000 while declaring cursor/freeing prepared
Sure. I'm using ECPG (ecpg -t -r no_indicator -C INFORMIX) in a TRU64 operating system for PostgreSQL version 8.0.2. By occasionally, I mean that I don't observe this problems for each declare and free statement that I've encoded - only for a subset of those commands. But I do observe this problem consistently within that subset - the same command fails every time for a small number of declares and frees. Unfortunately, I do not have a case that can be easily reproduced. The commands that fail are part of a large system. It seems like an odd error for these kinds of commands. Thanks ... Andy Michael Fuhr wrote: On Mon, Aug 29, 2005 at 02:28:24PM -0500, andy rost wrote: I'm in the process of porting Informix ESQL to PostgreSQL. I occasionally get sqlcode = 100 and sqlstate = 02000 when declaring cursors or freeing prepared statements. Is this normal? For example: $declare loop1 cursor with hold for select distinct ( tabname ) from meta ; results in sqlca.sqlcode = 100 and sqlca.sqlstate = '02000' Could you provide a little more context? Are you using ECPG? What version of PostgreSQL are you using? When you say that you "occasionally" see this behavior, do you mean that you see it consistently with some commands and not with others, or do you mean that the same command sometimes does it and sometimes not? Do you have a reproducible test case? That is, everything that somebody could do to reproduce the behavior on their own system? -- Andrew Rost National Operational Hydrologic Remote Sensing Center (NOHRSC) National Weather Service, NOAA 1735 Lake Dr. West, Chanhassen, MN 55317-8582 Voice: (952)361-6610 x 234 Fax: (952)361-6634 [EMAIL PROTECTED] http://www.nohrsc.nws.gov ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] sqlstate 02000 while declaring cursor/freeing prepared
I worked on my problem a little further and have a little more information to share. The declare statement that fails consistently follows a select statement that returns zero rows (and sqlcode 100 and sqlstate '02000'). If I ommit the select statement from the code or set sqlcode to 0 before calling the declare statement, the declare statement works fine. It appears as though the declare statement is not updating the sqlca structure. Is this by design for the ecpg options that I'm using? Did I pick up bad habits while using Informix? Thanks ... Andy andy rost wrote: Sure. I'm using ECPG (ecpg -t -r no_indicator -C INFORMIX) in a TRU64 operating system for PostgreSQL version 8.0.2. By occasionally, I mean that I don't observe this problems for each declare and free statement that I've encoded - only for a subset of those commands. But I do observe this problem consistently within that subset - the same command fails every time for a small number of declares and frees. Unfortunately, I do not have a case that can be easily reproduced. The commands that fail are part of a large system. It seems like an odd error for these kinds of commands. Thanks ... Andy Michael Fuhr wrote: On Mon, Aug 29, 2005 at 02:28:24PM -0500, andy rost wrote: I'm in the process of porting Informix ESQL to PostgreSQL. I occasionally get sqlcode = 100 and sqlstate = 02000 when declaring cursors or freeing prepared statements. Is this normal? For example: $declare loop1 cursor with hold for select distinct ( tabname ) from meta ; results in sqlca.sqlcode = 100 and sqlca.sqlstate = '02000' Could you provide a little more context? Are you using ECPG? What version of PostgreSQL are you using? When you say that you "occasionally" see this behavior, do you mean that you see it consistently with some commands and not with others, or do you mean that the same command sometimes does it and sometimes not? Do you have a reproducible test case? That is, everything that somebody could do to reproduce the behavior on their own system? -- Andrew Rost National Operational Hydrologic Remote Sensing Center (NOHRSC) National Weather Service, NOAA 1735 Lake Dr. West, Chanhassen, MN 55317-8582 Voice: (952)361-6610 x 234 Fax: (952)361-6634 [EMAIL PROTECTED] http://www.nohrsc.nws.gov ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] sqlstate 02000 while declaring cursor/freeing prepared
Just so that we can snip this thread, we've confirmed that free cursor
and free statement do not affect sqlca structure elements sqlcode and
sqlstate.
Michael Fuhr wrote:
On Mon, Aug 29, 2005 at 04:39:36PM -0500, andy rost wrote:
I worked on my problem a little further and have a little more
information to share. The declare statement that fails consistently
follows a select statement that returns zero rows (and sqlcode 100 and
sqlstate '02000'). If I ommit the select statement from the code or set
sqlcode to 0 before calling the declare statement, the declare statement
works fine.
It appears as though the declare statement is not updating the sqlca
structure. Is this by design for the ecpg options that I'm using? Did I
pick up bad habits while using Informix?
Apparently since PostgreSQL doesn't actually have an OPEN statement,
the ECPG code generator doesn't issue the DECLARE until the code
OPENs the cursor. Observe:
% cat foo.pgc
int
main(void)
{
EXEC SQL CONNECT TO DEFAULT;
printf("before DECLARE\n");
EXEC SQL DECLARE curs CURSOR FOR SELECT 1;
printf("after DECLARE, before OPEN\n");
EXEC SQL OPEN curs;
printf("after OPEN\n");
EXEC SQL CLOSE curs;
EXEC SQL DISCONNECT;
return 0;
}
% ecpg foo.pgc
% cat foo.c
...
printf("before DECLARE\n");
/* declare curs cursor for select 1 */
#line 7 "foo.pgc"
printf("after DECLARE, before OPEN\n");
{ ECPGdo(__LINE__, 0, 1, NULL, "declare curs cursor for select 1 ",
ECPGt_EOIT, ECPGt_EORT);}
#line 9 "foo.pgc"
printf("after OPEN\n");
...
Notice that "after DECLARE" actually comes *before* the DECLARE
statement is issued, so references to sqlcode and sqlstate would
see values from a previous command. I don't know if DECLARE is
supposed to affect error codes or not; I'd have to consult the
standard.
--
Andrew Rost
National Operational Hydrologic Remote Sensing Center (NOHRSC)
National Weather Service, NOAA
1735 Lake Dr. West, Chanhassen, MN 55317-8582
Voice: (952)361-6610 x 234
Fax: (952)361-6634
[EMAIL PROTECTED]
http://www.nohrsc.nws.gov
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] Using descriptor areas to insert
I'm in the final stages of porting a large body of ESQL software from Informix to PostgreSQL (V8). The last few Informix-based functions involve Informix descriptor areas to insert rows into a table. I've sorted out the logic for using PostgreSQL descriptor areas for select statements. How do you go about using the PostgreSQL descriptor areas for insert statements? Is there another way to approach the problem of insert records into an arbitrary table using dynamic SQL? Thanks ... -- Andrew Rost National Operational Hydrologic Remote Sensing Center (NOHRSC) National Weather Service, NOAA 1735 Lake Dr. West, Chanhassen, MN 55317-8582 Voice: (952)361-6610 x 234 Fax: (952)361-6634 [EMAIL PROTECTED] http://www.nohrsc.nws.gov ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
