Bug #904: Deallocating of prepared statement in ECPG at COMMIT

Jiri Langr (jiri.langr@konero.cz) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Deallocating of prepared statement in ECPG at COMMIT

Long Description
When I prepare a statement in ECPG it lives only to first explicit transaction block. At the COMMIT it seems to be
deallocated.

It is not good behavior because the main advantage of prepared statements is once prepare and many times execute!

Sample Code
ESQL/C code
********************************************
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#define DBNAME  "langr"

/* Testing of any SQL error, printing error message and exit */
void test(int l_exit) {
  if(sqlca.sqlcode < 0) {
    printf("Error: %s\n", sqlca.sqlerrm.sqlerrmc);
    if(l_exit) {
      EXEC SQL DISCONNECT;
      exit(-1);
    }
  }
}

/* Demonstration of error concerning the DEALLOCATEing of PREPARED statement
   at the COMMIT in PostgreSQL ESQL/C */
int main() {

  EXEC SQL BEGIN DECLARE SECTION;
    char dbname[64] = DBNAME;
    char sql_string[1000];
    int l_id;
    char l_name[10 + 1];
  EXEC SQL END DECLARE SECTION;

  ECPGdebug(1, stderr);

/* Creating DB and connecting to them */
  /* strcpy(dbname, DBNAME); */
  EXEC SQL CONNECT TO :dbname;
  test(1);
  EXEC SQL SET AUTOCOMMIT TO ON;
  test(1);

/* Creating table */
  EXEC SQL DROP TABLE test;
  test(0); /* no exiting because it has not to exist yet */
  EXEC SQL CREATE TABLE test (
        id      INTEGER NOT NULL,
        name    CHAR(10)
        );
  test(1);

/* Preparing INSERT statement */
  strcpy(sql_string, "INSERT INTO test VALUES(?, ?)");
  EXEC SQL PREPARE prep_ins FROM :sql_string;
  test(1);

/* Inserting several rows in one transaction */
  EXEC SQL BEGIN;
  test(1);
  for(l_id = 0; l_id < 3; l_id++) {
    switch(l_id) {
      case 0: strcpy(l_name, "First"); break;
      case 1: strcpy(l_name, "Second"); break;
      case 2: strcpy(l_name, "Third"); break;
    }
    EXEC SQL EXECUTE prep_ins USING :l_id, :l_name;
    test(1);
  }
  EXEC SQL COMMIT;
  test(1);

/* It did work well, but the statement was DEALLOCATED automatically - WHY?? */

/* Inserting next line in separate transaction */
  l_id = 3;
  strcpy(l_name, "Fourth");
  EXEC SQL BEGIN;
  test(1);
  EXEC SQL EXECUTE prep_ins USING :l_id, :l_name;
  test(0); /* no exiting becase it doesn't work!! the statement doesn't exist */
  EXEC SQL COMMIT;
  test(1);

  EXEC SQL BEGIN;
  test(1);
  EXEC SQL DECLARE cur_test CURSOR FOR SELECT * FROM test;
  test(1);
  EXEC SQL OPEN cur_test;
  test(1);
  while(1) {
    EXEC SQL FETCH cur_test INTO :l_id, :l_name;
    test(1);
    if(sqlca.sqlcode == ECPG_NOT_FOUND) break;
    printf("%d: %s\n", l_id, l_name);
  }
  EXEC SQL CLOSE cur_test;
  test(1);
  EXEC SQL COMMIT;
  test(1);

  EXEC SQL FREE prep_ins;
  test(0); /* no exiting becase it doesn't work!! the statement doesn't exist */
  EXEC SQL DROP TABLE test;
  test(1);
  EXEC SQL DISCONNECT;
  test(1);
  return(0);
}

SQL code doing the same funcionality and work well!!
*****************************************************

/* Demonstration of the same functionality in SQL, where it DOES work well */

/* Creating table */
  CREATE TABLE test (
        id      INTEGER NOT NULL,
        name    CHAR(10)
        );

/* Preparing INSERT statement */
  PREPARE prep_ins(INTEGER, CHAR) AS INSERT INTO TEST VALUES($1, $2);

/* Inserting several rows in one transaction */
  BEGIN;
  EXECUTE prep_ins (0, 'First');
  EXECUTE prep_ins (1, 'Second');
  EXECUTE prep_ins (2, 'Third');
  COMMIT;

/* In SQL in the difference of ESQL/C the DEALLOCATION was not don, it is well *
/
/* Inserting next line in separate transaction */
  BEGIN;
  EXECUTE prep_ins (3, 'Fourth');
  COMMIT;

/* Reading data from the table */
  SELECT * FROM test;

/* Deallocating of the statement */
  DEALLOCATE prep_ins;
  DROP TABLE test;


No file was uploaded with this report