fetching data from db2 using stored procedure

[deepdata]

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

[Kiran Nair]

Hi,
I dont think this particular error came from stored procedure.May be
remove all the other SQL and just call the stored procedure.
If the stored procedure is supposed to return a result set, then its
wrong. As the stored proced cursor declaration should have WITH RETURN
FOR clause .And you jsut need to open the cursor, not need for the
fetch statement.
1) Is it good to have procedure for repeated select statements?
Its good to have stored procedure when you have the results of one sql
statement being used as input to another and if something like that is
done in client it increases network traffic. You can go for stored
procedures or perpared sql statement if you executing same sql many
times.

2) Difference between fetching multiple rows of data using for and
cursor.
FOR internally opens a cursor is what I belieave.

Did you try running the same procedure from the command line ?


deepdata wrote:

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