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

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