Thread: Re: 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.
Addingthe "AT" to the prepare statement seems to have fixed the named error :-). However, after that I'm not able to
compilemy 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
forthe ALLOCATE, but gave an error for the DEALLOCATE. I can see in the ecpg output that the "AT" is not (yet?) used
forthese 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.albe@wien.gv.at> wrote:

> leif@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

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