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
