Re: [BUGS] Bug #904: Deallocating of prepared statement in ECPG at - Mailing list pgsql-interfaces

Can someone comment on this?

---------------------------------------------------------------------------

pgsql-bugs@postgresql.org wrote:
> 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
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073