Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 5 of 5 total |
Is there something like 'IF EXISTS DATABASE X...' ? |
Thu, Mar 5 2009 10:43 PM | Permanent Link |
Kai | Is there something like 'IF EXISTS DATABASE X...' ?
I want to have a line that drops an entire database in a script and rebuild it afterwards. The script fails should the database not exist when run. Kai |
Fri, Mar 6 2009 3:56 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Kai
>Is there something like 'IF EXISTS DATABASE X...' ? No. You have to query the configuration database yourself. This is what I use for tables SCRIPT (IN SQLStatement VARCHAR, IN TableName VARCHAR, IN IdxSet VARCHAR) BEGIN DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt; DECLARE ResultCursor SENSITIVE CURSOR FOR ResultStmt; PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?'; OPEN InfoCursor USING TableName; IF (ROWCOUNT(InfoCursor) > 0) THEN EXECUTE IMMEDIATE 'DROP TABLE "'+TableName+'"'; END IF; CLOSE InfoCursor; PREPARE ResultStmt FROM 'CREATE TABLE "'+TableName+'" AS '+SQLStatement+' WITH DATA'; EXECUTE ResultStmt; IF IdxSet IS NOT NULL THEN PREPARE ResultStmt FROM 'CREATE INDEX Idx ON "'+TableName+'"('+IdxSet+')'; EXECUTE ResultStmt; END IF; END You can alter it fairly simply. Use Configuration.Databases rather than Information.Tables and whatever operations you want Roy Lambert [Team Elevate] |
Fri, Mar 6 2009 12:40 PM | Permanent Link |
Kai | Thanks Roy, that more than answers my question. Where did you gather the information contained in your post below? I cannot seem to find any documentation on concepts such as this anywhere... TIA Kai Roy Lambert wrote: Kai >Is there something like 'IF EXISTS DATABASE X...' ? No. You have to query the configuration database yourself. This is what I use for tables SCRIPT (IN SQLStatement VARCHAR, IN TableName VARCHAR, IN IdxSet VARCHAR) BEGIN DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt; DECLARE ResultCursor SENSITIVE CURSOR FOR ResultStmt; PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?'; OPEN InfoCursor USING TableName; IF (ROWCOUNT(InfoCursor) > 0) THEN EXECUTE IMMEDIATE 'DROP TABLE "'+TableName+'"'; END IF; CLOSE InfoCursor; PREPARE ResultStmt FROM 'CREATE TABLE "'+TableName+'" AS '+SQLStatement+' WITH DATA'; EXECUTE ResultStmt; IF IdxSet IS NOT NULL THEN PREPARE ResultStmt FROM 'CREATE INDEX Idx ON "'+TableName+'"('+IdxSet+')'; EXECUTE ResultStmt; END IF; END You can alter it fairly simply. Use Configuration.Databases rather than Information.Tables and whatever operations you want Roy Lambert [Team Elevate] |
Fri, Mar 6 2009 1:31 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Kai
The basics came from Tim after moaning about the loss of SELECT INTO, some from the OLH, some from scanning the ngs, some from experimentation and some from web searches. Most from Tim though. Roy Lambert |
Fri, Mar 6 2009 5:55 PM | Permanent Link |
Ulrich Becker | Kai,
you can find the information you are looking for in the manual chapter 5.8 "5.8 Querying Configuration Objects": The following example shows how to use a TEDBQuery component containing a SELECT statement to query the Databases Table in the Configuration database in order to see if the "Sales" database exists: // This example uses a query component that // has already been created and opened // called MyQuery with MyQuery do begin DatabaseName:='Configuration'; SQL:='SELECT * FROM Databases '+ 'WHERE Name='+Engine.QuotedSQLStr('Sales'); Open; if (RecordCount=1) then ShowMessage('The Sales database exists') else ShowMessage('The Sales database does not exist'); end; You can also use the TEDBSession Execute method as a quicker method to determine if a configuration object or objects exist. The Execute method returns the number of rows affected or returned by a particular SQL statement, so you can use the return value of an indication of whether any rows exist for the SELECT statement on the Configuration database: // This example uses a session component that // has already been created and opened // called MySession with MySession do begin if (Execute('SELECT * FROM Databases '+ 'WHERE Name='+Engine.QuotedSQLStr('Sales'))=1) then ShowMessage('The Sales database exists') else ShowMessage('The Sales database does not exist'); end; P.S. I found it by entering "exists" as search word. Regards Uli |
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 |