SQL Reference Guide

Previous Topic

Next Topic


add [an ODBC data source]

Make a data source available to ODBC through the Add operation of the ODBC Administrator utility. Adding a data source tells ODBC where a specific database resides and which ODBC driver to use to access it. Adding a data source also invokes a setup dialog box for the particular driver so you can provide other details the driver needs to connect to the database.


The user name for the database administrator in a FairCom DB SQL environment. The admin user is the owner of all system tables. Users must qualify references to system tables as admin.tablename.


A temporary name for a table or column specified in the FROM clause of an FairCom DB SQL query expression. Also called correlation name. Derived tables and search conditions that join a table with itself must specify an alias. Once a query specifies an alias, references to the table or column must use the alias and not the underlying table or column name.


A special kind of Java program whose compiled class files a Java-enabled browser can download from the Internet and run.


(American Standard Code for Information Interchange) A 7-bit character set that provides 128 character combinations.


Machine-independent code generated by the Java compiler and executed by the Java interpreter.

candidate key

Another term for unique key.


Number of rows in a result table.

Cartesian product

Also called cross-product. In a query expression, the result table generated when a FROM clause lists more than one table but specifies no join conditions. In such a case, the result table is formed by concatenating every row of every table with all other rows in all tables. Typically, Cartesian products are not useful and are slow to process.


Generally, in client/server systems, the part of the system that sends requests to servers and processes the results of those requests.

client character set

A client character set is a character set used by a client application for representing FairCom DB SQL statements and data


A temporary storage area for text and graphics that you want to copy from one location or application to another. Most Windows applications support the clipboard through cut, copy, and paste operations.


The rules used to control how character strings in a character set compare with each other. Each character set specifies a collating sequence that defines relative values of each character for comparing, merging and sorting character strings.

column alias

An alias specified for a column. See alias.


Part of a FairCom DB SQL table definition that restricts the values that can be stored in a table. When you insert, delete, or update column values, the constraint checks the new values against the conditions specified by the constraint. If the value violates the constraint, it generates an error. Along with triggers, constraints enforce referential integrity by insuring that a value stored in the foreign key of a table must either be null or be equal to some value in the matching unique or primary key of another table.

correlation name

Another term for alias.

cross product

Another term for Cartesian product.

data dictionary

Another term for system catalog.

data source

See ODBC data source

database administrator

The user name admin is the database administrator.

delete [an ODBC data source]

Remove information about an ODBC data source through the Delete operation of the ODBC Administrator utility. Deleting a data source does not delete the database it corresponds to, but removes information about the database’s location from the registry (on Windows NT) or the odbc.ini file (on Unix).

delimited identifiers

Names in FairCom DB SQL statements enclosed in double quotation marks (“”). Enclosing a name in double quotation marks preserves the case of the name and allows it to include reserved words and special characters. Subsequent references to a delimited identifier must also use enclosing double quotation marks.

derived table

A virtual table specified as a query expression in the FROM clause of another query expression.

driver manager

See JDBC driver manager and ODBC driver manager.

foreign key

A column or columns in a table whose values must either be null or equal to some value in a corresponding column (called the primary key) in another table. Use the REFERENCES clause in the FairCom DB SQL CREATE TABLE statement to create foreign keys.

form of use
The storage format for characters in a character set. Some character sets, such as ASCII, require one byte (octet) for each character. Others, such as Unicode, use two bytes, and are called multi-octet character sets.

input parameter

In a stored procedure specification, an argument that an application must pass when it calls the stored procedure. In an FairCom DB SQL statement, a parameter marker in the statement string that acts as a placeholder for a value that will be substituted when the statement executes.


In Java, a definition of a set of methods that one or more objects will implement. Interfaces declare only methods and constants, not variables. Interfaces provide multiple-inheritance capabilities.

Java snippet

See snippet.


Java Database Connectivity: a part of the Java language that allows applications to embed standard SQL statements and access any database that implements a JDBC driver.

JDBC driver

Database-specific software that receives calls from the JDBC driver manager, translates them into a form that the database can process, and returns data to the application.

JDBC driver manager

A Java class that implements methods to route calls from a JDBC application to the appropriate JDBC driver for a particular JDBC URL.


A relational operation that combines data from two tables.


A main component of FairCom DB SQL. FairCom DB SQL includes several managers, including the FairCom DB SQL statement manager, parser, and optimizer.


Data that details the structure of tables and indexes in the proprietary storage system. The FairCom DB SQL engine stores metadata in the system catalog.


A group of 8 bits. Synonymous with byte, and often used in descriptions of character-set encoding format.

ODBC Administrator

On Windows NT, a Microsoft-supplied utility to add and delete ODBC data sources and drivers. The Administrator maintains information in the system registry.

ODBC application

Any program that calls ODBC functions and uses them to issue FairCom DB SQL statements. Many vendors have added ODBC capabilities to their existing Windows-based tools.

ODBC data source

