[RBASE-L] - Re: Should cursors always be dropped beforecreating them?

[Michael J. Sinclair]

Will that work for temporary tables?

Mike

-------------- Original message from "Emmitt Dove" <emmitt.w.d...@comcast.net>: --------------

SELECT COUNT(*) INTO vtexists FROM SYS_TABLES WHERE SYS_TABLE_NAME = .vtablename

IF vtexists = 1 THEN

  --It exists

ENDIF

 

Note that the above works for both tables and views.  SYS_TABLES has a column named SYS_TABLE_TYPE to tell you if it is a table or a view.

 

Emmitt Dove

Manager, Converting Applications Development

Evergreen Packaging, Inc.

emmitt.d...@everpack.com

(203) 214-5683 m

(203) 643-8022 o

(203) 643-8086 f

emmitt.w.d...@comcast.net

 

From: rbase-l@rbase.com [mailto:rbas...@rbase.com] On Behalf Of Michael J. Sinclair
Sent: Sunday, November 08, 2009 7:37 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: Should cursors always be dropped beforecreating them?

 

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" <raz...@rbase.com>: --------------


> 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.
>
>