Hi,

I am trying to fetch data from db2 (express version) database b
calling stored procedure. I have tried to use both cursor and for loo
but still i am getting error.

--======Start procedure=============

Create PROCEDURE get_timedout_scripts
(
    time_now INTEGER,
    timeout INTEGER
)
language sql
dynamic result sets 1
Begin
    DECLARE uut_disconnected_script_id BIGINT;
    DECLARE uut_timed_out_id BIGINT;
    DECLARE    system_id BIGINT;
    DECLARE test_script_id BIGINT;
    DECLARE status VARCHAR(24);
   

    Declare c1 cursor for
        SELECT
            sk.system_id,
            s.test_script_id as test_script_id
        FROM
            test_script s,
            pcd_system_scripts sk
        WHERE
            s.test_script_id != sk.test_script_id AND
            NOT sk.test_script_id IS NULL AND
            s.system_id = sk.system_id AND
            s.is_running = 'Y';

    Declare c2 cursor for
        SELECT
            ts.system_id,
            sc.test_script_id
        FROM
            pcd_system_times st,
            pcd_system_scripts sc,
            test_script ts
        WHERE
            NOT sc.test_script_id IS NULL AND
            ts.test_script_id = sc.test_script_id AND
            sc.system_id = st.system_id AND
            NOT ts.result_id = uut_timed_out_id AND
            (time_now - st.last_access_time) > timeout ;
   

/*call procedures to get values in the variable */
call get_test_result_id('UUT_DISCONNECTED_SCRIPT'
uut_disconnected_script_id);
call get_test_result_id('UUT_TIMED_OUT', uut_timed_out_id);
   

    Open c1;
       
        fetch c1 into system_id, test_script_id;
        set status = 'disconnected';   
    close c1;

    open c2;
       
        fetch c2 into system_id, test_script_id;
        set status = 'timedout';
       
    close c2;

    /*
    FOR t1 as
        SELECT
            sk.system_id,
            s.test_script_id as test_script_id
        FROM
            test_script s,
            pcd_system_scripts sk
        WHERE
            s.test_script_id != sk.test_script_id AND
            NOT sk.test_script_id IS NULL AND
            s.system_id = sk.system_id AND
            s.is_running = 'Y'
        DO
        BEGIN
            set system_id = system_id;
            set test_script_id = test_script_id;
            set status = 'disconnected';   
        END;
    END FOR;
    */
   
   
    /*
    FOR t2 as
        SELECT
            ts.system_id,
            sc.test_script_id
        FROM
            pcd_system_times st,
            pcd_system_scripts sc,
            test_script ts
        WHERE
            NOT sc.test_script_id IS NULL AND
            ts.test_script_id = sc.test_script_id AND
            sc.system_id = st.system_id AND
            NOT ts.result_id = uut_timed_out_id AND
            (time_now - st.last_access_time) > timeout
        DO
        BEGIN   
            set system_id = system_id;
            set test_script_id = test_script_id;
            set status = 'timedout';   
        END;
    END FOR;
    */
END@       

--=====End of procedure

I am getting following error
-------------------------------

SQLSTATE[21000]: Cardinality violation: -811 [IBM][CLI Driver][DB2/NT
SQL0811N  The result of a scalar fullselect, SELECT INTO statement, o
VALUES INTO statement is more than one row.  SQLSTATE=21000\r [Thu Ma
25 12:05:37 2006] [error] [client 127.0.0.1]  (SQLExecute[-811] a
ext\\pdo_odbc\\odbc_stmt.c:133)'  

I have few questions regarding the same. i am new in db2 and porting m
existing database (firebird) to db2.

1) Is it good to have procedure for repeated select statements?

2) Difference between fetching multiple rows of data using for an
cursor. In my case both of them are giving errors.

Any help to fix above problem will be appreciated.

Thanks!
Regards

--
deepdat