Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 7 of 7 total
Thread TableExists function
Tue, Jun 24 2008 4:24 PMPermanent Link

"David Cornelius"
Use this in a script to see if a table exists or not:


CREATE FUNCTION "TableExists" (IN "TableName" VARCHAR(40) COLLATE ANSI)
RETURNS BOOLEAN
BEGIN
 DECLARE TblCursor CURSOR FOR TblStmt;
 DECLARE TblName VARCHAR;

 PREPARE TblStmt FROM
   ''SELECT * FROM Information.Tables
    WHERE Name = '''''' + TableName + '''''''';

 OPEN TblCursor;
 FETCH FIRST FROM TblCursor (Name) INTO TblName;
 CLOSE TblCursor;
 UNPREPARE TblStmt;

 RETURN COALESCE(TblName, '''') = TableName;
END


--
David Cornelius
CorneliusConcepts.com
Tue, Jun 24 2008 4:29 PMPermanent Link

"David Cornelius"
Example script:

SCRIPT
BEGIN
 IF NOT TableExists('abc') then
   EXECUTE IMMEDIATE 'CREATE TABLE "abc"
   (
      "ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0,
INCREMENT BY 1),
      "test" VARCHAR(30) COLLATE "ANSI",
      CONSTRAINT "PrimaryKey" PRIMARY KEY ("ID")
    );';
 END IF;

 IF TableExists('abc') then
   EXECUTE IMMEDIATE 'DROP TABLE "abc"';
 END IF;
END

--
David Cornelius
CorneliusConcepts.com
Thu, Sep 5 2013 2:12 PMPermanent Link

Pierre du Plessis

Comproware

Hi David,

I can't get this to execute?  I'm probably just doing something wrong.  You mentioned that you may have a better way to do this without cursors, could you post the new function here please?

Many thanks,
Pierre
Fri, Sep 6 2013 5:04 AMPermanent Link

Uli Becker

Pierre,

> I can't get this to execute?  I'm probably just doing something wrong.  You mentioned that you may have a better way to do this without cursors, could you post the new function here please?

How about this:

SCRIPT
BEGIN
EXECUTE IMMEDIATE 'CREATE FUNCTION "TableExists" (IN "FTablename"
VARCHAR(100) COLLATE ANSI_CI)
RETURNS BOOLEAN
BEGIN

   DECLARE FCount INTEGER;
   DECLARE FResult BOOLEAN;

   Execute Immediate ''select count(*) into ? from information.tables
where name = ?'' using FCount, FTablename;
   SET FResult = FCount > 0;
   RETURN FResult;

END

VERSION 1.00';
END
Fri, Sep 6 2013 4:38 PMPermanent Link

Pierre du Plessis

Comproware

Hu Uli,

Works Beautifully - Many thanks!

Kind regards
Pierre

Uli Becker wrote:

Pierre,

> I can't get this to execute?  I'm probably just doing something wrong.  You mentioned that you may have a better way to do this without cursors, could you post the new function here please?

How about this:
...
Wed, Sep 11 2013 1:23 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Uli,

Thanks for the example.

Just a quick optimization note for readers - you can remove the (100) from
the VARCHAR parameter declaration and it will still work fine.  This will
allow the function to accept any length string without having to worry about
whether the parameter is sized properly for the input values.

Tim Young
Elevate Software
www.elevatesoft.com
Sat, Sep 14 2013 1:39 AMPermanent Link

David Cornelius

Cornelius Concepts

Avatar

Thanks for posting the reply.  I don't check the newsgroups very regularly.

Good tips, both Uli and Tim.  Smile

--
David Cornelius
Cornelius Concepts
Image