How to hold an update cursor with ESQL/C
Hello everybody.
I am starting with ESQL/C and I am getting problems to produce a
program to remove rows from a table executing COMMIT at each 10000
rows deleted. After the first COMMIT I am losing the cursor. Could
anybody give me some guidance and show what is missing?
#include <stdio.h>
#include <stdlib.h>
#define DATAREF "1899-12-31"
EXEC SQL include datetime;
int ibsdate();
main()
{
int data_ibs=ibsdate();
int contareg=0;
long contador=0;
EXEC SQL BEGIN DECLARE SECTION;
int datalimite=data_ibs-366;
integer v_mis1date;
EXEC SQL END DECLARE SECTION;
printf("Data Limite: %d\\n", datalimite);
EXEC SQL connect to 'ibs';
EXEC SQL set isolation dirty read ;
EXEC SQL declare cur_mis1 cursor for
select mis1date
from mis1
where mis1date < :datalimite
for update ; EXEC SQL BEGIN WORk;
EXEC SQL open cur_mis1;
for(;;) {
EXEC SQL fetch cur_mis1 into :v_mis1date ;
EXEC SQL delete from mis1 where current of cur_mis1 ;
contareg++;
if ( contareg > 10000 ) {
EXEC SQL COMMIT WORK;
printf("Registros eliminados: %ld\\n", contador);
contareg=0;
EXEC SQL BEGIN WORK;
}
contador++;
}
EXEC SQL COMMIT WORK;
if (strncmp(SQLSTATE, "02", 2) != 0)
printf("SQLSTATE after fetch is %s\\n", SQLSTATE);
EXEC SQL close cur_mis1;
EXEC SQL free cur_mis1;
EXEC SQL disconnect current;
printf("Fim da limpeza.\\nRegistros eliminados: %ld\\n\\n", contador);
}
"Eduardo Bispo" <eabispo@attglobal.net> wrote in message news:91f3f8a2.0305291238.6f6c73eb@posting.google.com... > EXEC SQL declare cur_mis1 cursor for change it to EXEC SQL declare cur_mis1 WITH HOLD for
On 29 May 2003 13:38:17 -0700, eabispo@attglobal.net (Eduardo Bispo)
wrote:
Declare the cursor WITH HOLD.
EXEC SQL declare cur_mis1 cursor WITH HOLD for
select mis1date
from mis1
where mis1date < :datalimite
for update ;
>Hello everybody.
>I am starting with ESQL/C and I am getting problems to produce a
>program to remove rows from a table executing COMMIT at each 10000
>rows deleted. After the first COMMIT I am losing the cursor. Could
>anybody give me some guidance and show what is missing?
>
> #include <stdio.h>
>#include <stdlib.h>
>
>#define DATAREF "1899-12-31"
>
>EXEC SQL include datetime;
>
>int ibsdate();
>
>main()
>{
> int data_ibs=ibsdate();
> int contareg=0;
> long contador=0;
>
> EXEC SQL BEGIN DECLARE SECTION;
>
> int datalimite=data_ibs-366;
>
> integer v_mis1date;
>
> EXEC SQL END DECLARE SECTION;
>
> printf("Data Limite: %d\\n", datalimite);
>
> EXEC SQL connect to 'ibs';
> EXEC SQL set isolation dirty read ;
> EXEC SQL declare cur_mis1 cursor for
> select mis1date
> from mis1
> where mis1date < :datalimite
> for update ;> EXEC SQL BEGIN WORk;
> EXEC SQL open cur_mis1;
>
> for(;;) {
> EXEC SQL fetch cur_mis1 into :v_mis1date ;
> EXEC SQL delete from mis1 where current of cur_mis1 ;
> contareg++;
> if ( contareg > 10000 ) {
> EXEC SQL COMMIT WORK;
> printf("Registros eliminados: %ld\\n", contador);
> contareg=0;
> EXEC SQL BEGIN WORK;
> }
> contador++;
> }
> EXEC SQL COMMIT WORK;
> if (strncmp(SQLSTATE, "02", 2) != 0)
> printf("SQLSTATE after fetch is %s\\n", SQLSTATE);
>
> EXEC SQL close cur_mis1;
> EXEC SQL free cur_mis1;
> EXEC SQL disconnect current;
>
> printf("Fim da limpeza.\\nRegistros eliminados: %ld\\n\\n", contador);
>
>
>}