In ODBC terminology, the technical information needed to access data-the driver name, network address, network software, and so on. Before applications can access a database through ODBC, you use the ODBC Administrator (or, on Unix, edit the odbc.ini file) to add a data source for that database. More than one data source name can refer to the same database, and deleting a data source does not delete the associated database.

ODBC driver

Vendor-supplied software that processes ODBC function calls for a specific data source. The driver connects to the data source, translates the standard FairCom DB SQL statements into syntax the data source can process, and returns data to the application. FairCom DB SQL includes an ODBC driver that provides access to FairCom DB SQL.

ODBC driver manager

On Windows NT, a Microsoft-supplied program that routes calls from an application to the appropriate ODBC driver for a data source.


Within FairCom DB SQL the manager that analyzes costs and statistics associated with the statement and converts the relational algebra tree to the most efficient form for execution. The optimizer stores the trees for later use.

output parameter

In a stored procedure specification, an argument in which the stored procedure returns a value after it executes.


A group of related Java classes and interfaces, like a class library in C++. The Java development environment includes many packages of classes that procedures can import. The Java runtime system automatically imports the java.lang package. Stored procedures must explicitly import other classes by specifying them in the IMPORT clause of a CREATE PROCEDURE statement.

parameter marker

A question mark (?) in a procedure call or FairCom DB SQL statement string that acts as a placeholder for an input or output parameter supplied at runtime when the procedure executes. The CALL statement (or corresponding ODBC or JDBC escape clause) uses parameter markers to pass parameters to stored procedures. The SQLIStatement, SQLPStatement, and SQLCursor objects use them within procedures.

postfix notation

Notation in which the numbers precede the operation. For example, 2 + 2 is expressed as 2 2 +, and 10 - 3 * 4 would be 10 3 4 * -. If a storage manager supports processing of expressions, FairCom DB SQL passes them to the storage manager using postfix notation.

primary key

A subset of the fields in a table, characterized by the constraint that no two records in a table may have the same primary key value, and that no fields of the primary key may have a null value. Primary keys are specified in a CREATE TABLE statement.

procedure body

In a stored procedure, the Java code between the BEGIN and END keywords of a CREATE PROCEDURE statement.

procedure result set

In a stored procedure, a set of data rows returned to the calling application. The number and data types of columns in the procedure result set are specified in the RESULT clause of the CREATE PROCEDURE statement. The procedure can transfer data from an FairCom DB SQL result set to the procedure result set or it can store data generated internally. A stored procedure can have only one procedure result set.

procedure specification

In a CREATE PROCEDURE statement, the clauses preceding the procedure body that specify the procedure name, any input and output parameters, any result set columns, and any Java packages to import.

procedure variable

A Java variable declared within the body of a stored procedure, as compared to a procedure input parameter or output parameter, which are declared outside the procedure body and are visible to the application that calls the stored procedure.

query expression

The fundamental element in FairCom DB SQL syntax. Query expressions specify a result table derived from some combination of rows from the tables or views identified in the FROM clause of the expression. Query expressions are the basis of SELECT, CREATE VIEW, and INSERT statements, and can be used in some expressions and search conditions.

referential integrity

The condition where the value stored in a database table’s foreign key must either be null or be equal to some value in another table’s the matching unique or primary key. FairCom DB SQL provides two mechanisms to enforce referential integrity: constraints specified as part of CREATE TABLE statements prevent updates that violate referential integrity, and triggers specified in CREATE TRIGGER statements execute a stored procedure to enforce referential integrity.


The set of characters allowed in a character set.

result set

In a stored procedure, either an FairCom DB SQL result set or a procedure result set. More generally, another term for result table.

result table

A temporary table of values derived from columns and rows of one or more tables that meet conditions specified by a query expression.

row identifier

Another term for tuple identifier.

search condition

FairCom DB SQL syntax element that specifies a condition that is true or false about a given row or group of rows. Query expressions and UPDATE statements can specify a search condition. The search condition restricts the number of rows in the result table for the query expression or UPDATE statement. Search conditions contain one or more predicates. Search conditions follow the WHERE or HAVING keywords in FairCom DB SQL statements.


The fraction of a table’s rows returned by a query.


Generally, in client/server systems, the part of the system that receives requests from clients and responds with results to those requests.


In a stored procedure, the sequence of Java statements between the BEGIN and END keywords in the CREATE PROCEDURE (or CREATE TRIGGER) statement. The Java statements become a method in a class FairCom DB SQL creates and submits to the Java compiler.

SQL diagnostics area

A data structure that contains information about the execution status (success, error or warning conditions) of the most recent FairCom DB SQL statement. The SQL-92 standard specified the diagnostics area as a standardized alternative to widely varying implementations of the SQLCA. FairCom DB SQL supports both the SQLCA and the FairCom DB SQL diagnostics area. The GET DIAGNOSTICS statement returns information about the diagnostics area to an application, including the value of the SQLSTATE status parameter.

SQL engine

