SQL Statement and Function Reference

ALTER DATABASE

Adds secondary files to the current database. Available in SQL, DSQL, and isql.

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 until it reaches the 4GB limit. 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.

ALTER DOMAIN

Changes a domain definition. Available in SQL, DSQL, and isql.

ALTER DOMAIN 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 a datatype or NOT NULL setting of a domain, drop the domain and recreate it with the desired combination of features.

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.

ALTER EXCEPTION

Changes the message associated with an existing exception. Available in DSQL and isql.

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.

ALTER INDEX

Activates or deactivates an index. Available in SQL, DSQL, and isql.

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 and reactivating an index is useful when changes in the distribution of indexed data cause the index to become unbalanced.

Before inserting or updating a large number of rows, deactivate a table’s indexes to avoid altering the index incrementally. When finished, reactivate the index. Reactivating a deactivated index rebuilds and rebalances an 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

ALTER PROCEDURE

Changes the definition of an existing stored procedure. Available in DSQL and isql.

ALTER PROCEDURE name
[(param <datatype> [, param <datatype> …])]
[RETURNS (param <datatype> [, param <datatype> …])]
AS <procedure_body> [terminator]

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, since existing application code may assume the procedure has its original format.

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):

SET TERM !! ;

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 !!

SET TERM ; !!

See Also CREATE PROCEDURE , DROP PROCEDURE , EXECUTE PROCEDURE

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

ALTER TABLE

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

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 …]]

<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

[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 1 and the Description for ALTER TABLE on page 25 .

ⁿ 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 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.

Referential integrity constraints

ⁿ To ensure that the referential integrity of foreign keys is preserved, use the ON UPDATE and ON DELETE options for all REFERENCES statements. The values for these cascading referential integrity options are given in Table 1, “The ALTER TABLE statement,” on page 23 .

ⁿ If you do not use the ON UPDATE and ON DELETE options, you must drop the constraint or computed column before dropping the table column. To drop a PRIMARY KEY or UNIQUE constraints 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 easier to find when its name appears in a constraint violation error message.

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 adds two columns to a table and defines a UNIQUE constraint on one of them:

ALTER TABLE COUNTRY

ADD CAPITAL VARCHAR(25) NOT NULL UNIQUE,

ADD LARGEST_CITY VARCHAR(25) NOT NULL;

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.

ALTER TRIGGER

Changes an existing trigger. Available in DSQL and isql.

ALTER TRIGGER name
[ACTIVE | INACTIVE]
[{BEFORE | AFTER} {DELETE | INSERT | UPDATE}]
[POSITION number]
[AS <trigger_body>] [terminator]

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 isql statement modifies the trigger, SET_CUST_NO, to be inactive:

ALTER TRIGGER SET_CUST_NO INACTIVE;

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

SET TERM !! ;

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 !!

SET TERM ; !!

See Also CREATE TRIGGER , DROP TRIGGER

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

AVG( )

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

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( )

BASED ON 

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

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

BEGIN DECLARE
SECTION

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

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

CAST( )

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

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().

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);

See Also UPPER( )

CLOSE

Closes an open cursor. Available in SQL.

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

CLOSE (BLOB)

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

CLOSE blob_cursor;

Description CLOSE closes an opened read or insert Blob cursor. Generally a Blob cursor should only be closed 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)

COMMIT

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

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.

CONNECT

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

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 isc4.gdb, the user will receive the following error when attempting to view isc4.gdb 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.gdb' 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.

COUNT( )

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

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( )

CREATE DATABASE

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

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.

Note To change the dialect of a database, use gfix or the Properties dialog in IBConsole. See the Migration chapter in Getting Started for information about migrating databases.

ⁿ The character set used by the database.

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 wither 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 “Read-only databases” in Chapter 6: “Database Configuration and Maintenance” in the Operations Guide.

About file sizes

InterBase dynamically expands the last file in a database as needed until it reaches the 4GB limit. This applies to single-file database as well as to the last file of multifile databases. You should be aware that specifying a LENGTH for such files has no effect. InterBase database files are limited to 4GB. The total file size is the product of the number of database pages times the page size. The default page size is 1KB 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 1024:

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.

