From: | Sebastien FLAESCH <sf(at)4js(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | V8.3.0: PQprepare()/PQexecPrepared() and DECLARE CURSOR |
Date: | 2007-12-18 14:35:44 |
Message-ID: | 4767DAC0.6050408@4js.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi All,
I am new to this mailing list and want to participate to the 8.3.0 beta program.
(Sorry to be late BTW)
My name is Sebastien FLAESCH and I am in charge of the database interfaces at Four J's Development Tools.
Our product is a Informix 4gl compatible compiler / runtime system.
I wrote all the database interfaces to:
- Oracle (OCI),
- DB2 UDB (CLI),
- SQL Server (ODBC and Native Client),
- PostgreSQL (libpq),
- MySQL (libmysqlclient),
- Sybase ASA (dblib*),
- ANTs (ODBC).
Understand it's about a real database driver for our virtual machine (kind of php db or jdbc driver).
We have a large customer base using Informix and some of them have migrated / want to migrate to PostgreSQL.
We support a libpq-based driver for PostgreSQL since version 7, we support currently 8.1, 8.2 and now I am working in the 8.3 driver.
I do use prepared statements with the PQprepare() / PQexecPrepared() API since first version 8 - thanks for that by the way.
Now I want to take benefit of server cursors, using the DECLARE/FETCH/CLOSE instructions.
8.3 also introduced positioned update/deletes (WHERE CURRENT OF), so we do not more need to emulate this with oids.
...
The problem: It appears that the server gets confused when doing PQprepare("DECLARE...) followed by several PQexecPrepared().
Basically I do libpq API calls like this:
For SQL that does not return a result set:
PQprepare(... "cu1", "INSERT INTO ..." );
PQexecPrepared( ... "cu1" ... );
PQexecPrepared( ... "cu1" ... );
PQexecPrepared( ... "cu1" ... );
PQexec( "DEALLOCATE cu1" );
For SQL producing a result set:
PQprepare(... "cu1", "DECLARE cu1 CURSOR WITH HOLD ..." );
PQexecPrepared( ... "cu1" ... ); -- opens the cursor...
PQexec( "FETCH NEXT FROM cu1 ..." ... );
PQexec( "FETCH NEXT FROM cu1 ..." ... );
PQexec( "FETCH NEXT FROM cu1 ..." ... );
PQexec( "CLOSE cu1" ); -- frees cursor resources (need to re-execute DECLARE)
PQexecPrepared( ... "cu1" ... ); -- opens the cursor...
PQexec( "FETCH NEXT FROM cu1 ..." ... );
PQexec( "FETCH NEXT FROM cu1 ..." ... );
PQexec( "CLOSE cu1" ); -- frees cursor resources (need to re-execute DECLARE)
PQexecPrepared( ... "cu1" ... ); -- Here I get error: [42P03][cursor "cu1" already exists]
I wonder why the second PQexecPrepare() executes and the third fails...
To make this work, I need to de-allocate the statement and re-prepare with PQprepare() ...
I will try to provide you with a little sample to reproduce, but wanted to post this early to let you known.
Best regards,
Sebastien FLAESCH
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Dunstan | 2007-12-18 15:00:29 | Re: V8.3.0: PQprepare()/PQexecPrepared() and DECLARE CURSOR |
Previous Message | peter.trautmeier | 2007-12-18 13:48:23 | Re: ecxt_scantuple has wrong TupleDesc |