Re: sqlstate 02000 while declaring cursor/freeing prepared

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
arost(at)nohrsc(dot)nws(dot)gov
http://www.nohrsc.nws.gov
--------------------------------------------------------------------------------

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?

--
Michael Fuhr

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
arost(at)nohrsc(dot)nws(dot)gov
http://www.nohrsc.nws.gov
--------------------------------------------------------------------------------

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
arost(at)nohrsc(dot)nws(dot)gov
http://www.nohrsc.nws.gov
--------------------------------------------------------------------------------

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.

--
Michael Fuhr

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
arost(at)nohrsc(dot)nws(dot)gov
http://www.nohrsc.nws.gov
--------------------------------------------------------------------------------