pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedUSE OF CURSORS IN ECPG
4+ messages / 4 participants
[nested] [flat]
* USE OF CURSORS IN ECPG
@ 2000-11-07 16:52 Maurizio <maurizio@dreamtech-it.com>
0 siblings, 1 reply; 4+ messages in thread
From: Maurizio @ 2000-11-07 16:52 UTC (permalink / raw)
To: pgsql-interfaces
Hi,
I'm just starded using ecpg and I have some problem with cursors.
I wrote this lines
exec sql begin declare section;
char query[500];
int CodImpianto;
int Cod1;
int Cod2;
char Descr[100];
exec sql end declare section;
strcpy(query, "select ............");
exec sql connect db;
sub1()
{
exec sql prepare stmtunimpianto from :query;
exec sql declare CUR_UNIMPIANTO cursor for stmtunimpianto;
.
.
.
exec sql open CUR_UNIMPIANTO using :CodImpianto;
exec sql fetch CUR_UNIMPIANTO into :CodImpianto, :Descr;
.
.
exec sql close CUR_UNIMPIANTO;
exec sql execute StmtUpdImpianti using :Cod1, :Descr, :Cod2;
exec sql commit;
}
I call this routine a lot of time.
If I rem the commit all works fine but if I execute the commit the cursor was deleted and I receive some errors.
At open cursor : sqlca.sqlcode = -230 Invalid statement namei in line ......
at fetch : NOTICE : PerformPortalFetch: portal "cur_unimpianto" not found
Please could anyone help me ?
thanks
regards
Maurizio Cauci
DREAMTECH di Cauci Maurizio
Via Ronchetti, 2 - 21013 Gallarate (VA)
www.dreamtech-it.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: USE OF CURSORS IN ECPG
@ 2000-11-08 11:39 Michael Meskes <meskes@postgresql.org>
parent: Maurizio <maurizio@dreamtech-it.com>
0 siblings, 1 reply; 4+ messages in thread
From: Michael Meskes @ 2000-11-08 11:39 UTC (permalink / raw)
To: Maurizio <maurizio@dreamtech-it.com>; +Cc: pgsql-interfaces
On Tue, Nov 07, 2000 at 05:52:00PM +0100, Maurizio wrote:
> I'm just starded using ecpg and I have some problem with cursors.
> ...
> exec sql prepare stmtunimpianto from :query;
> .
> .
> exec sql commit;
> ...
> If I rem the commit all works fine but if I execute the commit the cursor was deleted and I receive some errors.
>
> At open cursor : sqlca.sqlcode = -230 Invalid statement namei in line ......
> at fetch : NOTICE : PerformPortalFetch: portal "cur_unimpianto" not found
No surprise here as ending a transaction forces a deallocation of all
prepared statements. AFAIK that's what the standard says.
Michael
--
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [INTERFACES] USE OF CURSORS IN ECPG
@ 2000-11-09 13:49 Maurizio <maurizio.c@libero.it>
parent: Michael Meskes <meskes@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: Maurizio @ 2000-11-09 13:49 UTC (permalink / raw)
To: Michael Meskes <meskes@postgresql.org>; +Cc: pgsql-hackers@postgresql.org
But, how can I do ?
I have to recall the same routine many times. I tried to prepare and declare
the cursor inside the routine but when I compile with ecpg I receive the
error Cursor already defined.
Maurizio
.
----- Original Message -----
From: "Michael Meskes" <meskes@postgresql.org>
To: "Maurizio" <maurizio@dreamtech-it.com>
Cc: <pgsql-interfaces@postgresql.org>
Sent: Wednesday, November 08, 2000 12:39 PM
Subject: Re: [INTERFACES] USE OF CURSORS IN ECPG
> On Tue, Nov 07, 2000 at 05:52:00PM +0100, Maurizio wrote:
> > I'm just starded using ecpg and I have some problem with cursors.
> > ...
> > exec sql prepare stmtunimpianto from :query;
> > .
> > .
> > exec sql commit;
> > ...
> > If I rem the commit all works fine but if I execute the commit the
cursor was deleted and I receive some errors.
> >
> > At open cursor : sqlca.sqlcode = -230 Invalid statement namei in line
......
> > at fetch : NOTICE : PerformPortalFetch: portal
"cur_unimpianto" not found
>
> No surprise here as ending a transaction forces a deallocation of all
> prepared statements. AFAIK that's what the standard says.
>
> Michael
>
> --
> Michael Meskes
> Michael@Fam-Meskes.De
> Go SF 49ers! Go Rhein Fire!
> Use Debian GNU/Linux! Use PostgreSQL!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Re: [INTERFACES] USE OF CURSORS IN ECPG
@ 2000-11-10 10:52 Christof Petig <christof.petig@wtal.de>
parent: Maurizio <maurizio.c@libero.it>
0 siblings, 0 replies; 4+ messages in thread
From: Christof Petig @ 2000-11-10 10:52 UTC (permalink / raw)
To: Maurizio <maurizio.c@libero.it>; +Cc: Michael Meskes <meskes@postgresql.org>; pgsql-hackers@postgresql.org
Maurizio wrote:
> But, how can I do ?
> I have to recall the same routine many times. I tried to prepare and declare
> the cursor inside the routine but when I compile with ecpg I receive the
> error Cursor already defined.
you should drop the cursor (exec sql close name;)
But there is trouble ahead (believe me - see below)
> Maurizioan
------------
(Oh, I misunderstood, if you get into trouble with transactions (waiting for
you ;-() you might consider the following lines)
------------
You certainly should disable autocommit!
exec sql set autocommit to off;
otherwise you have no control over when a transaction starts and whether this
succeded.
Then at the start of your SubProgram do
bool my_transaction;
exec sql begin work;
my_transaction=!sqlca.sqlcode;
at the end:
if (my_transaction) exec sql commit work;
This should give you an idea on how to accomplish it. If not feel free to ask
me.
But (sigh) you need my NOTICE to sqlca patch applied. It's not in 7.0 but in
7.1! I don't know any other way (I'll attach the patch)
Christof
PS: You might consider applying the patch for (update where not_found) -> 100
and the cache which doubles ecpg's speed.
Attachments:
Pgsql_Ecpg_Notice.diff.gz (application/x-gzip, 2.0K) download
Pgsql_Ecpg_Notice2.diff.gz (application/x-gzip, 291B) download
Pgsql_Ecpg_Update_100.diff.gz (application/x-gzip, 339B) download
Pgsql_Ecpg_Type_Cache.diff.gz (application/x-gzip, 1.4K) download
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2000-11-10 10:52 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-11-07 16:52 USE OF CURSORS IN ECPG Maurizio <maurizio@dreamtech-it.com>
2000-11-08 11:39 ` Michael Meskes <meskes@postgresql.org>
2000-11-09 13:49 ` Maurizio <maurizio.c@libero.it>
2000-11-10 10:52 ` Christof Petig <christof.petig@wtal.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox