• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1233
  • Last Modified:

fetching data from db2 database using stored procedure

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,
0
DipehKhakhkhar
Asked:
DipehKhakhkhar
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi DipehKhakhkhar,

Nothing in the code that you've posted looks like it should throw that error.  Are you sure that get_test_result_id() is working correctly?


Kent
0
 
DipehKhakhkharAuthor Commented:
Hi,

Thanks for replying.

Well i found the error in the above procedure.

1) First the cursor should have "with return clause". Well it is not an error though but this is what i guessi want as a result set
2) You got it right, everything works fine but my other procedure which i was calling from this procedure get_test_result_id() had the error and error was something like this.

In that procedure i am passing variable (in varName varType) and my column name in one of the select into statement is same which was causing that error. Why in first place db2 should compile it? Okay my bad to use same name, but it makes more sense to give meaningful name and i thought db2 would be intelligent enough to run it since it had compiled it.

Here is the corrected 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 c1 cursor with return
      for
            (SELECT
                  sk.system_id,
                  s.test_script_id as test_script_id,
                  'disconnected' as status
            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' )
            union

            (SELECT
                  ts.system_id,
                  sc.test_script_id,
                  'timeout' as status
            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 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;
end@      
0
 
GranModCommented:
PAQed with points refunded (50)

GranMod
Community Support Moderator
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now