Обсуждение: migration: parameterized statement and cursor

Hello.

We are trying to migrate from Informix 9.4 to PostgreSQL. As part of migration we are porting our client application.

So we need reimplement such functionality:
1. Declare a cursor using to _parameterized_ SELECT statement. It should be possible to specify cursor's name (and, perhaps, the statement) dynamically.
2. Open that cursor (in another C-function) passing parameters to it.
3. Fetch records from result set.
4. Close the cursor.

Steps 2-4 may be repeated if necessary.

Is it possible with ecpg or libpq?

----
Best regards,
Aleksey

On 1/11/2010 8:16 AM, Aleksey Onopriyenko wrote:
> Hello.
>
> We are trying to migrate from Informix 9.4 to PostgreSQL. As part of
> migration we are porting our client application.
>
> So we need reimplement such functionality:
> 1. Declare a cursor using to _parameterized_ SELECT statement. It should
> be possible to specify cursor's name (and, perhaps, the statement)
> dynamically.
> 2. Open that cursor (in another C-function) passing parameters to it.
> 3. Fetch records from result set.
> 4. Close the cursor.
>
> Steps 2-4 may be repeated if necessary.
>
> Is it possible with ecpg or libpq?
>
> ----
> Best regards,
> Aleksey

You dont need cursors, really.

In code (a little sudo-code), do:

q := prepare('select f1, f2 from table_x where id = $1');

then you can pass it around as you like, until...

q.params[0] := 42;
q.open();

while not q.eof()
begin
    print q.field[0].asString, "\n";
    q.next();
end



This is NOT libpq sudo-code, its delphi'ish, which uses libpq under the
hood.  My point being, you dont really need cursors, just prepare a
select statement, set it params, fire it off, iterate the result set...
done.  And its re-useable.

Unless I missed your purpose.

-Andy

2010/1/11 Aleksey Onopriyenko <aleksey.alt@gmail.com>:
> Hello.
>
> We are trying to migrate from Informix 9.4 to PostgreSQL. As part of
> migration we are porting our client application.
>
> So we need reimplement such functionality:
> 1. Declare a cursor using to _parameterized_ SELECT statement. It should be
> possible to specify cursor's name (and, perhaps, the statement) dynamically.

cursors are supported, but parametrised cursors are supported only
inside plpgsql. Maybe you can transform your code to plpgsql function.
http://www.postgresql.org/docs/8.4/interactive/plpgsql-cursors.html


> 2. Open that cursor (in another C-function) passing parameters to it.

only in plpgsql

> 3. Fetch records from result set.

yes

Regards
Pavel Stehule

> 4. Close the cursor.
>
> Steps 2-4 may be repeated if necessary.
>
> Is it possible with ecpg or libpq?
>


> ----
> Best regards,
> Aleksey
>