The core component of the FairCom DB SQL environment. FairCom DB SQL receives requests from applications, processes them, and returns results. FairCom DB SQL calls c-treeDB to convey requests to FairCom DB.

SQL result set

In a stored procedure, the set of data rows generated by an FairCom DB SQL statement (SELECT and, in some cases, CALL).


FairCom DB SQL Communications area: A data structure that contains information about the execution status (success, error or warning conditions) of the most recent c-treeSQL statement. The SQLCA includes an SQLCODE field. The SQLCA provides the same information as the FairCom DB SQL diagnostics area, however, is not compliant with the SQL-92 standard. FairCom DB SQL supports both the SQLCA and the FairCom DB SQL diagnostics area.


An integer status parameter whose value indicates the condition status returned by the most recent FairCom DB SQL statement. An SQLCODE value of zero means success, a positive value means warning, and a negative value means an error status. SQLCODE is superseded by SQLSTATE in the SQL-92 standard. Applications declare either SQLSTATE or SQLCODE, or both. FairCom DB SQL returns the status to SQLSTATE or SQLCODE after execution of each FairCom DB SQL statement.


A 5-character status parameter whose value indicates the condition status returned by the most recent FairCom DB SQL statement. SQLSTATE is specified by the SQL-92 standard as a replacement for the SQLCODE status parameter (which was part of SQL-89). SQLSTATE defines many more specific error conditions than SQLCODE, which allows applications to implement more portable error handling. Applications declare either SQLSTATE or SQLCODE, or both. FairCom DB SQL returns the status to SQLSTATE or SQLCODE after execution of each FairCom DB SQL statement.

storage environment

The combination of storage systems which have implemented the storage interfaces. One possible combination of storage systems in an implementation is the flat-file and main-memory storage system, with a proprietary database containing user data.

stored procedure

A snippet of Java source code embedded in an CREATE PROCEDURE statement. The source code can use all standard Java features as well as use FairCom DB SQL supplied Java classes for processing any number of FairCom DB SQL statements.

system catalog

Tables created by FairCom DB SQL that store information about tables, columns, and indexes that make up the database. FairCom DB SQL creates and manages the system catalog.

system tables

Another term for system catalog.


Another term for tuple identifier.


A group of operations whose changes can be made permanent or undone only as a unit to protect against data corruption.


A special type of stored procedure that helps insure referential integrity for a database. Like stored procedures, triggers also contain Java source code (embedded in a CREATE TRIGGER statement) and use FairCom DB SQL Java classes. However, triggers are automatically invoked (“fired”) by certain FairCom DB SQL operations (an insert, update, or delete operation) on the trigger’s target table.

trigger action time

The BEFORE or AFTER keywords in a CREATE TRIGGER statement. The trigger action time specifies whether the actions implemented by the trigger execute before or after the triggering INSERT, UPDATE, or DELETE statement.

trigger event

The statement that causes a trigger to execute. Trigger events can be FairCom DB SQL INSERT, UPDATE, or DELETE statements that affect the table for which a trigger is defined.

triggered action

The Java code within the BEGIN END clause of a CREATE TRIGGER statement. The code implements actions to be completed when a triggering statement specifies the target table.

tuple identifier

A unique identifier for a tuple (row) in a table. A tuple identifier for the tuple that was inserted is returned after an insert operation. FairCom DB SQL passes a tuple identifier to the delete, update, and fetch stubs to indicate which tuple is affected. The FairCom DB SQL scalar function ROWID and related functions return tuple identifiers to applications.


A superset of the ASCII character set that uses two bytes for each character rather than ASCII’s 7-bit representation. Able to handle 65,536 character combinations instead of ASCII’s 128, Unicode includes alphabets for many of the world’s languages. The first 128 codes of Unicode are identical to ASCII, with a second-byte value of zero.

unique key

A column or columns in a table whose value (or combination of values) must be unique. Use the UNIQUE clause of the SQL CREATE TABLE statement to create unique keys. Unique keys are also called candidate keys.


In general, a Universal Resource Locator used to specify protocols and locations of items on the Internet. In JDBC, a database connection string in the form jdbc:subprotocol:subname. The FairCom DB SQL JDBC Driver format for database URLs is jdbc:ctree:T:host_name:db_name.

utility class

A set of utility functions that a storage manager uses to assemble and disassemble data elements passed through the storage interfaces.


A virtual table that recreates the result table specified by a SELECT statement. No data is stored in a view, but other queries can refer to it as if it were a table containing data corresponding to the result table it specifies.

virtual machine

The Java specification for a hardware-independent and portable language environment. Java language compilers generate code that can execute on a virtual machine. Implementations of the Java virtual machine for specific hardware and software platforms allow the same compiled code to execute without modification.

virtual table

A table of values that is not physically stored in a database, but instead derived from columns and rows of other tables. FairCom DB SQL generates virtual tables in its processing of query expressions: the FROM, WHERE, GROUP BY and HAVING clauses each generate a virtual table based on their input.