SQL Statement and Function Reference

This chapter describes the following SQL statements:

ALTER DATABASE

Adds secondary files to the current database. Available in gpre, DSQL, and isql, but not in the trigger or stored procedure language.

Syntax  ALTER {DATABASE | SCHEMA}
                 ADD add_clause;

<add_clause> = FILE 'filespec' [fileinfo] [add_clause]

<fileinfo> = LENGTH [=] int [PAGE[S]] 
  | STARTING [AT [PAGE]] int [fileinfo]

Important In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in isql, the semicolon is a terminating symbol for the statement, so it must be included.

Description ALTER DATABASE adds secondary files to an existing database. Secondary files permit databases to spread across storage devices, but they must remain on the same node as the primary database file. A database can be altered by its creator, the SYSDBA user, and any users with operating system root privileges.

ALTER DATABASE requires exclusive access to the database.

Note InterBase dynamically expands the last file in a database as needed. The maximum size of the last file is system-dependent. You should be aware that specifying a LENGTH for such files has no effect.

You cannot use ALTER DATABASE to split an existing database file. For example, if your existing database is 80,000 pages long and you add a secondary file STARTING AT 50000, InterBase starts the new database file at page 80,001.

Tip To split an existing database file into smaller files, back it up and restore it. When you restore a database, you are free to specify secondary file sizes at will, without reference to the number and size of the original files.

Example The following isql statement adds two secondary files to an existing database. The command creates a secondary database file called employee2.gdb that is 10,000 pages long and another called employee3.gdb. InterBase starts using employee2.gdb only when the primary file reaches 10,000 pages.

ALTER DATABASE 
  ADD FILE 'employee2.gdb' 
  STARTING AT PAGE 10001 LENGTH 10000
  ADD FILE 'employee3.gdb';

See also CREATE DATABASE, DROP DATABASE

See the Data Definition Guide for more information about multi-file databases and the Operations Guide for more information about exclusive database access.

u Back to top

ALTER DOMAIN

Changes a domain definition. Available in gpre, DSQL, and isql, but not in the stored procedure or trigger language.

ALTER DOMAIN { name | old_name TO new_name }
  SET DEFAULT {literal | NULL | USER} 
  | DROP DEFAULT 
  | ADD [CONSTRAINT] CHECK (dom_search_condition) 
  | DROP CONSTRAINT
  | new_col_name
  | TYPE datatype;

<dom_search_condition> = 
  VALUE operator val 
  | VALUE [NOT] BETWEEN val AND val 
  | VALUE [NOT] LIKE val [ESCAPE val] 
  | VALUE [NOT] IN (val [, val …]) 
  | VALUE IS [NOT] NULL 
  | VALUE [NOT] CONTAINING val 
  | VALUE [NOT] STARTING [WITH] val
  | (dom_search_condition)
  | NOT dom_search_condition
  | dom_search_condition OR dom_search_condition
  | dom_search_condition AND dom_search_condition

<operator> = {= | < | > | <= | >= | !< | !> | <> | !=}

Important In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in isql, the semicolon is a terminating symbol for the statement, so it must be included.

Description ALTER DOMAIN changes any aspect of an existing domain except its NOT NULL setting. Changes made to a domain definition affect all column definitions based on the domain that have not been overridden at the table level.

Note To change the NOT NULL setting of a domain, drop the domain and recreate it with the desired combination of features.

The TYPE clause of ALTER DOMAIN does not allow you to make datatype conversions that could lead to data loss.

A domain can be altered by its creator, the SYSDBA user, and any users with operating system root privileges.

Example The following isql statements create a domain that must have a value > 1,000, then alter it by setting a default of 9,999:

CREATE DOMAIN CUSTNO
  AS INTEGER
  CHECK (VALUE > 1000);
ALTER DOMAIN CUSTNO SET DEFAULT 9999;

See also CREATE DOMAIN, CREATE TABLE, DROP DOMAIN

For a complete discussion of creating domains, and using them to create column definitions, see the Data Definition Guide.

u Back to top

ALTER EXCEPTION

Changes the message associated with an existing exception. Available in DSQL and isql but not the embedded language or stored procedure and trigger language.

Syntax  ALTER EXCEPTION name 'message'

Description ALTER EXCEPTION changes the text of an exception error message.

An exception can be altered by its creator, the SYSDBA user, and any users with operating system root privileges.

Example This isql statement alters the message of an exception:

ALTER EXCEPTION CUSTOMER_CHECK 'Hold shipment for customer
  remittance.';

See also ALTER PROCEDURE, ALTER TRIGGER, CREATE EXCEPTION, CREATE PROCEDURE, CREATE TRIGGER, DROP EXCEPTION

For more information on creating, raising, and handling exceptions, see the Data Definition Guide.

u Back to top

ALTER INDEX

Activates or deactivates an index. Available in embedded SQL, DSQL, and isql, but not in the stored procedure or trigger language.

Syntax  ALTER INDEX name {ACTIVE | INACTIVE};

Important In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in isql, the semicolon is a terminating symbol for the statement, so it must be included.

Description ALTER INDEX makes an inactive index available for use, or disables the use of an active index. Deactivating an index is exactly like dropping it, except that the index definition remains in the database. Activating an index creates a new index structure.

Before inserting, updating, or deleting a large number of rows, deactivate a table’s indexes to avoid altering the index incrementally. When finished, reactivate the index. A reasonable metric is that if you intend to add or delete more than 15% of the rows in a table, or update an indexed column in more than 10% of the rows, you should consider deactivating and reactivating the index.

If an index is in use, ALTER INDEX does not take effect until the index is no longer in use.

ALTER INDEX fails and returns an error if the index is defined for a UNIQUE, PRIMARY KEY, or FOREIGN KEY constraint. To alter such an index, use DROP INDEX to delete the index, then recreate it with CREATE INDEX.

An index can be altered by its creator, the SYSDBA user, and any users with operating system root privileges.

Note To add or drop index columns or keys, use DROP INDEX to delete the index, then recreate it with CREATE INDEX.

Example The following isql statements deactivate and reactivate an index to rebuild it:

ALTER INDEX BUDGETX INACTIVE;
ALTER INDEX BUDGETX ACTIVE;

See also ALTER TABLE, CREATE INDEX, DROP INDEX, SET STATISTICS

u Back to top

ALTER PROCEDURE

Changes the definition of an existing stored procedure. Available in DSQL and isql but not in the embedded language or in the stored procedures or triggers.

Syntax  ALTER PROCEDURE name
                 [(param datatype [, param datatype …])] 
                 [RETURNS (param datatype [, param datatype …])]
                 AS procedure_body ;

Description ALTER PROCEDURE changes an existing stored procedure without affecting its dependencies. It can modify a procedure’s input parameters, output parameters, and body.

The complete procedure header and body must be included in the ALTER PROCEDURE statement. The syntax is exactly the same as CREATE PROCEDURE, except CREATE is replaced by ALTER.

Important Be careful about changing the type, number, and order of input and output parameters to a procedure, because existing application code may assume the procedure has its original format. Check for dependencies between procedures before changing parameters. Should you change parameters and find that another procedure can neither be altered to accept the new parameters or deleted, change the original procedure back to its original parameters, fix the calling procedure, then change the called procedure.

A procedure can be altered by its creator, the SYSDBA user, and any users with operating system root privileges. Procedures in use are not altered until they are no longer in use. ALTER PROCEDURE changes take effect when they are committed. Changes are then reflected in all applications that use the procedure without recompiling or relinking.

Example The following isql statements alter the GET_EMP_PROJ procedure, changing the return parameter to have a datatype of VARCHAR(20):

ALTER PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT) 
RETURNS (PROJ_ID VARCHAR(20)) AS
  BEGIN
     FOR SELECT PROJ_ID
     FROM EMPLOYEE_PROJECT
     WHERE EMP_NO = :emp_no
     INTO :proj_id
     DO
        SUSPEND;
  END;

See also CREATE PROCEDURE, DROP PROCEDURE, EXECUTE PROCEDURE

For more information on creating and using procedures, see the Data Definition Guide.

For a complete description of the statements in procedure and trigger language, see Chapter 3, “Procedures and Triggers.”

u Back to top

ALTER TABLE

Changes a table by adding, dropping, or modifying columns or integrity constraints. Available in gpre, DSQL, and isql.

Syntax  ALTER TABLE table operation [, operation …];

<operation> = ADD col_def 
  | ADD tconstraint
  | ALTER [COLUMN] column_name alt_col_clause
  | DROP col 
  | DROP CONSTRAINT constraint

<alt_col_clause> = TO new_col_name 
  | TYPE new_col_datatype 
  | POSITION new_col_position

<col_def> = col {datatype | COMPUTED [BY] (expr) | domain}
  [DEFAULT {literal | NULL | USER}]
  [NOT NULL] 
  [col_constraint]
  [COLLATE collation]

<datatype> = 
  {SMALLINT | INTEGER | FLOAT | DOUBLE PRECISION}[array_dim]
  | (DATE | TIME | TIMESTAMP} [array_dim] 
  | {DECIMAL | NUMERIC} [(precision [, scale])] [array_dim] 
  | {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} [(int)]
     [array_dim] [CHARACTER SET charname] 
  | {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR} 
     [VARYING] [(int)] [array_dim] 
  | BLOB [SUB_TYPE {int | subtype_name}] [SEGMENT SIZE int]
     [CHARACTER SET charname]
  | BLOB [(seglen [, subtype])]array_dim = [[x:]y [, [x:]y …]] 
  | BOOLEAN

<expr> = A valid SQL expression that results in a single value.

