At 05:26 PM 11/8/2009, Michael J. Sinclair wrote:
Is there a way to tell if a cursor has already been declared before
I drop it and the recreate it?
Did you know that you can use the (CHKCUR('cursorname')) function to
find the existence of already declared cursor?
(CHKCUR('cursorname')) function checks to see if a cursor is declared
and is not dropped. The function returns an integer value of 1 if the
name exists and is not dropped, and 0 if it is not declared or dropped.
-- A very simple example to implement this feature
-- Start here
IF (CHKCUR('c1')) <> 1 THEN
DECLARE c1 CURSOR FOR SELECT column1, column2,column3 +
FROM [tablename] +
WHERE [clause]
OPEN c1
FETCH c1 INTO +
vVar1 INDIC ivVar1, +
vVar2 INDIC ivVar2, +
vVar3 INDIC ivVar3
WHILE SQLCODE <> 100 THEN
-- Do what you have to do
FETCH c1 INTO +
vVar1 INDIC ivVar1, +
vVar2 INDIC ivVar2, +
vVar3 INDIC ivVar3
ENDWHILE
DROP CURSOR c1
ELSE
-- Do what you need to do while the cursor is already declared
ENDIF
RETURN
-- End here
Or do most programmers just drop the cursor before creating it
to make sure it can be created?
I am trying to improve my coding skills. I don't know if dropping
a cursor that has not already been created is considered a "bad
practice" beause it genereates an error (even though the error
message can be turned off) or a "good practice" because it helps
to ensure that the cursor will be created properly.
Here is another example of code that you can re-cycle ...
-- Example
SET ERROR MESSAGE 705 OFF
DROP CURSOR c1
SET ERROR MESSAGE 705 ON
DECLARE c1 CURSOR FOR SELECT column1, column2,column3 +
FROM [tablename] +
WHERE [clause]
OPEN c1
FETCH c1 INTO +
vVar1 INDIC ivVar1, +
vVar2 INDIC ivVar2, +
vVar3 INDIC ivVar3
WHILE SQLCODE <> 100 THEN
-- Do what you have to do
FETCH c1 INTO +
vVar1 INDIC ivVar1, +
vVar2 INDIC ivVar2, +
vVar3 INDIC ivVar3
ENDWHILE
DROP CURSOR c1
RETURN
Make sure that all variables used within the WHILE - ENDWHILE
loop are pre-defined with appropriate data types.
Very Best R:egards,
Razzak.