CREATE DOMAIN

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

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])]

<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

ⁿ 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 10 and ends at 20:

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.

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 BLOB 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.

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(31) [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.

CREATE EXCEPTION

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

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.

CREATE GENERATOR

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

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’;

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;

SET TERM !! ;

CREATE TRIGGER CREATE_EMPNO FOR EMPLOYEES

BEFORE INSERT POSITION 0

AS BEGIN

NEW.EMPNO = GEN_ID(EMPNO_GEN, 1);

END

SET TERM ; !!

Important Because each statement in a stored procedure body must be terminated by a semicolon, you must define a different symbol to terminate the CREATE TRIGGER in isql. Use SET TERM before CREATE TRIGGER to specify a terminator other than a semicolon. After CREATE TRIGGER, include another SET TERM to change the terminator back to a semicolon.

See Also GEN_ID( ) , SET GENERATOR

CREATE INDEX

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

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

CREATE PROCEDURE

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

CREATE PROCEDURE name
[(param <datatype> [, param <datatype> …])]
[RETURNS <datatype> [, param <datatype> …])]
AS <procedure_body> [terminator]

<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)]

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.

Important Because each statement in a stored procedure body must be terminated by a semicolon, you must define a different symbol to terminate the CREATE PROCEDURE statement in isql. Use SET TERM before CREATE PROCEDURE to specify a terminator other than a semicolon. After the CREATE PROCEDURE statement, include another SET TERM to change the terminator back to a semicolon.

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.

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:

* department id

*

*Returns:

* total budget

* average budget

* min budget

* max budget */

SET TERM !! ;

CREATE PROCEDURE SUB_TOT_BUDGET (HEAD_DEPT CHAR(3))

RETURNS (tot_budget 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 !!

SET TERM ; !!

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.

CREATE ROLE

Creates a role.

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

CREATE SHADOW

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

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.

CREATE TABLE

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

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])]<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

[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.

· 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.

ⁿ 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.

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 statemnent 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 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.

CREATE TRIGGER

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

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

<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)]

<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.

Important Because each statement in the trigger body must be terminated by a semicolon, you must define a different symbol to terminate the trigger body itself. In isql, include a SET TERM statement before CREATE TRIGGER to specify a terminator other than a semicolon. After the body of the trigger, include another SET TERM to change the terminator back to a semicolon.

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.

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:

SET TERM !! ;

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 !!

SET TERM ; !!

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:

SET TERM !! ;

CREATE TRIGGER SET_CUST_NO FOR CUSTOMER

BEFORE INSERT AS

BEGIN

NEW.CUST_NO = GEN_ID(CUST_NO_GEN, 1);

END !!

SET TERM ; !!

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

SET TERM !! ;

CREATE TRIGGER POST_NEW_ORDER FOR SALES

AFTER INSERT AS

BEGIN

POST_EVENT 'new_order';

END !!

SET TERM ; !!

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.

CREATE VIEW

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

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.

DECLARE CURSOR

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

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

DECLARE CURSOR
(BLOB)

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

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)

DECLARE EXTERNAL
FUNCTION

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

