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. [email protected] (203) 214-5683 m (203) 643-8022 o (203) 643-8086 f [email protected] From: [email protected] [mailto:[email protected]] 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" <[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. > >
