Consultor Eletrônico

Status: Verified

SYMPTOM(s):

ESQL/C - Error (7671) executing ESQL/C code involving a mixture of SQL Static and Dynamic statements.

Statement not allowed in readonly isolation level (7671)

Unable to change the TRANSACTION ISOLATION LEVEL in an ESQL/C program executing multiple Static and / or Dynamic SQL Queries

The program attempts to change the TRANSACTION ISOLATION LEVEL using multiple EXEC SQL SET TRANSACTION ISOLATION LEVEL statements.

Once the TRANSACTION ISOLATION LEVEL is set, the program seems to fail to change it again.

Executing code similar to the following ESQL/C sample program where the highlighted EXEC SQL SET TRANSACTION ISOLATION LEVEL READ COMMITTED; statement is failing as evidenced by the generated (7671) error:
void DynamicSelectStatementProcessing();
struct sqlca sqlca;
dh_i32_t SQLCODE;
int main()
{
EXEC SQL BEGIN DECLARE SECTION ;
char ConnectionString[120];
long var1;
char var2 [10];
EXEC SQL END DECLARE SECTION ;
strncpy(ConnectionString, "progress:T:localhost:23456:sports2000", 119);

EXEC SQL WHENEVER SQLERROR GOTO HandleMainSqlError ;
EXEC SQL CONNECT TO :ConnectionString AS 'conn' ;

DynamicSelectStatementProcessing("select name, city from pub.customer where custnum < 5");
EXEC SQL COMMIT WORK;

/*** SET ISOLATION LEVEL TO READ COMMITTED ***/
EXEC SQL SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
EXEC SQL DECLARE Cust_cur CURSOR FOR SELECT custnum, city from pub.customer WHERE custnum = 10 FOR UPDATE OF City;
EXEC SQL OPEN Cust_cur ;
EXEC SQL FETCH Cust_cur INTO :var1, :var2 ;
EXEC SQL UPDATE pub.customer SET City = 'Boston' WHERE custnum = :var1;
EXEC SQL COMMIT WORK;

EXEC SQL DISCONNECT 'conn';
exit(0);

/************ HandleMainSqlError ********/
HandleMainSqlError:
printf("The following SQLERROR occured in Main():\n%s\n", sqlca.sqlerrm);
exit(1);
}
/******************************************DynamicSelectStatementProcessing**********************************************/
void DynamicSelectStatementProcessing(char * s)
{
EXEC SQL BEGIN DECLARE SECTION ;
char SqlStatement[256];
char field1[256];
EXEC SQL END DECLARE SECTION ;

strncpy(SqlStatement, s, 255);

EXEC SQL WHENEVER SQLERROR GOTO HandleSqlError ;
EXEC SQL SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
EXEC SQL PREPARE stmtid FROM :SqlStatement ;
EXEC SQL DECLARE x CURSOR FOR stmtid ;
EXEC SQL OPEN x ;
&.nbsp;
EXEC SQL WHENEVER NOT FOUND GOTO HandleNotFound ;
while(1)
{
EXEC SQL FETCH x INTO :field1 ;
printf("%s\n", field1);
}

/************ HandleNotFound ********/
HandleNotFound:
printf("No More records available\n\n");
EXEC SQL CLOSE x ;
EXEC SQL COMMIT WORK ;
return;

/************ HandleSqlError ********/
HandleSqlError:
printf("DynamicSelectStatementProcessing: SQL Error (%ld) %s\n", sqlca.sqlcode, sqlca.sqlerrm);
return;
}.

FACT(s) (Environment):

All Supported Operating Systems
OpenEdge 10.x

CAUSE:

Bug# OE00183771

CAUSE:

A changed isolation level from a Static ESQL/C client , even after a transaction ends, is not communicated to the server. The ODBC and JDBC drivers communicate isolation level changes by sending an executable statement to the server. So does dynamic ESQL. Static ESQL does not do that at this time.

FIX:

Upgrade to OpenEdge 10.2A03, OpenEdge 10.2B01 or later. If upgrading is not feasible, a workaround is to change all the Static SQL Statements to Dynamic SQL Statements and to execute the EXEC SQL SET TRANSACTION ISOLATION LEVEL statement before each EXEC SQL PREPARE statement. See the sample workaround1.pc ESQL/C program in the note below.
Another less performing workaround is to execute each Static SQL Statements in its own connection object and to execute the EXEC SQL SET TRANSACTION ISOLATION LEVEL statement after each new EXEC SQL CONNECT statement. See the sample workaround2.pc ESQL/C program in the note below.