Skip site navigation (1) Skip section navigation (2)

Re: [BUGS] Bug #904: Deallocating of prepared statement in ECPG at

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: jiri(dot)langr(at)konero(dot)cz
Cc: PostgreSQL-interfaces <pgsql-interfaces(at)postgresql(dot)org>
Subject: Re: [BUGS] Bug #904: Deallocating of prepared statement in ECPG at
Date: 2003-03-19 21:31:38
Message-ID: 200303192131.h2JLVcQ14766@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackerspgsql-interfaces
Can someone comment on this?

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

pgsql-bugs(at)postgresql(dot)org wrote:
> Jiri Langr (jiri(dot)langr(at)konero(dot)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(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2003-03-19 21:38:52
Subject: Re: ALTER USER
Previous:From: Bruce MomjianDate: 2003-03-19 21:20:26
Subject: Re: More outdated examples

pgsql-bugs by date

Next:From: pgsql-bugsDate: 2003-03-19 21:55:09
Subject: Bug #914: Possible bug with regards to multiple persistant connections
Previous:From: Chris BrownDate: 2003-03-19 03:23:34
Subject: libpq PQexec not thread-safe

pgsql-interfaces by date

Next:From: Philip YarraDate: 2003-03-19 23:10:31
Subject: Re: [BUGS] Bug #904: Deallocating of prepared statement in ECPG at
Previous:From: Bruce MomjianDate: 2003-03-19 00:54:41
Subject: Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group