Re: Testing for table existance

[Arnon Oppenheimer <arnono@SEMECH.CO.IL>]

Testing for table existanceChad,

1. I recommend to use :

begin-sql on-error=xxxx
drop temp_omr_marks;
end-select

So, if it exist it pass and if not it will jump to the error procedure,
there you can check $sql-error.

2. If you'll do it in the begin-setup section you can use on-error=SKIP (or
WARN).
    Please note that this good if you do not use .sqt  .

3. To your code : If the table does not exist, it will fail at compile time.
    You need to force dynamic SQL :
let $temp_omr_marks_exist = 'Y'
MOVE 'temp_omr_marks' TO $ttbl
begin-select on-error=var_setting_omr
rownum &1
from
[$ttbl]  ! Dynamic SQL
end-select

Regards,
Arnon Oppenheimer
 -------------------------------------------------------------------
 SEMECH SOFTWARE MARKETING LTD.
 TEL : (972) - 3 - 5333144
 FAX : (972) - 3 - 5333132
 Email: arnono@semech.co.il
 -------------------------------------------------------------------
----- Original Message -----
From: Slattery, Chad
To: SQR-USERS@list.iex.net
Sent: Monday, September 04, 2000 11:50 AM
Subject: Testing for table existance




Hi all,
Im testing for the existance of a table at the start of my sqr.  If the
table exists Im going to drop it.  The way Im doing this, unless anyone has
a better way, is to select rownum from the table.  I set a variable to 'Y',
if the table doesnt exist then use sqr's on-error sql checking to call a
procedure to set the variable to 'N'.  According to the manual, the on-error
should should call the proc 'var_setting_omr' if I get a database error.
The table doesnt exist but my sqr is failing with the table missing as the
reason.  Any ideas? My code is below
TIA,
Chad.

let $temp_omr_marks_exist = 'Y'
begin-select on-error=var_setting_omr
rownum &1
from
temp_omr_marks
end-select

begin-procedure var_setting_omr
 let $temp_omr_marks_exist = 'N'
end-procedure