Hi,
I am trying to fetch data from db2 (express version) database by
calling stored procedure. I have tried to use both cursor and for loop
but still i am getting error.
--======Start procedure====== =======
Create PROCEDURE get_timedout_sc ripts
(
time_now INTEGER,
timeout INTEGER
)
language sql
dynamic result sets 1
Begin
DECLARE uut_disconnecte d_script_id BIGINT;
DECLARE uut_timed_out_i d 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_i d as test_script_id
FROM
test_script s,
pcd_system_scri pts sk
WHERE
s.test_script_i d != 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_time s st,
pcd_system_scri pts 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_i d AND
(time_now - st.last_access_ time) > timeout ;
/*call procedures to get values in the variable */
call get_test_result _id('UUT_DISCON NECTED_SCRIPT',
uut_disconnecte d_script_id);
call get_test_result _id('UUT_TIMED_ OUT', uut_timed_out_i d);
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_i d as test_script_id
FROM
test_script s,
pcd_system_scri pts sk
WHERE
s.test_script_i d != 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_time s st,
pcd_system_scri pts 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_i d 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, or
VALUES INTO statement is more than one row. SQLSTATE=21000\ r [Thu May
25 12:05:37 2006] [error] [client 127.0.0.1] (SQLExecute[-811] at
ext\\pdo_odbc\\ odbc_stmt.c:133 )'
I have few questions regarding the same. i am new in db2 and porting my
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 and
cursor. In my case both of them are giving errors.
Any help to fix above problem will be appreciated.
Thanks!
Regards,
--
deepdata
------------------------------------------------------------------------
deepdata's Profile:
http://www.dbtalk.net/m335
View this thread:
http://www.dbtalk.net/t309363