Re-2: Declaring Cursor inside a loop

[Ivan Smahin]

Hello Fabian,

Wednesday, August 20, 2008, 7:40:20 PM, you wrote:

> Hi Ivan,

> I reported it.
> So for this time I simply splitted it in a procedure and a
> function. But there I have a new problem:

> DECLARE vCustomer_id VARCHAR(255);
>         DECLARE vTdate DATE;
>         DECLARE vNet double;
>                 
>         DECLARE vcurTransactions CURSOR FOR select
> getdate(TRANSACTIONS.tdate),TRANSACTIONS.NET,TRANSACTIONS.customer_id from transactions;
>                               
>         OPEN vcurTransactions;

>         BEGIN
>             FETCH FIRST vcurTransactions INTO vTdate,vNet,vCustomer_id;
>                         CALL
> SaveStatisticDoubleOnDateForAddress(vCustomer_id,vTdate,'transaction',vNet);



> -- this is my declaration for function
> SaveStatisticsDoubleOnDateForAddress
> CREATE OR REPLACE PROCEDURE [SaveStatisticDoubleOnDateForAddress](
> IN VADDRESSID VARCHAR, IN VDATE DATE, IN VNAME VARCHAR, IN VVAL DOUBLE )


> But the log says:  18:40:37 Kernel error: 0x62501. Date value
> "2006-06-29" is invalid. Illegal symbol at "3" position.

> -------- Original Message --------
> Subject: Re: Declaring Cursor inside a loop (20-Aug-2008 15:04)
> From:    Ivan Smahin <ivan_smahin at paradigmasoft.com>
> To:      valentina at lists.macserve.net

>> 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
>> 

Ok, I see the possible scenario:
Look:

OPEN vcurDatesum2; -- It's Ok

FETCH FIRST vcurDatesum2 INTO vVal1; -- Ok, but assume there is no
records?
-- So we get an exception ERR_CURSOR_WRONG_POSITION right here! and next
line is never executed!

close vcurDatesum2;  -- Probably never get here!!! So next loop will be
trying to open cursor again

... 





You should do something like this:

CREATE TABLE t1 ( f1 long );
CREATE TABLE t2 ( f1 long );
INSERT INTO t1 VALUES ( 1 );
INSERT INTO t1 VALUES ( 2 );
INSERT INTO t1 VALUES ( 3 );


CREATE PROCEDURE sp1() 
BEGIN 
        DECLARE res1 LONG; 
        DECLARE res2 LONG; 
        DECLARE cur1 CURSOR FOR select f1 from t1; 
        OPEN cur1; 
        LOOP 
                BEGIN 
                        FETCH NEXT cur1 INTO res1; 
                        BEGIN 
                                DECLARE cur2 CURSOR FOR select f1 FROM t1 WHERE false; 
                                OPEN cur2; 
                                        BEGIN 
                                                FETCH FIRST cur2 INTO res2; 
                                        EXCEPTION WHEN ERR_CURSOR_WRONG_POSITION THEN 
                                                INSERT INTO t2 VALUES( 1 ); 
                                        End     
                                close cur2; 
                        End     
                EXCEPTION WHEN ERR_CURSOR_WRONG_POSITION THEN 
                        LEAVE 
                END 
        END LOOP 
        SELECT * FROM t2; 
END

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