DECLARE EXTERNAL FUNCTION name [datatype | CSTRING (int)
[, datatype | CSTRING (int) …]]
RETURNS {datatype [BY VALUE] | CSTRING (int)} [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 redelcare 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.

The module name does not need to include a path. However, the module must either be placed in ib_install_dir/UDF or must be listed in the InterBase configuration file.

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 and NetWare, the line does not include a drive letter:

EXTERNAL_FUNCTION_DIRECTORY \Mylibraries\InterBase

Note that beginning with InterBase 6, it is no longer sufficient to include a complete path name for the module in the DECLARE EXTERNAL FUNCTION statement. You must list the path in the InterBase configuration file if it is other than ib_install_dir/UDF. A path name is no longer useful in the DECLARE EXTERNAL FUNCTION statement.

The InterBase configuration file is called ibconfig on Windows machines, isc_config on UNIX machines, and isc_conf on Netware.

FOR NETWARE SERVERS Beginning with InterBase 5.6, the UDF library is statically linked to NetWare servers so that the UDFs are available as external functions. To make them available to a database on a Netware server, follow htese steps:

1. On a Windows client, connect to the database where you need the functions.

2. Run the ib_udf.sql script that is located in the ib_install_dir/Examples/UDF directory (InterBase 6 and later) or Examples/API directory (InterBase 5.6) using ISQL with the -i switch:

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.dll';

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.dll';

See Also DROP EXTERNAL FUNCTION

For more information about writing UDFs, see “Working with UDFs” in the Developer’s Guide.

DECLARE FILTER

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

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.

Important Do not use DECLARE FILTER when creating a database on a NetWare server. Blob filters cannot be created or used on NetWare servers.

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';

See Also DROP FILTER

For instructions on writing Blob filters, see the Embedded SQL Guide.

For more information about Blob subtypes, see the Data Definition Guide.

DECLARE STATEMENT

Identifies dynamic SQL statements before they are prepared and executed in an embedded program. Available in SQL.

DECLARE <statement> STATEMENT;

Description DECLARE STATEMENT names an SQL variable for a user-supplied SQL statement to prepare and execute at run time. DECLARE STATEMENT is not executed, so it does not produce run-time errors. The statement provides internal documentation.

Example The following embedded SQL statement declares Q1 to be the name of a string for preparation and execution.

EXEC SQL

DECLARE Q1 STATEMENT;

See Also EXECUTE , EXECUTE IMMEDIATE , PREPARE

DECLARE TABLE

Describes the structure of a table to the preprocessor, gpre, before it is created with CREATE TABLE. Available in SQL.

DECLARE table TABLE (<table_def>);

Description DECLARE TABLE causes gpre to store a table description. You must use it if you both create and populate a table with data in the same program. If the declared table already exists in the database or if the declaration contains syntax errors, gpre returns an error.

When a table is referenced at run time, the column descriptions and datatypes are checked against the description stored in the database. If the table description is not in the database and the table is not declared, or if column descriptions and datatypes do not match, the application returns an error.

DECLARE TABLE can include an existing domain in a column definition, but must give the complete column description if the domain is not defined at compile time.

DECLARE TABLE cannot include integrity constraints and column attributes, even if they are present in a subsequent CREATE TABLE statement.

Important DECLARE TABLE cannot appear in a program that accesses multiple databases.

Example The following embedded SQL statements declare and create a table:

EXEC SQL
DECLARE STOCK TABLE
(MODEL SMALLINT,
MODELNAME CHAR(10),
ITEMID INTEGER);

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

See Also CREATE DOMAIN , CREATE TABLE

DELETE

Removes rows in a table or in the active set of a cursor. Available in SQL, DSQL, and isql.

SQL and DSQL form:

Important Omit the terminating semicolon for DSQL.

DELETE [TRANSACTION transaction] FROM table
{[WHERE <search_condition>] | WHERE CURRENT OF cursor};

<search_condition> = Search condition as specified in SELECT.

isql form:

DELETE FROM TABLE [WHERE <search_condition>];

DELETE specifies one or more rows to delete from a table or updatable view. DELETE is one of the database privileges controlled by the GRANT and REVOKE statements.

The TRANSACTION clause can be used in multiple transaction SQL applications to specify which transaction controls the DELETE operation. The TRANSACTION clause is not available in DSQL or isql.

For searched deletions, the optional WHERE clause can be used to restrict deletions to a subset of rows in the table.

Important Without a WHERE clause, a searched delete removes all rows from a table.

When performing a positioned delete with a cursor, the WHERE CURRENT OF clause must be specified to delete one row at a time from the active set.

Examples The following isql statement deletes all rows in a table:

DELETE FROM EMPLOYEE_PROJECT;

The next embedded SQL statement is a searched delete in an embedded application. It deletes all rows where a host-language variable equals a column value.

EXEC SQL

DELETE FROM SALARY_HISTORY

WHERE EMP_NO = :emp_num;

The following embedded SQL statements use a cursor and the WHERE CURRENT OF option to delete rows from CITIES with a population less than the host variable, min_pop. They declare and open a cursor that finds qualifying cities, fetch rows into the cursor, and delete the current row pointed to by the cursor.

EXEC SQL

DECLARE SMALL_CITIES CURSOR FOR

SELECT CITY, STATE

FROM CITIES

WHERE POPULATION < :min_pop;

EXEC SQL

OPEN SMALL_CITIES;

EXEC SQL

FETCH SMALL_CITIES INTO :cityname, :statecode;

WHILE (!SQLCODE)

{EXEC SQL

DELETE FROM CITIES

WHERE CURRENT OF SMALL_CITIES;

EXEC SQL

FETCH SMALL_CITIES INTO :cityname, :statecode;}

EXEC SQL

CLOSE SMALL_CITIES;

See Also DECLARE CURSOR , FETCH , GRANT , OPEN , REVOKE , SELECT

For more information about using cursors, see the Embedded SQL Guide.

DESCRIBE

Provides information about columns that are retrieved by a dynamic SQL (DSQL) statement, or information about dynamic parameters that statement passes. Available in SQL.

DESCRIBE [OUTPUT | INPUT] statement
{INTO | USING} SQL DESCRIPTOR xsqlda;

Description DESCRIBE has two uses:

ⁿ As a describe output statement, DESCRIBE stores into an XSQLDA a description of the columns that make up the select list of a previously prepared statement. If the PREPARE statement included an INTO clause, it is unnecessary to use DESCRIBE as an output statement.

ⁿ As a describe input statement, DESCRIBE stores into an XSQLDA a description of the dynamic parameters that are in a previously prepared statement.

DESCRIBE is one of a group of statements that process DSQL statements.

Separate DESCRIBE statements must be issued for input and output operations. The INPUT keyword must be used to store dynamic parameter information.

Important When using DESCRIBE for output, if the value returned in the sqld field in the XSQLDA is larger than the sqln field, you must:

ⁿ Allocate more storage space for XSQLVAR structures.

ⁿ Reissue the DESCRIBE statement.

Note The same XSQLDA structure can be used for input and output if desired.

Example The following embedded SQL statement retrieves information about the output of a SELECT statement:

EXEC SQL

DESCRIBE Q INTO xsqlda

The next embedded SQL statement stores information about the dynamic parameters passed with a statement to be executed:

EXEC SQL

DESCRIBE INPUT Q2 USING SQL DESCRIPTOR xsqlda;

See Also EXECUTE , EXECUTE IMMEDIATE , PREPARE

For more information about DSQL programming and the XSQLDA, see the Embedded SQL Guide.

DISCONNECT

Detaches an application from a database. Available in SQL.

DISCONNECT {{ALL | DEFAULT} | dbhandle [, dbhandle] …]};

