"request synchronization error" when calling isc_dsql_sql_info with isc_info_sql_records parameter after last record fetched with "execute procedure" [CORE2154]

[firebird-automations]

Submitted by: Thiago Borges (thborges)

Attachments:
testit.cpp

I got "request syncronization error" when calling isc_dsql_sql_info after EOF was reached.

This error appear in CORE1310, and was solved in 2.1 Beta 1 for "select * from table".
But I getting the error now running "execute procedure X(P)"

With fbclient.dll 2.0.4.13130 all seems ok.

Commits: 285879c 5fdc028

[firebird-automations]

Commented by: Thiago Borges (thborges)

I modified the example provided in CORE1310 to use "execute procedure" and the error reappear.

[firebird-automations]

Modified by: Thiago Borges (thborges)

Attachment: testit.cpp [ 11130 ]

[firebird-automations]

Modified by: Thiago Borges (thborges)

Version: 2.1.1 [ 10223 ]

Version: 2.5 Alpha 1 [ 10224 ]

description: I getting this error always when a table contains the fields with double precision data type and has at least a few hudreds records. Everything is ok if a table does not have double precision data type fields, or there are a few records in it, or double precision fileds are not used in a query.

Last problem bulid tested: LI-T2.1.0.15947 Firebird 2.1 Beta 1
Last good build I have: LI-T2.1.0.15853 Firebird 2.1 Beta 1

Test:
/*meta-test.sql*/
SET SQL DIALECT 3;

create database 'localhost:/tmp/testdb.fdb' page_size=16384;

CREATE TABLE "TEST"
(
"ID" INTEGER NOT NULL PRIMARY KEY,
"TEXTFLD" VARCHAR(64),
"DOUBLEFLD" double precision
);
/*=== end of meta-test.sql =======================*/
/* test-sql.sql */
set term ^;
execute block
as
declare variable i integer;
begin
i=0;
while (i<1000) do
begin
insert into test
(id,textfld,doublefld)
values
(:i,'NAME ' || :i, :i*0.245);
i=i+1;
end
end
^
/*== end of test-sql.sql */

/*testit.cpp*/
#⁠include <ibase.h>
#⁠include <stdio.h>
#⁠include<stdlib.h>

#⁠define ERREXIT(status, rc) {isc_print_status(status); return rc;}
#⁠define IS_ISC_ERROR(status) (status[0] == 1 && status[1])

isc_db_handle db = NULL;
isc_tr_handle trans = NULL;
isc_stmt_handle stmt = NULL;
ISC_STATUS_ARRAY status;
int fetch_stat=0;
XSQLDA * sqlda;

void free_sqlda(XSQLDA *sqlda) {
int i;
for (i=0; i<sqlda->sqld; i++) {
free(sqlda->sqlvar[i].sqldata);
free(sqlda->sqlvar[i].sqlind);
}
sqlda->sqld=0;
}

void set_sqlda(XSQLDA *sqlda) {
int i;
for (i=0; i<sqlda->sqld; i++) {
if ((sqlda->sqlvar[i].sqltype & ~1)==SQL_VARYING)
sqlda->sqlvar[i].sqldata = (char*)malloc(sqlda->sqlvar[i].sqllen + sizeof(short));
else
sqlda->sqlvar[i].sqldata = (char*)malloc(sqlda->sqlvar[i].sqllen);

sqlda->sqlvar[i].sqlind = (short*)calloc(sizeof(short),1);
}
}

int affectedRows(int * sCnt=0,int * uCnt=0, int * iCnt=0, int *dCnt=0) {
static char count_info[] = {isc_info_sql_records};
char info_buffer[128];
char *p ;
int affected_rows=-1;
int selectCount,insertCount,updateCount,deleteCount;

selectCount=insertCount=updateCount=deleteCount=0;

isc_dsql_sql_info(status, &stmt,
sizeof (count_info), count_info,sizeof (info_buffer), info_buffer);
if (IS_ISC_ERROR(status)) return -1;

p=info_buffer+3;
affected_rows=0;
while (*p != isc_info_end) {
int count;
char item=*p++;
short len = (short)isc_vax_integer(p,2);
p+=2;
count=isc_vax_integer(p,len);
switch(item) {
case isc_info_req_select_count: selectCount+=count; break;
case isc_info_req_insert_count: insertCount+=count; break;
case isc_info_req_update_count: updateCount+=count; break;
case isc_info_req_delete_count: deleteCount+=count; break;
}

 p \+= len;

}
if (sCnt) *sCnt=selectCount;
if (uCnt) *uCnt=updateCount;
if (iCnt) *iCnt=insertCount;
if (dCnt) *dCnt=deleteCount;

affected_rows = selectCount+updateCount+insertCount+deleteCount;

return affected_rows;
}

