Thread: embedded SQL cursos declare fails
Taken almost literally from the tutorial example (http://www.postgresql.org/idocs/index.php?app-ecpg.html) the followingcode: <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;
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!
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 justmy 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! >
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 justmy 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
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 justmy 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 Index: doc/src/sgml/ecpg.sgml =================================================================== RCS file: /home/projects/pgsql/cvsroot/pgsql/doc/src/sgml/ecpg.sgml,v retrieving revision 1.18 diff -c -r1.18 ecpg.sgml *** doc/src/sgml/ecpg.sgml 2000/12/22 21:51:57 1.18 --- doc/src/sgml/ecpg.sgml 2001/08/24 17:52:43 *************** *** 805,857 **** </listitem> </varlistentry> - <!--WARNING: FROM HERE ON THE TEXT IS OUTDATED!--> - <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> - </listitem> - </varlistentry> - - <varlistentry> - <term>Commit statement</term> - <listitem> - <para> - A commit statement looks like - <programlisting> - exec sql commit; - </programlisting> - and is translated on the output to - <programlisting> - ECPGcommit(__LINE__); - </programlisting> - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>Rollback statement</term> - <listitem> - <para> - A rollback statement looks like - <programlisting> - exec sql rollback; - </programlisting> - and is translated on the output to - <programlisting> - ECPGrollback(__LINE__); - </programlisting> - </para> - </listitem> - </varlistentry> - - <!--STARTING HERE IT IS OKAY AGAIN!--> <varlistentry> <term>Other statements</term> <listitem> --- 805,810 ----
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. > - <!--WARNING: FROM HERE ON THE TEXT IS OUTDATED!--> 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!
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. > > > - <!--WARNING: FROM HERE ON THE TEXT IS OUTDATED!--> > > 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
I have committed these changes to ecpg.sgml. You can see the result in the development docs tomorrow. > > 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. > > > > > - <!--WARNING: FROM HERE ON THE TEXT IS OUTDATED!--> > > > > 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 -- 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