Description DISCONNECT closes a specific database identified by a database handle or all databases, releases resources used by the attached database, zeroes database handles, commits the default transaction if the gpre -manual option is not in effect, and returns an error if any non-default transaction is not committed.

Before using DISCONNECT, commit or roll back the transactions affecting the database to be detached.

To reattach to a database closed with DISCONNECT, reopen it with a CONNECT statement.

Examples The following embedded SQL statements close all databases:

EXEC SQL

DISCONNECT DEFAULT;

EXEC SQL

DISCONNECT ALL;

The next embedded SQL statements close the databases identified by their
handles:

EXEC SQL

DISCONNECT DB1;

EXEC SQL

DISCONNECT DB1, DB2;

See Also COMMIT , CONNECT , ROLLBACK , SET DATABASE

DROP DATABASE

Deletes the currently attached database. Available in isql.

DROP DATABASE;

Description DROP DATABASE deletes the currently attached database, including any associated secondary, shadow, and log files. Dropping a database deletes any data it contains.

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

Example The following isql statement deletes the current database:

DROP DATABASE;

See Also ALTER DATABASE , CREATE DATABASE

DROP DOMAIN

Deletes a domain from a database. Available in SQL, DSQL, and isql.

DROP DOMAIN 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 DROP DOMAIN removes an existing domain definition from a database.

