Thread: 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
the8.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(
"CLOSEcu1" ); -- 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
Sebastien FLAESCH wrote:
> 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.
>
>
>
This example would be clearer if you used different names for the cursor
and the prepared statement.
cheers
andrew
Sorry I should have double checked, it's my fault.
I do not CLOSE the cursor before the third PQexecPrepare()...
Never mind.
Seb
Sebastien FLAESCH wrote:
> 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
>
Thank you Andrew, I agree the example is a bit confusing, anyway it's my fault... problem fixed. However, could you please confirm that I can use the same name for a prepared statement and a server cursor? This seems to work: test1=> declare s1 cursor with hold for select * from dbit2; test1=> open s1; test1=> prepare s1 as select * from dbit2; -- or PQprepare ( "s1" ) test1=> execute s1; As these are different objects for PostgreSQL - right? Seb Andrew Dunstan wrote: > 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 >
Sebastien FLAESCH wrote: > Thank you Andrew, > > I agree the example is a bit confusing, anyway it's my fault... > problem fixed. > > However, could you please confirm that I can use the same name for a > prepared statement and a server cursor? > > Your example would have failed earlier otherwise. Please also do not top-answer on the mailing list - it makes threads unreadable. cheers andrew