int main (int argc, char** argv)
{

char dbname[]="localhost:/tmp/testdb.fdb";
if (isc_attach_database(status, 0, dbname, &db, 0, NULL))
{
printf("Could not open database %s\n", dbname);
ERREXIT(status, 1);
}
sqlda = (XSQLDA *) malloc(XSQLDA_LENGTH (3));
sqlda->sqln = 3;
sqlda->version = 1;

isc\_start\_transaction\(status,&trans,1,&db,0,0\);    
if \(\!trans\) ERREXIT\(status, 1\);
isc\_dsql\_allocate\_statement\(status, &db, &stmt\);
if \(\!stmt\) ERREXIT\(status, 1\);

if \(isc\_dsql\_prepare\(status, &trans, &stmt, 0, "select \* from test", 3, sqlda\)\)
 ERREXIT\(status, 1\);
 
set\_sqlda\(sqlda\);
if \(isc\_dsql\_execute\(status, &trans, &stmt, 3,sqlda\)\)
 ERREXIT\(status, 1\);

int ar;    

while\(\!\(fetch\_stat=isc\_dsql\_fetch\(status, &stmt, 3, sqlda\)\)\);

if \(fetch\_stat\!=100L\) ERREXIT\(status, 1\);

if \(\(ar=affectedRows\(\)\)<0\) ERREXIT\(status, 1\);

printf\("Affected rows: %i\\n",ar\);

free\_sqlda\(sqlda\);
free \(sqlda\);
isc\_commit\_transaction\(status, &trans\);
isc\_detach\_database\(status, &db\);

return 0;    

}
/* end of testit.cpp*/

/*Makefile*/
GDS = fbclient

OBJS = testit.o
TARGETS = testit

.cpp.o:
g++ -g -c $&lt; -o $@

testit: $(OBJS)
g++ -o $@ $^ -l$(GDS)
clean:
rm -f $(TARGETS) $(OBJS)
cleanall: clean
rm -f /tmp/testdb.fdb
#⁠end of Makedile

/*http://runit.sh*/
#⁠!/bin/bash
prompt_pass () {
echo -n "DB User(sysdba): "
read ISC_USER
echo -n "DB Password(masterkey): "
stty -echo
read ISC_PASSWORD
echo
stty echo

if [ "${ISC_USER}" == "" ]
then
ISC_USER="sysdba"
fi

if [ "${ISC_PASSWORD}" == "" ]
then
ISC_PASSWORD="masterkey"
fi

}

clear

make || exit -1

echo
prompt_pass
export ISC_USER
export ISC_PASSWORD

if [ ! -f /tmp/testdb.fdb ]
then
echo -n "Firebird dir(/opt/firebird/): "
read FBPATH;
if [ "${FBPATH}" == "" ]
then
FBPATH="/opt/firebird/"
fi
${FBPATH}/bin/isql -i meta-test.sql || exit -1
${FBPATH}/bin/isql -i test-sql.sql localhost:/tmp/testdb.fdb || exit -1
fi

./testit
#⁠end of http://runit.sh

=>

I got "request syncronization error" when calling isc_dsql_sql_info after EOF was reached.

This error appear in CORE1310, and was solved in 2.1 Beta 1 for "select * from table".
But I getting the error now running "execute procedure X(P)"

With fbclient.dll 2.0.4.13130 all seems ok.

environment: Debian Linux Sarge 3.1, gcc 3.3.5 => Windows/VC++ 8

Version: 2.1 Beta 1 [ 10141 ] =>

[firebird-automations]

Commented by: @hvlad

> I got "request syncronization error" when calling isc_dsql_sql_info after EOF was reached.

And this is perfectly valid. What else you expected ?

[firebird-automations]

Commented by: @hvlad

Hmm... your test case is not correct as you must not fetch from "execute procedure" request.
Instead you must use isc_dsql_execute2 and get result row (output parameters) there

[firebird-automations]

Commented by: Thiago Borges (thborges)

Sorry, I try to say:

The SQL command return 1 row.

The first call to isc_dsql_fetch return 0
The second returns 335544364, and not 100L. This is ok?

[firebird-automations]

Commented by: @hvlad

You should read at least documentation for IB6 to understand statement type and how statements of different types must be prepared and executed.
Read "Working with Dynamic SQL"\"DSQL programming methods" chapter
From this POV your code is not correct.

From another POV we may return EOF on second fetch and only after it isc_req_sync for such scenario.
I'll look for behavior of fbclient before 2.1 and came back with my conclusion

[firebird-automations]

Commented by: @hvlad

Conclusion is : when engine executed non-select statement, i.e. statement which have no cursor, it must disallow fetch from such statement handle.

This check will be implemented in 2.5 engine only to allow bad application work untouched with current Firebird versions.

> From another POV we may return EOF on second fetch and only after it isc_req_sync for such scenario.
Engine can't return EOF in this case as there is no cursor open

> I'll look for behavior of fbclient before 2.1 and came back with my conclusion
Before v2.1 client silently ignore isc_req_sync error if after first fetch there was no second fetch call. I consider it as a bug, fixed in v2.1.

[firebird-automations]

Modified by: @hvlad

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.5 Beta 1 [ 10251 ]

Fix Version: 2.1 Beta 1 [ 10141 ] =>

[firebird-automations]

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

[firebird-automations]

Modified by: @pavel-zotov

QA Status: No test