If a domain is currently used in any column definition in the database, the DROP operation fails. To prevent failure, use ALTER TABLE to delete the columns based on the domain before executing DROP DOMAIN.

A domain may be dropped by its creator, the SYSDBA, and any users with operating system root privileges.

Example The following isql statement deletes a domain:

DROP DOMAIN COUNTRYNAME;

See Also ALTER DOMAIN , ALTER TABLE , CREATE DOMAIN

DROP EXCEPTION

Deletes an exception from a database. Available in DSQL and isql.

DROP EXCEPTION name

Description DROP EXCEPTION removes an exception from a database.

Exceptions used in existing procedures and triggers cannot be dropped.

Tip In isql, SHOW EXCEPTION displays a list of exceptions’ dependencies, the procedures and triggers that use the exceptions.

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

Example This isql statement drops an exception:

DROP EXCEPTION UNKNOWN_EMP_ID;

See Also ALTER EXCEPTION , ALTER PROCEDURE , ALTER TRIGGER , CREATE EXCEPTION , CREATE PROCEDURE , CREATE TRIGGER

DROP EXTERNAL
FUNCTION

Removes a user-defined function (UDF) declaration from a database. Available in SQL, DSQL, and isql.

DROP EXTERNAL FUNCTION 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 DROP EXTERNAL FUNCTION deletes a UDF declaration from a database. Dropping a UDF declaration from a database does not remove it from the corresponding UDF library, but it does make the UDF inaccessible from the database. Once the definition is dropped, any applications that depend on the UDF will return run-time errors.

A UDF can be dropped by its declarer, the SYSDBA user, or any users with operating system root privileges.

Important UDFs are not available for databases on NetWare servers. If a UDF is accidentally declared for a database on a NetWare server, DROP EXTERNAL FUNCTION should be used to remove the declaration.

Example This isql statement drops a UDF:

DROP EXTERNAL FUNCTION TOPS;

See Also DECLARE EXTERNAL FUNCTION

DROP FILTER

Removes a Blob filter declaration from a database. Available in SQL, DSQL, and isql.

DROP FILTER 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 DROP FILTER removes a Blob filter declaration from a database. Dropping a Blob filter declaration from a database does not remove it from the corresponding Blob filter library, but it does make the filter inaccessible from the database. Once the definition is dropped, any applications that depend on the filter will return run-time errors.

DROP FILTER fails and returns an error if any processes are using the filter.

A filter can be dropped by its creator, the SYSDBA user, or any user with operating system root privileges.

Important Blob filters are not available for databases on NetWare servers. If a Blob filter is accidentally declared for a database on a NetWare server, DROP FILTER should be used to remove the declaration.

Example This isql statement drops a Blob filter:

DROP FILTER DESC_FILTER;

See Also DECLARE FILTER

DROP INDEX

Removes an index from a database. Available in SQL, DSQL, and isql.

DROP INDEX 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 DROP INDEX removes a user-defined index from a database.

An index can be dropped by its creator, the SYSDBA user, or any user with operating system root privileges.

Important You cannot drop system-defined indexes, such as those for UNIQUE, PRIMARY KEY, and FOREIGN KEY.

An index in use is not dropped until it is no longer in use.

Example The following isql statement deletes an index:

DROP INDEX MINSALX;

See Also ALTER INDEX , CREATE INDEX

For more information about integrity constraints and system-defined indexes, see the Data Definition Guide.

DROP PROCEDURE

Deletes an existing stored procedure from a database. Available in DSQL, and isql.

DROP PROCEDURE name

Description DROP PROCEDURE removes an existing stored procedure definition from a database.

Procedures used by other procedures, triggers, or views cannot be dropped. Procedures currently in use cannot be dropped.

Tip In isql, SHOW PROCEDURE displays a list of procedures’ dependencies, the procedures, triggers, exceptions, and tables that use the procedures.

