Hi Damir,
I know how valuable is other people's time. I will do diligence on my part when raising the issue.
I am successfully able to update the status column after the procedure starts executing and gives me terminal output. I allow it to run for some time. Then I update the status table from another connection and it is successful, but I does not seem to read the value. It always set the status column as c only.
Either I am Making some mistake in the code or db2 is not behaving properly or my understanding is incorrect.
I just need a clarification that if you close cursor and open again inside a stored procedure will it pickup the changed value or any setting should be used to force the cursor to pickup the new value.
Hope I have given some clarification.
The sad part is the functionality I want to achieve is very simple yet it requires good understanding, but others do not always find time to write a dimple code and verify and suggest things as certain. But I know it is too much to expect as there are no incentives involved.
Any case thanks for your suggestions.
Thanks for the response.
Original Message:
Sent: 12/18/2024 8:23:00 AM
From: Damir Wilder
Subject: RE: inside a stored procedure loop how to break based on table column status
Hi,
First of all, there is no evidence in the provided output that the STATUS column was actually updated (and committed!) to the value 'S'.
But, assuming this was indeed done, here's what you can try next:
Remove the "status_cursor" CURSOR definition, as well as the related OPEN CURSOR, FETCH and CLOSE CURSOR commands from the SP body.
(the other cursor "cursor_loop" is OK to remain in the SP)
Instead, use the following command inside the infinte loop, right before testing the status, to load the status column value into the v_status variable :
SELECT STATUS_COLUMN into v_status FROM STATUS_TABLE WHERE ID=1 FETCH FIRST 1 ROW ONLY;
|
I cannot be entirely sure the above will work OK as it's been a (long) while since I've coded anything in DB2 SQL PL - but anyway try it out and see how it goes!
Regards, Damir
------------------------------
Damir Wilder
Senior Consultant
Triton Consulting
London
------------------------------
Original Message:
Sent: Tue December 17, 2024 05:43 AM
From: Harishkumar Pathangay
Subject: inside a stored procedure loop how to break based on table column status
Hi All,
what is the problem with this procedure.
A procedure is in a infinite loop, every time it checks if status_column in table status_table is 'S', then it will break out of loop and exit procedure.
If status is other than 'S' , it will continue for ever until another application changes status_column value. the issue is it is not coming out of loop based on status value column. Please find procedure code and output also, you can see in output it reads the value 'C' and keeps on looping infinitely, but does not come out even after update of column value to 'S' .
CREATE OR REPLACE PROCEDURE LOOP_UNTIL_STATUS_CHANGE()
LANGUAGE SQL
BEGIN
DECLARE v_id INT;
DECLARE v_value VARCHAR(100);
DECLARE v_status CHAR(1);
DECLARE done SMALLINT DEFAULT 0;
DECLARE fl_done SMALLINT DEFAULT 1;
-- Cursor declaration
DECLARE cursor_loop CURSOR FOR
SELECT ID, VALUE_COLUMN FROM MY_TABLE;
DECLARE status_cursor CURSOR FOR
SELECT STATUS_COLUMN FROM STATUS_TABLE WHERE ID =1;
-- Declare a handler for when the cursor reaches the end of the table
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fl_done = 0;
-- Infinite loop
LOOP_INFI: LOOP
-- Open the cursor
OPEN cursor_loop;
OPEN status_cursor;
FETCH status_cursor INTO v_status;
FETCH_LOOP: LOOP
-- Fetch data from the cursor
FETCH cursor_loop INTO v_id, v_value;
IF fl_done = 0 THEN
CLOSE cursor_loop;
LEAVE FETCH_LOOP;
END IF;
set fl_done = fl_done + 1;
call dbms_output.put_line('Fetch Looping ' || v_id || ' ' || v_value);
END LOOP FETCH_LOOP;
IF v_status = 'S' THEN
-- Exit the loop if status is 'S'
LEAVE LOOP_INFI;
END IF;
-- Close the cursor (optional in each cycle)
set done = done + 1;
set fl_done = 1;
call dbms_output.put_line('Looping ' || done || ' ' || v_status);
CLOSE status_cursor;
END LOOP LOOP_INFI;
call dbms_output.put_line('Out of Infinite Looping ' || done );
END@
the above code works infinitely, but it is not coming out of the infinite loop based on status_column value. the status column value is always C as seen at the start of procedure, it never picks up S from database after my update successful in another connection....
Fetch Looping 1 Asherah
Fetch Looping 2 Baal
Fetch Looping 3 El
Fetch Looping 4 Manat
Fetch Looping 5 Mot
Looping 21 C
Fetch Looping 1 Asherah
Fetch Looping 2 Baal
Fetch Looping 3 El
Fetch Looping 4 Manat
Fetch Looping 5 Mot
Looping 22 C
Fetch Looping 1 Asherah
Fetch Looping 2 Baal
Fetch Looping 3 El
Fetch Looping 4 Manat
Fetch Looping 5 Mot
Looping 23 C
As you can see in output , it never picks up S at all keeps going in infinite loop.
how to solve it?
Thanks
Harishkumar
------------------------------
Harishkumar Pathangay
------------------------------