Message-ID: <43138098.40201@noaa.gov>
Date: Mon, 29 Aug 2005 16:39:36 -0500
From: andy rost <andy.rost@noaa.gov>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.4) Gecko/20030624 Netscape/7.1
MIME-Version: 1.0
To: andy rost <andy.rost@noaa.gov>
Cc: Michael Fuhr <mike@fuhr.org>, pgsql-sql@postgresql.org
Subject: Re: sqlstate 02000 while declaring cursor/freeing prepared
References: <431361D8.8050900@noaa.gov>
	<20050829200935.GA98664@winnie.fuhr.org>
	<43137572.4090107@noaa.gov>
In-Reply-To: <43137572.4090107@noaa.gov>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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