embedded SQL cursos declare fails

Started by Thalis A. Kalfigopoulosalmost 25 years ago7 messagesgeneral

thalis@cs.pitt.edu

almost 25 years ago

Taken almost literally from the tutorial example (http://www.postgresql.org/idocs/index.php?app-ecpg.html) the following code:

<connectino establishment succesfull up to here>
EXEC SQL DECLARE my_cursor CURSOR FOR SELECT a,b FROM lala WHERE a= :i;
EXEC SQL FETCH FORWARD NEXT FROM my_cursor INTO :tmpa,:tmpb;

throws the following error in the logs:

2001-06-18 15:41:02 DEBUG: query: begin transaction
2001-06-18 15:41:02 DEBUG: ProcessUtility: begin transaction
2001-06-18 15:41:02 DEBUG: query: fetch forward next from my_cursor
2001-06-18 15:41:02 DEBUG: ProcessUtility: fetch forward next from my_cursor
2001-06-18 15:41:02 NOTICE: PerformPortalFetch: portal "my_cursor" not found

I check the .c file generated by ecpg and the cursor declaration is commented out :-(

#line 10 "main.pgc"

/* declare my_cursor cursor for select a , b from lala where a = ? */
#line 11 "main.pgc"

what's missing?

thanks,
thalis

ps there is a spelling mistake in the tutorial:
EXEC SQL FETCH foo_bar INTO :FooBar,DooDad; should be
EXEC SQL FETCH foo_bar INTO :FooBar,:DooDad;

Michael Meskes

meskes@postgresql.org

almost 25 years ago

In reply to: Thalis A. Kalfigopoulos (#1)

Re: embedded SQL cursos declare fails

On Mon, Jun 18, 2001 at 03:59:56PM -0400, Thalis A. Kalfigopoulos wrote:

EXEC SQL DECLARE my_cursor CURSOR FOR SELECT a,b FROM lala WHERE a= :i;
EXEC SQL FETCH FORWARD NEXT FROM my_cursor INTO :tmpa,:tmpb;

Is there an EXEC SQL OPEN CURSOR my_cursor in between these lines?

I check the .c file generated by ecpg and the cursor declaration is commented out :-(

That's correct. It's placed at the OPEN spot.

Michael
--
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

Thalis A. Kalfigopoulos

thalis@cs.pitt.edu

almost 25 years ago

In reply to: Michael Meskes (#2)

Re: embedded SQL cursos declare fails

On Tue, 19 Jun 2001, Michael Meskes wrote:

On Mon, Jun 18, 2001 at 03:59:56PM -0400, Thalis A. Kalfigopoulos wrote:

EXEC SQL DECLARE my_cursor CURSOR FOR SELECT a,b FROM lala WHERE a= :i;
EXEC SQL FETCH FORWARD NEXT FROM my_cursor INTO :tmpa,:tmpb;

Is there an EXEC SQL OPEN CURSOR my_cursor in between these lines?

I check the .c file generated by ecpg and the cursor declaration is commented out :-(

That's correct. It's placed at the OPEN spot.

Correct, it works. But I was confused because the documentation (http://www.postgresql.org/idocs/index.php?ecpg-develop.html) mentions:

Open cursor statement
An open cursor statement looks like:
exec sql open cursor;
and is ignore and not copied from the output.

This gave me the impression that no explicit OPEN is necessary to operate on the cursor (normal Pg behavior). If not just my misunderstanding, someone rephrase thin in the docs.

cheers,
thalis

Show quoted text

Michael
--
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

Bruce Momjian

bruce@momjian.us

almost 25 years ago

In reply to: Thalis A. Kalfigopoulos (#3)

Re: embedded SQL cursos declare fails

Can someone comment on this? I can't tell from the grammar.

On Tue, 19 Jun 2001, Michael Meskes wrote:

On Mon, Jun 18, 2001 at 03:59:56PM -0400, Thalis A. Kalfigopoulos wrote:

EXEC SQL DECLARE my_cursor CURSOR FOR SELECT a,b FROM lala WHERE a= :i;
EXEC SQL FETCH FORWARD NEXT FROM my_cursor INTO :tmpa,:tmpb;

Is there an EXEC SQL OPEN CURSOR my_cursor in between these lines?

I check the .c file generated by ecpg and the cursor declaration is commented out :-(

That's correct. It's placed at the OPEN spot.

Correct, it works. But I was confused because the documentation (http://www.postgresql.org/idocs/index.php?ecpg-develop.html) mentions:

Open cursor statement
An open cursor statement looks like:
exec sql open cursor;
and is ignore and not copied from the output.

This gave me the impression that no explicit OPEN is necessary to operate on the cursor (normal Pg behavior). If not just my misunderstanding, someone rephrase thin in the docs.

cheers,
thalis

Michael
--
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian

bruce@momjian.us

over 24 years ago

In reply to: Thalis A. Kalfigopoulos (#3)

Re: embedded SQL cursos declare fails

OK, I have applied the following patch. Interestingly, the SGML code
marked this block as outdated, but didn't remove it for some reason.

On Tue, 19 Jun 2001, Michael Meskes wrote:

On Mon, Jun 18, 2001 at 03:59:56PM -0400, Thalis A. Kalfigopoulos wrote:

EXEC SQL DECLARE my_cursor CURSOR FOR SELECT a,b FROM lala WHERE a= :i;
EXEC SQL FETCH FORWARD NEXT FROM my_cursor INTO :tmpa,:tmpb;

Is there an EXEC SQL OPEN CURSOR my_cursor in between these lines?

I check the .c file generated by ecpg and the cursor declaration is commented out :-(

That's correct. It's placed at the OPEN spot.

Correct, it works. But I was confused because the documentation (http://www.postgresql.org/idocs/index.php?ecpg-develop.html) mentions:

Open cursor statement
An open cursor statement looks like:
exec sql open cursor;
and is ignore and not copied from the output.

This gave me the impression that no explicit OPEN is necessary to operate on the cursor (normal Pg behavior). If not just my misunderstanding, someone rephrase thin in the docs.

cheers,
thalis

Michael
--
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Michael Meskes

meskes@postgresql.org

over 24 years ago

In reply to: Bruce Momjian (#5)

Re: embedded SQL cursos declare fails

On Fri, Aug 24, 2001 at 02:07:48PM -0400, Bruce Momjian wrote:

OK, I have applied the following patch. Interestingly, the SGML code
marked this block as outdated, but didn't remove it for some reason.

I'm afraid this patch is incorrect.

-

Don't know why this is said.

- <varlistentry>
- <term>Open cursor statement</term>
- <listitem>
- <para>
- An open cursor statement looks like:
- <programlisting>
- exec sql open <replaceable>cursor</replaceable>;
- </programlisting>
- and is ignore and not copied from the output.
- </para>

This is partly correct. The OPEN statement is not copied. Instead the
DECLARE statement is issued at the place of the OPEN statement since it
opens the cursor too.

- A commit statement looks like
- <programlisting>
- exec sql commit;

Thsi certainly exists.

- and is translated on the output to
- <programlisting>
- ECPGcommit(__LINE__);

But the translation is not correct anymore.

- exec sql rollback;
- </programlisting>
- and is translated on the output to
- <programlisting>
- ECPGrollback(__LINE__);

Same here.

These functions have been combined to
ECPGtrans(int lineno, const char *connection_name, const char *transaction)

Michael
--
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

Bruce Momjian

bruce@momjian.us

over 24 years ago

In reply to: Michael Meskes (#6)

Re: embedded SQL cursos declare fails

OK, I have backed out that patch. Would you review that section of the
SGML and either commit a patch or send me one. Thanks.

On Fri, Aug 24, 2001 at 02:07:48PM -0400, Bruce Momjian wrote:

OK, I have applied the following patch. Interestingly, the SGML code
marked this block as outdated, but didn't remove it for some reason.

I'm afraid this patch is incorrect.

- 

Don't know why this is said.

- <varlistentry>
- <term>Open cursor statement</term>
- <listitem>
- <para>
- An open cursor statement looks like:
- <programlisting>
- exec sql open <replaceable>cursor</replaceable>;
- </programlisting>
- and is ignore and not copied from the output.
- </para>

This is partly correct. The OPEN statement is not copied. Instead the
DECLARE statement is issued at the place of the OPEN statement since it
opens the cursor too.

- A commit statement looks like
- <programlisting>
- exec sql commit;

Thsi certainly exists.

- and is translated on the output to
- <programlisting>
- ECPGcommit(__LINE__);

But the translation is not correct anymore.

- exec sql rollback;
- </programlisting>
- and is translated on the output to
- <programlisting>
- ECPGrollback(__LINE__);

Same here.

These functions have been combined to
ECPGtrans(int lineno, const char *connection_name, const char *transaction)

Michael
--
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

embedded SQL cursos declare fails

Attachments: