Re: Return cursor - Mailing list pgsql-general

From Sergey E. Volkov
Subject Re: Return cursor
Date
Msg-id 9ej088$ulc$1@news.tht.net
Whole thread Raw
In response to Re: Return cursor  (Alexander Dederer <dederer@spb.cityline.ru>)
List pgsql-general
If you are planing  to use ecpg :

exec sql declare cuserbase cursor for
     select column1, column2, column3 from my_table order by 1;
exec sql open cuserbase;

exec sql whenever not found do break;
while ( true ) {
   exec sql fetch cuserbase into ...
   // do same work
}

exec sql close cuserbase;

Please read documentation for more detail.

Good luck.

Sergey.

"Alla" <alla@sergey.com> ???????/???????? ? ???????? ?????????:
news:9275d56e.0105240427.2fa28c31@posting.google.com...
> Alexander Dederer <dederer@spb.cityline.ru> wrote in message
news:<9ehged$k4f$1@news.tht.net>...
> > Can you send PL/SQL code and back-end code used this PL/SQL code?
> > Myself  trubles with CURSOR I resolve use LIMIT ... OFFSET ...
>
> It would look something like this:
>
> create or replace package my_package
> AS
>    type gc_cursor is ref cursor;
>
>    procedure load_users
>    (pp_user_base     out   gc_cursor);
> end my_package;
> /
>
> create or replace package body my_package
> as
> procedure load_users
> (pp_user_base     out   gc_cursor)
> as
> begin
>    open pp_user_base for
>       select column1, column2, column3
>       from my_table
>       order by 1;
>
> exception
>    when others then
>       raise_application_error(-20100, 'Error while trying to load user
base ' ||
>          sqlerrm);
> end load_users;
>
> end my_package;
> /
>
> C program would look something like this:
>
> void
> load_user_base()
> {
> EXEC SQL BEGIN DECLARE SECTION;
>    sql_cursor cUserBase;
> EXEC SQL END DECLARE SECTION;
>
>   struct USER_PROFILE {
>     .......
>   } user_profile[ARRAY_LENGTH];
>
>   struct USER_PROFILE_IND {
>     .......
>   } user_profile_ind[ARRAY_LENGTH];
>
>   EXEC SQL ALLOCATE :cUserBase;  /* allocate the cursor variable */
>
>   EXEC SQL EXECUTE
>     BEGIN
>       my_package.load_users(:cUserBase);
>     END;
>   END-EXEC;
>
>   EXEC SQL WHENEVER NOT FOUND DO break;
>
>   for (;;) {
>     EXEC SQL FOR :i FETCH :cUserBase INTO :user_profile:user_profile_ind;
>
>     if (sqlca.sqlcode != 0) {
>       fprintf(stderr, "Fetching users %s\n", sqlca.sqlerrm.sqlerrmc);
>       break;
>     }
>   }
>
> ........
> ........
>
> Thanks for your response
>
> Alla



pgsql-general by date:

Previous
From: Alexander Dederer
Date:
Subject: Re: Return cursor
Next
From: Justin Clift
Date:
Subject: CLISP ?