<col_constraint> = [CONSTRAINT constraint] 
  { UNIQUE 
  | PRIMARY KEY 
  | REFERENCES other_table [(other_col [, other_col …])]
     [ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
     [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
  | CHECK (search_condition)}

<tconstraint> = [CONSTRAINT constraint] 
  {{PRIMARY KEY | UNIQUE} (col [, col …]) 
  | FOREIGN KEY (col [, col …]) 
     REFERENCES other_table [(other_col [, other_col …])]
        [ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
        [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
  | CHECK (search_condition)}

<search_condition> = val operator {val | (select_one)} 
  | val [NOT] BETWEEN val AND val 
  | val [NOT] LIKE val [ESCAPE val] 
  | val [NOT] IN (val [, val …] | select_list) 
  | val IS [NOT] NULL 
  | val {>= | <=} 
  | val [NOT] {= | < | >} 
  | {ALL | SOME | ANY} (select_list) 
  | EXISTS (select_expr) 
  | SINGULAR (select_expr) 
  | val [NOT] CONTAINING val 
  | val [NOT] STARTING [WITH] val 
  | (search_condition) 
  | NOT search_condition 
  | search_condition OR search_condition 
  | search_condition AND search_condition

<val> = { col [array_dim] | :variable
  | constant | expr | function
  | udf ([val [, val …]])
  | NULL | USER | RDB$DB_KEY | ? }
  [COLLATE collation]

<constant> = num | 'string' | charsetname 'string'

<function> = COUNT (* | [ALL] val | DISTINCT val)
  | SUM ([ALL] val | DISTINCT val)
  | AVG ([ALL] val | DISTINCT val)
  | MAX ([ALL] val | DISTINCT val)
  | MIN ([ALL] val | DISTINCT val)
  | CAST (val AS datatype)
  | UPPER (val)
  | GEN_ID (generator, val)

<operator> = {= | < | > | <= | >= | !< | !> | <> | !=}

<select_one> = SELECT on a single column; returns exactly one value.

<select_list> = SELECT on a single column; returns zero or more values.

<select_expr> = SELECT on a list of values; returns zero or more values.

Important In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in isql, the semicolon is a terminating symbol for the statement, so it must be included.

Notes on ALTER TABLE syntax

• The column constraints for referential integrity were new in InterBase 5. See constraint_def in Table 2.1 and the Description for ALTER TABLE on page 2-13.

• You cannot specify a COLLATE clause for Blob columns.

• When declaring arrays, you must include the outermost brackets, shown below in bold. For example, the following statement creates a 5 by 5 two-dimensional array of strings, each of which is 6 characters long:

my_array = varchar(6)[5,5]

Use the colon (:) to specify an array with a starting point other than 1. The following example creates an array of integers that begins at 20 and ends at 30:

my_array = integer[20:30]

• For the full syntax of search_condition, see CREATE TABLE.

Description ALTER TABLE modifies the structure of an existing table. A single ALTER TABLE statement can perform multiple adds and drops.

• A table can be altered by its creator, the SYSDBA user, and any users with operating system superuser privileges.

• ALTER TABLE fails if the new data in a table violates a PRIMARY KEY or UNIQUE constraint definition added to the table. Dropping or altering a column fails if any of the following are true:

• The column is part of a UNIQUE, PRIMARY, or FOREIGN KEY constraint

• The column is used in a CHECK constraint

• The column is used in the value expression of a computed column

• The column is referenced by another database object such as a view

Important When a column is dropped, all data stored in it is lost.

Constraints

• Referential integrity constraints include optional ON UPDATE and ON DELETE clauses. They define the change to be made to the referencing column when the referenced column is updated or deleted. The values for these cascading referential integrity options are given in Table 2.1, “The ALTER TABLE statement,” on page 11.

• To delete a column referenced by a computed column, you must drop the computed column before dropping the referenced column. To drop a column referenced in a FOREIGN KEY constraint, you must drop the constraint before dropping the referenced column. To drop a PRIMARY KEY or UNIQUE constraint on a column that is referenced by FOREIGN KEY constraints, drop the FOREIGN KEY constraint before dropping the PRIMARY KEY or UNIQUE key it references.

• You can create a FOREIGN KEY reference to a table that is owned by someone else only if that owner has explicitly granted you the REFERENCES privilege on that table using GRANT. Any user who updates your foreign key table must have REFERENCES or SELECT privileges on the referenced primary key table.

• You can add a check constraint to a column that is based on a domain, but be aware that changes to tables that contain CHECK constraints with subqueries may cause constraint violations.

• Naming column constraints is optional. If you do not specify a name, InterBase assigns a system-generated name. Assigning a descriptive name can make a constraint easier to find for changing or dropping, and more descriptive when its name appears in a constraint violation error message.

• When creating new columns is tables with data, do not use the UNIQUE constraint. If you use the NOT NULL constraint on a table with data, you should also specify a default value.

Example The following isql statement adds a column to a table and drops a column:

ALTER TABLE COUNTRY
  ADD CAPITAL VARCHAR(25),
  DROP CURRENCY;

This statement results in the loss of all data in the dropped CURRENCY column.

The next isql statement changes the name of the LARGEST_CITY column to BIGGEST_CITY:

ALTER TABLE COUNTRY ALTER LARGEST_CITY TO BIGGEST_CITY;

See also ALTER DOMAIN, CREATE DOMAIN, CREATE TABLE

For more information about altering tables, see the Embedded SQL Guide.

u Back to top

ALTER TRIGGER

Changes an existing trigger. Available in DSQL and isql.

Syntax  ALTER TRIGGER name
                 [ACTIVE | INACTIVE]
                 [{BEFORE | AFTER} {DELETE | INSERT | UPDATE}]
                 [POSITION number]
                 [AS trigger_body] ; 

Description ALTER TRIGGER changes the definition of an existing trigger. If any of the arguments to ALTER TRIGGER are omitted, then they default to their current values, that is the value specified by CREATE TRIGGER, or the last ALTER TRIGGER.

ALTER TRIGGER can change:

• Header information only, including the trigger activation status, when it performs its actions, the event that fires the trigger, and the order in which the trigger fires compared to other triggers.

• Body information only, the trigger statements that follow the AS clause.

• Header and trigger body information. In this case, the new trigger definition replaces the old trigger definition.

A trigger can be altered by its creator, the SYSDBA user, and any users with operating system root privileges.

Note To alter a trigger defined automatically by a CHECK constraint on a table, use ALTER TABLE to change the constraint definition.

Examples The following statement modifies the trigger, SET_CUST_NO, to be inactive:

ALTER TRIGGER SET_CUST_NO INACTIVE;

The next statement modifies the trigger, SET_CUST_NO, to insert a row into the table, NEW_CUSTOMERS, for each new customer.

ALTER TRIGGER SET_CUST_NO FOR CUSTOMER
BEFORE INSERT AS
  BEGIN
     NEW.CUST_NO = GEN_ID(CUST_NO_GEN, 1);
     INSERT INTO NEW_CUSTOMERS(NEW.CUST_NO, TODAY)
  END ;

See also CREATE TRIGGER, DROP TRIGGER

For a complete description of the statements in procedure and trigger language, see Chapter 3, “Procedures and Triggers.”

For more information about triggers, see the Data Definition Guide.

u Back to top

AVG( )

Calculates the average of numeric values in a specified column or expression. Available in gpre, DSQL, and isql.

Syntax  AVG ([ALL] value | DISTINCT value)

Description AVG() is an aggregate function that returns the average of the values in a specified column or expression. Only numeric datatypes are allowed as input to AVG().

If a field value involved in a calculation is NULL or unknown, it is automatically excluded from the calculation. Automatic exclusion prevents averages from being skewed by meaningless data.

AVG() computes its value over a range of selected rows. If the number of rows returned by a SELECT is zero, AVG() returns a NULL value.

Examples The following embedded SQL statement returns the average of all rows in a table:

EXEC SQL
  SELECT AVG (BUDGET) FROM DEPARTMENT INTO :avg_budget;

The next embedded SQL statement demonstrates the use of SUM(), AVG(), MIN(), and MAX() over a subset of rows in a table:

EXEC SQL
  SELECT SUM (BUDGET), AVG (BUDGET), MIN (BUDGET), MAX (BUDGET)
  FROM DEPARTMENT
  WHERE HEAD_DEPT = :head_dept
  INTO :tot_budget, :avg_budget, :min_budget, :max_budget;

See also COUNT( ), MAX( ), MIN( ), SUM( )

u Back to top

BASED ON

Declares a host-language variable based on a column. Available in gpre.

Syntax  BASED [ON] [dbhandle.]table.col[.SEGMENT] variable; 

Description BASED ON is a preprocessor directive that creates a host-language variable based on a column definition. The host variable inherits the attributes described for the column and any characteristics that make the variable type consistent with the programming language in use. For example, in C, BASED ON adds one byte to CHAR and VARCHAR variables to accommodate the NULL character terminator.

Use BASED ON in a program’s variable declaration section.

Note BASED ON does not require the EXEC SQL keywords.

To declare a host-language variable large enough to hold a Blob segment during FETCH operations, use the SEGMENT option of the BASED ON clause. The variable’s size is derived from the segment length of a Blob column. If the segment length for the Blob column is changed in the database, recompile the program to adjust the size of host variables created with BASED ON.

Examples The following embedded statements declare a host variable based on a column:

EXEC SQL
  BEGIN DECLARE SECTION
     BASED_ON EMPLOYEE.SALARY salary;
EXEC SQL
  END DECLARE SECTION;

See also BEGIN DECLARE SECTION, CREATE TABLE, END DECLARE SECTION

u Back to top

BEGIN DECLARE SECTION

Identifies the start of a host-language variable declaration section. Available in gpre.

Syntax  BEGIN DECLARE SECTION;

Description BEGIN DECLARE SECTION is used in embedded SQL applications to identify the start of host-language variable declarations for variables that will be used in subsequent SQL statements. BEGIN DECLARE SECTION is also a preprocessor directive that instructs gpre to declare SQLCODE automatically for the applications programmer.

Important BEGIN DECLARE SECTION must always appear within a module’s global variable declaration section.

Example The following embedded SQL statements declare a section and a host-language variable:

EXEC SQL
  BEGIN DECLARE SECTION;
     BASED ON EMPLOYEE.SALARY salary;
EXEC SQL
  END DECLARE SECTION;

See also BASED ON, END DECLARE SECTION

u Back to top

CAST( )

Converts a column from one datatype to another. Available in gpre, DSQL, and isql.

Syntax  CAST (value AS datatype)

Description CAST() allows mixing of numerics and characters in a single expression by converting val to a specified datatype.

Normally, only similar datatypes can be compared in search conditions. CAST() can be used in search conditions to translate one datatype into another for comparison purposes.

Datatypes can be converted as shown in the following table:

An error results if a given datatype cannot be converted into the datatype specified in CAST(). For example, you will get a string conversion error if you attempt to cast from a numeric type to a date.

Example In the following WHERE clause, CAST() is used to translate a CHARACTER datatype, INTERVIEW_DATE, to a DATE datatype to compare against a DATE datatype, HIRE_DATE:

. . .
  WHERE HIRE_DATE = CAST (INTERVIEW_DATE AS DATE);

To cast a VARCHAR datatype, you must specify the length of the string, for example:

UPDATE client SET charef = CAST (clientref AS VARCHAR(20));

See also UPPER( )

u Back to top

CLOSE

Closes an open cursor. Available in gpre.

Syntax  CLOSE cursor;

Description CLOSE terminates the specified cursor, releasing the rows in its active set and any associated system resources. A cursor is a one-way pointer into the ordered set of rows retrieved by the select expression in the DECLARE CURSOR statement. A cursor enables sequential access to retrieved rows in turn and update in place.

There are four related cursor statements:

FETCH statements cannot be issued against a closed cursor. Until a cursor is closed and reopened, InterBase does not reevaluate values passed to the search conditions. Another user can commit changes to the database while a cursor is open, making the active set different the next time that cursor is reopened.

Note In addition to CLOSE, COMMIT and ROLLBACK automatically close all cursors in a transaction.

Example The following embedded SQL statement closes a cursor:

EXEC SQL 
  CLOSE BC;

See also CLOSE (BLOB), COMMIT, DECLARE CURSOR, FETCH, OPEN, ROLLBACK

u Back to top

CLOSE (BLOB)

Terminates a specified Blob cursor and releases associated system resources. Available in gpre.

Syntax  CLOSE blob_cursor;

Description CLOSE closes an opened read or insert Blob cursor. Generally a Blob cursor should only be closed only after:

• Fetching all the Blob segments for BLOB READ operations.

• Inserting all the segments for BLOB INSERT operations.

Example The following embedded SQL statement closes a Blob cursor:

EXEC SQL 
  CLOSE BC;

See also DECLARE CURSOR (BLOB), FETCH (BLOB), INSERT CURSOR (BLOB), OPEN (BLOB)

u Back to top

COMMIT

Makes a transaction’s changes to the database permanent, and ends the transaction. Available in gpre, DSQL, and isql.

Syntax  COMMIT [WORK] [TRANSACTION name] [RELEASE] [RETAIN [SNAPSHOT]];

Important In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in isql, the semicolon is a terminating symbol for the statement, so it must be included.

Description COMMIT is used to end a transaction and:

• Write all updates to the database.

• Make the transaction’s changes visible to subsequent SNAPSHOT transactions or READ COMMITTED transactions.

• Close open cursors, unless the RETAIN argument is used.

A transaction ending with COMMIT is considered a successful termination. Always use COMMIT or ROLLBACK to end the default transaction.

Tip After read-only transactions, which make no database changes, use COMMIT rather than ROLLBACK. The effect is the same, but the performance of subsequent transactions is better and the system resources used by them are reduced.

Important The RELEASE argument is only available for compatibility with previous versions of InterBase. To detach from a database use DISCONNECT.

Examples The following isql statement makes permanent the changes to the database made by the default transaction:

COMMIT;

The next embedded SQL statement commits a named transaction:

EXEC SQL
  COMMIT TR1;

The following embedded SQL statement uses COMMIT RETAIN to commit changes while maintaining the current transaction context:

EXEC SQL 
  COMMIT RETAIN;

See also DISCONNECT, ROLLBACK

For more information about handling transactions, see the Embedded SQL Guide.

u Back to top

CONNECT

Attaches to one or more databases. Available in gpre. A subset of CONNECT options is available in isql.

Syntax  isql form:

CONNECT 'filespec' [USER 'username'][PASSWORD 'password']
  [CACHE int] [ROLE 'rolename']

SQL form:

CONNECT [TO] {ALL | DEFAULT} config_opts
  | db_specs config_opts [, db_specs config_opts...];

<db_specs> = dbhandle 
  | {'filespec' | :variable} AS dbhandle 

<config_opts> =  [USER {'username' | :variable}] 
  [PASSWORD {'password' | :variable}]
  [ROLE {'rolename' | :variable}] 
  [CACHE int [BUFFERS]]

Description The CONNECT statement:

• Initializes database data structures.

• Determines if the database is on the originating node (a local database) or on another node (a remote database). An error message occurs if InterBase cannot locate the database.

• Optionally specifies one or more of a user name, password, or role for use when attaching to the database. PC clients must always send a valid user name and password. InterBase recognizes only the first 8 characters of a password.

If an InterBase user has ISC_USER and ISC_PASSWORD environment variables set and the user defined by those variables is not in the InterBase security database (admin.ib by default), the user receives the following error when attempting to view users from the local server manager connection: “undefined user name and password.” This applies only to the local connection; the automatic connection made through Server Manager bypasses user security.

• Attaches to the database and verifies the header page. The database file must contain a valid database, and the on-disk structure (ODS) version number of the database must be the one recognized by the installed version of InterBase on the server, or InterBase returns an error.

• Optionally establishes a database handle declared in a SET DATABASE statement.

• Specifies a cache buffer for the process attaching to a database.

In SQL programs before a database can be opened with CONNECT, it must be declared with the SET DATABASE statement. isql does not use SET DATABASE.

In SQL programs while the same CONNECT statement can open more than one database, use separate statements to keep code easy to read.

When CONNECT attaches to a database, it uses the default character set (NONE), or one specified in a previous SET NAMES statement.

In SQL programs the CACHE option changes the database cache size count (the total number of available buffers) from the default of 75. This option can be used to:

• Sets a new default size for all databases listed in the CONNECT statement that do not already have a specific cache size.

• Specifies a cache for a program that uses a single database.

• Changes the cache for one database without changing the default for all databases used by the program.

The size of the cache persists as long as the attachment is active. If a database is already attached through a multi-client server, an increase in cache size due to a new attachment persists until all the attachments end. A decrease in cache size does not affect databases that are already attached through a server.

A subset of CONNECT features is available in isql: database file name, USER, and PASSWORD. isql can only be connected to one database at a time. Each time CONNECT is used to attach to a database, previous attachments are disconnected.

Examples The following statement opens a database for use in isql. It uses all the CONNECT options available to isql:

CONNECT 'employee.gdb' USER 'ACCT_REC' PASSWORD 'peanuts';

The next statement, from an embedded application, attaches to a database file stored in the host-language variable and assigns a previously declared database handle to it:

EXEC SQL 
  SET DATABASE DB1 = 'employee.gdb';
EXEC SQL 
  CONNECT :db_file AS DB1;

The following embedded SQL statement attaches to employee.gdb and allocates 150 cache buffers:

EXEC SQL 
  CONNECT 'accounts.ib' CACHE 150; 

The next embedded SQL statement connects the user to all databases specified with previous SET DATABASE statements:

EXEC SQL 
  CONNECT ALL USER 'ACCT_REC' PASSWORD 'peanuts' 
  CACHE 50; 

The following embedded SQL statement attaches to the database, employee.gdb, with 80 buffers and database employee2.gdb with the default of 75 buffers:

EXEC SQL 
  CONNECT 'employee.gdb' CACHE 80, 'employee2.gdb';

The next embedded SQL statement attaches to all databases and allocates 50 buffers:

EXEC SQL 
  CONNECT ALL CACHE 50;

The following embedded SQL statement connects to EMP1 and v, setting the number of buffers for each to 80:

EXEC SQL 
  CONNECT EMP1 CACHE 80, EMP2 CACHE 80;

The next embedded SQL statement connects to two databases identified by variable names, setting different user names and passwords for each:

EXEC SQL 
  CONNECT 
  :orderdb AS DB1 USER 'ACCT_REC' PASSWORD 'peanuts',
  :salesdb AS DB2 USER 'ACCT_PAY' PASSWORD 'payout';

See also DISCONNECT, SET DATABASE, SET NAMES

Se the Data Definition Guide for more information about cache buffers and the Operations Guide for more information about database security and isql.

u Back to top

COUNT( )

Calculates the number of rows that satisfy a query’s search condition. Available in gpre, DSQL, and isql.

Syntax  COUNT ( * | [ALL] value | DISTINCT value)

Description COUNT() is an aggregate function that returns the number of rows that satisfy a query’s search condition. It can be used in views and joins as well as in tables.

Example The following embedded SQL statement returns the number of unique currency values it encounters in the COUNTRY table:

EXEC SQL
  SELECT COUNT (DISTINCT CURRENCY) INTO :cnt FROM COUNTRY;

See also AVG( ), MAX( ), MIN( ) SUM( )

u Back to top

CREATE DATABASE

Creates a new database. Available in gpre, DSQL, and isql.

Syntax  CREATE {DATABASE | SCHEMA} 'filespec'
                 [USER 'username' [PASSWORD 'password']]
                 [PAGE_SIZE [=] int]
                 [LENGTH [=] int [PAGE[S]]]
                 [DEFAULT CHARACTER SET charset]
                 [secondary_file];

<secondary_file> = FILE 'filespec' [fileinfo] [secondary_file]

<fileinfo> = [LENGTH [=] int [PAGE[S]]  | STARTING [AT [PAGE]] int }
  [fileinfo]

Important In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in isql, the semicolon is a terminating symbol for the statement, so it must be included.

Description CREATE DATABASE creates a new, empty database and establishes the following characteristics for it:

• The name of the primary file that identifies the database for users.

By default, databases are contained in single files.

• The name of any secondary files in which the database is stored.

A database can reside in more than one disk file if additional file names are specified as secondary files. If a database is created on a remote server, secondary file specifications cannot include a node name.

• The size of database pages.

Increasing page size can improve performance for the following reasons:

• Indexes work faster because the depth of the index is kept to a minimum.

• Keeping large rows on a single page is more efficient.

• Blob data is stored and retrieved more efficiently when it fits on a single page.

If most transactions involve only a few rows of data, a smaller page size might be appropriate, since less data needs to be passed back and forth and less memory is used by the disk cache.

• The number of pages in each database file.

• The dialect of the database.

The initial dialect of the database is the dialect of the client that creates it. For example, if you are using isql, either start it with the -sql_dialect n switch or issue the SET SQL DIALECT n command before issuing the CREATE DATABASE command. Typically, you would create all databases in dialect 3. Dialect 1 exists to ease the migration of legacy databases.

To change the dialect of a database, use gfix or the Properties dialog in IBConsole. See the Migration appendix in the InterBase Operations Guide for information about migrating databases.

• The character set used by the database.

For a list of the character sets recognized by InterBase, see Chapter 7, “Character Sets and Collation Orders.”

Choice of DEFAULT CHARACTER SET limits possible collation orders to a subset of all available collation orders. Given a specific character set, a specific collation order can be specified when data is selected, inserted, or updated in a column.

If you do not specify a default character set, the character set defaults to NONE. Using character set NONE means that there is no character set assumption for columns; data is stored and retrieved just as you originally entered it. You can load any character set into a column defined with NONE, but you cannot load that same data into another column that has been defined with a different character set. In that case, no transliteration is performed between the source and destination character sets, and transliteration errors may occur during assignment.

• System tables that describe the structure of the database.

After creating the database, you define its tables, views, indexes, and system views as well as any triggers, generators, stored procedures, and UDFs that you need.

Important In DSQL, you must execute CREATE DATABASE EXECUTE IMMEDIATE. The database handle and transaction name, if present, must be initialized to zero prior to use.

Read-only databases 

Databases are always created in read-write mode. You can change a table to read-only mode in one of two ways: you can specify mode -read_only when you restore a backup, or you can use gfix -mode read_only to change the mode of a table to read-only. See Chapter 6 in the Operations Guide for more information on database configuration and maintenance.

About file sizes

InterBase dynamically expands the last file in a database as needed. The maximum file size is system-dependent. This applies to single-file databases as well as to the last file of multifile databases. You should be aware that specifying a LENGTH for such files has no effect.

The total file size is the product of the number of database pages times the page size. The default page size is 4KB and the maximum page size is 8KB. However, InterBase files are small at creation time and increase in size as needed. The product of number of pages times page size represents a potential maximum size, not the size at creation.

Examples The following isql statement creates a database in the current directory using isql:

CREATE DATABASE 'employee.gdb';

The next embedded SQL statement creates a database with a page size of 2048 bytes rather than the default of 4096:

EXEC SQL
  CREATE DATABASE 'employee.gdb' PAGE_SIZE 2048;

The following embedded SQL statement creates a database stored in two files and specifies its default character set:

EXEC SQL
  CREATE DATABASE 'employee.gdb'
     DEFAULT CHARACTER SET ISO8859_1
     FILE 'employee2.gdb' STARTING AT PAGE 10001;

See also ALTER DATABASE, DROP DATABASE

See the Data Definition Guide for more information about secondary files, character set specification, and collation order; see the Operations Guide for more information about page size.

u Back to top

CREATE DOMAIN

Creates a column definition that is global to the database. Available in gpre, DSQL, and isql.

Syntax  CREATE DOMAIN domain [AS] datatype
                 [DEFAULT {literal | NULL | USER}] 
                 [NOT NULL] [CHECK (dom_search_condition)]
                 [COLLATE collation];

<datatype> = 
  {SMALLINT|INTEGER|FLOAT|DOUBLE PRECISION} [array_dim]
  | {DATE|TIME|TIMESTAMP} [array_dim]
  | {DECIMAL | NUMERIC} [(precision [, scale])] [array_dim] 
  | {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} [(int)]
     [array_dim] [CHARACTER SET charname] 
  | {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR} 
     [VARYING] [(int)] [array_dim] 
  | BLOB [SUB_TYPE {int | subtype_name}] [SEGMENT SIZE int]
     [CHARACTER SET charname]
  | BLOB [(seglen [, subtype])]
  | BOOLEAN

<array_dim> = [[x:]y [, [x:]y …]]

<dom_search_condition> = 
  VALUE operator value 
  | VALUE [NOT] BETWEEN value AND value 
  | VALUE [NOT] LIKE value [ESCAPE value] 
  | VALUE [NOT] IN (value [, value …]) 
  | VALUE IS [NOT] NULL 
  | VALUE [NOT] CONTAINING value 
  | VALUE [NOT] STARTING [WITH] value
  | (dom_search_condition)
  | NOT dom_search_condition
  | dom_search_condition OR dom_search_condition
  | dom_search_condition AND dom_search_condition

<operator> = {= | < | > | <= | >= | !< | !> | <> | !=}

Note on the CREATE DOMAIN syntax

• COLLATE is useful only for text data, not for numeric types. Also, you cannot specify a COLLATE clause for Blob columns.

• When declaring arrays, you must include the outermost brackets, shown below in bold. For example, the following statement creates a 5 by 5 two-dimensional array of strings, each of which is 6 characters long:

my_array = varchar(6)[5,5]

Use the colon (:) to specify an array with a starting point other than 1. The following example creates an array of integers that begins at 20 and ends at 30:

my_array = integer[20:30]

Important In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in isql, the semicolon is a terminating symbol for the statement, so it must be included.

Description CREATE DOMAIN builds an inheritable column definition that acts as a template for columns defined with CREATE TABLE or ALTER TABLE. The domain definition contains a set of characteristics, which include:

• Datatype

• An optional default value

• Optional disallowing of NULL values

• An optional CHECK constraint

• An optional collation clause

The CHECK constraint in a domain definition sets a dom_search_condition that must be true for data entered into columns based on the domain. The CHECK constraint cannot reference any domain or column.

Note Be careful not to create a domain with contradictory constraints, such as declaring a domain NOT NULL and assigning it a DEFAULT value of NULL.

The datatype specification for a CHAR or VARCHAR text domain definition can include a CHARACTER SET clause to specify a character set for the domain. Otherwise, the domain uses the default database character set. For a complete list of character sets recognized by InterBase, see Chapter 7, “Character Sets and Collation Orders.”

If you do not specify a default character set, the character set defaults to NONE. Using character set NONE means that there is no character set assumption for columns; data is stored and retrieved just as you originally entered it. You can load any character set into a column defined with NONE, but you cannot load that same data into another column that has been defined with a different character set. In these cases, no transliteration is performed between the source and destination character sets, so errors can occur during assignment.

The COLLATE clause enables specification of a particular collation order for CHAR, VARCHAR, and NCHAR text datatypes. Choice of collation order is restricted to those supported for the domain’s given character set, which is either the default character set for the entire database, or a different set defined in the CHARACTER SET clause as part of the datatype definition. For a complete list of collation orders recognized by InterBase, see Chapter 7, “Character Sets and Collation Orders.”

Columns based on a domain definition inherit all characteristics of the domain. The domain default, collation clause, and NOT NULL setting can be overridden when defining a column based on a domain. A column based on a domain can add additional CHECK constraints to the domain CHECK constraint.

Examples The following isql statement creates a domain that must have a positive value greater than 1,000, with a default value of 9,999. The keyword VALUE substitutes for the name of a column based on this domain.

CREATE DOMAIN CUSTNO
  AS INTEGER
  DEFAULT 9999
  CHECK (VALUE > 1000);

The next isql statement limits the values entered in the domain to four specific values:

CREATE DOMAIN PRODTYPE
  AS VARCHAR(12) 
  CHECK (VALUE IN ('software', 'hardware', 'other', 'N/A'));

The following isql statement creates a domain that defines an array of CHAR datatype:

CREATE DOMAIN DEPTARRAY AS CHAR(67) [4:5];

In the following isql example, the first statement creates a domain with USER as the default. The next statement creates a table that includes a column, ENTERED_BY, based on the USERNAME domain.

CREATE DOMAIN USERNAME AS VARCHAR(20)
  DEFAULT USER;

CREATE TABLE ORDERS (ORDER_DATE DATE, ENTERED_BY USERNAME, 
  ORDER_AMT DECIMAL(8,2));

INSERT INTO ORDERS (ORDER_DATE, ORDER_AMT)
  VALUES ('1-MAY-93', 512.36);

The INSERT statement does not include a value for the ENTERED_BY column, so InterBase automatically inserts the user name of the current user, JSMITH:

SELECT * FROM ORDERS;

1-MAY-93 JSMITH 512.36

The next isql statement creates a BLOB domain with a TEXT subtype that has an assigned character set:

CREATE DOMAIN DESCRIPT AS 
  BLOB SUB_TYPE TEXT SEGMENT SIZE 80
  CHARACTER SET SJIS;

See also ALTER DOMAIN, ALTER TABLE, CREATE TABLE, DROP DOMAIN

For more information about character set specification and collation orders, see the Data Definition Guide.

u Back to top

CREATE EXCEPTION

Creates a used-defined error and associated message for use in stored procedures and triggers. Available in DSQL and isql.

Syntax  CREATE EXCEPTION name 'message';

Important In SQL statements passed to DSQL, omit the terminating semicolon. In isql, the semicolon is a terminating symbol for the statement, so it must be included.

Description CREATE EXCEPTION creates an exception, a user-defined error with an associated message. Exceptions may be raised in triggers and stored procedures.

Exceptions are global to the database. The same message or set of messages is available to all stored procedures and triggers in an application. For example, a database can have English and French versions of the same exception messages and use the appropriate set as needed.

When raised by a trigger or a stored procedure, an exception:

• Terminates the trigger or procedure in which it was raised and undoes any actions performed (directly or indirectly) by it.

• Returns an error message to the calling application. In isql, the error message appears on the screen, unless output is redirected.

Exceptions may be trapped and handled with a WHEN statement in a stored procedure or trigger.

Examples This isql statement creates the exception, UNKNOWN_EMP_ID:

CREATE EXCEPTION UNKNOWN_EMP_ID 'Invalid employee number 
  or project id.';

The following statement from a stored procedure raises the previously created exception when SQLCODE -530 is set, which is a violation of a FOREIGN KEY constraint:

. . .
  WHEN SQLCODE -530 DO
     EXCEPTION UNKNOWN_EMP_ID;
. . .

See also ALTER EXCEPTION, ALTER PROCEDURE, ALTER TRIGGER, CREATE PROCEDURE, CREATE TRIGGER, DROP EXCEPTION

For more information on creating, raising, and handling exceptions, see the Data Definition Guide.

u Back to top

CREATE GENERATOR

Declares a generator to the database. Available in gpre, DSQL, and isql.

Syntax  CREATE GENERATOR name;

Important In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in isql, the semicolon is a terminating symbol for the statement, so it must be included.

Description CREATE GENERATOR declares a generator to the database and sets its starting value to zero. A generator is a sequential number that can be automatically inserted in a column with the GEN_ID() function. A generator is often used to ensure a unique value in a PRIMARY KEY, such as an invoice number, that must uniquely identify the associated row.

A database can contain any number of generators. Generators are global to the database, and can be used and updated in any transaction. InterBase does not assign duplicate generator values across transactions.

You can use SET GENERATOR to set or change the value of an existing generator when writing triggers, procedures, or SQL statements that call GEN_ID().

Note There is no “drop generator” statement. To remove a generator, delete it from the system table. For example:

DELETE FROM RDB$GENERATOR WHERE RDB$GENERATOR_NAME = ‘EMPNO_GEN’;

This DELETE statement requires permission to write to the system tables, so you must be either the database owner or the SYSDBA user, unless one of these users has granted write permission to you or to PUBLIC.

Example The following isql script fragment creates the generator, EMPNO_GEN, and the trigger, CREATE_EMPNO. The trigger uses the generator to produce sequential numeric keys, incremented by 1, for the NEW.EMPNO column:

CREATE GENERATOR EMPNO_GEN;
COMMIT;
CREATE TRIGGER CREATE_EMPNO FOR EMPLOYEES
  BEFORE INSERT  POSITION 0  AS 
  BEGIN
     NEW.EMPNO = GEN_ID(EMPNO_GEN, 1); 
  END

See also GEN_ID( ), SET GENERATOR

u Back to top

CREATE INDEX

Creates an index on one or more columns in a table. Available in gpre, DSQL, and isql.

Syntax  CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]] INDEX index 
                 ON table (col [, col …]);

Important In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in isql, the semicolon is a terminating symbol for the statement, so it must be included.

Description Creates an index on one or more columns in a table. Use CREATE INDEX to improve speed of data access. Using an index for columns that appear in a WHERE clause may improve search performance.

Important You cannot index Blob columns or arrays.

A UNIQUE index cannot be created on a column or set of columns that already contains duplicate or NULL values.

ASC and DESC specify the order in which an index is sorted. For faster response to queries that require sorted values, use the index order that matches the query’s ORDER BY clause. Both an ASC and a DESC index can be created on the same column or set of columns to access data in different orders.

Tip To improve index performance, use SET STATISTICS to recompute index selectivity, or rebuild the index by making it inactive, then active with sequential calls to ALTER INDEX.

Examples The following isql statement creates a unique index:

CREATE UNIQUE INDEX PRODTYPEX ON PROJECT (PRODUCT, PROJ_NAME);

The next isql statement creates a descending index:

CREATE DESCENDING INDEX CHANGEX ON SALARY_HISTORY (CHANGE_DATE);

The following isql statement creates a two-column index:

CREATE INDEX NAMEX ON EMPLOYEE (LAST_NAME, FIRST_NAME);

See also ALTER INDEX, DROP INDEX, SELECT, SET STATISTICS

u Back to top

CREATE PROCEDURE

Creates a stored procedure, its input and output parameters, and its actions. Available in DSQL, and isql.

Syntax  CREATE PROCEDURE name
                 [(param datatype [, param datatype …])] 
                 [RETURNS param datatype [, param datatype …])]
                 AS procedure_body ;

<procedure_body> =
  [variable_declaration_list] 
  block

<variable_declaration_list> = 
  DECLARE VARIABLE var datatype; 
  [DECLARE VARIABLE var datatype; …]

<block> = 
BEGIN
  compound_statement 
  [compound_statement …] 
END

<compound_statement> = block | statement;

<datatype> = { SMALLINT | INTEGER | FLOAT | DOUBLE PRECISION}
  | {DECIMAL | NUMERIC} [(precision [, scale])]
  | {DATE | TIME | TIMESTAMP)
  | {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} 
     [(int)]  [CHARACTER SET charname] 
  | {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR} [VARYING] [(int)]
  | BOOLEAN

Description CREATE PROCEDURE defines a new stored procedure to a database. A stored procedure is a self-contained program written in InterBase procedure and trigger language, and stored as part of a database’s metadata. Stored procedures can receive input parameters from and return values to applications.

InterBase procedure and trigger language includes all SQL data manipulation statements and some powerful extensions, including IF … THEN … ELSE, WHILE … DO, FOR SELECT … DO, exceptions, and error handling.

There are two types of procedures:

• Select procedures that an application can use in place of a table or view in a SELECT statement. A select procedure must be defined to return one or more values, or an error will result.

• Executable procedures that an application can call directly, with the EXECUTE PROCEDURE statement. An executable procedure need not return values to the calling program.

A stored procedure is composed of a header and a body.

The procedure header contains:

• The name of the stored procedure, which must be unique among procedure and table names in the database.

• An optional list of input parameters and their datatypes that a procedure receives from the calling program.

• RETURNS followed by a list of output parameters and their datatypes if the procedure returns values to the calling program.

The procedure body contains:

• An optional list of local variables and their datatypes.

• A block of statements in InterBase procedure and trigger language, bracketed by BEGIN and END. A block can itself include other blocks, so that there may be many levels of nesting.

InterBase does not allow database changes that affect the behavior of an existing stored procedure (for example, DROP TABLE or DROP EXCEPTION). To see all procedures defined for the current database or the text and parameters of a named procedure, use the isql internal commands SHOW PROCEDURES or SHOW PROCEDURE procedure.

InterBase procedure and trigger language is a complete programming language for stored procedures and triggers. It includes:

• SQL data manipulation statements: INSERT, UPDATE, DELETE, and singleton SELECT.

• SQL operators and expressions, including generators and UDFs that are linked with the database.

• Extensions to SQL, including assignment statements, control-flow statements, context variables (for triggers), event-posting statements, exceptions, and error-handling statements.

The following table summarizes language extensions for stored procedures. For a complete description of each statement, see Chapter 3, “Procedures and Triggers.”

The stored procedure and trigger language does not include many of the statement types available in DSQL or gpre. The following statement types are not supported in triggers or stored procedures:

• Data definition language statements: CREATE, ALTER, DROP, DECLARE EXTERNAL FUNCTION, and DECLARE FILTER

• Transaction control statements: SET TRANSACTION, COMMIT, ROLLBACK

• Dynamic SQL statements: PREPARE, DESCRIBE, EXECUTE

• CONNECT/DISCONNECT, and sending SQL statements to another database

• GRANT/REVOKE

• SET GENERATOR

• EVENT INIT/WAIT

• BEGIN/END DECLARE SECTION

• BASED ON

• WHENEVER

• DECLARE CURSOR

• OPEN

• FETCH

Examples The following procedure, SUB_TOT_BUDGET, takes a department number as its input parameter, and returns the total, average, minimum, and maximum budgets of departments with the specified HEAD_DEPT.

/* Compute total, average, smallest, and largest department budget.
*Parameters:w1
* department id
*
*Returns:
* total budget
* average budget
* min budget
* max budget */

CREATE PROCEDURE SUB_TOT_BUDGET (HEAD_DEPT CHAR(3))
  RETURNS (tot_bw1udget DECIMAL(12, 2), avg_budget DECIMAL(12, 2),
     min_budget DECIMAL(12, 2), max_budget DECIMAL(12, 2))
  AS
  BEGIN
     SELECT SUM(BUDGET), AVG(BUDGET), MIN(BUDGET), MAX(BUDGET)
        FROM DEPARTMENT
        WHERE HEAD_DEPT = :head_dept
        INTO :tot_budget, :avg_budget, :min_budget, :max_budget;
     EXIT;
  END 
;

The following select procedure, ORG_CHART, displays an organizational chart:

/* Display an org-chart.
*
* Parameters:
*    --
* Returns:
*    parent department
*    department name
*    department manager
*    manager's job title
*    number of employees in the department */
CREATE PROCEDURE ORG_CHART
  RETURNS (HEAD_DEPT CHAR(25), DEPARTMENT CHAR(25),
     MNGR_NAME CHAR(20), TITLE CHAR(5), EMP_CNT INTEGER)
  AS
     DECLARE VARIABLE mngr_no INTEGER;
     DECLARE VARIABLE dno CHAR(3);
  BEGIN
     FOR SELECT H.DEPARTMENT, D.DEPARTMENT, D.MNGR_NO, D.DEPT_NO
        FROM DEPARTMENT D
        LEFT OUTER JOIN DEPARTMENT H ON D.HEAD_DEPT = H.DEPT_NO
        ORDER BY D.DEPT_NO
        INTO :head_dept, :department, :mngr_no, :dno
     DO
        BEGIN
          IF (:mngr_no IS NULL) THEN
             BEGIN
                MNGR_NAME = '--TBH--';
                TITLE = '';
             END
          ELSE
             SELECT FULL_NAME, JOB_CODE
                FROM EMPLOYEE
                WHERE EMP_NO = :mngr_no
                INTO :mngr_name, :title;
             SELECT COUNT(EMP_NO)
                FROM EMPLOYEE
                WHERE DEPT_NO = :dno
                INTO :emp_cnt;
             SUSPEND;
        END
  END ;

When ORG_CHART is invoked, for example in the following isql statement:

SELECT * FROM ORG_CHART

it displays the department name for each department, which department it is in, the department manager’s name and title, and the number of employees in the department.

ORG_CHART must be used as a select procedure to display the full organization. If called with EXECUTE PROCEDURE, the first time it encounters the SUSPEND statement, it terminates, returning the information for Corporate Headquarters only.

See also ALTER EXCEPTION, ALTER PROCEDURE, CREATE EXCEPTION, DROP EXCEPTION, DROP PROCEDURE, EXECUTE PROCEDURE, SELECT

For more information on creating and using procedures, see the Data Definition Guide.

For a complete description of the statements in procedure and trigger language, see Chapter 3, “Procedures and Triggers.”

u Back to top

CREATE ROLE

Creates a role.

Syntax  CREATE ROLE rolename;

Important In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in isql, the semicolon is a terminating symbol for the statement, so it must be included.

Description Roles created with CREATE ROLE can be granted privileges just as users can. These roles can be granted to users, who then inherit the privilege list that has been granted to the role. Users must specify the role at connect time. Use GRANT to grant privileges (ALL, SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES) to a role and to grant a role to users. Use REVOKE to revoke them.

Example The following statement creates a role called “administrator.”

CREATE ROLE administrator;

See also GRANT, REVOKE, DROP ROLE

u Back to top

CREATE SHADOW

Creates one or more duplicate, in-sync copies of a database. Available in gpre, DSQL, and isql.

Syntax  CREATE SHADOW set_num [AUTO | MANUAL] [CONDITIONAL]
                 'filespec' [LENGTH [=] int [PAGE[S]]] 
                 [secondary_file];

<secondary_file> = FILE 'filespec' [fileinfo] [secondary_file]

<fileinfo> = LENGTH [=] int [PAGE[S]] | STARTING [AT [PAGE]] int
  [fileinfo]

Important In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in isql, the semicolon is a terminating symbol for the statement, so it must be included.

Description CREATE SHADOW is used to guard against loss of access to a database by establishing one or more copies of the database on secondary storage devices. Each copy of the database consists of one or more shadow files, referred to as a shadow set. Each shadow set is designated by a unique positive integer.

Disk shadowing has three components:

• A database to shadow.

• The RDB$FILES system table, which lists shadow files and other information about the database.

• A shadow set, consisting of one or more shadow files.

When CREATE SHADOW is issued, a shadow is established for the database most recently attached by an application. A shadow set can consist of one or multiple files. In case of disk failure, the database administrator (DBA) activates the disk shadow so that it can take the place of the database. If CONDITIONAL is specified, then when the DBA activates the disk shadow to replace an actual database, a new shadow is established for the database.

If a database is larger than the space available for a shadow on one disk, use the secondary_file option to define multiple shadow files. Multiple shadow files can be spread over several disks.

Tip To add a secondary file to an existing disk shadow, drop the shadow with DROP SHADOW and use CREATE SHADOW to recreate it with the desired number of files.

Examples The following isql statement creates a single, automatic shadow file for employee.gdb:

CREATE SHADOW 1 AUTO 'employee.shd';

The next isql statement creates a conditional, single, automatic shadow file for employee.gdb:

CREATE SHADOW 2 CONDITIONAL 'employee.shd' LENGTH 1000;

The following isql statements create a multiple-file shadow set for the employee.gdb database. The first statement specifies starting pages for the shadow files; the second statement specifies the number of pages for the shadow files.

CREATE SHADOW 3 AUTO 
     'employee.sh1'
  FILE 'employee.sh2'
        STARTING AT PAGE 1000
  FILE 'employee.sh3' 
        STARTING AT PAGE 2000;
CREATE SHADOW 4 MANUAL 'employee.sdw' 
  LENGTH 1000
  FILE 'employee.sh1' 
     LENGTH 1000 
  FILE 'employee.sh2'; 

See also DROP SHADOW

For more information about using shadows, see the Operations Guide or the Data Definition Guide.

u Back to top

CREATE TABLE

Creates a new table in an existing database. Available in gpre, DSQL, and isql.

Syntax  CREATE TABLE table [EXTERNAL [FILE] 'filespec']
                 (col_def [, col_def | tconstraint …]);

<col_def> = col {datatype | COMPUTED [BY] (expr) | domain}
  [DEFAULT {literal | NULL | USER}]
  [NOT NULL] 
  [col_constraint]
  [COLLATE collation]

<datatype> = 
  {SMALLINT | INTEGER | FLOAT | DOUBLE PRECISION}[array_dim]
  | (DATE | TIME | TIMESTAMP} [array_dim] 
  | {DECIMAL | NUMERIC} [(precision [, scale])] [array_dim] 
  | {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} [(int)]
     [array_dim] [CHARACTER SET charname] 
  | {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR} 
     [VARYING] [(int)] [array_dim] 
  | BLOB [SUB_TYPE {int | subtype_name}] [SEGMENT SIZE int]
     [CHARACTER SET charname]
  | BLOB [(seglen [, subtype])]
  | BOOLEAN

<array_dim> = [[x:]y [, [x:]y …]] 

<expr> = A valid SQL expression that results in a single value.

<col_constraint> = [CONSTRAINT constraint] 
  { UNIQUE 
  | PRIMARY KEY 
  | REFERENCES other_table [(other_col [, other_col …])]
     [ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
     [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
  | CHECK (search_condition)}

<tconstraint> = [CONSTRAINT constraint] 
  {{PRIMARY KEY | UNIQUE} (col [, col …]) 
  | FOREIGN KEY (col [, col …]) 
     REFERENCES other_table [(other_col [, other_col …])]
        [ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
        [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
  | CHECK (search_condition)}

<search_condition> = val operator {val | (select_one)} 
  | val [NOT] BETWEEN val AND val 
  | val [NOT] LIKE val [ESCAPE val] 
  | val [NOT] IN (val [, val …] | select_list) 
  | val IS [NOT] NULL 
  | val {>= | <=} 
  | val [NOT] {= | < | >} 
  | {ALL | SOME | ANY} (select_list) 
  | EXISTS (select_expr) 
  | SINGULAR (select_expr) 
  | val [NOT] CONTAINING val 
  | val [NOT] STARTING [WITH] val 
  | (search_condition) 
  | NOT search_condition 
  | search_condition OR search_condition 
  | search_condition AND search_condition

<val> = { col [array_dim] | :variable
  | constant | expr | function
  | udf ([val [, val …]])
  | NULL | USER | RDB$DB_KEY | ? }
  [COLLATE collation]

<constant> = num | 'string' | charsetname 'string'

<function> = COUNT (* | [ALL] val | DISTINCT val)
  | SUM ([ALL] val | DISTINCT val)
  | AVG ([ALL] val | DISTINCT val)
  | MAX ([ALL] val | DISTINCT val)
  | MIN ([ALL] val | DISTINCT val)
  | CAST (val AS datatype)
  | UPPER (val)
  | GEN_ID (generator, val)

<operator> = {= | < | > | <= | >= | !< | !> | <> | !=}

<select_one> = SELECT on a single column; returns exactly one value.

<select_list> = SELECT on a single column; returns zero or more values.

<select_expr> = SELECT on a list of values; returns zero or more values.

Important In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in isql, the semicolon is a terminating symbol for the statement, so it must be included.

Notes on the CREATE TABLE statement

• When declaring arrays, you must include the outermost brackets, shown below in bold. For example, the following statement creates a 5 by 5 two-dimensional array of strings, each of which is 6 characters long:

  my_array VARCHAR(6)[5,5]

• Use the colon (:) to specify an array with a starting point other than 1. The following example creates an array of integers that begins at 10 and ends at 20:

  my_array INTEGER[10:20]

• In SQL and isql, you cannot use val as a parameter placeholder (like “?”).

• In DSQL and isql, val cannot be a variable.

• You cannot specify a COLLATE clause for Blob columns.

• expr is any complex SQL statement or equation that produces a single value.

Description CREATE TABLE establishes a new table, its columns, and integrity constraints in an existing database. The user who creates a table is the table’s owner and has all privileges for it, including the ability to GRANT privileges to other users, triggers, and stored procedures.

• CREATE TABLE supports several options for defining columns:

• Local columns specify the name and datatype for data entered into the column.

• Computed columns are based on an expression. Column values are computed each time the table is accessed. If the datatype is not specified, InterBase calculates an appropriate one. Columns referenced in the expression must exist before the column can be defined.

• Domain-based columns inherit all the characteristics of a domain, but the column definition can include a new default value, a NOT NULL attribute, additional CHECK constraints, or a collation clause that overrides the domain definition. It can also include additional column constraints.

• The datatype specification for a CHAR, VARCHAR, or Blob text column definition can include a CHARACTER SET clause to specify a particular character set for the single column. Otherwise, the column uses the default database character set. If the database character set is changed, all columns subsequently defined have the new character set, but existing columns are not affected. For a complete list of character sets recognized by InterBase, see Chapter 7, “Character Sets and Collation Orders.”

• If you do not specify a default character set, the character set defaults to NONE. Using character set NONE means that there is no character set assumption for columns; data is stored and retrieved just as you originally entered it. You can load any character set into a column defined with NONE, but you cannot load that same data into another column that has been defined with a different character set. In this case, no transliteration is performed between the source and destination character sets, and errors may occur during assignment.

• The COLLATE clause enables specification of a particular collation order for CHAR, VARCHAR, and Blob text datatypes. Choice of collation order is restricted to those supported for the column’s given character set, which is either the default character set for the entire database, or a different set defined in the CHARACTER SET clause as part of the datatype definition. For a complete list of collation orders recognized by InterBase, see Chapter 7, “Character Sets and Collation Orders.”

• NOT NULL is an attribute that prevents the entry of NULL or unknown values in column. NOT NULL affects all INSERT and UPDATE operations on a column.

Important A DECLARE TABLE must precede CREATE TABLE in embedded applications if the same SQL program both creates a table and inserts data in the table.

• The EXTERNAL FILE option creates a table whose data resides in an external file, rather than in the InterBase database. Use this option to:

• Define an InterBase table composed of data from an external source, such as data in files managed by other operating systems or in non-database applications.

• Transfer data to an existing InterBase table from an external file.

External files must either be placed in <interbase_home>/ext or their location must be specified in the ibconfig configuration file using the EXTERNAL_FILE_DIRECTORY entry.

Referential integrity constraints

• You can define integrity constraints at the time you create a table. These constraints are rules that validate data entries by enforcing column-to-table and table-to-table relationships. They span all transactions that access the database and are automatically maintained by the system. CREATE TABLE supports the following integrity constraints:

• A PRIMARY KEY is one or more columns whose collective contents are guaranteed to be unique. A PRIMARY KEY column must also define the NOT NULL attribute. A table can have only one primary key.

• UNIQUE keys ensure that no two rows have the same value for a specified column or ordered set of columns. A unique column must also define the NOT NULL attribute. A table can have one or more UNIQUE keys. A UNIQUE key can be referenced by a FOREIGN KEY in another table.

• Referential constraints (REFERENCES) ensure that values in the specified columns (known as the foreign key) are the same as values in the referenced UNIQUE or PRIMARY KEY columns in another table. The UNIQUE or PRIMARY KEY columns in the referenced table must be defined before the REFERENCES constraint is added to the secondary table. REFERENCES has ON DELETE and ON UPDATE clauses that define the action on the foreign key when the referenced primary key is updated or deleted. The values for ON UPDATE and ON DELETE are as follows:

• You can create a FOREIGN KEY reference to a table that is owned by someone else only if that owner has explicitly granted you REFERENCES privilege on that table. Any user who updates your foreign key table must have REFERENCES or SELECT privileges on the referenced primary key table.

• CHECK constraints enforce a search_condition that must be true for inserts or updates to the specified table. search_condition can require a combination or range of values or can compare the value entered with data in other columns.

Note Specifying USER as the value for a search_condition references the login of the user who is attempting to write to the referenced table.

• Creating PRIMARY KEY and FOREIGN KEY constraints requires exclusive access to the database.

• For unnamed constraints, the system assigns a unique constraint name stored in the RDB$RELATION_CONSTRAINTS system table.

Note Constraints are not enforced on expressions.

Examples The following isql statement creates a simple table with a PRIMARY KEY:

CREATE TABLE COUNTRY (COUNTRY COUNTRYNAME NOT NULL PRIMARY KEY,
  CURRENCY VARCHAR(10) NOT NULL);

The next isql statement creates both a column-level and a table-level UNIQUE constraint:

CREATE TABLE STOCK (
  MODEL SMALLINT NOT NULL UNIQUE,
  MODELNAME CHAR(10) NOT NULL, 
  ITEMID INTEGER NOT NULL, 
  CONSTRAINT MOD_UNIQUE UNIQUE (MODELNAME, ITEMID));

The following isql statement illustrates table-level PRIMARY KEY, FOREIGN KEY, and CHECK constraints. The PRIMARY KEY constraint is based on three columns. This example also illustrates creating an array column of VARCHAR.

CREATE TABLE JOB (
  JOB_CODE JOBCODE NOT NULL,
  JOB_GRADE JOBGRADE NOT NULL,
  JOB_COUNTRY COUNTRYNAME NOT NULL,
  JOB_TITLE VARCHAR(25) NOT NULL,
  MIN_SALARY SALARY NOT NULL,
  MAX_SALARY SALARY NOT NULL,
  JOB_REQUIREMENT BLOB(400,1),
  LANGUAGE_REQ VARCHAR(15) [5],
  PRIMARY KEY (JOB_CODE, JOB_GRADE, JOB_COUNTRY),
  FOREIGN KEY (JOB_COUNTRY) REFERENCES COUNTRY (COUNTRY),
  CHECK (MIN_SALARY < MAX_SALARY));

In the next example, the F2 column in table T2 is a foreign key that references table T1 through T1’s primary key P1. When a row in T1 changes, that change propagates to all affected rows in table T2. When a row in T1 is deleted, all affected rows in the F2 column of table T2 are set to NULL.

CREATE TABLE T1 (P1 INTEGER NOT NULL PRIMARY KEY);

CREATE TABLE T2 (F2 INTEGER FOREIGN KEY (F2) REFERENCES T1 (P1)
  ON UPDATE CASCADE
  ON DELETE SET NULL);

The next isql statement creates a table with a calculated column:

CREATE TABLE SALARY_HISTORY (
  EMP_NO EMPNO NOT NULL,
  CHANGE_DATE DATE DEFAULT 'NOW' NOT NULL,
  UPDATER_ID VARCHAR(20) NOT NULL,
  OLD_SALARY SALARY NOT NULL,
  PERCENT_CHANGE DOUBLE PRECISION
     DEFAULT 0
     NOT NULL
     CHECK (PERCENT_CHANGE BETWEEN -50 AND 50),
  NEW_SALARY COMPUTED BY
     (OLD_SALARY + OLD_SALARY * PERCENT_CHANGE / 100),
  PRIMARY KEY (EMP_NO, CHANGE_DATE, UPDATER_ID),
  FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE (EMP_NO));

In the following isql statement the first column retains the default collating order for the database’s default character set. The second column has a different collating order, and the third column definition includes a character set and a collating order.

CREATE TABLE BOOKADVANCE (
  BOOKNO CHAR(6), 
  TITLE CHAR(50) COLLATE ISO8859_1, 
  EUROPUB CHAR(50) CHARACTER SET ISO8859_1 COLLATE FR_FR);

See also CREATE DOMAIN, DECLARE TABLE, GRANT, REVOKE

For more information on creating metadata, using integrity constraints, external tables, datatypes, collation order, and character sets, see the Data Definition Guide.

u Back to top

CREATE TRIGGER

Creates a trigger, including when it fires, and what actions it performs. Available in DSQL, and isql.

Syntax  CREATE TRIGGER name FOR table
                 [ACTIVE | INACTIVE]
                 {BEFORE | AFTER}
                 {DELETE | INSERT | UPDATE}
                 [POSITION number]
                 AS trigger_body ;

<trigger_body> = [variable_declaration_list] block

<variable_declaration_list> = 
  DECLARE VARIABLE variable datatype;
  [DECLARE VARIABLE variable datatype; …]

<block> =
BEGIN
  compound_statement
  [compound_statement …]
END

<datatype> =  SMALLINT
  | INTEGER
  | FLOAT
  | DOUBLE PRECISION
  | {DECIMAL | NUMERIC} [(precision [, scale])]
  | {DATE | TIME | TIMESTAMP)
  | {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} 
     [(int)]  [CHARACTER SET charname] 
  | {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR} [VARYING] [(int)]
  | BOOLEAN

     <compound_statement> = block | statement;

Description CREATE TRIGGER defines a new trigger to a database. A trigger is a self-contained program associated with a table or view that automatically performs an action when a row in the table or view is inserted, updated, or deleted.

A trigger is never called directly. Instead, when an application or user attempts to INSERT, UPDATE, or DELETE a row in a table, any triggers associated with that table and operation automatically execute, or fire. Triggers defined for UPDATE on non-updatable views fire even if no update occurs.

A trigger is composed of a header and a body.

The trigger header contains:

• A trigger name, unique within the database, that distinguishes the trigger from all others.

• A table name, identifying the table with which to associate the trigger.

• Statements that determine when the trigger fires.

The trigger body contains:

• An optional list of local variables and their datatypes.

• A block of statements in InterBase procedure and trigger language, bracketed by BEGIN and END. These statements are performed when the trigger fires. A block can itself include other blocks, so that there may be many levels of nesting.

A trigger is associated with a table. The table owner and any user granted privileges to the table automatically have rights to execute associated triggers.

Triggers can be granted privileges on tables, just as users or procedures can be granted privileges. Use the GRANT statement, but instead of using TO username, use TO TRIGGER trigger_name. Triggers’ privileges can be revoked similarly using REVOKE.

When a user performs an action that fires a trigger, the trigger will have privileges to perform its actions if one of the following conditions is true:

• The trigger has privileges for the action.

• The user has privileges for the action.

InterBase procedure and trigger language is a complete programming language for stored procedures and triggers. It includes:

• SQL data manipulation statements: INSERT, UPDATE, DELETE, and singleton SELECT.

• SQL operators and expressions, including generators and UDFs that are linked with the calling application.

• Powerful extensions to SQL, including assignment statements, control-flow statements, context variables, event-posting statements, exceptions, and error-handling statements.

The following table summarizes language extensions for triggers. For a complete description of each statement, see Chapter 3, “Procedures and Triggers.”

The stored procedure and trigger language does not include many of the statement types available in DSQL or gpre. The following statement types are not supported in triggers or stored procedures:

• Data definition language statements: CREATE, ALTER, DROP, DECLARE EXTERNAL FUNCTION, and DECLARE FILTER

• Transaction control statements: SET TRANSACTION, COMMIT, ROLLBACK

• Dynamic SQL statements: PREPARE, DESCRIBE, EXECUTE

• CONNECT/DISCONNECT, and sending SQL statements to another database

• GRANT/REVOKE

• SET GENERATOR

• EVENT INIT/WAIT

• BEGIN/END DECLARE SECTION

• BASED ON

• WHENEVER

• DECLARE CURSOR

• OPEN

• FETCH

Examples The following trigger, SAVE_SALARY_CHANGE, makes correlated updates to the SALARY_HISTORY table when a change is made to an employee’s salary in the EMPLOYEE table:

CREATE TRIGGER SAVE_SALARY_CHANGE FOR EMPLOYEE
  AFTER UPDATE AS
  BEGIN
     IF (OLD.SALARY <> NEW.SALARY) THEN
     INSERT INTO SALARY_HISTORY
     (EMP_NO, CHANGE_DATE, UPDATER_ID, OLD_SALARY, PERCENT_CHANGE)
        VALUES (OLD.EMP_NO, 'now', USER, OLD.SALARY,
        (NEW.SALARY - OLD.SALARY) * 100 / OLD.SALARY);
  END ;

The following trigger, SET_CUST_NO, uses a generator to create unique customer numbers when a new customer record is inserted in the CUSTOMER table.

CREATE TRIGGER SET_CUST_NO FOR CUSTOMER
  BEFORE INSERT AS
  BEGIN
     NEW.CUST_NO = GEN_ID(CUST_NO_GEN, 1);
  END ;

The following trigger, POST_NEW_ORDER, posts an event named “new_order” whenever a new record is inserted in the SALES table.

CREATE TRIGGER POST_NEW_ORDER FOR SALES
  AFTER INSERT AS
  BEGIN
     POST_EVENT 'new_order';
  END ;

The following four fragments of trigger headers demonstrate how the POSITION option determines trigger firing order:

CREATE TRIGGER A FOR accounts 
  BEFORE UPDATE 
  POSITION 5 … /*Trigger body follows*/

CREATE TRIGGER B FOR accounts 
  BEFORE UPDATE 
  POSITION 0 … /*Trigger body follows*/ 

CREATE TRIGGER C FOR accounts 
  AFTER UPDATE 
  POSITION 5 … /*Trigger body follows*/

CREATE TRIGGER D FOR accounts 
  AFTER UPDATE 
  POSITION 3 … /*Trigger body follows*/

When this update takes place:

UPDATE accounts SET account_status = 'on_hold' 
  WHERE account_balance <0;

The triggers fire in this order:

1 Trigger B fires.

2 Trigger A fires.

3 The update occurs.

4 Trigger D fires.

5 Trigger C fires.

See also ALTER EXCEPTION, ALTER TRIGGER, CREATE EXCEPTION, CREATE PROCEDURE, DROP EXCEPTION, DROP TRIGGER, EXECUTE PROCEDURE

For more information on creating and using triggers, see the Data Definition Guide.

For a complete description of the statements in procedure and trigger language, see Chapter 3, “Procedures and Triggers.”

u Back to top

CREATE VIEW

Creates a new view of data from one or more tables. Available in gpre, DSQL, and isql.

Syntax  CREATE VIEW name [(view_col [, view_col …])] 
                 AS select [WITH CHECK OPTION];

Important In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in isql, the semicolon is a terminating symbol for the statement, so it must be included.

Description CREATE VIEW describes a view of data based on one or more underlying tables in the database. The rows to return are defined by a SELECT statement that lists columns from the source tables. Only the view definition is stored in the database; a view does not directly represent physically stored data. It is possible to perform select, project, join, and union operations on views as if they were tables.

The user who creates a view is its owner and has all privileges for it, including the ability to GRANT privileges to other users, roles, triggers, views, and stored procedures. A user may have privileges to a view without having access to its base tables. When creating views:

• A read-only view requires SELECT privileges for any underlying tables.

• An updatable view requires ALL privileges to the underlying tables.

The view_col option ensures that the view always contains the same columns and that the columns always have the same view-defined names.

View column names correspond in order and number to the columns listed in the SELECT clause, so specify all view column names or none.

A view_col definition can contain one or more columns based on an expression that combines the outcome of two columns. The expression must return a single value, and cannot return an array or array element. If the view includes an expression, the view-column option is required.

Note Any columns used in the value expression must exist before the expression can be defined.

A SELECT statement clause cannot include the ORDER BY clause.

When SELECT * is used rather than a column list, order of display is based on the order in which columns are stored in the base table.

WITH CHECK OPTION enables InterBase to verify that a row added to or updated in a view is able to be seen through the view before allowing the operation to succeed. Do not use WITH CHECK OPTION for read-only views.

Note You cannot select from a view that is based on the result set of a stored procedure.

Note DSQL does not support view definitions containing UNION clauses. To create such a view, use embedded SQL.

A view is updatable if:

• It is a subset of a single table or another updatable view.

• All base table columns excluded from the view definition allow NULL values.

• The view’s SELECT statement does not contain subqueries, a DISTINCT predicate, a HAVING clause, aggregate functions, joined tables, user-defined functions, or stored procedures.

If the view definition does not meet these conditions, it is considered read-only.

Note Read-only views can be updated by using a combination of user-defined referential constraints, triggers, and unique indexes.

Examples The following isql statement creates an updatable view:

CREATE VIEW SNOW_LINE (CITY, STATE, SNOW_ALTITUDE) AS
  SELECT CITY, STATE, ALTITUDE
     FROM CITIES
     WHERE ALTITUDE > 5000;

The next isql statement uses a nested query to create a view:

CREATE VIEW RECENT_CITIES AS 
  SELECT STATE, CITY, POPULATION 
     FROM CITIES WHERE STATE IN
        (SELECT STATE FROM STATES WHERE STATEHOOD > '1-JAN-1850');

In an updatable view, the WITH CHECK OPTION prevents any inserts or updates through the view that do not satisfy the WHERE clause of the CREATE VIEW SELECT statement:

CREATE VIEW HALF_MILE_CITIES AS
  SELECT CITY, STATE, ALTITUDE 
     FROM CITIES
     WHERE ALTITUDE > 2500
     WITH CHECK OPTION;

The WITH CHECK OPTION clause in the view would prevent the following insertion:

INSERT INTO HALF_MILE_CITIES (CITY, STATE, ALTITUDE)
  VALUES ('Chicago', 'Illinois', 250);

On the other hand, the following UPDATE would be permitted:

INSERT INTO HALF_MILE_CITIES (CITY, STATE, ALTITUDE)
  VALUES ('Truckee', 'California', 2736);

The WITH CHECK OPTION clause does not allow updates through the view which change the value of a row so that the view cannot retrieve it. For example, the WITH CHECK OPTION in the HALF_MILE_CITIES view prevents the following update:

UPDATE HALF_MILE_CITIES
  SET ALTITUDE = 2000
  WHERE STATE = 'NY';

The next isql statement creates a view that joins two tables, and so is read-only:

CREATE VIEW PHONE_LIST AS 
  SELECT EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, LOCATION, PHONE_NO
     FROM EMPLOYEE, DEPARTMENT
     WHERE EMPLOYEE.DEPT_NO = DEPARTMENT.DEPT_NO;

See also CREATE TABLE, DROP VIEW, GRANT, INSERT, REVOKE, SELECT, UPDATE

For a complete discussion of views, see the Data Definition Guide.

u Back to top

DECLARE CURSOR

Defines a cursor for a table by associating a name with the set of rows specified in a SELECT statement. Available in gpre and DSQL.

Syntax  SQL form:

DECLARE cursor CURSOR FOR select [FOR UPDATE OF col [, col…]];

DSQL form:

DECLARE cursor CURSOR FOR statement_id

Blob form: See DECLARE CURSOR (BLOB)

Description DECLARE CURSOR defines the set of rows that can be retrieved using the cursor it names. It is the first member of a group of table cursor statements that must be used in sequence.

select specifies a SELECT statement that determines which rows to retrieve. The SELECT statement cannot include INTO or ORDER BY clauses.

The FOR UPDATE OF clause is necessary for updating or deleting rows using the WHERE CURRENT OF clause with UPDATE and DELETE.

A cursor is a one-way pointer into the ordered set of rows retrieved by the select expression in the DECLARE CURSOR statement. It enables sequential access to retrieved rows in turn. There are four related cursor statements:

Examples The following embedded SQL statement declares a cursor with a search condition:

EXEC SQL
  DECLARE C CURSOR FOR
  SELECT CUST_NO, ORDER_STATUS
     FROM SALES
     WHERE ORDER_STATUS IN ('open', 'shipping');

The next DSQL statement declares a cursor for a previously prepared statement, QUERY1:

DECLARE Q CURSOR FOR QUERY1

See also CLOSE, DECLARE CURSOR (BLOB), FETCH, OPEN, PREPARE, SELECT

u Back to top

DECLARE CURSOR (BLOB)

Declares a Blob cursor for read or insert. Available in gpre.

Syntax  DECLARE cursor CURSOR FOR 
                 {READ BLOB column FROM table 
                 | INSERT BLOB column INTO table}
                 [FILTER [FROM subtype] TO subtype]
                 [MAXIMUM_SEGMENT length];

Description Declares a cursor for reading or inserting Blob data. A Blob cursor can be associated with only one Blob column.

To read partial Blob segments when a host-language variable is smaller than the segment length of a Blob, declare the Blob cursor with the MAXIMUM_SEGMENT clause. If length is less than the Blob segment, FETCH returns length bytes. If the same or greater, it returns a full segment (the default).

Examples The following embedded SQL statement declares a READ BLOB cursor and uses the MAXIMUM_SEGMENT option:

EXEC SQL 
  DECLARE BC CURSOR FOR 
  READ BLOB JOB_REQUIREMENT FROM JOB MAXIMUM_SEGMENT 40;

The next embedded SQL statement declares an INSERT BLOB cursor:

EXEC SQL 
  DECLARE BC CURSOR FOR
  INSERT BLOB JOB_REQUIREMENt INTO JOB;

See also CLOSE (BLOB), FETCH (BLOB), INSERT CURSOR (BLOB), OPEN (BLOB)

u Back to top

DECLARE EXTERNAL FUNCTION

Declares an existing user-defined function (UDF) to a database. Available in gpre, DSQL, and isql.

Syntax  DECLARE EXTERNAL FUNCTION name [datatype 
                     | CSTRING (int) [, datatype | CSTRING (int) …]]
                 RETURNS {datatype [BY VALUE] | CSTRING (int) | PARAMETER n} [FREE_IT]
                 ENTRY_POINT 'entryname' MODULE_NAME 'modulename';

Important In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in isql, the semicolon is a terminating symbol for the statement, so it must be included.

Note Whenever a UDF returns a value by reference to dynamically allocated memory, you must declare it using the FREE_IT keyword in order to free the allocated memory.

Description DECLARE EXTERNAL FUNCTION provides information about a UDF to a database: where to find it, its name, the input parameters it requires, and the single value it returns. Each UDF in a library must be declared once to each database where it will be used. As long as the entry point and module name do not change, there is no need to redeclare a UDF, even if the function itself is modified.

entryname is the actual name of the function as stored in the UDF library. It does not have to match the name of the UDF as stored in the database.

Important The module name does not need to include a path. However, the module must either be placed in <interbase_home>/UDF or be listed in the InterBase configuration file using the EXTERNAL_FUNCTION_DIRECTORY parameter.

To specify a location for UDF libraries in a configuration file, enter a line of the following form for Windows platforms:

EXTERNAL_FUNCTION_DIRECTORY D:\Mylibraries\InterBase

For UNIX, the line does not include a drive letter:

EXTERNAL_FUNCTION_DIRECTORY \Mylibraries\InterBase

The InterBase configuration file is called ibconfig on all platforms.

Examples The following isql statement declares the TOPS() UDF to a database:

DECLARE EXTERNAL FUNCTION TOPS 
  CHAR(256), INTEGER, BLOB
  RETURNS INTEGER BY VALUE
  ENTRY_POINT 'te1' MODULE_NAME 'tm1';

This example does not need the FREE_IT keyword because only cstrings, CHAR, and VARCHAR return types require memory allocation.

The next example declares the LOWERS() UDF and frees the memory allocated for the return value:

DECLARE EXTERNAL FUNCTION LOWERS VARCHAR(256)
  RETURNS CSTRING(256) FREE_IT 
  ENTRY POINT 'fn_lower' MODULE_NAME 'udflib';

See also DROP EXTERNAL FUNCTION

For more information about writing UDFs and for a complete list of UDFs supplied by InterBase, see “Working with UDFs and Blob Filters” in the Developer’s Guide.

u Back to top

DECLARE FILTER

Declares an existing Blob filter to a database. Available in gpre, DSQL, and isql.

Syntax  DECLARE FILTER filter 
                 INPUT_TYPE subtype OUTPUT_TYPE subtype
                 ENTRY_POINT 'entryname' MODULE_NAME 'modulename';

Important In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in isql, the semicolon is a terminating symbol for the statement, so it must be included.

Description DECLARE FILTER provides information about an existing Blob filter to the database: where to find it, its name, and the Blob subtypes it works with. A Blob filter is a user-written program that converts data stored in Blob columns from one subtype to another.

INPUT_TYPE and OUTPUT_TYPE together determine the behavior of the Blob filter. Each filter declared to the database should have a unique combination of INPUT_TYPE and OUTPUT_TYPE integer values. InterBase provides a built-in type of 1, for handling text. User-defined types must be expressed as negative values.

entryname is the name of the Blob filter stored in the library. When an application uses a Blob filter, it calls the filter function with this name.

Example The following isql statement declares a Blob filter:

 DECLARE FILTER DESC_FILTER
   INPUT_TYPE 1
   OUTPUT_TYPE -4
   ENTRY_POINT 'desc_filter'
   MODULE_NAME 'FILTERLIB';