A procedure can be dropped by its creator, the SYSDBA user, or any user with operating system root privileges.

Example The following isql statement deletes a procedure:

DROP PROCEDURE GET_EMP_PROJ;

See Also ALTER PROCEDURE , CREATE PROCEDURE , EXECUTE PROCEDURE

DROP ROLE

Deletes a role from a database. Available in SQL, DSQL, and isql.

DROP 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 DROP ROLE deletes a role that was previously created using CREATE ROLE. Any privileges that users acquired or granted through their membership in the role are revoked.

A role can be dropped by its creator, the SYSDBA user, or any user with superuser privileges.

Example The following isql statement deletes a role from its database:

DROP ROLE administrator;

See Also CREATE ROLE , GRANT , REVOKE

DROP SHADOW

Deletes a shadow from a database. Available in SQL, DSQL, and isql.

DROP SHADOW set_num;

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 DROP SHADOW deletes a shadow set and detaches from the shadowing process. The isql SHOW DATABASE command can be used to see shadow set numbers for a database.

A shadow can be dropped by its creator, the SYSDBA user, or any user with operating system root privileges.

Example The following isql statement deletes a shadow set from its database:

DROP SHADOW 1;

See Also CREATE SHADOW

DROP TABLE

Removes a table from a database. Available in SQL, DSQL, and isql.

DROP TABLE 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 DROP TABLE removes a table’s data, metadata, and indexes from a database. It also drops any triggers that reference the table.

A table referenced in an SQL expression, a view, integrity constraint, or stored procedure cannot be dropped. A table used by an active transaction is not dropped until it is free.

Note When used to drop an external table, DROP TABLE only removes the table definition from the database. The external file is not deleted.

A table can be dropped by its creator, the SYSDBA user, or any user with operating system root privileges.

Example The following embedded SQL statement drops a table:

EXEC SQL

DROP TABLE COUNTRY;

See Also ALTER TABLE , CREATE TABLE

DROP TRIGGER

Deletes an existing user-defined trigger from a database. Available in DSQL and isql.

DROP TRIGGER name

Description DROP TRIGGER removes a user-defined trigger definition from the database. System-defined triggers, such as those created for CHECK constraints, cannot be dropped. Use ALTER TABLE to drop the CHECK clause that defines the trigger.

Triggers used by an active transaction cannot be dropped until the transaction is terminated.

A trigger can be dropped by its creator, the SYSDBA user, or any user with operating system root privileges.

Tip To inactivate a trigger temporarily, use ALTER TRIGGER and specify INACTIVE in the header.

Example The following isql statement drops a trigger:

DROP TRIGGER POST_NEW_ORDER;

See Also ALTER TRIGGER , CREATE TRIGGER

DROP VIEW

Removes a view definition from the database. Available in SQL, DSQL, and isql.

DROP VIEW 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 DROP VIEW enables a view’s creator to remove a view definition from the database if the view is not used in another view, stored procedure, or CHECK constraint definition.

A view can be dropped by its creator, the SYSDBA user, or any user with operating system root privileges.

Example The following isql statement removes a view definition:

DROP VIEW PHONE_LIST;

See Also CREATE VIEW

END DECLARE
SECTION

Identifies the end of a host-language variable declaration section. Available in SQL.

END DECLARE SECTION;

Description END DECLARE SECTION is used in embedded SQL applications to identify the end of host-language variable declarations for variables used in subsequent SQL statements.

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

EXEC SQL

BEGIN DECLARE SECTION;

BASED_ON EMPLOYEE.SALARY salary;

EXEC SQL

END DECLARE SECTION;

See Also BASED ON , BEGIN DECLARE SECTION

EVENT INIT

Registers interest in one or more events with the InterBase event manager. Available in SQL.

EVENT INIT request_name [dbhandle]
[('string' | :variable [, 'string' | :variable …]);

Description EVENT INIT is the first step in the InterBase two-part synchronous event mechanism:

1. EVENT INIT registers an application’s interest in an event.

2. EVENT WAIT causes the application to wait until notified of the event’s occurrence.

EVENT INIT registers an application’s interest in a list of events in parentheses. The list should correspond to events posted by stored procedures or triggers in the database. If an application registers interest in multiple events with a single EVENT INIT, then when one of those events occurs, the application must determine which event occurred.

Events are posted by a POST_EVENT call within a stored procedure or trigger.

The event manager keeps track of events of interest. At commit time, when an event occurs, the event manager notifies interested applications.

Example The following embedded SQL statement registers interest in an event:

EXEC SQL

EVENT INIT ORDER_WAIT EMPDB ('new_order');

See Also CREATE PROCEDURE , CREATE TRIGGER , EVENT WAIT , SET DATABASE

For more information about events, see the Embedded SQL Guide.

EVENT WAIT

Causes an application to wait until notified of an event’s occurrence. Available in SQL.

EVENT WAIT request_name;

Description EVENT WAIT is the second step in the InterBase two-part synchronous event mechanism. After a program registers interest in an event, EVENT WAIT causes the process running the application to sleep until the event of interest occurs.

Examples The following embedded SQL statements register an application event name and indicate the program is ready to receive notification when the event occurs:

EXEC SQL

EVENT INIT ORDER_WAIT EMPDB ('new_order');

EXEC SQL

EVENT WAIT ORDER_WAIT;

See Also EVENT INIT

For more information about events, see the Embedded SQL Guide.

EXECUTE 

Executes a previously prepared dynamic SQL (DSQL) statement. Available in SQL.

EXECUTE [TRANSACTION transaction] statement
[USING SQL DESCRIPTOR xsqlda] [INTO SQL DESCRIPTOR xsqlda];

Description EXECUTE carries out a previously prepared DSQL statement. It is one of a group of statements that process DSQL statements.

Before a statement can be executed, it must be prepared using the PREPARE statement. The statement can be any SQL data definition, manipulation, or transaction management statement. Once it is prepared, a statement can be executed any number of times.

The TRANSACTION clause can be used in SQL applications running multiple, simultaneous transactions to specify which transaction controls the EXECUTE operation.

USING DESCRIPTOR enables EXECUTE to extract a statement’s parameters from an XSQLDA structure previously loaded with values by the application. It need only be used for statements that have dynamic parameters.

INTO DESCRIPTOR enables EXECUTE to store return values from statement execution in a specified XSQLDA structure for application retrieval. It need only be used for DSQL statements that return values.

Note If an EXECUTE statement provides both a USING DESCRIPTOR clause and an INTO DESCRIPTOR clause, then two XSQLDA structures must be provided.

Example The following embedded SQL statement executes a previously prepared DSQL statement:

EXEC SQL

EXECUTE DOUBLE_SMALL_BUDGET;

The next embedded SQL statement executes a previously prepared statement with parameters stored in an XSQLDA:

EXEC SQL

EXECUTE Q USING DESCRIPTOR xsqlda;

The following embedded SQL statement executes a previously prepared statement with parameters in one XSQLDA, and produces results stored in a second XSQLDA:

EXEC SQL

EXECUTE Q USING DESCRIPTOR xsqlda_1 INTO DESCRIPTOR xsqlda_2;

See Also DESCRIBE , EXECUTE IMMEDIATE , PREPARE

For more information about DSQL programming and the XSQLDA, see the Embedded SQL Guide.

EXECUTE IMMEDIATE

Prepares a dynamic SQL (DSQL) statement, executes it once, and discards it. Available in SQL.

EXECUTE IMMEDIATE [TRANSACTION transaction]
{:variable | 'string'} [USING SQL DESCRIPTOR xsqlda];

Description EXECUTE IMMEDIATE prepares a DSQL statement stored in a host-language variable or in a literal string, executes it once, and discards it. To prepare and execute a DSQL statement for repeated use, use PREPARE and EXECUTE instead of EXECUTE IMMEDIATE.

The TRANSACTION clause can be used in SQL applications running multiple, simultaneous transactions to specify which transaction controls the EXECUTE IMMEDIATE operation.

The SQL statement to execute must be stored in a host variable or be a string literal. It can contain any SQL data definition statement or data manipulation statement that does not return output.

USING DESCRIPTOR enables EXECUTE IMMEDIATE to extract the values of a statement’s parameters from an XSQLDA structure previously loaded with appropriate values.

Example The following embedded SQL statement prepares and executes a statement in a host variable:

EXEC SQL

EXECUTE IMMEDIATE :insert_date;

See Also DESCRIBE , EXECUTE IMMEDIATE , PREPARE

For more information about DSQL programming and the XSQLDA, see the Embedded SQL Guide.

EXECUTE PROCEDURE

Calls a stored procedure. Available in SQL, DSQL, and isql.

SQL form:

EXECUTE PROCEDURE [TRANSACTION transaction]
name [:param [[INDICATOR]:indicator]]
[, :param [[INDICATOR]:indicator] …]
[RETURNING_VALUES :param [[INDICATOR]:indicator]
[, :param [[INDICATOR]:indicator] …]];

DSQL form:

EXECUTE PROCEDURE name [param [, param …]]
[RETURNING_VALUES param [, param …]]

isql form:

EXECUTE PROCEDURE name [param [, param …]]

Description EXECUTE PROCEDURE calls the specified stored procedure. If the procedure requires input parameters, they are passed as host-language variables or as constants. If a procedure returns output parameters to an SQL program, host variables must be supplied in the RETURNING_VALUES clause to hold the values returned.

In isql, do not use the RETURN clause or specify output parameters. isql will automatically display return values.

Note In DSQL, an EXECUTE PROCEDURE statement requires an input descriptor area if it has input parameters and an output descriptor area if it has output parameters.

In embedded SQL, input parameters and return values may have associated indicator variables for tracking NULL values. Indicator variables are integer values that indicate unknown or NULL values of return values.

An indicator variable that is less than zero indicates that the parameter is unknown or NULL. An indicator variable that is zero or greater indicates that the associated parameter is known and not NULL.

Examples The following embedded SQL statement demonstrates how the executable procedure, DEPT_BUDGET, is called from embedded SQL with literal parameters:

EXEC SQL

EXECUTE PROCEDURE DEPT_BUDGET 100 RETURNING_VALUES :sumb;

The next embedded SQL statement calls the same procedure using a host variable instead of a literal as the input parameter:

EXEC SQL

EXECUTE PROCEDURE DEPT_BUDGET :rdno RETURNING_VALUES :sumb;

See Also ALTER PROCEDURE , CREATE PROCEDURE , DROP PROCEDURE

For more information about indicator variables, see the Embedded SQL Guide.

FETCH

Retrieves the next available row from the active set of an opened cursor. Available in SQL and DSQL.

SQL form:

FETCH cursor
[INTO :hostvar [[INDICATOR] :indvar]
[, :hostvar [[INDICATOR] :indvar] …]];

DSQL form:

FETCH cursor {INTO | USING} SQL DESCRIPTOR xsqlda

Blob form: See FETCH (BLOB).

Description FETCH retrieves one row at a time into a program from the active set of a cursor. The first FETCH operates on the first row of the active set. Subsequent FETCH statements advance the cursor sequentially through the active set one row at a time until no more rows are found and SQLCODE is set to 100.

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. There are four related cursor statements:

The number, size, datatype, and order of columns in a FETCH must be the same as those listed in the query expression of its matching DECLARE CURSOR statement. If they are not, the wrong values can be assigned.

Examples The following embedded SQL statement fetches a column from the active set of a cursor:

EXEC SQL

FETCH PROJ_CNT INTO :department, :hcnt;

See Also CLOSE , DECLARE CURSOR , DELETE , FETCH (BLOB) , OPEN

For more information about cursors and XSQLDA, see the Embedded SQL Guide.

FETCH (BLOB)

Retrieves the next available segment of a Blob column and places it in the specified local buffer. Available in SQL.

FETCH cursor INTO
[:<buffer> [[INDICATOR] :segment_length];