if then drop table

[JoaoMendes]

Hi

Can I in Informix do something like this

if ExistsTable( 'table1' ) then
drop table table1;
end if;

Regards

 

[vpshriyan]

Hi,

To make sure the table exists try the following SQL:
select tabname from systables where tabname='table1'

If exists and you have the permission; you can drop the table.

Regards,
Shriyan

 

[JoaoMendes]

Thanks for your answer Shriyan, but i think i did not give you all the elements.

Suppose that i want to do if then drop in an automated way(like a SP-but i believe SP can't drop tables :-( ). Is it possible? My problem is that i might have several tables that i want to drop and i can't be sure if they exist on the database(they might be dropped by someone else). So, if i give a statement -drop table xxxx, and the table does not exist i get an error, which i dont want.
Want i to do is, in an automated way, is this:
if a certain table exists i want to drop it.

Regards

 

[vpshriyan]

Hi Joao,

Here is a sample procedure that tries to accomplish your requirement. You can execute it by following syntax:

SQL/TRIGGER: execute procedure droptab('tablename')
SPL: call droptab('tablename') returning xstat (0 success, 1 fail)

Note: If you are creating this procedure with DBA keyword, then you can remove all the checkings done for privileges involving usertype variable.

Regards,
Shriyan

create dba procedure droptab(xtabname varchar(18)) returning smallint;

define xowner varchar(18);
define xdb varchar(18);
define xusertype char;

select owner into xowner from systables where tabname=xtabname;
if xowner is null or xowner[1,1]=" " then
raise exception -746, 0, xtabname||" table does not exists.";
return 1;
end if;

select usertype into xusertype from sysusers where username=xowner;
-- users having connect privilege only
if xusertype="C" then
raise exception -746, 0, xtabname||" table can not be deleted. "
||"No permission.";
return 1;
end if;

-- user is a DBA or with Resource privilege
if xusertype="D" or (xusertype="R" and xowner==user) then
--following select is suggested by Jonathan Leffler
--to asertain the current database
select odb_dbname into xdb from sysmaster:sysopendb
where odb_sessionid=dbinfo('sessionid') and odb_iscurrent='Y';
system 'echo "drop table '||xtabname||'"'||
'|$INFORMIXDIR/bin/dbaccess '||xdb ;

--make sure the SPL system command executed with success or not.
select owner into xowner from systables where tabname=xtabname;
if xowner is null or xowner[1,1]=" " then
return 0;
else
raise exception -746, 0, xtabname||": Unable to drop.";
return 1;
end if;
end if;

end procedure;