help: pypgsql: this code works in Fedora core 1, Not in FC2

[J Dubal]

Cliff Wells <clifford.wells at comcast.net> wrote in message news:<mailman.4107.1096498925.5135.python-list at python.org>...
> On Sun, 2004-09-26 at 00:41 -0700, J Dubal wrote:
> > Hello good people,
> > 
> > Following works in FC1 (python-2.2.3-7, postgresql-7.3.4-11,
> > kernel-2.4.22-1.2194.nptl, pyPgSQL-2.4)
> > 
> > from pyPgSQL   import PgSQL
> > conn  = PgSQL.connect('localhost',database='rop')
> > loccur  = conn.cursor()
> > loccur.execute("DECLARE itemcursor CURSOR FOR \
> >               SELECT * FROM locmf")
> > loccur.execute("FETCH ALL FROM itemcursor")
> > rec=loccur.fetchall()
> > print rec
> > 
> > However, following errors are produced in FC2 (python-2.3.3-6,
> > postgresql-7.4.2-1, kernel-2.6.8-1.521, pyPgSQL-2.4):
> > 
> > /usr/lib/python2.3/site-packages/pyPgSQL/PgSQL.py:2644: FutureWarning:
> > %u/%o/%x/%X of negative int will return a signed string in Python 2.4
> > and up
> >   name = "PgSQL_%08X" % id(self)
> > Traceback (most recent call last):
> >   File "tt1.py", line 5, in ?
> >     loccur.execute("DECLARE itemcursor CURSOR FOR \
> >   File "/usr/lib/python2.3/site-packages/pyPgSQL/PgSQL.py", line 3091,
> > in execute
> >     self.res = self.conn.conn.query('FETCH 1 FROM "%s"' % self.name)
> > libpq.OperationalError: ERROR:  cursor "PgSQL_F6F9906C" does not exist
> > 
> > Out of this, the first error is produced by conn.cursor() statement.
> > 
> > We have to use the "declare cursor" statement because we have to use
> > "fetch prior" statement later in the program. Is there a better way of
> > achiving this?
> > 
> > Something seems to have changed either python or in postgresql that is
> > causing this error. Can someone provide a solution/workaround?
> 
> Don't know if anything changed between the versions of the software you
> are using, but see
> 
> http://www.postgresql.org/docs/current/static/sql-declare.html
> 
> particularly the section on "WITH HOLD/WITHOUT HOLD" and the first
> paragraph in "Notes".  I'm guessing that perhaps you aren't starting a
> new transaction before declaring the cursor and that perhaps pyPgSQL is
> autocommitting the DECLARE statement which would result in your declared
> cursor going out of scope.  Try adding WITH HOLD or put your stuff
> inside of "BEGIN/COMMIT" statements.
> 
> Regards,
> Cliff
> 
> > Thanks in advance.
> > J Dubal.

Many thanks for your response. We tried a few things and found that
although the above code does not work on FC2, it works if we change
this statement:
   conn  = PgSQL.connect('localhost',database='rop')
to this:
   conn  = PgSQL.connect('192.168.1.5',database='rop')
192.168.1.5 happens to be another host running redhat 8.0 and
postgresql-server-7.3.4-1PGDG. It appears that the older (7.3.4)
engine was allowing the above syntax but the newer (7.4.2) is not
allowing.

Then we tried conn.conn.toggleShowQuery. This shows what sql
statements are executed. Based on its output we worked out the
following solution. Relace these lines:
loccur.execute("DECLARE itemcursor CURSOR FOR \
               SELECT * FROM locmf")
loccur.execute("FETCH ALL FROM itemcursor")
by these lines:
loccur.execute("SELECT * FROM locmf")
curname=loccur.name
loccur.execute('FETCH ALL FROM "%s"' %curname)

Then everything works as expected. Even with the old engine.
Thanks and regards.
J Dubal.