Thread: ECPG Deallocate PREPARE statement - bug ?

Hi guys,

   I have a program that I need compile using PostgreSQL 8.4.0 (or later) and it must be able to run on an 8.3.5 based
systemas well as 8.4.0. I'm using embedded SQL for C and I have the following sequence of statements: 

   snprintf( stmt, 3000, "SELECT count(*) FROM %s WHERE %s", *table, *where );
   EXEC SQL AT :_thisDbConn PREPARE cntstmt FROM :stmt;
   EXEC SQL AT :_thisDbConn EXECUTE cntstmt INTO :recCount :fnull;
   .
   .
   EXEC SQL DEALLOCATE PREPARE cntstmt;

   This seems to be ok running on the 8.4.0 system, but when running it on the 8.3.5, it complains that it is an
'Invalidstatement name cntstmt' for the deallocation. 

   I then tried to add the 'AT :_thisDbConn' to the DEALLOCATE statement, but ecpg complained that there was no "at"
allowedfor deallocate. However, looking at the output (the .c file) I noticed that it had generated an apparently
correctECPG_deallocate() call. Manually compiling this and linking the program turned out to be able to run on both the
8.3.5and the 8.4.0 system without problems. 

   Is this a bug in ecpg or am I doing something  wrong ?

  Please advise,

 Leif

leif@crysberg.dk wrote:
>    I have a program that I need compile using PostgreSQL 
> 8.4.0 (or later) and it must be able to run on an 8.3.5 based 
> system as well as 8.4.0. I'm using embedded SQL for C and I 
> have the following sequence of statements:
> 
>    snprintf( stmt, 3000, "SELECT count(*) FROM %s WHERE %s", 
> *table, *where );
>    EXEC SQL AT :_thisDbConn PREPARE cntstmt FROM :stmt;
>    EXEC SQL AT :_thisDbConn EXECUTE cntstmt INTO :recCount :fnull;
>    .
>    .
>    EXEC SQL DEALLOCATE PREPARE cntstmt;
> 
>    This seems to be ok running on the 8.4.0 system, but when 
> running it on the 8.3.5, it complains that it is an 'Invalid 
> statement name cntstmt' for the deallocation.
> 
>    I then tried to add the 'AT :_thisDbConn' to the 
> DEALLOCATE statement, but ecpg complained that there was no 
> "at" allowed for deallocate. However, looking at the output 
> (the .c file) I noticed that it had generated an apparently 
> correct ECPG_deallocate() call. Manually compiling this and 
> linking the program turned out to be able to run on both the 
> 8.3.5 and the 8.4.0 system without problems.
> 
>    Is this a bug in ecpg or am I doing something  wrong ?

I cannot reproduce this.

I used the followind program that is based on your samples:

#include <stdio.h>
#include <stdlib.h>

int main(int argc, char** argv) {
    EXEC SQL BEGIN DECLARE SECTION;
    char stmt[100], _thisDbConn[] = "mydb";
    int fnull, recCount;
    EXEC SQL END DECLARE SECTION;
    const char table[] = "sometable", where[] = "TRUE";

    EXEC SQL WHENEVER SQLWARNING SQLPRINT;
    EXEC SQL WHENEVER SQLERROR SQLPRINT;

    EXEC SQL CONNECT TO "unix:postgresql://localhost:1237/test" AS :_thisDbConn;

    snprintf( stmt, 3000, "SELECT count(*) FROM %s WHERE %s", table, where );
    EXEC SQL AT :_thisDbConn PREPARE cntstmt FROM :stmt;
    EXEC SQL AT :_thisDbConn EXECUTE cntstmt INTO :recCount :fnull;
    EXEC SQL DEALLOCATE PREPARE cntstmt;

    EXEC SQL DISCONNECT :_thisDbConn;

    printf("Ergebnis: %d\n", recCount);

    return 0;
}

I prepared and compiled it on both 8.4.0 and 8.3.5 and ran both programs
against both versions (all 4 combinations).

There were no errors or warnings, and the correct result was returned.

Yours,
Laurenz Albe