V8.3.0: PQprepare()/PQexecPrepared() and DECLARE CURSOR

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Sebastien FLAESCH wrote:

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.

This example would be clearer if you used different names for the cursor
and the prepared statement.

cheers

andrew