From: pgsql-bugs@postgresql.org
To: pgsql-bugs@postgresql.org
Subject: Bug #904: Deallocating of prepared statement in ECPG at COMMIT
Date: Thu, 27 Feb 2003 06:35:19 -0500 (EST)
Message-ID: <20030227113519.37C69474E4F@postgresql.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: pgsql-bugs@postgresql.org
Subject: Re: Bug #904: Deallocating of prepared statement in ECPG at COMMIT
In-Reply-To: <20030227113519.37C69474E4F@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox