pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
sqlstate 02000 while declaring cursor/freeing statement
4+ messages / 3 participants
[nested] [flat]

* sqlstate 02000 while declaring cursor/freeing statement
@ 2005-08-29 19:19  andy rost <andy.rost@noaa.gov>
  0 siblings, 1 reply; 4+ messages in thread

From: andy rost @ 2005-08-29 19:19 UTC (permalink / raw)
  To: pgsql-admin

I'm new to the PostgreSQL community so please pardon what is probably a 
silly question ...

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@nohrsc.nws.gov
http://www.nohrsc.nws.gov
--------------------------------------------------------------------------------





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: sqlstate 02000 while declaring cursor/freeing statement
@ 2005-08-30 14:01  Thomas F. O'Connell <tfo@sitening.com>
  parent: andy rost <andy.rost@noaa.gov>
  0 siblings, 1 reply; 4+ messages in thread

From: Thomas F. O'Connell @ 2005-08-30 14:01 UTC (permalink / raw)
  To: andy rost <andy.rost@noaa.gov>; +Cc: pgsql-admin


On Aug 29, 2005, at 2:19 PM, andy rost wrote:

> I'm new to the PostgreSQL community so please pardon what is  
> probably a silly question ...
>
> 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'

Andy,

According to the docs, a positive value of sqlcode indicates a  
harmless condition:

http://www.postgresql.org/docs/8.0/static/ecpg-errors.html

The sqlstate of 02000 looks like the NO DATA code:

http://www.postgresql.org/docs/8.0/static/errcodes-appendix.html

I don't see anything wrong with your cursor declaration, so this just  
looks like it's indicating that no rows were returned.

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)


^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: sqlstate 02000 while declaring cursor/freeing statement
@ 2005-08-30 15:13  Michael Fuhr <mike@fuhr.org>
  parent: Thomas F. O'Connell <tfo@sitening.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Michael Fuhr @ 2005-08-30 15:13 UTC (permalink / raw)
  To: Thomas F. O'Connell <tfo@sitening.com>; +Cc: andy rost <andy.rost@noaa.gov>; pgsql-admin

On Tue, Aug 30, 2005 at 09:01:36AM -0500, Thomas F. O'Connell wrote:
> On Aug 29, 2005, at 2:19 PM, andy rost wrote:
> >
> >    $declare loop1 cursor with hold for
> >    select distinct ( tabname )
> >    from meta ;
> >
> >results in sqlca.sqlcode = 100 and sqlca.sqlstate = '02000'
>
> [snip]
>
> I don't see anything wrong with your cursor declaration, so this just  
> looks like it's indicating that no rows were returned.

See my response to the same thread in pgsql-sql about what's
happening:

http://archives.postgresql.org/pgsql-sql/2005-08/msg00274.php

The ECPG code generator doesn't issue the DECLARE statement until
the cursor is opened, so checking sqlcode/sqlstate immediately after
DECLARE but before OPEN will get the error code values from a
previous command.

Here's an excerpt from the DECLARE documentation:

  The PostgreSQL  server does not implement an OPEN statement for
  cursors; a cursor is considered to be open when it is declared.
  However, ECPG, the embedded SQL preprocessor for PostgreSQL,
  supports the standard SQL cursor conventions, including those
  involving DECLARE and OPEN statements.

Since declaring a cursor in PostgreSQL also opens it, ECPG apparently
defers doing anything with the cursor until it finds the OPEN
statement (i.e., EXEC SQL DECLARE generates no code, and EXEC SQL
OPEN generates a DECLARE).

-- 
Michael Fuhr



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: sqlstate 02000 while declaring cursor/freeing statement
@ 2005-08-30 20:10  andy rost <andy.rost@noaa.gov>
  parent: Michael Fuhr <mike@fuhr.org>
  0 siblings, 0 replies; 4+ messages in thread

From: andy rost @ 2005-08-30 20:10 UTC (permalink / raw)
  To: Michael Fuhr <mike@fuhr.org>; +Cc: Thomas F. O'Connell <tfo@sitening.com>; pgsql-admin

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 Tue, Aug 30, 2005 at 09:01:36AM -0500, Thomas F. O'Connell wrote:
> 
>>On Aug 29, 2005, at 2:19 PM, andy rost wrote:
>>
>>>   $declare loop1 cursor with hold for
>>>   select distinct ( tabname )
>>>   from meta ;
>>>
>>>results in sqlca.sqlcode = 100 and sqlca.sqlstate = '02000'
>>
>>[snip]
>>
>>I don't see anything wrong with your cursor declaration, so this just  
>>looks like it's indicating that no rows were returned.
> 
> 
> See my response to the same thread in pgsql-sql about what's
> happening:
> 
> http://archives.postgresql.org/pgsql-sql/2005-08/msg00274.php
> 
> The ECPG code generator doesn't issue the DECLARE statement until
> the cursor is opened, so checking sqlcode/sqlstate immediately after
> DECLARE but before OPEN will get the error code values from a
> previous command.
> 
> Here's an excerpt from the DECLARE documentation:
> 
>   The PostgreSQL  server does not implement an OPEN statement for
>   cursors; a cursor is considered to be open when it is declared.
>   However, ECPG, the embedded SQL preprocessor for PostgreSQL,
>   supports the standard SQL cursor conventions, including those
>   involving DECLARE and OPEN statements.
> 
> Since declaring a cursor in PostgreSQL also opens it, ECPG apparently
> defers doing anything with the cursor until it finds the OPEN
> statement (i.e., EXEC SQL DECLARE generates no code, and EXEC SQL
> OPEN generates a DECLARE).
> 

-- 
--------------------------------------------------------------------------------
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@nohrsc.nws.gov
http://www.nohrsc.nws.gov
--------------------------------------------------------------------------------





^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2005-08-30 20:10 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-29 19:19 sqlstate 02000 while declaring cursor/freeing statement andy rost <andy.rost@noaa.gov>
2005-08-30 14:01 ` Thomas F. O'Connell <tfo@sitening.com>
2005-08-30 15:13   ` Michael Fuhr <mike@fuhr.org>
2005-08-30 20:10     ` andy rost <andy.rost@noaa.gov>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox