The ISQL utility is an easy interface to work directly with the c-treeACE SQL database engine. The ability to conditionally work with SQL statements based on an existing database state provides a flexible method of updating and administratively maintaining tables and database information. The c-treeACE SQL ISQL utility now supports conditional expressions to make this possible.
Syntax
{IF [NOT] EXISTS} | {IF_EXISTS} | {IF_NOT_EXISTS} (<query>)
{BEGIN
<list of statements>
END} | { <statement> }
[ELSE
{BEGIN
<list of statements>
END} | { <statement> } ]
Conditional expressions can be nested. The following example checks for the existence of a table and creates it if it does not exist. The nested condition updates an existing table if it is required.
Example
IF NOT EXISTS (SELECT * FROM systables where tbl = 'custmast')
BEGIN
CREATE TABLE custmast (name CHAR(10), custid INTEGER IDENTITY (1,1), balance MONEY, modtime TIMESTAMP);
END
ELSE
BEGIN
IF NOT EXISTS (SELECT * FROM syscolumns where tbl='custmast' AND col='modtime')
BEGIN
ALTER TABLE custmast ADD (modtime TIMESTAMP);
END
ELSE
BEGIN
SELECT * FROM custmast;
END
END
COMMIT WORK;
The following keywords have been added to the ISQL lexicon to support conditional expressions:
Statements that use these keywords as identifiers should enclose them in double quotes.