[RBASE-L] - Re: How do I tell if a temporary table exists?

[Albert Berry]

I have put the following stored procedure into databases for the last 
few years.

-- DropTable.pro
SET VAR vTabName TEXT
SELECT COUNT(*) INTO vCount FROM sys_tables WHERE sys_table_name = .vTabName
IF vCount > 0 THEN
   DROP TABLE &vTabName
ENDIF
RETURN
In code all I need to do is
CALL DropTable ('tablename')
which reduces the clutter quite a bit, especially when using temporary 
tables.

So far it even works for views, but I do not expect that to last 
forever, and plan to add DropView.pro sooner or later.

Albert

Mike Byerley wrote:
If the table already exists, you can create it again as you know.
With the err mess and mess turned off, the error will happen and 
you'll just go on to the next line of code.

The overwhelming argument against this practice would be that it works 
today but maybe not next year, next month, next week, or after the 
next update.

If it is shown that the attempt is creating any instability or it just 
doesn't fit in to the migration path of the software, it could change 
in behavior.

Additionally, when you create a Temp table, I always liken it to 
cleaning the blackboard before writing a new bunch of info.

When I step back to take a look at it, I don't want to be (any more) 
confused or distracted by unwanted info...

So prudence dictates that "good technique" or "best practice" should 
be applied in every instance.

I endeavor to make every attempt at avoiding "do overs".  They are 
costly and doing it right the first time is usually the shortest time 
you can spend at a task.




----- Original Message ----- From: <mj...@bellsouth.net>
To: "RBASE-L Mailing List" <rbase-l@rbase.com>
Sent: Sunday, July 11, 2010 3:35 PM
Subject: [RBASE-L] - Re: How do I tell if a temporary table exists?


Thanks Mike! Question.... If method 1 is considered a "good 
technique", is there anything wrong with just turning the error 
messages off and creating the table even when it already exists? Is 
there any reason to think that would cause any problems like a memory 
leak or database corruption if that were done many times? Could it 
slow down the application?
Mike


(\__/)
(='.'=)
(")_(")


On Jul 11, 2010, at 3:03 PM, "Mike Byerley" <mbyer...@byerley.net> 
wrote:

You can do it the following way:

{begin Code}
SET ERROR VAR verr
SET MESSAGE OFF
SET ERROR MESSAGES OFF
SET VAR vmsg TEXT = NULL

CREATE TEMP TABLE ttemp (dummy TEXT (8))

{first run as is then rem out the next line and unREM the one after 
to see diff}
SET VAR vtmptable = 'tTemp'
--SET VAR vtmptable = 'SomeTableName'

LIST &vtmptable
IF verr = 2038 THEN
SET VAR vmsg = ('Table' & (LUC(.vtmptable)) & 'doesn''t exist!')
PAUSE 2 USING .vmsg
ELSE
SET VAR vmsg = ('Table' & (LUC(.vtmptable)) & 'found!')
PAUSE 2 USING .vmsg
ENDIF

SET MESSAGE ON
SET ERROR MESSAGES ON

RETURN

{End Code}

or you can do


SET VAR vcount INTEGER = 0
SET VAR vtmptable = 'tTemp'
SELECT COUNT (*) INTO vcount IND vin0 FROM sys_tables WHERE 
sys_table_name = .vtmptable
IF vcount = 0 THEN
SET VAR vmsg = ('Table' & (LUC(.vtmptable)) & 'doesn''t exist!')
PAUSE 2 USING .vmsg
ELSE
SET VAR vmsg = ('Table' & (LUC(.vtmptable)) & 'found!')
PAUSE 2 USING .vmsg
ENDIF



----- Original Message ----- From: "Michael J. Sinclair" 
<mj...@bellsouth.net>
To: "RBASE-L Mailing List" <rbase-l@rbase.com>
Sent: Sunday, July 11, 2010 2:16 PM
Subject: [RBASE-L] - How do I tell if a temporary table exists?


Hi All,
How can I tell if a temporary table exists? I know there is a 
function to find
out if a file exists, is there something similar for a temporary table?
Mike