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

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]

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]

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

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