Re: [GENERAL] Invalid statement name (null) in line ## - what am I doing wrong ?
l...@crysberg.dk wrote:
I have a problem when upgrading from 8.2.4 to 8.3/8.4 using ecpg with a
prepare statement:
EXEC SQL PREPARE execquery FROM :stmt; line = __LINE__;
EXEC SQL AT :_thisDbConn DECLARE execcurs CURSOR FOR execquery; line =
__LINE__;
EXEC SQL AT :_thisDbConn OPEN execcurs; line = __LINE__;
EXEC SQL ALLOCATE DESCRIPTOR execdesc; line = __LINE__;
I get an SQL error: invalid statement name (null) on line ## (3rd line
above) on the OPEN cursor statement.
You have two different connections, right?
And you PREPARE the statement on one connection and then use it on the other,
right?
This used to work because prepared statements were treated as global in 8.2,
while from
8.3 on a prepared statement belongs to a specific connection.
The change in the source code was made here:
http://archives.postgresql.org/pgsql-committers/2007-09/msg00408.php
This makes prepared statements thread-safe, which is more sane anyway.
If you look at the C file output by the preprocessor, you'll find
in 8.2 something like:
{ ECPGdo(__LINE__, 0, 1, _thisDbConn, declare execcurs cursor for ?,
ECPGt_char_variable,(ECPGprepared_statement(execquery)),(long)1,(long)1,(1)*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);
and in 8.4 something like:
{ ECPGdo(__LINE__, 0, 1, _thisDbConn, 0, ECPGst_normal, declare execcurs
cursor for $1,
ECPGt_char_variable,(ECPGprepared_statement(_thisDbConn, execquery,
__LINE__)),(long)1,(long)1,(1)*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);
So you see, the ECPGprepared_statement function used to treate a prepared
statement
as something global rather than belonging to a certain connection.
The solution is to fix your program so that it uses a prepared statement
only on the connection where you prepared it.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Invalid statement name (null) in line ## - what am I doing wrong ?
Hi Albe,
Thank you for precise answer. And yes, I have (at least) 2 connections, all
named. So I am even not using the 'default' connection prepared statement as
you point out.
I have looked a little further into the output of ecpg as well as adding the
AT connection to my statements. Adding the AT to the prepare statement
seems to have fixed the named error :-). However, after that I'm not able to
compile my stuff on the 8.2 installation. Is there a way to 'detect' (using #if
) whether I am using 8.2 or 8.3+ ?
I also tried to put the AT connection on the the ALLOCATE/DEALLOCATE
DESCRIPTOR statements, which was accepted for the ALLOCATE, but gave an error
for the DEALLOCATE. I can see in the ecpg output that the AT is not (yet?)
used for these statements, so I guess that it is ok to leave them as is?
Are all this documented somewhere ?
Once again, thank you for pointing out the problem.
Leif
- Albe Laurenz laurenz.a...@wien.gv.at wrote:
l...@crysberg.dk wrote:
I have a problem when upgrading from 8.2.4 to 8.3/8.4 using ecpg
with a prepare statement:
EXEC SQL PREPARE execquery FROM :stmt; line = __LINE__;
EXEC SQL AT :_thisDbConn DECLARE execcurs CURSOR FOR execquery; line
= __LINE__;
EXEC SQL AT :_thisDbConn OPEN execcurs; line = __LINE__;
EXEC SQL ALLOCATE DESCRIPTOR execdesc; line = __LINE__;
I get an SQL error: invalid statement name (null) on line ##
(3rd line above) on the OPEN cursor statement.
You have two different connections, right?
And you PREPARE the statement on one connection and then use it on the
other, right?
This used to work because prepared statements were treated as global
in 8.2, while from
8.3 on a prepared statement belongs to a specific connection.
The change in the source code was made here:
http://archives.postgresql.org/pgsql-committers/2007-09/msg00408.php
This makes prepared statements thread-safe, which is more sane
anyway.
If you look at the C file output by the preprocessor, you'll find
in 8.2 something like:
{ ECPGdo(__LINE__, 0, 1, _thisDbConn, declare execcurs cursor
for ?,
ECPGt_char_variable,(ECPGprepared_statement(execquery)),(long)1,(long)1,(1)*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);
and in 8.4 something like:
{ ECPGdo(__LINE__, 0, 1, _thisDbConn, 0, ECPGst_normal, declare
execcurs cursor for $1,
ECPGt_char_variable,(ECPGprepared_statement(_thisDbConn,
execquery, __LINE__)),(long)1,(long)1,(1)*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);
So you see, the ECPGprepared_statement function used to treate a
prepared statement
as something global rather than belonging to a certain connection.
The solution is to fix your program so that it uses a prepared
statement
only on the connection where you prepared it.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Invalid statement name (null) in line ## - what am I doing wrong ?
leif wrote: Thank you for precise answer. And yes, I have (at least) 2 connections, all named. So I am even not using the 'default' connection prepared statement as you point out. I have looked a little further into the output of ecpg as well as adding the AT connection to my statements. Adding the AT to the prepare statement seems to have fixed the named error :-). However, after that I'm not able to compile my stuff on the 8.2 installation. Is there a way to 'detect' (using #if ) whether I am using 8.2 or 8.3+ ? You could #include pg_config.h and check PG_VERSION_NUM. I also tried to put the AT connection on the the ALLOCATE/DEALLOCATE DESCRIPTOR statements, which was accepted for the ALLOCATE, but gave an error for the DEALLOCATE. I can see in the ecpg output that the AT is not (yet?) used for these statements, so I guess that it is ok to leave them as is? I tend to agree; I'd say that descriptors are not associated with connections, they are something that lives on the client side. But I don't know for certain. I would say that you should leave them without AT, but it is weird that ALLOCATE lets you use AT without complaining. Are all this documented somewhere ? The documentation seems to be a bit vague on these things... Looking at the C output and the source helps... Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Invalid statement name (null) in line ## - what am I doing wrong ?
Hi again, Will do. Thanks again, Leif - Albe Laurenz laurenz.a...@wien.gv.at wrote: leif wrote: Thank you for precise answer. And yes, I have (at least) 2 connections, all named. So I am even not using the 'default' connection prepared statement as you point out. I have looked a little further into the output of ecpg as well as adding the AT connection to my statements. Adding the AT to the prepare statement seems to have fixed the named error :-). However, after that I'm not able to compile my stuff on the 8.2 installation. Is there a way to 'detect' (using #if ) whether I am using 8.2 or 8.3+ ? You could #include pg_config.h and check PG_VERSION_NUM. Ah, yes. I also tried to put the AT connection on the the ALLOCATE/DEALLOCATE DESCRIPTOR statements, which was accepted for the ALLOCATE, but gave an error for the DEALLOCATE. I can see in the ecpg output that the AT is not (yet?) used for these statements, so I guess that it is ok to leave them as is? I tend to agree; I'd say that descriptors are not associated with connections, they are something that lives on the client side. But I don't know for certain. I would say that you should leave them without AT, but it is weird that ALLOCATE lets you use AT without complaining. Are all this documented somewhere ? The documentation seems to be a bit vague on these things... Looking at the C output and the source helps... The real documentation ;-) Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Invalid statement name (null) in line ## - what am I doing wrong ?
can we see the original statement ? can you combine the 2 statements to produce the necessary cursor OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);http://www.postgresql.org/docs/8.1/interactive/plpgsql-cursors.html takk Martin __ Verzicht und Vertraulichkeitanmerkung Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Date: Fri, 19 Jun 2009 03:08:35 +0200 From: l...@crysberg.dk To: pgsql-general@postgresql.org Subject: [GENERAL] Invalid statement name (null) in line ## - what am I doing wrong ? Hi, I have a problem when upgrading from 8.2.4 to 8.3/8.4 using ecpg with a prepare statement: EXEC SQL PREPARE execquery FROM :stmt; line = __LINE__; EXEC SQL AT :_thisDbConn DECLARE execcurs CURSOR FOR execquery; line = __LINE__; EXEC SQL AT :_thisDbConn OPEN execcurs; line = __LINE__; EXEC SQL ALLOCATE DESCRIPTOR execdesc; line = __LINE__; I get an SQL error: invalid statement name (null) on line ## (3rd line above) on the OPEN cursor statement. I have been digging a little in the ecpg output and noticed that the format of the ECPGprepare() function call has changed between 8.2.4 and 8.3++. Also the ecpg library has changed version libecpg.so.5 - libecpg.so.6. I am compiling my program on an (older) system with 8.2.4 installed and I need have my program running on a production system running 8.3.5 or higher. This seems to work fine, but for other reasons I wanted to upgrade my compile system with 8.3.5 and this started to give me the error mentioned above. An ldd on the program gives: libecpg.so.6 = /usr/local/Packages/pgsql-8.3.5/lib/libecpg.so.6 (0xb7ed8000) libpq.so.5 = /usr/local/Packages/pgsql-8.3.5/lib/libpq.so.5 (0xb7ebc000) which looks to be ok. (The 8.2.4 compiled program was using libecpg.so.5.) It seems to me that somehow the prepare statement (first line above) is compiled into a ECPGprepare() call with 5 parameters, but using a library function with only 3 parameters, even though it points to the libecpg.so.6, which includes code having the 5 parameter ECPGprepare() function. All PostgreSQL systems are natively installed from source on the various systems, i.e. compiled individually on each system. All my systems are running Linux, but with different versions of Linux. I have also tried the very newest version 8.4rc1, but with same result, the error above. Please help, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general _ Microsoft brings you a new way to search the web. Try Bing™ now http://www.bing.com?form=MFEHPGpubl=WLHMTAGcrea=TEXT_MFEHPG_Core_tagline_try bing_1x1
Re: [GENERAL] Invalid statement name (null) in line ## - what am I doing wrong ?
Oops, forgot to include that, sorry. This is from a log output since it is dynamically generated: stmt= SELECT groupid, dg.ctrlid, userid, description, phoneno, ipaddr, online, active, dt.typename, null FROM devicegroup dg, device d, devtype dt WHERE userid = 23 AND typename = 'adm' AND dg.ctrlid = d.id AND dt.id = d.devtypeid ORDER BY d.id; Running this (copy/paste) in psql gives me the expected result as well as when using the 8.2.4 compiled version. I don't think I would be able to do what you suggests, since both fields, tables and where are dynamically generated based on many things. Leif - Martin Gainty mgai...@hotmail.com wrote: can we see the original statement ? can you combine the 2 statements to produce the necessary cursor OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1); http://www.postgresql.org/docs/8.1/interactive/plpgsql-cursors.html takk Martin __ Verzicht und Vertraulichkeitanmerkung Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Date: Fri, 19 Jun 2009 03:08:35 +0200 From: l...@crysberg.dk To: pgsql-general@postgresql.org Subject: [GENERAL] Invalid statement name (null) in line ## - what am I doing wrong ? Hi, I have a problem when upgrading from 8.2.4 to 8.3/8.4 using ecpg with a prepare statement: EXEC SQL PREPARE execquery FROM :stmt; line = __LINE__; EXEC SQL AT :_thisDbConn DECLARE execcurs CURSOR FOR execquery; line = __LINE__; EXEC SQL AT :_thisDbConn OPEN execcurs; line = __LINE__; EXEC SQL ALLOCATE DESCRIPTOR execdesc; line = __LINE__; I get an SQL error: invalid statement name (null) on line ## (3rd line above) on the OPEN cursor statement. I have been digging a little in the ecpg output and noticed that the format of the ECPGprepare() function call has changed between 8.2.4 and 8.3++. Also the ecpg library has changed version libecpg.so.5 - libecpg.so.6. I am compiling my program on an (older) system with 8.2.4 installed and I need have my program running on a production system running 8.3.5 or higher. This seems to work fine, but for other reasons I wanted to upgrade my compile system with 8.3.5 and this started to give me the error mentioned above. An ldd on the program gives: libecpg.so.6 = /usr/local/Packages/pgsql-8.3.5/lib/libecpg.so.6 (0xb7ed8000) libpq.so.5 = /usr/local/Packages/pgsql-8.3.5/lib/libpq.so.5 (0xb7ebc000) which looks to be ok. (The 8.2.4 compiled program was using libecpg.so.5.) It seems to me that somehow the prepare statement (first line above) is compiled into a ECPGprepare() call with 5 parameters, but using a library function with only 3 parameters, even though it points to the libecpg.so.6, which includes code having the 5 parameter ECPGprepare() function. All PostgreSQL systems are natively installed from source on the various systems, i.e. compiled individually on each system. All my systems are running Linux, but with different versions of Linux. I have also tried the very newest version 8.4rc1, but with same result, the error above. Please help, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Microsoft brings you a new way to search the web. Try Bing™ now -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
