Product Documentation

c-treeACE V10.0 Update Guide

Previous Topic

Next Topic

Conditional ISQL Expressions

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:

  • IF
  • IF_EXISTS
  • IF_NOT_EXISTS
  • BEGIN
  • ELSE
  • END

Statements that use these keywords as identifiers should enclose them in double quotes.

TOCIndex