Progress
Embedded SQL-92
Guide and Reference


Glossary

Active Set

The collection of rows that SQL identifies when it opens a cursor and executes the query associated with the cursor. Also called a result set.

Atomicity

The property of transactions that either all the operations in a transaction are completed (if the transaction is committed) or none are completed (if the transaction is rolled back).

Candidate Key

Another term for UNIQUE key.

Commit

Make permanent all changes made during a transaction.

Constraint

Part of an SQL table or column definition that restricts the values that can be stored in a table or column. When you INSERT, DELETE, or UPDATE values, the constraint checks the new values against the conditions specified in the constraint. If the value violates the constraint, it generates an error. Constraints enforce REFERENTIAL INTEGRITY by ensuring 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.

Current Row

The current row of the active set of an open cursor. After an OPEN statement, the cursor is positioned just before the first row of the active set. The first FETCH statement advances the cursor position and makes the first row the current row. Subsequent FETCH statements make subsequent rows the current row.

Cursor

The active set defined by the query in a DECLARE CURSOR statement. Host programs use cursors to retrieve multiple rows of data returned by queries.

Cycle

The process of creating database tables when the first table has a foreign key that references the second table, the second table has a foreign key that references the third table and so on, and the last table has a foreign key that references the first table.

Data Control Language (DCL) Statements

SQL-92 GRANT and REVOKE statements. DCL statements control access to data and the rights to issue DCL statements.

Data Definition Language (DDL) Statements

SQL-92 CREATE, ALTER, and DROP statements used to manage tables, views, indexes, and other database objects.

Data Manipulation Language (DML) Statements

SQL-92 SELECT, INSERT, UPDATE, and DELETE statements that access or modify data.

Database Storage Representation

The format used to store a value in the database. For some data types this format is different than host language representation.

Durability

The characteristic of transactions that requires that all changes made during a transaction be permanent after the transaction is committed.

Dynamic SQL

A set of special SQL-92 statements (PREPARE, DESCRIBE, EXECUTE, and EXECUTE IMMEDIATE) and data structures (SQLCA and SQLDA) that allow programs to accept or generate SQL-92 statements at run time. Such dynamically-generated statements are not necessarily part of the program source code, but can be generated at run time.

Embedded SQL

SQL-92 statements that are embedded within a host language program. The ESQL precompiler translates embedded SQL-92 statements to equivalent C Language calls to routines in the Progress SQL-92 application programming interface.

ESQLC

The Progress Embedded SQL-92 precompiler for C host programs, and the command-line syntax to invoke it.

Exception Handler

Host program code that tests for a variety of possible errors and specifies what action will be taken if they arise.

Exclusive Locks

Locks that SQL-92 acquires on rows that have been modified by a transaction. Exclusive locks prevent other transactions from either reading or modifying the rows until the transaction is committed or rolled back.

Foreign Key

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

Host Language

Any programming language in which SQL statements can be embedded for database access. The ESQL precompiler supports embedding SQL statements in C Language programs.

Host Language Representation

The format used by a host language to represented values. This format is different from the database storage representation used by Progress SQL-92 for some data types. In some cases, applications must explicitly convert between data types to insert data or manipulate data retrieved from the database.

Host Program

An application program in which SQL-92 statements are embedded.

Host Variable

Any host language variable that is used in embedded SQL-92 statements. Programs must declare host variables in the DECLARE SECTION. Host variables can be declared as a database, host-language, or user-defined types. Depending on how they are used in an SQL-92 statement, host variables are either INPUT, where values stored by the program are used as an expression in an SQL-92 statement, or OUTPUT, where SQL-92 stores values returned from queries for use by the program.

Index

A database structure that speeds access to particular rows in a table. Indexes specify one or more columns as an index key. Typically queries that use the index key retrieve data faster than those that do not take advantage of an index.

Indicator Variable

A variable that is used to represent good value, truncation, or NULL VALUE in an application program. An indicator variable must be associated with a host variable. If the OUTPUT value retrieved by a query is null, SQL stores a negative in the indicator variable. Programs set INPUT indicator variables to -1 to specify a NULL VALUE for insertion into the database. Programs declare indicator variables as data type LONG or INTEGER.

