Обсуждение: V8.3.0: PQprepare()/PQexecPrepared() and DECLARE CURSOR

Поиск
Список
Период
Сортировка

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

От
Sebastien FLAESCH
Дата:
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

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

От
Andrew Dunstan
Дата:

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

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

От
Sebastien FLAESCH
Дата:
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
>

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

От
Sebastien FLAESCH
Дата:
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
>

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

От
Andrew Dunstan
Дата:

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