Declaring Cursor inside a loop

[Ivan Smahin]

Hello Fabian,

Wednesday, August 20, 2008, 2:26:01 PM, you wrote:

> Hi,

> I am working on my first procedure.

> Inside a loop I have to check if a table already has a record for a
> specific date, so I redeclare a cursor with every loop.
> But valentina tells me 

>  13:25:28 Kernel error: 0x56501. Cursor "vcurDatesum2" is opened already.

> This is my code inside the loop.

> LOOP
>                                 BEGIN
>                                         print 'Begin Loop';
>                                         FETCH NEXT vcurTransactions INTO vTdate,vNet,vCustomer_id;
>                                         BEGIN
>                                                 DECLARE
> vcurDatesum2 CURSOR FOR select VAL1 from statistics where val1 =
> datetrunc(vTdate,'day') AND addressid = vCustomer_id;
>                                                 DECLARE vVal1 VARCHAR(255);
>                                                 
>                                                 OPEN vcurDatesum2;
>                                                 FETCH FIRST vcurDatesum2 INTO vVal1;
>                                                 close vcurDatesum2;
>                                                 Print 'Fetched first Datesum';
>                                                 update statistics
> set val2 = val2 + vNet where val1 = datetrunc(vTdate,'day') AND addressid = vCustomer_id;
>                                         EXCEPTION WHEN ERR_CURSOR_WRONG_POSITION THEN
>                                                 PRINT 'Exception in Loop with Datesum2';
>                                                 INSERT INTO
> statistics (addressid,val1,val2) VALUES
> (vCustomer_id,datetrunc(vTdate,'day'),vNet);
>                                                 
>                                         End
>                                         
>                 
>                                 EXCEPTION WHEN ERR_CURSOR_WRONG_POSITION THEN
>                                         LEAVE
>                                 END

>                         END LOOP

> What can I do?

Seems to be a bug. Report it to Mantis please

-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com