Input Host Variable

A host variable that is used as INPUT to an embedded SQL-92 statement. Embedded SQL-92 statements can refer to host variables anywhere they can refer to an expression. Host programs use input variables to provide values to INSERT and UPDATE data in the database, and as arguments to search conditions.

Input SQLDA

An SQLDA (SQL Descriptor Area) used by a dynamic SQL-92 program to determine the number and data type of input parameters of an SQL-92 statement, and to store values for those input parameters. DESCRIBE, BIND VARIABLES, EXECUTE, and OPEN statements can specify an input SQLDA that SQL-92 uses to store information about input parameters.

Integrity Constraint

Another term for a category of constraints.

Isolation Level

The degree by which a transaction is isolated from the database modification operations of other concurrently active transactions.

Null Value

The absence of a value. Host programs use indicator variables or SQL-92 constructs to specify NULL VALUES and to retrieve NULL VALUES from the database.

Output Host Variable

A host variable that SQL-92 uses to store results from a query.

Output SQLDA

An SQLDA (SQL Descriptor Area) used by a dynamic SQL-92 program to determine the number and data type of columns in the result set returned by a query, and to retrieve values of that result set. DESCRIBE, SELECT LIST, and FETCH statements can specify an output SQLDA that SQL-92 uses to store the column information and data.

Parameter Marker

Question marks (?) in the statement string of a PREPARE statement. Parameter markers act as placeholders for input variables in dynamic SQL statements.

Precompiler

A translator that translates embedded SQL-92 statements in a host program to the equivalent C Language calls to functions in the Progress SQL-92 application programming interface (API).

Primary Key

A subset of the fields in a table, characterized by the constraint that no two records in a table can have the same PRIMARY KEY value, and that no fields of the PRIMARY KEY can have a NULL VALUE. You specify PRIMARY KEYS in a CREATE TABLE statement.

Referential Constraint

Another term for a category of constraints.

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 matching UNIQUE or PRIMARY KEY. Constraints specified as part of CREATE TABLE statements prevent updates that violate referential integrity.

Relation

Another term for table.

Result Set

Another term for active set.

Result Table

A temporary table of values returned by a query.

Rollback

Undo all changes made during a transaction.

Share Locks

Locks that SQL-92 acquires on rows that have been read by a transaction. Share locks allow other transactions to read the row but prevent them from modifying the row until this transaction is either committed or rolled back.

SQLCA (SQL Communication Area)

A host structure that SQL-92 uses to store information about the execution of an SQL-92 statement. The SQLCA contains information about the most recently executed SQL statement.

SQLCODE

One of the fields in the SQLCA that contains a LONG INTEGER value indicating the status of the execution of an SQL-92 statement.

SQLDA (SQL Descriptor Area)

A host structure used in dynamic SQL-92 programs. The host program uses the SQLDA to determine the number and data types of INPUT and OUTPUT parameters of a dynamically-generated SQL-92 statement. SQL-92 uses the SQLDA to determine where to retrieve INPUT parameter values or to store OUTPUT query results.

Statement String

In dynamic SQL-92, the SQL-92 statement used as an argument to the EXECUTE IMMEDIATE and PREPARE statements.

Static SQL Statement

An SQL-92 statement that is known to the application at compile time, as opposed to a SQL-92 statement that is generated at run time.

Substitution Name

In the statement string of a PREPARE statement, a name preceded by a colon. Substitution names do not refer to host-language variable names, but act only as placeholders for INPUT variables in dynamic SQL-92 statements.

Table

The representation of data in a relational database as a collection of columns and rows. Also called a relation.

Transaction

A group of database operations whose changes can be made permanent or undone only as a unit.

Transaction Isolation Level

Another term for isolation level.

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-92 CREATE TABLE statement to create UNIQUE KEYS. UNIQUE KEYS are also called CANDIDATE KEYS.

View

A virtual table that re-creates 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.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095