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
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
