The following bug has been logged online:
Bug reference: 5643
Logged by: Alexander Pyhalov
Email address: alp@rsu.ru
PostgreSQL version: 8.4.4
Operating system: FreeBSD 8.1
Description: ecpg program doesn't process WHENEVER NOT DATA FOUND
Details:
I have test program in embedded SQL, which works perfectly on 8.4.3 server
and 8.4.0 client, but refuses to work on 8.4.4.
The following code (lines 58-66):
EXEC SQL WHENEVER NOT FOUND DO BREAK;
while(1){
EXEC SQL FETCH NEXT FROM ecpg_tc INTO
:id,:name,:surname,:dept,:phone,:hiredate,:job,:salary;
printf("error code %ld, state %s, message %s, rows %ld, warning %d\n",
sqlca.sqlcode, sqlca.sqlstate, sqlca.sqlerrm.sqlerrmc,
sqlca.sqlerrd[2],
sqlca.sqlwarn[0]);
printf("%5d %20s %20s %5s %20s %20s %50s
%10d\n",id,name,surname,dept,phone,hiredate,job,salary);
}
EXEC SQL WHENEVER NOT FOUND CONTINUE;
never breaks on NOT DATA FOUND.
The output from program looks like:
[13112]: ecpg_execute on line 60: query: fetch next from ecpg_tc; with 0
parameter(s) on connection test
[13112]: ecpg_execute on line 60: using PQexec
[13112]: ecpg_execute on line 60: correctly got 0 tuples with 8 fields
[13112]: raising sqlcode 100 on line 60: no data found on line 60
error code 0, state 00000, message , rows 0, warning 0
7 Sergey Spiridonov MNT 3431
2002-07-02 System Administrator
4800
[13112]: ecpg_execute on line 60: query: fetch next from ecpg_tc; with 0
parameter(s) on connection test
[13112]: ecpg_execute on line 60: using PQexec
[13112]: ecpg_execute on line 60: correctly got 0 tuples with 8 fields
[13112]: raising sqlcode 100 on line 60: no data found on line 60
error code 0, state 00000, message , rows 0, warning 0
7 Sergey Spiridonov MNT 3431
2002-07-02 System Administrator
4800
[13112]: ecpg_execute on line 60: query: fetch next from ecpg_tc; with 0
parameter(s) on connection test
[13112]: ecpg_execute on line 60: using PQexec
[13112]: ecpg_execute on line 60: correctly got 0 tuples with 8 fields
[13112]: raising sqlcode 100 on line 60: no data found on line 60
error code 0, state 00000, message , rows 0, warning 0
7 Sergey Spiridonov MNT 3431
2002-07-02 System Administrator
4800
Where we can see, that sqlcode is raising, but in program we receive
sqlcode=0.
ecpg-generated C code for this fragment is:
/* exec sql whenever not found break ; */
#line 58 "test_ecpg.pgc"
while(1){
{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "fetch next from
ecpg_tc", ECPGt_EOIT,
ECPGt_int,&(id),(long)1,(long)1,sizeof(int),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_char,(name),(long)255,(long)1,(255)*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_char,(surname),(long)255,(long)1,(255)*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_char,(dept),(long)4,(long)1,(4)*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_char,(phone),(long)20,(long)1,(20)*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_char,(hiredate),(long)20,(long)1,(20)*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_char,(job),(long)255,(long)1,(255)*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_int,&(salary),(long)1,(long)1,sizeof(int),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
#line 60 "test_ecpg.pgc"
if (sqlca.sqlcode == ECPG_NOT_FOUND) break;
#line 60 "test_ecpg.pgc"
if (sqlca.sqlcode < 0) sqlprint();}
#line 60 "test_ecpg.pgc"
printf("error code %ld, state %s, message %s, rows %ld, warning %d\n",
sqlca.sqlcode, sqlca.sqlstate, sqlca.sqlerrm.sqlerrmc,
sqlca.sqlerrd[2],
sqlca.sqlwarn[0]);
printf("%5d %20s %20s %5s %20s %20s %50s
%10d\n",id,name,surname,dept,phone,hiredate,job,salary);
}
/* exec sql whenever not found continue ; */
#line 66 "test_ecpg.pgc"
{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "close ecpg_tc",
ECPGt_EOIT, ECPGt_EORT);
#line 68 "test_ecpg.pgc"
if (sqlca.sqlcode < 0) sqlprint();}
#line 68 "test_ecpg.pgc"
