Now that I am thinking about it, is there a way to check to see if a table or a view exists?
Mike
-------------- Original message from "A. Razzak Memon" <[email protected]>: --------------
> 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.
>
>
