Re: Invalid statement name (null) in line ## - what am I doing wrong ?

leif(at)crysberg(dot)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