Re: PQprepare in PostgreSQL 7.4 (lack of SAVEPOINTs)

I have figured it out and everything works wonderfully!!!

Here is test code that allows preparing a query with cursor using libpq
library:

PGconn* conn = PQconnectdb("host=localhost dbname=postgres user=postgres
password=12345 port=5432");

PGresult* res = ::PQexec(conn, "START TRANSACTION", 0, 0); // need
transaction scope for the cursor

res = ::PQprepare(conn, "abcd", "DECLARE cur1 CURSOR FOR SELECT * FROM test",
0, NULL);

res = ::PQexecPrepared(conn, "abcd", 0, 0, 0, 0, 0, 0);

res = ::PQexec(conn, "FETCH FORWARD 100 FROM cur1", 0, 0);

int cColumns = PQnfields(res);

int cRows = PQntuples(res);

for (int field_num = 0; field_num < cColumns; field_num++)

{

char* szName = PQfname(res, field_num);

int nColumnSize = PQfsize(res, field_num);

Oid nType = PQftype(res, field_num);

int nMod = PQfmod(res, field_num);

}

res = ::PQexec(conn, "ABORT", 0, 0);

On Mon, Jun 28, 2010 at 9:11 AM, Konstantin Izmailov <pgfizm(at)gmail(dot)com>wrote:

> Looks like other people were asking similar question, but there is no
> answer:
> http://forums.devshed.com/postgresql-help-21/combine-prepare-and-declare-cursor-437562.html
>
> On Mon, Jun 28, 2010 at 1:00 AM, Konstantin Izmailov <pgfizm(at)gmail(dot)com>wrote:
>
>> lol
>>
>> Seriosly, this customer issues resulted in improvement of the way our
>> driver prepares statements. Keeping the map of prepared statements names is
>> actually faster than using Savepoints (less roundtrips to server).
>>
>> I found that DECLARE ... CURSOR FOR ... cannot be prepared. Basically I'm
>> looking for a way to prepare a complex query and then use cursor for reading
>> tuples. Is this possible?
>> This works: PREPARE abcd AS SELECT * FROM test; EXECUTE abcd;
>> This does not work: PREPARE sdsdsd AS DECLARE csr1 CURSOR FOR SELECT *
>> FROM test;
>> This does not work (after prepared the query): DECLARE csr1 CURSOR FOR
>> EXECUTE abcd;
>>
>> Thank you!
>> Konstantin
>> On Wed, Jun 23, 2010 at 9:41 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>
>>> Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> writes:
>>> > On Wed, Jun 23, 2010 at 10:55 PM, Konstantin Izmailov <
>>> pgfizm(at)gmail(dot)com> wrote:
>>> >> The company is not willing to upgrade from 7.4 to a later version due
>>> to
>>> >> risk.
>>>
>>> > The risk of upgrading is less than the risk of staying on an
>>> > unsupported version of pgsql. The company that won't upgrade is
>>> > making a poorly informed decision.
>>>
>>> Indeed. Point out to them that 7.4 is going to be unsupported after the
>>> end of this month:
>>> http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy
>>>
>>> If they don't have a plan to get off of 7.4 within the pretty near
>>> future, they're fools.
>>>
>>> regards, tom lane
>>>
>>
>>
>