Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Extensions » View Thread |
Messages 1 to 7 of 7 total |
TableExists function |
Tue, Jun 24 2008 4:24 PM | Permanent 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 PM | Permanent 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 PM | Permanent 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 AM | Permanent 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 PM | Permanent 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 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 AM | Permanent Link |
David Cornelius Cornelius Concepts | Thanks for posting the reply. I don't check the newsgroups very regularly.
Good tips, both Uli and Tim. -- David Cornelius Cornelius Concepts |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |