Overview

See Also

The Relativity ODBC driver allows you to open and query your COBOL data files through the Open Database Connectivity (ODBC) interface.

The application/driver architecture is:

Application
|
ODBC Driver Manager (odbc32.dll)
|
Relativity DBMS ODBC Driver (reldbm32.dll)
|
Relativity File Manager Interface (relfxx32.dll)
|
COBOL File Manager (e.g., mffh.dll)
|
Catalog and Your COBOL Data Files

The Relativity ODBC driver is constructed from two components: the Simba SQL Engine and the COBOL Relational Data Manager (CRDM). The application communicates with the Simba SQL Engine (via the ODBC Driver Manager). The CRDM uses the the catalog and the COBOL File Manager to present the COBOL data files as a relational database.

See Also

For All Users

Adding, Modifying, and Deleting a Relativity Data Source

Connecting to a Relativity Data Source

Hardware and Software Requirements

Using the Relativity ODBC Driver


Hardware and Software Requirements

To access your data, you must have:

  • The Relativity Data Manager (ODBC driver)
  • Your database files
  • Microsoft Windows XP, Windows Vista, Windows 7 or Windows 8.
  • The ODBC Driver Manager 3.5 or later (odbc32.dll).

To add, modify, or delete drivers or data sources, you should have the ODBC Control Panel option installed on your computer.


Adding, Modifying, and Deleting a Relativity Data Source

See Also

Before you can access data with the Relativity ODBC driver, you must add a data source for it. The Relativity ODBC driver uses the information you enter to access the data. You can change or delete a data source at any time.

To add a Relativity data source

  1. On the Start menu, select Control Panel. In the Control Panel window, click (or double-click on Windows XP) Administrative Tools. Then, the double-click the the Data Sources (ODBC) - 32-bit icon.
  2. In the Data Source Administrator dialog box, select either the User DSN or System DSN tab.
  3. Click the Add button.
  4. In the Add Data Source dialog box, select "Relativity (*.rcg)" from the Installed ODBC Drivers list and choose OK.
  5. When the Relativity data source setup dialog box appears, enter information in the dialog to set up the new data source.

To modify a Relativity data source

  1. On the Start menu, select Control Panel. In the Control Panel window, click (or double-click on Windows XP) Administrative Tools. Then, the double-click the Data Sources (ODBC) - 32-bit icon.
  2. In the Data Source Administrator dialog box, select the data source from the User Data Sources or System Data Sources list.
  3. Choose the Configure button.
  4. In the Relativity data source setup dialog box, enter information to set up the data source.

To delete a Relativity data source

  1. On the Start menu, select Control Panel. In the Control Panel window, click (or double-click on Windows XP) Administrative Tools. Then, the double-click the Data Sources (ODBC) - 32-bit icon.
  2. In the Data Source Administrator dialog box, select the data source from the User Data Sources or System Data Sources list.
  3. Choose the Remove button, and then choose the Yes button to confirm the deletion.

See Also

For All Users

Connecting to a Relativity Data Source

For Programmers

SQLConfigDataSource Attribute Strings (Programming)


Connecting to a Relativity Data Source

See Also

When you connect to a Relativity data source, an application may prompt you to enter the name of a database. If you are prompted, enter or select the catalog containing the Relativity database you want to access.

See Also

For All Users

Adding, Modifying, and Deleting a Relativity Data Source

Using the Relativity ODBC Driver

For Advanced Users

Connection Strings (Advanced)


Using the Relativity ODBC Driver

The following information may be helpful when using the Relativity ODBC driver:

Using Reserved Words

Do not use reserved words listed in the SQL grammar in Appendix C of the Microsoft ODBC Programmer’s Reference as identifiers (that is, table or column names), unless you surround the word in double quotation marks (for example, "DATE").

Column and Table Names

If column or table names contain any characters except letters, numbers, and underscores, they must be delimited. To delimit a column or table name, enclose the name in double quotes("). Characters with an ASCII value greater than 127 are converted to underscores.


Connection Strings (Advanced)

See Also

The connection string for the Relativity ODBC driver uses the following keywords:

Keyword | Description
DSN | The name of the Relativity data source
UID | A user ID (name)
PWD | The password corresponding to the user ID
QryPlan | An optional flag that indicates whether a query plan file should be produced (1) or not (0).

For example, to connect to the Accounting Main data source with the user ID "Sam" and no password, use the following connection string:

DSN=Accounting Main;UID=Sam;PWD=

Additionally, Relativity accepts a connection string that uses the DRIVER keyword rather than the DSN. Such a connection string is used when there is no data source on the local machine. It requires some additional keywords to identify the data source.

The connection string used for the Relativity Data Manager uses the following keywords:

Keyword | Description
DRIVER | The name of the Relativity driver. Usually "Relativity".
DBQ | The database qualifier, if there is a data source.
Catalog | The name of the catalog file, if there is no data source.
FileMgr | The identifier of the file manager, if there is no data source. "MF" is the only supported value.
UID | A user ID (name)
PWD | The password corresponding to the user ID
QryPlan | An optional flag that indicates whether a query plan file should be produced (1) or not (0).

For example, to connect to the data source whose qualifier is Accounting_Main with the user ID "Sam" and no password, use the following connection string:

DRIVER=Relativity;DBQ=Accounting_Main;UID=Sam;PWD=

To connect to the catalog at D:\Data\account.reg with the user ID "Sam" and no password, use the following connection string.

DRIVER=Relativity;UID=Sam;PWD=;FileMgr=MF;Catalog=D:\Data\account.reg

The connection string used for the Relativity Client uses the following keywords:

Keyword | Description
DRIVER | The name of the Relativity driver. Usually "Relativity Client".
ServerName | The server name or IP address, followed by a period and then the port number.
ServerDSN | The DSN of the data source on the server. Use this or DBQ.
DBQ | The database qualifier of the data source on the server. Use this or ServerDSN.
UID | A user ID (name)
PWD | The password corresponding to the user ID
ArrayFetchOn | An optional flag that may be set to 0 to prevent the server from buffering data. Set this to 1 or leave it unspecified.
ArrayBufferSize | The size of the server buffer, in unit of 1024 bytes. The default value is 8.
QryPlan | An optional flag that indicates whether a query plan file should be produced (1) or not (0).

For example, to connect to the server data source on the server rwserv, at port 1583, whose name (in the server’s odbc.ini file) is Accounting Main, and whose qualifier (in the server’s odbc.ini file) is Accounting_Main with the user ID "Sam" and no password, use the either of the following connection strings:

DRIVER=Relativity Client;ServerName=rwserv.1583;ServerDSN=Accounting Main;UID=Sam;PWD=
DRIVER=Relativity Client;ServerName=rwserv.1583;DBQ=Accounting_Main;UID=Sam;PWD=

Note that the strings in the previous example do not require the creation of a client data source. The client driver (Relativity Client) must still be installed on the client machine, however.

See Also

For All Users

Connecting to a Relativity Data Source


Data Types (Advanced)

See Also

The following table shows how Relativity data types are mapped to ODBC SQL data types. Note that not all ODBC SQL data types are supported. (The unsupported types are SQL_BINARY, SQL_VARBINARY, SQL_BIGINT, SQL_FLOAT and SQL_NUMERIC.)

Relativity Data Type | ODBC Data Type
Bit | SQL_BIT
Tinyint | SQL_TINYINT
Smallint | SQL_SMALLINT
Integer | SQL_INTEGER
Decimal(p,s) | SQL_DECIMAL
Single | SQL_REAL
Double | SQL_DOUBLE
Char(n) | SQL_CHAR
Varchar(n) | SQL_VARCHAR
Longvarchar(n) | SQL_LONGVARCHAR
Longvarbinary(n) | SQL_LONGVARBINARY
Date | SQL_DATE
Time | SQL_TIME
Timestamp | SQL_TIMESTAMP

Note SQLGetTypeInfo returns ODBC SQL data types. All conversions in Appendix D of the Microsoft ODBC Programmer's Reference are supported for the ODBC SQL data types listed earlier in this topic.

Limitations

The Relativity ODBC driver imposes the following limitations on the data types:

  • The maximum length of a LONGVARCHAR column is 65,500 bytes. Updates are limited to 32,768 bytes. Truncation occurs to updated data that extends past 32,768 bytes.
  • When converting Relativity database data to the C data type SQL_C_TINYINT, numbers from 0 to 127 are converted correctly. Numbers from 128 to 255 are converted to numbers from -128 to -1. Numbers less than 0 or greater than 255 cannot be converted.

    When converting data from the C data type SQL_C_TINYINT to Relativity database data, numbers from 0 to 127 are converted correctly. Numbers from -128 to -1 are converted to numbers from 128 to 255.

    This occurs because SQL_C_TINYINT is signed, but the Relativity ODBC driver uses unsigned single-byte integers.

See Also

For Advanced Users

CREATE TABLE Statement (Advanced)

SQL Data Type Syntax (Advanced)

SQL Statements (Advanced)


Error Messages (Advanced)

See Also

When an error occurs, the Relativity ODBC driver returns the native error number, the SQLSTATE (an ODBC error code), and an error message.

Native Error

For errors that occur in the CRDM, the Relativity ODBC driver returns the native error generated by the CRDM. For errors that are detected by the Simba SQL Engine, the driver returns a native error of zero.

SQLSTATE

For errors that occur in the data source, the Relativity ODBC driver maps the returned native error to the appropriate SQLSTATE. For errors that are detected by the driver or the Driver Manager, the Relativity ODBC driver or Driver Manager generates the appropriate SQLSTATE.

Error Message

For errors that occur in the data source, the Relativity ODBC driver returns an error message generated by the CRDM. For errors that occur in the Relativity ODBC driver or the Driver Manager, the Relativity ODBC driver returns an error message based on the text associated with the SQLSTATE.

Error messages have the following format:

[vendor][ODBC-component][data-source]message-text

where the prefixes in brackets ([ ]) identify the location of the error. When the error occurs in the Driver Manager or Simba SQL Engine, data-source is not given. When the error occurs in the data source, the [vendor] and [ODBC-component] prefixes identify the vendor and name of the ODBC component that received the error from the data source.

The following table shows the error messages returned by the Driver Manager, Simba SQL Engine and Relativity CRDM:

Error Message | Error location
[Microsoft][ODBC DLL]message-text | Driver Manager (ODBC.DLL)
[Micro Focus][Relativity]message-text | Simba SQL Engine
[Micro Focus][Relativity][CRDM]message-text | Relativity CRDM (see CRDM Errors)

See Also

For All Users

CRDM Errors


SQL Statements (Advanced)

See Also

The Relativity ODBC driver supports all SQL statements and clauses in the ODBC minimum grammar with the following limitations:

Minimum Grammar Element | Limitation
CREATE TABLE | DEFAULT is not supported in a column-definition. UNIQUE, PRIMARY KEY, REFERENCES and CHECK are not supported in a column-constraint-definition. CHECK and FOREIGN KEY are not supported in a table-constraint-definition.
DROP TABLE | [CASCADE | RESTRICT] is not supported.

The following SQL statements are supported by Relativity:

SQLstatement ::=
create-index-statement
| create-table-statement
| create-view-statement
| delete-statement-searched
| drop-table-statement
| drop-view-statement
| insert-statement
| ODBC-procedure-extension
| select-statement
| update-statement-searched

For information about ODBC SQL grammar limitations, additional and driver-specific grammar supported, and ODBC SQL reserved words, see the following topics:

Additional Supported ODBC SQL Grammar (Advanced)
Driver-specific ODBC SQL Grammar (Advanced)
Limitations to ODBC SQL Grammar (Advanced)
Reserved Words (Advanced)

See Also

For Advanced Users

Data Types (Advanced)


Additional Supported ODBC SQL Grammar (Advanced)

See Also

The Relativity ODBC driver completely supports the following SQL statements and clauses in the Core and Extended ODBC grammar:

Core and Extended Grammar | Comments
Approximate numeric literal | Supported
AVG(expression), COUNT(*), MAX(expression), MIN(expression), and SUM(expression) | Supported. See also the description of COUNT(expression) in Driver-specific ODBC SQL Grammar.
BETWEEN predicate | Supported.
Correlation names are fully supported, including within the table list.

Column alias names are also supported.

 For example, in the following string, E1 is the correlation name for the table named Emp:
SELECT * FROM Emp E1
WHERE E1.LastName = 'Smith'
Exact numeric literal | Supported.
[HAVING search-condition] | Supported. Must be part of a GROUP BY clause.
EXISTS (sub-query) | Supported.
IN (valuelist) Implemented as specified in the ODBC core grammar. For example:
SELECT * FROM EMP
WHERE Dept IN ('Sales','Marketing')
IN (sub-query) Implemented as specified in the ODBC core grammar. For example:
SELECT * FROM EMP
WHERE Dept IN
(SELECT DISTINCT Dept FROM EMP
WHERE JobTitle = 'DBA')
quantified-predicate | Supported.
UNION and UNION ALL | Supported. However, the Order By clause is not allowed.

See Also

For Advanced Users

Driver-specific ODBC SQL Grammar (Advanced)

Limitations to ODBC SQL Grammar (Advanced)


Driver-Specific ODBC SQL Grammar (Advanced)

See Also

The Relativity ODBC driver supports the following driver-specific ODBC SQL grammar:

Driver-specific ODBC SQL Grammar | Comments
BETWEEN predicate The syntax:

expression1 BETWEEN expression2 AND expression3

returns True only if expression1 is greater than or equal to expression2 and expression1 is less than or equal to expression3.
COUNT(expression) Counts all non-NULL values for an expression across a predicate. This function behaves like other set functions, such as SUM, AVG, MIN, and MAX. For example:
SELECT COUNT(A+B)
FROM Q

counts all the rows in Q where A+B does not equal NULL.
Date arithmetic | The driver supports adding and subtracting an integer from a date column. The integer specifies the number of days to add or subtract. The driver also supports subtracting one date column from another to return a number of days.
Date literals | The 'YYYY-MM-DD' format is supported.
GROUP BY expression-list | GROUP BY supports an expression list as well as a column name.
ORDER BY expression-list If the expression is a single integer literal, it is interpreted as the number of the column in the result set. Ordering is done on one of the result table columns. No ordering is allowed on Set functions or an expression that contains a Set function.

For example, in the following clauses the table is ordered by three key expressions: a+b, c+d, and e.

SELECT * FROM emp
ORDER BY a+b,c+d,e
ORDER BY with GROUP BY | ORDER BY can be performed on any expression in the GROUP BY expression-list or any column in the result set.
Inner Joins | A SELECT statement can contain a list of INNER JOIN clauses.
Outer Joins | A SELECT statement can contain a list of LEFT OUTER JOIN clauses.
Scalar Functions | Supported. In addition to many of the standard ODBC scalar functions, two driver specific functions, IF and NULL are supported.
Table names that occur in the FROM clause of SELECT, after the INTO clause in INSERT, after CREATE and DROP TABLE, and after CREATE INDEX can contain a valid table qualifier, primary name, and filename extension. Use of a table name elsewhere in a SQL statement does not support the use of table qualifiers or extensions but will accept only the primary name (for example, EMP FROM C:\ABC\EMP).

If a filename extension is not specified, .XYZ is assumed.

Correlation names (aliases) can be used. For example:

SELECT * FROM
C:\ABC\EMP T1
WHERE T1.COL1 = 'aaa'
Time literals | The 'HH:MM:SS' format is supported.
Timestamp literals | The 'YYYY-MM-DD HH:MM:SS' format is supported.
USE <database qualifier> | Sets the current database qualifier. This opens the database identified by the qualifier.

See Also

For Advanced Users

Additional Supported ODBC SQL Grammar (Advanced)

Limitations to ODBC SQL Grammar (Advanced)


Limitations to ODBC SQL Grammar (Advanced)

See Also

The Relativity ODBC driver imposes the following limitations on the ODBC SQL grammar:

Grammar | Limitation
Character string literal | A character in a character string literal may be any ANSI character between 1 and 255 decimal. A single quote (') must be represented as two consecutive single quotes ('').
Columns | Maximum number is 256 when a table is created.
Comparison predicate | For SQL_BIT data, comparisons can be made for equality and inequality only (= and <> operators).
Literals | Maximum length is 1000 characters.
Predicates | LIKE predicate operates on the first 65500 characters of the column data. All other predicates operate on the first 256 characters of the column data. Maximum number of predicates is 300. This may vary depending upon the stack size allocated by the application.
Set Functions | AVG, MAX, MIN, and SUM do not support the DISTINCT keyword.
Sort Keys | SELECT statements with GROUP BY, DISTINCT, and ORDER BY return all of the data but only order on the first 256 bytes.

See Also

For Advanced Users

Additional Supported ODBC SQL Grammar (Advanced)

Driver-specific ODBC SQL Grammar (Advanced)


Reserved Words (Advanced)

See Also

The ODBC standard recommends that the following SQL keywords be reserved and be used neither as table-identifiers nor column-identifiers by the developer.

Relativity SQL Reserved Words List
ABSOLUTE | ACTION | ADA
ADD | ALL | ALLOCATE
ALTER | AND | ANY
ARE | AS | ASC
ASSERTION | AT | AUTHORIZATION
AVG | BEGIN | BETWEEN
BIT | BIT_LENGTH | BOTH
BY | CASCADE | CASCADED
CASE | CAST | CATALOG
CHAR | CHAR_LENGTH | CHARACTER
CHARACTER_LENGTH | CHECK | CLOSE
COALESCE | COBOL | COLLATE
COLLATION | COLUMN | COMMIT
CONNECT | CONNECTION | CONSTRAINT
CONSTRAINTS | CONTINUE | CONVERT
CORRESPONDING | COUNT | CREATE
CROSS | CURRENT | CURRENT_DATE
CURRENT_TIME | CURRENT_TIMESTAMP | CURRENT_USER
CURSOR | DATABASE | DATE
DAY | DEALLOCATE | DEC
DECIMAL | DECLARE | DEFAULT
DEFERRABLE | DEFERRED | DELETE
DESC | DESCRIBE | DESCRIPTOR
DIAGNOSTICS | DISCONNECT | DISTINCT
DOMAIN | DOUBLE | DROP
ELSE | END | END-EXEC
ESCAPE | EXCEPT | EXCEPTION
EXEC | EXECUTE | EXISTS
EXTERNAL | EXTRACT | FALSE
FETCH | FIRST | FLOAT
FOR | FOREIGN | FORTRAN
FOUND | FROM | FULL
GET | GLOBAL | GO
GOTO | GRANT | GROUP
HAVING | HOUR | IDENTITY
IF | IMMEDIATE | IN
INCLUDE | INDEX | INDICATOR
INITIALLY | INNER | INPUT
INSENSITIVE | INSERT | INT
INTEGER | INTERSECT | INTERVAL
INTO | IS | ISOLATION
JOIN | KEY | LANGUAGE
LAST | LEADING | LEFT
LEVEL | LIKE | LOCAL
LOWER | MATCH | MAX
MIN | MINUTE | MODULE
MONTH | MUMPS | NAMES
NATIONAL | NATURAL | NCHAR
NEXT | NO | NONE
NOT | NULL | NULLIF
NUMERIC | OCTET_LENGTH | OF
ON | ONLY | OPEN
OPTION | OR | ORDER
OUTER | OUTPUT | OVERLAPS
PAD | PARTIAL | PASCAL
PLI | POSITION | PRECISION
PREPARE | PRESERVE | PRIMARY
PRIOR | PRIVILEGES | PROCEDURE
PUBLIC | READ | REAL
REFERENCES | RELATIVE | RESTRICT
REVOKE | RIGHT | ROLLBACK
ROWS | SCHEMA | SCROLL
SECOND | SECTION | SELECT
SEQUENCE | SESSION | SESSION_USER
SET | SIZE | SMALLINT
SOME | SPACE | SQL
SQLCA | SQLCODE | SQLERROR
SQLSTATE | SQLWARNING | SUBSTRING
SUM | SVBEGIN | SVEND
SYSTEM_USER | TABLE | TEMPORARY
THEN | TIME | TIMESTAMP
TIMEZONE_HOUR | TIMEZONE_MINUTE | TO
TRAILING | TRANSACTION | TRANSLATE
TRANSLATION | TRIM | TRUE
UNION | UNIQUE | UNKNOWN
UPDATE | UPPER | USAGE
USER | USING | VALUE
VALUES | VARCHAR | VARYING
VIEW | WHEN | WHENEVER
WHERE | WITH | WORK
WRITE | YEAR | ZONE

See Also

For Advanced Users

SQL Statements (Advanced)


Scalar Functions (Advanced)

See Also

The Relativity ODBC driver supports the following scalar functions:

CONCAT | LCASE | QUARTER
CONVERT | LEFT | RIGHT
CURDATE | LENGTH | RTRIM
CURTIME | LOCATE | SUBSTRING
DATABASE | LTRIM | UCASE
DAYOFMONTH | MOD | USER
DAYOFWEEK | MONTH | YEAR
IF | NULL |

For detailed information about the arguments and return values of scalar functions, see Appendix E of the Microsoft ODBC Programmer's Reference.

The IF and NULL functions are driver-specific system functions, not ODBC standard system functions. They are not related to the unsupported ODBC standard system function IFNULL.

See Also

For Advanced Users

SQL Expression Syntax (Advanced)

SQL ODBC-Scalar-Function-Extension Syntax (Advanced)


Data File Path Names (Advanced)

A COBOL data file is identified in the Relativity catalog by a string called a path name. A path name associated with a table connection is usually simply a (relative or absolute) file name. However, beginning with Relativity 6, the path name may contain strings called "tags." These tags are interpreted by Relativity when it opens a table, at which time they are evaluated and replaced. The following describes the allowed tags, their format and their meaning.

A path name tag is always enclosed in curly braces ("{" and "}"), except for the Abbreviated Environment Variable tag (see below). Between the braces is a sequence of substrings separated by commas. The first substring is a keyword called the "tag name;" it determines the type of the tag and the meaning of the remainder of the tag. The remaining substrings are called parameters.

Each of the tag types allows for optional parameters. In particular, the final parameter in each tag format described below is an optional parameter named "default-value." This parameter is used only if an error occurs in the evaluation of the tag. The default is the actual text to be used in place of the tag if an error occurs; it may be an empty string. If the default and the comma preceding it are both omitted, then if an error occurs in the evaluation of the tag, the error is reported to the application.

The parameter values in tags are usually simple strings. However, Relativity permits them to be embedded path name tags. For an example, click here.

Column Tag

The long format of the Column tag is as follows:

{COL, table-name, column1-name, column2-name, compare-value, default-value}
- or -
{COL, table-name.column1-name, column2-name= compare-value, default-value}

This tag is replaced by the value of a column (column1-name) in a table (table-name). The table is searched for the first row in which another column (column2-name) contains a value equal to the value (compare-value) in the fourth parameter. The compare-value is either a literal, in the same format as literals in SQL, or an embedded path name tag. In case of an error, the optional default-value parameter is used.

The short format of the Column tag is as follows:

{COL, table-name, column1-name, default-value}

This tag is replaced by the value of a column (column1-name) in a table (table-name). The column value is obtained from the first row of the table. In case of an error, the optional default-value parameter is used.

Connection String Tag

The format of the Connection String tag is as follows:

{CONN, attribute-keyword, default-value}

This tag is replaced by the value of a connection string attribute. The first parameter is the name of the keyword that identifies the attribute in the connection string. The keywords "DSN", "DBQ" and "UID" are always available. The keyword "PWD" is never available. If the connection string attribute does not exist, an error occurs. In case of an error, the optional default-value parameter is used.

Environment Variable Tag

The format of the Environment Variable tag is as follows:

{ENV, environment-variable, default-value}

This tag is replaced by the value of an environment variable. The first parameter is the name of the environment variable. If the environment variable does not exist, an error occurs. In case of an error, the optional default-value parameter is used.

Note that when an Environment Variable tag is used in a catalog served by a Relativity Data Server, the environment variable is obtained from the Data Server’s environment table, not the Data Client's environment. The Data Server's environment contains two variables that are specific to the client: CLIENT_HOST and CLIENT_USER. CLIENT_HOST is set to the name of the client machine. CLIENT_USER is set to the user name of the client user.

Abbreviated Environment Variable Tag

The format of the Abbreviated Environment Variable tag is as follows:

%environment-variable%

This special tag is replaced by the value of an environment variable. The string between the percent signs is the name of the environment variable. If the environment variable does not exist, an empty string is used. The Abbreviated Environment Variable tag is equivalent to the following:

{ENV, environment-variable, }


Stored Procedures (Advanced)

See Also

Stored procedures are used to encapsulate a set of operations or queries to execute on a database server.

The Relativity ODBC driver supports the following (built-in) "stored" procedures:

DBA_AddFileConn | DBA_ListTableConns
DBA_AddPathName | DBA_ListTableDefs
DBA_AddTableConn | DBA_ModifyPathName
DBA_DeleteFileConn | DBA_RelabelPathName
DBA_DeletePathName | DBA_RenameTableConn
DBA_DeleteTableConn | RDB_CreateFile
DBA_ListFileConns | RDB_Limit

All of these procedures are driver-specific procedures. The Relativity driver does not presently allow users to define stored procedures.

For more details on working with DBA stored procedures, click one of the following topics:

Calling DBA Stored Procedures
Concepts Used in DBA Stored Procedures
Terms Used in DBA Stored Procedures
Sample Programs

See Also

For Advanced Users

Procedure Call Statement (Advanced)


Relativity Indexes (Advanced)

The Relativity ODBC driver automatically creates indexes (in the ODBC sense) for a table that is based an indexed file. The indexes are derived from the file’s keys.

An index is an ordered set of columns that identifies one or more rows of a table. An index is unique if the combination of values of the columns in the index are different for each row in the table. Indexes serve two purposes: locating and ordering the rows of the table. The SQL engine uses indexes for both purposes. Some applications use the columns in a unique index to identify the rows of a table.

The Relativity ODBC driver attempts to create all possible indexes for each table. The driver considers all columns, including columns based on data items ("real columns") and columns based on subscript items ("subscript columns"). A set of columns can form an index if all of the following conditions are met:

  • There must be at least one real column.
  • All of the real columns precede all of the subscript columns, if any.
  • For each of the real columns, the corresponding data item must have a type that orders the data correctly. Also, the transform must not lose this order. See below for more information.
  • For all of the real columns, the corresponding data items must be part of a single key of the indexed file.
  • The order of the real columns in the index must match the order of the corresponding data items in the record.
  • If there are gaps between the data items corresponding to the real columns, the values of the data in those gaps must be filled by condition items specified in a filter expression.
  • There are no subscript columns if the entire indexed file key is not accounted for.

Each index is given a name that indicates the columns that comprise the index. These names are for information only; they are not used.

In addition, nulls introduce some problems:

  • If a real column may contain nulls (if it is "nullable"), and the corresponding data item's null condition does not define the null representation to be zeroes (for a numeric data item) or low values (for a nonnumeric data item), then the index is unordered. An unordered index is used only to select individual values; it cannot be used to select rows with a null (IS NULL) or with range of values (e.g., col-name > 10) in the real column.
  • If a null condition defines more than one value to represent null, the index is nonunique. Many front-end tools, like Access, require that a table have at least one unique index.

Many numeric data items do not order the data correctly. An index can include a column defined on a numeric data item if:

  • Whatever data item values, if any, that correspond to a null in the column must precede all other values.
  • The key order must be the same as numeric order. For example, negative numbers must precede positive numbers; for many signed data types, negative numbers follow positive numbers. Note: If the data item is signed but the column is marked unsigned, the Relativity ODBC driver will disallow negative values; the column can then be included in an index.
  • There must be only one way to represent a given value in the numeric data item. For example, if the data item allows two or more representations of the sign, no index is possible. In some cases, the Relativity ODBC driver may limit the allowed representations of data items that are partially or totally within a file key so that an index can be built.


Viewing a Query Plan (Advanced)

See Also

At the heart of Relativity database lies the query optimizer. This component of Relativity is responsible for creating and executing a query execution plan for optimizing joins, filters, and order by, group by, subquery, and distinct clauses in SQL statements. At SQLPrepare time, the query optimizer compiles an SQL statement into a query execution plan, and at SQLFetch time, Relativity executes the plan.

In arriving at a query execution plan, the query optimizer tries to use existing indexes on the columns in the query. Relativity assumes that indexes already exist for commonly executed queries; it does not create new indexes for the query.

Relativity allows you to save graphical representations of query execution plans to a file and provides a Windows utility, the Query Plan Viewer, for viewing them.

Saving Query Execution Plans

To enable the query plan display, you must check the Generate Query Plan check box from the Setup dialog box for this data source and designate a file into which to place the query plan by pressing the Browse button. This check box and button is located by pressing the Options button. From now on, when an SQL statement is issued, the query execution plan will be appended to the query plan file. If the file does not exist, it is created automatically.

Note that not all versions of Client/Server Relativity support displaying the query plan on the client. In these versions, the Generate Query Plan check box will be disabled.

Starting the Query Execution Plan Viewer

The Query Plan Viewer can be started from a Windows shortcut or from a command prompt.

Starting from a Windows Shortcut

To view your query execution plan, select Query Plan Viewer from the Relativity group on the Windows Start menu.

Starting from a Command Prompt

To start the Query Plan Viewer at a command prompt, enter the following command using the filename of the Query Plan File to open:

simvw32 filename

Using the Query Plan Viewer

After the Query Plan Viewer is started, the following window appears:

To open a query plan file automatically when you start the Query Plan Viewer, add the new key

Simview Options: DefaultFile=filename

under HKEY_CURRENT_USER\Software\ODBC\odbc.ini. The file you specify will automatically be loaded when you start the Query Plan Viewer.

To open a query plan file manually, choose Open from the File menu and select your Query Plan File. Click OK.

The file is opened and the menu bar indicates the number of query execution plans that have been loaded from that file.

Select an option from the View menu to display the first, last, next or previous query execution plan in the file. The default is to display the last plan. The Goto option allows you to view a specific plan by entering its plan number. You can also choose an option by pressing the Control key and the option’s underlined letter simultaneously. For example, press CTRL+g to choose the Goto option.

When you select an option from the View menu, the SQL statement associated with that query execution plan appears in the Query Plan Viewer window. A separate Query Plan window displays a graphical representation of the query execution plan which consists of one or more nodes. Each node represents a table object which can have rows fetched from it. For example, a Base (Normal) query execution plan node represents the base table object which constitutes all the rows in the base database table. In the case of a filter node, the set of rows returned is all those rows which meet the filter criteria.

To generate a detailed description of a node, double-click on the node in the Query Plan window.

To view a subquery, click on the Subquery menu in the Query Plan window and select the subquery (1-10) that you wish to view. If no subquery or union exists for that selection, a message appears stating that fact.

To refresh query execution plans

If the current query plan file changes, you are prompted to refresh the file or leave the current plan on screen. The application checks for changes every 60 seconds.

To have your plans refreshed automatically (without the prompt), add the line AutoReload=1 to Simview Options under HKEY_CURRENT_USER\\Software\ODBC\odbc.ini for 32-bit Windows.

Note you can use the Refresh option in the Query Plan Viewers File menu to reopen the active query plan file at any time. This is useful if a new plan has been added to the query plan file you are currently viewing.

See Also

For Advanced Users

Examples of QEP Output

Examples of Optimization


Write Support (Advanced)

The following describes some aspects of the Relativity ODBC driver’s support for SQL statements that modify files.

SQL Statements and File Operations

The Relativity ODBC driver implements INSERT, UPDATE, and DELETE statements. For each affected row in an SQL table the driver operates on a single record in the underlying data file. For an INSERT, a record is written to the file. For a DELETE, each deleted row must correspond to a record which is deleted. There is a one-to-one correspondence between the rows inserted in or deleted from the SQL table and the records written to or deleted from the file.

For an UPDATE, each updated row must correspond to a record which is updated. However, several rows may refer to the same record. When this happens, the record is updated multiple times, once for each row.

Repeating Items

When a column in an SQL table is defined on a repeating item, and the item’s subscripts are not bound, a single record in the underlying file may correspond to multiple rows. In such a table, INSERT and DELETE operations are prohibited.

UPDATEs on such a table are permitted, but the only columns that may be updated are the columns defined on repeating items. If any of the other columns were allowed to be updated, an update of a single row would actually change all of the rows defined on that record.

Updating Prime Record Key of File

The Relativity ODBC driver implementation of an UPDATE statement normally rewrites a record for each row updated. However, when an UPDATE statement causes the value of prime record key of the underlying indexed file to be modified, the record cannot be rewritten. Instead, the driver deletes the old record, and then it writes a new record that reflects the modifications made by the UPDATE statement. If a system failure occurs between these operations, there is the possibility that the record could be lost.


Literal Column Support (Advanced)

See Also

A SELECT statement’s select-list may contain an literal value, in which case the output column that results from the literal value will contain that value in all rows. The type of this output column is a function of the literal itself. In general, it is the largest data type needed to contain the literal.

If the literal is a numeric-literal, the column will be a numeric type. Leading zeros in the literal are ignored when determining the type and precision of the column. If the numeric literal is an exact-numeric-literal, then the column will be an integer column type, either SQL_BIT, SQL_TINYINT, SQL_SMALLINT, or SQL_INTEGER, depending on the value of the literal. If the numeric literal contains a decimal point or an E, then the column will be an floating point type, either SQL_REAL or SQL_DOUBLE. Note that, unless the CONVERT scalar function is used or the DefaultLiteralToDecimal option is enabled, the column will not be a type SQL_DECIMAL. If it is desired that a specific numeric literal be treated as decimal column, use the CONVERT scalar function to force the desired type of the column. If it is desired that all appropriate numeric literals be treated as decimal, enable the DefaultLiteralToDecimal option.

See Also

Relativity options

SQL Literal Syntax

Scalar Function CONVERT


Relativity Options (Advanced)

Some Relativity configuration options currently can not be configured via an administrative interface. These options are described here:

  • To set these options for a Windows Data Server requires the use of the regedit utility on the Windows machine on which the Data Server is running.
  • To set these options for a Windows Data Manager requires the use of the regedit utility.
  • To set these options for a UNIX Data Server requires the use of a text editor, such as vi, on the UNIX machine on which the Data Server is running.

The currently documented advanced options are:

Simba Engine Options

Simba Server Options


SQLGetInfo Return Values (Programming)

See Also

The following table lists the C language #defines for the fInfoType argument and the corresponding values returned by the SQLGetInfo function. This information can be retrieved by passing the listed C language #defines to SQLGetInfo in the fInfoType argument. Where SQLGetInfo returns a 32-bit bitmask, a vertical bar (|) represents a bitwise OR. For more information about the values return by SQLGetInfo, see the Microsoft ODBC Programmer's Reference..

fInfoType Value (#define) | Returned value
SQL_ACCESSIBLE_PROCEDURES | "Y"
SQL_ACCESSIBLE_TABLES | "N"
SQL_ACTIVE_ENVIRONMENTS | 0
SQL_ALTER_DOMAIN | 0x00000000
SQL_ALTER_TABLE | 0x00000000
SQL_DATETIME_LITERALS | 0x00000000
SQL_ASYNC_MODE | SQL_AM_NONE
SQL_BATCH_ROW_COUNT | 0x00000000
SQL_BATCH_SUPPORT | 0x00000000
SQL_BOOKMARK_PERSISTENCE | 0x00000000
SQL_CATALOG_LOCATION | SQL_CL_START
SQL_CATALOG_NAME | "Y"
SQL_CATALOG_NAME_SEPARATOR | "." (period) Driver also accepts "\" (backslash) and "/" (forward slash) as catalog name separators.
SQL_CATALOG_TERM | "database"
SQL_CATALOG_USAGE | SQL_CU_DML_STATEMENTS | SQL_CU_TABLE_DEFINITION | SQL_CU_INDEX_DEFINITION
SQL_COLLATION_SEQ | ""
SQL_COLUMN_ALIAS | "Y"
SQL_CONCAT_NULL_BEHAVIOR | SQL_CB_NULL
SQL_CONVERT_BIGINT | 0x00000000
SQL_CONVERT_BINARY | 0x00000000
SQL_CONVERT_BIT | SQL_CVT_BIT | SQL_CVT_CHAR | SQL_CVT_DECIMAL | SQL_CVT_DOUBLE | SQL_CVT_INTEGER | SQL_CVT_LONGVARCHAR | SQL_CVT_REAL | SQL_CVT_SMALLINT | SQL_CVT_TINYINT | SQL_CVT_VARCHAR
SQL_CONVERT_CHAR | SQL_CVT_BIT | SQL_CVT_CHAR | SQL_CVT_DATE | SQL_CVT_DECIMAL | SQL_CVT_DOUBLE | SQL_CVT_INTEGER | SQL_CVT_LONGVARCHAR | SQL_CVT_REAL | SQL_CVT_SMALLINT | SQL_CVT_TIME | SQL_CVT_TIMESTAMP | SQL_CVT_TINYINT | SQL_CVT_VARCHAR
SQL_CONVERT_DATE | SQL_CVT_CHAR | SQL_CVT_DATE | SQL_CVT_TIMESTAMP | SQL_CVT_VARCHAR
SQL_CONVERT_DECIMAL | SQL_CVT_BIT | SQL_CVT_CHAR | SQL_CVT_DECIMAL | SQL_CVT_DOUBLE | SQL_CVT_INTEGER | SQL_CVT_LONGVARCHAR | SQL_CVT_REAL | SQL_CVT_SMALLINT | SQL_CVT_TINYINT | SQL_CVT_VARCHAR
SQL_CONVERT_DOUBLE | SQL_CVT_BIT | SQL_CVT_CHAR | SQL_CVT_DECIMAL | SQL_CVT_DOUBLE | SQL_CVT_INTEGER | SQL_CVT_LONGVARCHAR | SQL_CVT_REAL | SQL_CVT_SMALLINT | SQL_CVT_TINYINT | SQL_CVT_VARCHAR
SQL_CONVERT_FLOAT | SQL_CVT_BIT | SQL_CVT_CHAR | SQL_CVT_DECIMAL | SQL_CVT_DOUBLE | SQL_CVT_INTEGER | SQL_CVT_LONGVARCHAR | SQL_CVT_REAL | SQL_CVT_SMALLINT | SQL_CVT_TINYINT | SQL_CVT_VARCHAR
SQL_CONVERT_FUNCTIONS | SQL_FN_CVT_CONVERT
SQL_CONVERT_INTEGER | SQL_CVT_BIT | SQL_CVT_CHAR | SQL_CVT_DECIMAL | SQL_CVT_DOUBLE | SQL_CVT_INTEGER | SQL_CVT_LONGVARCHAR | SQL_CVT_REAL | SQL_CVT_SMALLINT | SQL_CVT_TINYINT | SQL_CVT_VARCHAR
SQL_CONVERT_INTERVAL_YEAR_MONTH | 0x00000000
SQL_CONVERT_INTERVAL_DAY_TIME | 0x00000000
SQL_CONVERT_LONGVARBINARY | 0x00000000
SQL_CONVERT_LONGVARCHAR | SQL_CVT_CHAR | SQL_CVT_VARCHAR | SQL_CVT_LONGVARCHAR
SQL_CONVERT_NUMERIC | SQL_CVT_BIT | SQL_CVT_CHAR | SQL_CVT_DECIMAL | SQL_CVT_DOUBLE | SQL_CVT_INTEGER | SQL_CVT_LONGVARCHAR | SQL_CVT_REAL | SQL_CVT_SMALLINT | SQL_CVT_TINYINT | SQL_CVT_VARCHAR
SQL_CONVERT_REAL | SQL_CVT_BIT | SQL_CVT_CHAR | SQL_CVT_DECIMAL | SQL_CVT_DOUBLE | SQL_CVT_INTEGER | SQL_CVT_LONGVARCHAR | SQL_CVT_REAL | SQL_CVT_SMALLINT | SQL_CVT_TINYINT | SQL_CVT_VARCHAR
SQL_CONVERT_SMALLINT | SQL_CVT_BIT | SQL_CVT_CHAR | SQL_CVT_DECIMAL | SQL_CVT_DOUBLE | SQL_CVT_INTEGER | SQL_CVT_LONGVARCHAR | SQL_CVT_REAL | SQL_CVT_SMALLINT | SQL_CVT_TINYINT | SQL_CVT_VARCHAR
SQL_CONVERT_TIME | SQL_CVT_CHAR | SQL_CVT_TIME | SQL_CVT_TIMESTAMP | SQL_CVT_VARCHAR
SQL_CONVERT_TIMESTAMP | SQL_CVT_CHAR | SQL_CVT_DATE | SQL_CVT_TIME | SQL_CVT_TIMESTAMP | SQL_CVT_VARCHAR
SQL_CONVERT_TINYINT | SQL_CVT_BIT | SQL_CVT_CHAR | SQL_CVT_DECIMAL | SQL_CVT_DOUBLE | SQL_CVT_INTEGER | SQL_CVT_LONGVARCHAR | SQL_CVT_REAL | SQL_CVT_SMALLINT | SQL_CVT_TINYINT | SQL_CVT_VARCHAR
SQL_CONVERT_VARBINARY | 0x00000000
SQL_CONVERT_VARCHAR | SQL_CVT_BIT | SQL_CVT_CHAR | SQL_CVT_DATE | SQL_CVT_DECIMAL | SQL_CVT_DOUBLE | SQL_CVT_INTEGER | SQL_CVT_LONGVARCHAR | SQL_CVT_REAL | SQL_CVT_SMALLINT | SQL_CVT_TIME | SQL_CVT_TIMESTAMP | SQL_CVT_TINYINT | SQL_CVT_VARCHAR
SQL_CONVERT_WCHAR | 0x00000000
SQL_CONVERT_WLONGVARCHAR | 0x00000000
SQL_CONVERT_WVARCHAR | 0x00000000
SQL_CORRELATION_NAME | SQL_CN_ANY
SQL_CREATE_ASSERTION | 0x00000000
SQL_CREATE_CHARACTER_SET | 0x00000000
SQL_CREATE_COLLATION | 0x00000000
SQL_CREATE_DOMAIN | 0x00000000
SQL_CREATE_SCHEMA | 0x00000000
SQL_CREATE_TABLE | SQL_CT_CREATE_TABLE
SQL_CREATE_TRANSLATION | 0x00000000
SQL_CREATE_VIEW | SQL_CV_CREATE_VIEW
SQL_CURSOR_COMMIT_BEHAVIOR | SQL_CB_CLOSE
SQL_CURSOR_ROLLBACK_BEHAVIOR | SQL_CB_CLOSE
SQL_CURSOR_SENSITIVITY | SQL_UNSPECIFIED
SQL_DATA_SOURCE_NAME | The data source name used during connection.
SQL_DATA_SOURCE_READ_ONLY | "N" (The driver does not check to see whether the disk drive is read-only.)
SQL_DATABASE_NAME | The database qualifier. ODBC 3.x calls this a "catalog".
SQL_DBMS_NAME | "Relativity" (may include additional information specific to the driver; e.g., "Relativity for MF")
SQL_DBMS_VER | "02.02.yyyy/mm/dd" (yyyy/mm/dd specifies the build date.)
SQL_DEFAULT_TXN_ISOLATION | SQL_TXN_READ_COMMITTED
SQL_DESCRIBE_PARAMETER | "Y"
SQL_DM_VER | "03.80.7601.0000"
SQL_DRIVER_HDBC | The driver's connection handle value.
SQL_DRIVER_HDESC | The driver's descriptor handle value.
SQL_DRIVER_HENV | The driver's environment handle value.
SQL_DRIVER_HLIB | The driver DLL library handle.
SQL_DRIVER_HSTMT | The driver's statement handle value.
SQL_DRIVER_NAME | The driver name, which varies for different Relativity products, e.g. "relcln32.dll", "reldbms32.dll"
SQL_DRIVER_ODBC_VER | "03.52"
SQL_DRIVER_VER | "02.02.yyyy/mm/dd" (yyyy/mm/dd specifies the build date.)
SQL_DROP_ASSERTION | 0x00000000
SQL_DROP_CHARACTER_SET | 0x00000000
SQL_DROP_COLLATION | 0x00000000
SQL_DROP_DOMAIN | 0x00000000
SQL_DROP_SCHEMA | 0x00000000
SQL_DROP_TABLE | SQL_DT_DROP_TABLE
SQL_DROP_TRANSLATION | 0x00000000
SQL_DROP_VIEW | SQL_DV_DROP_VIEW
SQL_DYNAMIC_CURSOR_ATTRIBUTES1 | 0x00000000
SQL_DYNAMIC_CURSOR_ATTRIBUTES2 | 0x00000000
SQL_EXPRESSIONS_IN_ORDERBY | "Y"
SQL_FETCH_DIRECTION | SQL_FD_FETCH_NEXT
SQL_FILE_USAGE | SQL_FILE_QUALIFIER for Windows-based Client/Server Relativity; SQL_FILE_NOT_SUPPORTED for other Client/Server Relativity
SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES1 | SQL_CA1_NEXT
SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES2 | SQL_CA2_READ_ONLY_CONCURRENCY | SQL_CA2_MAX_ROWS_SELECT
SQL_GETDATA_EXTENSIONS | SQL_GD_ANY_COLUMN | SQL_GD_ANY_ORDER | SQL_GD_BLOCK
SQL_GROUP_BY | SQL_GB_GROUP_BY_CONTAINS_SELECT
SQL_IDENTIFIER_CASE | SQL_IC_MIXED
SQL_IDENTIFIER_QUOTE_CHAR | """ (double quotation mark).
SQL_INDEX_KEYWORDS | SQL_IK_ASC | SQL_IK_DESC
SQL_INFO_SCHEMA_VIEWS | 0x00000000
SQL_INTEGRITY | "N"
SQL_KEYSET_CURSOR_ATTRIBUTES1 | 0x00000000
SQL_KEYSET_CURSOR_ATTRIBUTES2 | 0x00000000
SQL_KEYWORDS | "USE,IF,SVBEGIN,SVEND"
SQL_LIKE_ESCAPE_CLAUSE | "N"
SQL_LOCK_TYPES | SQL_LCK_EXCLUSIVE
SQL_MAX_ASYNC_CONCURRENT_STATEMENTS | 0
SQL_MAX_BINARY_LITERAL_LEN | 1000
SQL_MAX_CATALOG_NAME_LEN | 68
SQL_MAX_CHAR_LITERAL_LEN | 1000
SQL_MAX_COLUMN_NAME_LEN | 64
SQL_MAX_COLUMNS_IN_GROUP_BY | 255
SQL_MAX_COLUMNS_IN_INDEX | 255
SQL_MAX_COLUMNS_IN_ORDER_BY | 255
SQL_MAX_COLUMNS_IN_SELECT | 256
SQL_MAX_COLUMNS_IN_TABLE | 256
SQL_MAX_CONCURRENT_ACTIVITIES | 0
SQL_MAX_CURSOR_NAME_LEN | 18
SQL_MAX_DRIVER_CONNECTIONS | 0
SQL_MAX_IDENTIFIER_LEN | 64
SQL_MAX_INDEX_SIZE | 0
SQL_MAX_PROCEDURE_NAME_LEN | 64
SQL_MAX_ROW_SIZE | 0
SQL_MAX_ROW_SIZE_INCLUDES_LONG | "Y"
SQL_MAX_SCHEMA_NAME_LEN | 0
SQL_MAX_STATEMENT_LEN | 16384
SQL_MAX_TABLE_NAME_LEN | 64
SQL_MAX_TABLES_IN_SELECT | 0
SQL_MAX_USER_NAME_LEN | 128
SQL_MULT_RESULT_SETS | "N"
SQL_MULTIPLE_ACTIVE_TXN | "N"
SQL_NEED_LONG_DATA_LEN | "N"
SQL_NON_NULLABLE_COLUMNS | SQL_NNC_NON_NULL
SQL_NULL_COLLATION | SQL_NC_LOW
SQL_NUMERIC_FUNCTIONS | SQL_FN_NUM_MOD
SQL_ODBC_API_CONFORMANCE | SQL_OAC_LEVEL2
SQL_ODBC_INTERFACE_CONFORMANCE | SQL_OIC_CORE
SQL_ODBC_SAG_CLI_CONFORMANCE | SQL_OSCC_COMPLIANT
SQL_ODBC_SQL_CONFORMANCE | SQL_OSC_MINIMUM
SQL_ODBC_SQL_OPT_IEF | "N"
SQL_ODBC_VER | "03.80.0000" (The ODBC Driver Manager version)
SQL_OJ_CAPABILITIES | SQL_OJ_LEFT | SQL_OJ_NESTED | SQL_OJ_NOT_ORDERED | SQL_OJ_ALL_COMPARISON_OPS
SQL_OUTER_JOINS | "Y"
SQL_ORDER_BY_COLUMNS_IN_SELECT | "N"
SQL_PARAM_ARRAY_ROW_COUNTS | 0
SQL_PARAM_ARRAY_SELECTS | SQL_PAS_NO_SELECT
SQL_POS_OPERATIONS | SQL_POS_POSITION
SQL_POSITIONED_STATEMENTS | 0
SQL_PROCEDURE_TERM | "predefined procedure"
SQL_PROCEDURES | "Y"
SQL_QUOTED_IDENTIFIER_CASE | SQL_IC_MIXED
SQL_ROW_UPDATES | "N"
SQL_SCHEMA_TERM | ""
SQL_SCHEMA_USAGE | 0x00000000
SQL_SCROLL_OPTIONS | SQL_SO_FORWARD_ONLY | SQL_SO_STATIC
SQL_SCROLL_CONCURRENCY | SQL_SCCO_READ_ONLY
SQL_SEARCH_PATTERN_ESCAPE | "\" (backslash)
SQL_SERVER_NAME | "Relativity" (may include additional information specific to the driver; e.g., "Relativity for MF")
SQL_SPECIAL_CHARACTERS | ""
SQL_SQL_CONFORMANCE | 0x00000000
SQL_SQL92_DATETIME_FUNCTIONS | 0x00000000
SQL_SQL92_FOREIGN_KEY_DELETE_RULE | 0x00000000
SQL_SQL92_FOREIGN_KEY_UPDATE_RULE | 0x00000000
SQL_SQL92_GRANT | 0x00000000
SQL_SQL92_NUMERIC_VALUE_FUNCTIONS | 0x00000000
SQL_SQL92_PREDICATES | 0x00000000
SQL_SQL92_RELATIONAL_JOIN_OPERATORS | 0x00000000
SQL_SQL92_REVOKE | 0x00000000
SQL_SQL92_ROW_VALUE_CONSTRUCTOR | 0x00000000
SQL_SQL92_STRING_FUNCTIONS | 0x00000000
SQL_SQL92_VALUE_EXPRESSIONS | 0x00000000
SQL_STANDARD_CLI_CONFORMANCE | 0x00000000
SQL_STATIC_CURSOR_ATTRIBUTES1 | SQL_CA1_NEXT | SQL_CA1_ABSOLUTE | SQL_CA1_RELATIVE | SQL_CA1_LOCK_NO_CHANGE | SQL_CA1_POS_POSITION
SQL_STATIC_CURSOR_ATTRIBUTES2 | SQL_CA2_READ_ONLY_CONCURRENCY | SQL_CA2_MAX_ROWS_SELECT
SQL_STATIC_SENSITIVITY | 0x00000000
SQL_STRING_FUNCTIONS | SQL_FN_STR_CONCAT | SQL_FN_STR_LCASE | SQL_FN_STR_LEFT | SQL_FN_STR_LENGTH | SQL_FN_STR_LOCATE | SQL_FN_STR_LOCATE_2 | SQL_FN_STR_LTRIM | SQL_FN_STR_RIGHT | SQL_FN_STR_RTRIM | SQL_FN_STR_SUBSTRING | SQL_FN_STR_UCASE
SQL_SUBQUERIES | SQL_SQ_COMPARISON | SQL_SQ_EXISTS | SQL_SQ_IN | SQL_SQ_QUANTIFIED | SQL_SQ_CORRELATED_SUBQUERIES
SQL_SYSTEM_FUNCTIONS | SQL_FN_SYS_DBNAME | SQL_FN_SYS_USERNAME
SQL_TABLE_TERM | "table"
SQL_TIMEDATE_ADD_INTERVALS | 0x00000000
SQL_TIMEDATE_DIFF_INTERVALS | 0x00000000
SQL_TIMEDATE_FUNCTIONS | SQL_FN_TD_CURDATE | SQL_FN_TD_CURTIME | SQL_FN_TD_DAYOFMONTH | SQL_FN_TD_DAYOFWEEK | SQL_FN_TD_MONTH | SQL_FN_TD_QUARTER | SQL_FN_TD_YEAR
SQL_TXN_CAPABLE | SQL_TC_DML
SQL_TXN_ISOLATION_OPTION | SQL_TXN_READ_UNCOMMITTED | SQL_TXN_READ_COMMITTED
SQL_UNION | SQL_U_UNION | SQL_U_UNION_ALL
SQL_USER_NAME | The name of the user specified during connection.
SQL_XOPEN_CLI_YEAR | ""

See Also

For Advanced Users

SQL Statements (Advanced)

Data Types (Advanced)

For Programmers

Scalar Functions (Programming)


ODBC API Functions (Programming)

See Also

The Relativity ODBC driver supports all Core and Level 1 functions and the following Level 2 functions:

  • SQLDataSources
  • SQLMoreResults

These ODBC API functions have the following implementations with the Relativity ODBC driver:

Function | Description
SQLDriverConnect | The following keywords are supported in the connection string: DSN, UID, and PWD.
SQLGetConnectAttr and SQLSetConnectAttr | These functions support the SQL_ATTR_ACCESS_MODE, SQL_ATTR_AUTOCOMMIT, SQL_ATTR_CURRENT_CATALOG, SQL_ATTR_TRACE, SQL_ATTR_TRACEFILE, and SQL_ATTR_TXN_ISOLATION connection attributes, among others. Note that for SQL_ATTR_TXN_ISOLATION, SQLSetConnectAttr only supports SQL_TXN_READ_UNCOMMITTED and SQL_TXN_READ_COMMITTED.
SQLGetData | This function can retrieve data from any column, whether or not there are bound columns after it and regardless of the order in which the columns are retrieved.
SQLGetStmtAttr and SQLSetStmtAttr These functions support the SQL_ATTR_MAX_LENGTH, SQL_ATTR_MAX_ROWS, and SQL_ATTR_NOSCAN statement options, among others.

They also support the following driver-specific statement options:

1151 (Driver-specific option): Sets the password for a table so that the user will not have to be prompted for the password (vParam is in the format "<tablename>,<password>".

1152 (Driver-specific option): A vParam of 1 disables the use of indexes. A vParam of 0 re-enables it.

1153 (Driver-specific option): SQL_LOCK_TABLES
SQLGetTypeInfo | Only data type names returned by SQLGetTypeInfo can be used with CREATE statements.
SQLMoreResults | SQLMoreResults always returns SQL_NO_DATA_FOUND. It cannot return additional results.
SQLSetCursorName and SQLGetCursorName | These functions are supported, but cannot be used for positioned updates or deletes (for example, WHERE CURRENT OF cursorname).
SQLSpecial Columns | Unique indexes are used for this function.
SQLEndTran | The Relativity ODBC driver does support transactions. In manual-commit mode, all modifications to all files are deferred until SQLEndTran is called with CompletionType equal to SQL_COMMIT.
SQLTablePrivileges and SQLColumnPrivileges | A privilege record is returned when the user is either the grantor or grantee of the privilege. Any privilege granted to PUBLIC is also returned.

See Also

For Advanced Users

Error Messages (Advanced)

For Programmers

Implementation Issues (Programming)


SQL_LOCK_TABLES Statement Option (Programming)

The Relativity ODBC driver supports a driver-specific statement option, SQL_LOCK_TABLES, that supports the values DEFLOCK and XLOCK. When the value of the option is DEFLOCK, tables used by the hstmt are subject only to the default locking mechanisms of the Relativity ODBC driver. This is the default setting.

When the value of the option is XLOCK, all tables used by the hstmt are exclusively locked when a SELECT, UPDATE, INSERT, DELETE, or CREATE INDEX statement is executed on the hstmt. The tables remain locked until the hstmt is dropped (by calling SQLFreeStmt with the SQL_DROP option) or the option is set to DEFLOCK and the hstmt is reexecuted.

Locked tables can only be used by the locking hstmt; they cannot be used by any other hstmts. For example, the last function call in the following code generates an access violation because it uses a different hstmt:

SQLSetStmtOption(hstmt1,SQL_LOCK_TABLES,XLOCK);
SQLExecDirect(hstmt1,"SELECT * FROM EMP",SQL_NTS);
SQLExecDirect(hstmt1,"UPDATE EMP SET DEPT=12",SQL_NTS);
/* This call generates an access violation */
SQLExecDirect(hstmt2,"SELECT * FROM EMP",SQL_NTS);

Furthermore, SQLColumns, SQLSpecialColumns, and SQLStatistics cannot retrieve information about a table locked on another hstmt.

Caution Be careful to avoid locking more files than necessary. If the same hstmt is used to execute statements for many different tables, all the tables will remain locked until the hstmt is dropped or locking is turned off.

The SQL_LOCK_TABLES statement option uses the following #defines:

#define SQL_LOCK_TABLES 1153
#define DEFLOCK 0
#define XLOCK 1

The SQL_LOCK_TABLES statement option cannot be used with SQLSetConnectOption.


SQLConfigDataSource Attribute Strings (Programming)

SQLConfigDataSource is a function in the ODBC Installer DLL. The prototype is in the header file odbcinst.h. To use it, you should get the ODBC SDK or Windows SDK from Microsoft. This function may be used to add, modify, or delete a data source.

SQLConfigDataSource accepts a parameter which is a string of "attributes" for the data source. Each attribute is a keyword, an equal sign, a value, and a terminating null (binary zero) byte; the last attribute is followed by an additional null byte. The values should not contain the []{}(),;?*=!@ characters.

The attribute string for the Relativity ODBC driver uses the following keywords:

Keyword | Description
DSN | Name of the Relativity data source.
DESC | Description of the data source.
DBQ | Data source qualifier.
Catalog | The name of the catalog file.
CreateCat | 1 to create the catalog, 0 or omitted attribute to use existing catalog.
FileMgr | Choice of file manager. MF is the only supported value.
CodePage | Windows code page of all applications that will use this data source.
QryPlan | 1 to write query plans, 0 to not write query plans, or omitted to default.
QryFile | The name of the query plan file.
FileShare | 1 if Fileshare is enabled for this data source.
CatForm | MF catalog format (used when creating a catalog): DEFAULT, BTRIEVE, CISAM, LEVELII, IDX4, or IDX8.
FilForm | MF file format of created tables: DEFAULT, BTRIEVE, CISAM, LEVELII, IDX4, or IDX8.
DatCom | An integer in the range of 0 to 255, representing the data compression in created tables. At present, only 0 and 1 are defined.
KeyCom | Key compression options for created tables. Any combination of LeadingCharacters, TrailingSpaces, and Duplicates, separated by | characters.
Sparse | An integer in the range of 0 to 255, representing the sparse character for keys in created tables.
ServerName | For a client data source, the name of the server machine followed by a period (.) and the server port number (normally 1583).
ServerDSN | For a client data source, the name of the server data source.

For example, to create the Accounting data source using the MF File Manager with a new catalog named C:\Acct\Catalog.rcg, use the following attribute string:

DSN=Accounting\0FileMgr=MF\0Catalog=C:\Acct\Catalog.rcg\0CreateCat=1\0\0


Implementation Issues (Programming)

The following implementation-specific issues might affect the use of the Relativity ODBC driver.

Arithmetic Errors

The Relativity ODBC driver evaluates the WHERE clause in a SELECT statement as it fetches each row. If a row contains a value that causes an arithmetic error, such as divide-by-zero or numeric overflow, the driver returns all rows, but returns errors for columns with arithmetic errors. When inserting or updating, however, the Relativity ODBC driver stops inserting or updating data when the first arithmetic error is encountered.

Closing Open Tables (Files)

Calling SQLCloseCursor or calling SQLFreeStmt with the SQL_CLOSE option changes the statement state but does not close the tables used by the hstmt, unless the tables were opened by SQLExecDirect. Otherwise, to close the tables currently used by hstmt, you must free the statement handle with a call to SQLFreeHandle.

In the following example, when SQLFreeStmt is called, the emp and dept tables remain open:

SQLPrepare(hStmt,"SELECT * FROM emp,dept
WHERE emp.dept = dept.dept_id",SQL_NTS);
SQLExecute(hStmt);
/* SQLFetch until NO_DATA_FOUND */
SQLFreeStmt(hStmt,SQL_CLOSE); /* or SQLCloseCursor(hstmt); */
SQLPrepare(hStmt,"SELECT * FROM emp",SQL_NTS);

Note Each file used by the Relativity ODBC driver requires a file handle. Because tables (files) remain open until the statement handled is freed by SQLFreeHandle, reusing an hstmt for different tables without dropping it can result in an error caused by attempting to open too many files.

Range of Valid Dates

The Relativity ODBC driver does not permit years in dates less than 1582 or greater than 3000. Dates which contain years outside this range generate error number -4070 (Year value outside the range of the transform function).

Sorting with DISTINCT, GROUP BY, JOIN, or ORDER BY

DISTINCT, GROUP BY, JOIN, and ORDER BY always result in a sort. If indexes are found, data is dynamically fetched and the sort is based using those indexes. If indexes are not found, a temporary table called a snapshot is taken of the data and the sort occurs on the temporary table. This type of sort is not based on dynamic data since the temporary table is built from data found in the original data file at SQLExecute time.


DELETE Statement Processing (Advanced)

The Relativity ODBC driver implements a DELETE statement by deleting an existing record from the underlying data file. Consequently, the table may not have any columns defined on repeating items with unbound subscripts. The record is deleted by the following sequence of steps:

  • The next record to be deleted is located. Normally, the DELETE statement specifies a predicate (in a WHERE clause) that limits the records to be deleted. The driver follows the same procedure it would for a SELECT; only the processing of selected records is different.
  • The driver verifies that the table is insertable. This means that the database designer marked the table as "insertable" using the Designer and that the table meets certain requirements.
  • The driver verifies that the user has the delete privilege on the table.
  • The driver rereads the selected record with lock. This read will wait until the record becomes available.
  • The table filter and record type filter, if any, are evaluated. If the record no longer passes both filters (if they exist) then an error (−1102) is returned.
  • If no error occurs to this point, the record is deleted. If an error occurs after locking the record, the record is unlocked.


Embedded Path Name Tags Example

The following example assumes that the catalog contains a table named "UserToFile". This table has two columns named "UserName" and "FileName". This path name directs Relativity to look for a row in this table in which the UserName column equals the user ID given by the connection string attribute named "UID". If the connection string attribute cannot be found, the string "Unknown" is used in place of the user ID. If no row of the table matches the user ID, or if some error occurs opening the UserToFile table, the path name "orderfil" is used. Otherwise, the path name is the contents of the FileName column in the UserToFile table.

{COL, UserToFile.FileName, UserName={CONN, UID, Unknown}, orderfil}


Joins (Advanced)

See Also

The Relativity ODBC driver extends the OUTER JOIN syntax to support inner joins and nested outer joins. The syntax for an INNER JOIN or LEFT OUTER JOIN is:

inner-join ::=
    table-reference INNER JOIN table-reference
   ON search-condition
left-outer-join ::=
    table-reference LEFT OUTER JOIN table-reference
   ON search-condition
table-reference ::=
   table-name | [(] inner-join [)] | [(] left-outer-join [)]

where table-name can be a table name or a table name followed by a correlation name. For example, the following statement uses a three-way outer join to create a list of sales orders. For each sales order, all line numbers (if any) are listed, and for each line number, the part and description (if any) are listed.

SELECT Order.SONum,
    Line.LineNum,
    Part.PartNum,
    Part.Description
FROM Order LEFT OUTER JOIN
    (Line LEFT OUTER JOIN Part
    ON Line.PartNum=Part.PartNum)
    ON Order.SONum=Line.SONum

Note The rightmost ON corresponds to the leftmost LEFT OUTER JOIN.

See Also

For Advanced Users

Additional Supported ODBC SQL Grammar (Advanced)

Limitations to ODBC SQL Grammar (Advanced)

SQL Table Reference List Syntax


INSERT Statement Processing (Advanced)

The Relativity ODBC driver implements an INSERT statement by constructing a new record and writing it to the underlying data file. Consequently, the table may not have any columns defined on repeating items with unbound subscripts. The record is constructed by the following sequence of steps:

  • The driver verifies that the table is insertable. This means that the database designer marked the table as "insertable" using the Designer and that the table meets certain requirements.
  • The driver verifies that each column specified in the INSERT statement is insertable and that the user has the insert privilege on that column.
  • The driver verifies that no column is specified more than once in the INSERT statement.
  • The driver verifies, for each column in the table:
    1. If no value is provided for a column, the column either has a default or is marked as nullable.
    2. If a NULL is provided for a column, the column is marked as nullable
    3. If a value is provided for a column, the provided value does not exceed that maximum length for the column.
  • If the table is defined on multiple record types, the driver attempts to determine which record type will be used for the new record. If there is more than one possibility, the driver will perform the following steps until they succeed or fail; if the following steps fail another record type will be selected and the following steps tried again. This will continue until either the process succeeds or all possible record types have been tried.
  • The driver initializes a record area to spaces. Then, every data item in the file definition is examined. If an item will not receive a value from a column (either a column specified in the INSERT or a column with a default value) and the item is not a redefinition of another item and the item is not subscripted (a repeating item), then the item is initialized. (Note: If the table definition refers to one or more record types, and if an item in the record type is a redefinition of an item which is not in the record type, the redefining item is initialized anyway.) The initialization proceeds as follows:
    • If the item has an associated null condition item, the null condition item is set to indicate a null.
    • If the item has a default value, the default value is stored in the item.
    • Otherwise, if the item is a numeric elementary or function item, zero is stored in the item.
  • The driver attempts to ensure that the table filter and record type filter, if any, are true. It does this by looking through the filter expression(s) for condition items which are not "ORed" with other condition items. It attempts to make these condition items true or, if the filter specifies that the item is complemented, false.
  • For each column which is being set to null (whether because no value was specified and the column has no default or because a null was explicitly specified), the null condition items associated with the items under the column are set.
  • For each column which is being set to a value (either specified in the INSERT or the column default value), the value is stored into every item under the column. Normally there is only one such item per column, but there are more if the item is part of a set of redundant data items.
  • The driver computes the length of the record just constructed.
  • The table filter and record type filter, if any, are evaluated. The generated record must pass both filters, if they exist, before it can be placed in the file.
  • If an error has occurred since the record type was chosen, and the table is defined on another record type, the next record type is selected and the driver attempts to construct another record, starting with the initialization of a new record area.
  • The driver writes the record to the data file. If the data file is a sequential file or if it is a relative file and no column is defined on the record number item, the record is written to the end of the file. Otherwise, the driver performs a "random write" to put the record in the file with the record number (if relative) or key values (if indexed) determined by the above procedure.


INSERT Requirements

The Relativity ODBC driver uses the following procedure to determine whether a table is insertable:

  • A table created by a CREATE TABLE statement is insertable.
  • A table which is not marked as "insertable" using the Designer is not insertable.
  • If any columns are defined on subscript items or on items with unbound subscripts, the table is not insertable.
  • If the table is defined on one or more record types and if all of the items (in the record type) underlying all of the columns in the table are not subordinate to a single 01-level item, the table is not insertable.
  • If the table is not defined on a record type and all of the items underlying all of the columns in the table are not subordinate to a single 01-level item, the table is not insertable.
  • If any pair of columns have overlapping items (items occupying the same part of the record area), the table is not insertable.
  • If any two elementary items in the file definition overlap (occupy the same part of the record area), the table is not insertable. (An item that is a redefinition of another item is ignored for this purpose.) If the table is defined on one or more record types then, for each record type, this test is performed only on the items that are part of that record type. (A redefining item that is part of a record type is ignored only if the redefined item is also part of the record type.) If the test fails for a given record type, that record type may not be used for inserts; the table is insertable unless all of the record types fail the test.


Nullable Columns

A nullable column is one which may be null. Null is not a value; it indicates the absence of a value. There are several conditions that may cause the Relativity ODBC driver to return a null indication when a column value is requested. Some of these are not conditions that can be forced when inserting or updating a row. For example, if a table is defined on multiple record types, there may be a column that is defined on an item that is in only some of the record types; this column will be null for any row derived from a record which of a type which does not include the item. In such a row, the column cannot be set to a (nonnull) value; on other rows, it may not be possible to set it to null.

The Relativity ODBC driver can only put a null into a column if it is marked nullable (by turning on the appropriate check box) in the Designer and one of the following is true:

  • The column is defined on a data item which has a null condition item. If the data item has the “Null Not” attribute set, the null condition item must have a “Set To False” value.
  • The column type is DATE or TIMESTAMP.


Examples of QEP Output

Examples of Query Execution Plan Output

Single-Table SELECT

Relativity processes this SQL statement:

SELECT * FROM emp WHERE last_name='walker'

The Query Plan window displays:

The square box labeled EMP represents a Base (Normal) node. The diamond represents a filter node. By double-clicking on the diamond labelled Filter (Normal) in this example, you get a description of the node.

Single-Table SELECT with Subquery

Relativity processes this SQL statement:

SELECT t1.last_name FROM emp t1, dept t2
WHERE t1.dept=t2.dept_id AND t1.salary > ALL
(SELECT t3.salary FROM emp t3 WHERE t1.dept='d19')

The Query Plan window displays:

When an SQL statement contains subqueries or unions, you can view the query execution plan for each subquery (to a maximum of ten) by selecting one of the ten subquery menu selections.

For the sample query we submitted, if you click on the Subquery menu and select Subquery 1, the following window appears:

Query Execution Plan Nodes

The following nodes may appear in a query execution tree:

Base (Normal) | Synonymous with a base table. If the index name is set in this node, the node will use the named index for ordering purposes. An asterisk (*) appears after the index name if it is a unique index.
Base (Range) | Manages the execution of ranges on the base table.
Distinct | Handles the distinct operation.
Filter (Normal) | Performs all non-optimized filters. The filter node filters out data which does not pass the associated where predicates.
Filter (Range) | Contains and manages the index ranges for optimized filters. Also downloads the index ranges for optimized filters to base range nodes.
Group break | Detects breaks based on the group by clause.
Group by | Works in conjunction with the group break node.
Join (Outer Normal) | Outer join Cartesian product node.
Join (Outer Range) | Outer join range node, where the outer join uses an index.
Join (Normal) | Cartesian product join node. This node appears in a non-optimized query execution plan as well as some optimized query execution plans.
Join (Range) | Range join node. This node appears in an optimized query execution plan.
Set function | Calculates MIN, MAX, SUM and AVG aggregate function results.
Subquery | Handles all fetching from a subquery query execution plan tree for a single subquery.
Temp Table | Contains all expressions required to fill a temporary table at SQLExecute time. Any future reference to base tables below the temporary table is replaced with references to the temporary table. This node is also used for group by and distinct operations.
Union | Handles cycling through all union query execution plan trees and select lists.


Examples of Optimization

The following examples illustrate how Relativity optimizes different types of SQL queries. These examples assume that an index exists on the table emp, with the dept column as the first segment in the key and the last_name column as the second segment in the key.

Query SELECT

SELECT * FROM emp WHERE dept > 'D11'

For this query, the query execution plan would use a filter range to locate the row set. The literal 'D11' would be used as the key for this filter.

Multiple ANDed Filters

SELECT * FROM emp WHERE last_name = 'Lambert' AND dept = 'D22'

Here, the key would be set to D22,Lambert, and the index would be positioned to the first record matching this key. Once the base table exceeded this range, Relativity would return NO_DATA_FOUND. Segmented keys can be used for single-key seeks, but only in a left-to-right order in the multi-segment key. For example, last_name could not be optimized for filtering using indexes without dept also included as a filter predicate.

Multiple ORed Conditions

SELECT * FROM emp WHERE dept = 'D11' OR dept = 'D22'

In this case, multiple ranges would be used. First, the range would be set to D11 and the row set would be returned. Next, the range would be set to D22, and the row set for the range would be returned.

Push-Down Filter

SELECT * FROM emp, dept,_addr
WHERE addr.street = 'Main' AND emp.dept = dept.dept_id

The term push-down filter means moving the filter as close to the base table as possible. This limits the number of rows that join nodes will have to process. Push-down filters are in effect whether indexes exist or not.

In this example, the filter addr.street = ‘Main’ would be pushed below the join node, thereby reducing the rows returned by the table addr.

Join Ordering

SELECT * FROM emp t1, dept t2 WHERE t1.dept = t2.dept_id

In this case, Relativity would use the index on emp.dept to control access to the table emp. Relativity would use the table dept to control the current range of rows returned by emp. Once dept has been traversed, the query is complete.

In three-way or larger joins, tables are ordered with the least number of rows to the left in the join ordering plan. In the join ordering plan, seeks are performed on the right tables in a join node based upon the keys specified in the left table. This results in the fewest number of seeks for any indexed tables. The basis for this assumption is that the smaller table will be the primary keyed tables, and any other larger tables will contain foreign keys. Index ordering will always take precedence over join ordering.

Filter Cost-Based Optimization

The examples below assume that table emp has 20 rows, table dept has 5 rows, and the following conditions exist:

  • emp has a unique index on column emp_id
  • emp has a non-unique index on column dept
  • dept has a unique index on column dept_id

Simple join ordering

SELECT * FROM emp, dept WHERE dept.dept_id = emp.dept

In this case Relativity will use the emp.dept index as table emp has the largest number of rows (20). This is called join ordering. Table emp will then get 5 seeks with the 5 rows in table dept.

Join ordering with cost estimation

SELECT * FROM emp, dept
WHERE dept.dept_id = emp.dept AND emp.emp_id = 'E4404'

Relativity calculates that the most rows table emp can return is 1. The new value 1 is the virtual row count of table emp based on any where clause that refers to table emp. It bases this on the unique index that matches the where clause. Non-unique indexes (even no index at all) will still affect the virtual row count of a table. The virtual row count can be seen by turning on query plan and double clicking any base table node.

Based on the fact that table emp’s virtual row count is 1, the new plan will put table emp to the left and position table dept using index dept.dept_id. This can be seen in the query plan. It is an example of cost-based optimization where a calculated or estimated cost is assigned to each base table predicate. Using this data, the join optimizer can make better decisions when ordering the query plan.

ORDER BY, GROUP BY, and DISTINCT

SELECT * FROM emp, dept WHERE emp.dept = dept.dept_id
ORDER BY dept.name

Relativity will use the index for emp for the join, and the index on dept.name will be used to control ordering. Only a single pass through each file is then required to complete this query.

Subqueries

SELECT * FROM marketing_emps
WHERE marketing_emps.salary > ALL
(select sales_emps.salary from sales)

Example 1: No index exists on sales_emps.salary

Relativity will perform an Fcalc (Maximum) optimization on the subquery predicate. The first time the query is executed the entire subquery will be evaluated and the maximum value of sales_emps.salary stored. From that point on no calls to read table sales_emps. This is in fact a snapshot of table sales_emps in the same manner that an order by temporary table snapshots a table’s data.

Example 2: An index exists on sales_emps.salary.

Relativity will perform an Fcalc (Maximum) optimization and an indexed seek. The table will be positioned to the last record in the table (based on the index ordering). This will be the only call to read the table until the query is complete.

Note, where, group by and distinct clauses in the subquery cause these optimizations to be disabled under certain circumstances.

Correlated Subqueries

Correlated predicates are optimized as compare predicates. The column that references the outer table is treated like a literal for the purpose of the inner query. The filter optimizer will then optimize the correlated predicate in a normal manner. Both Fcalc and indexed subquery optimizations are disabled for subqueries that contain correlated predicates.


UPDATE Statement Processing (Advanced)

The Relativity ODBC driver implements an UPDATE statement by reading an existing record (with lock), modifying the record image, and rewriting it in the underlying data file.

The record is updated by the following sequence of steps:

  • The next record to be updated is located. Normally, the UPDATE statement specifies a predicate (in a WHERE clause) that limits the records to be updated. The driver follows the same procedure it would for a SELECT; only the processing of selected records is different.
  • The driver verifies that the table is updatable. This means that the database designer marked the table as “updatable” using the Designer.
  • The driver rereads the selected record with lock. This read will wait until the record becomes available.
  • The driver verifies that each column specified in the UPDATE statement is updatable and that the user has the update privilege on that column.
  • If any column is defined on a repeating item with unbound subscripts, the driver gives an error if the UPDATE attempts to change a column which is not defined on a repeating item with unbound subscripts. In such a table, the only columns which may be updated are those defined on repeating items.
  • If any column specified in the UPDATE statement is defined on an item which is part of the key which the driver is using to read the record, the update operation would move the record. It would be possible that the driver could enter an infinite loop, updating the same record. The driver uses the prime key as a unique identifier to prevent this. If the prime key allows duplicates, the driver cannot do this, and it returns an error.
  • The driver verifies that no column is specified more than once in the UPDATE statement.
  • The driver verifies, for each column specified in the UPDATE statement:
    1. If a NULL is provided for a column, the column is marked as nullable.
    2. If a value is provided for a column, the provided value does not exceed that maximum length for the column.
  • For each column which is being set to null, the null condition items associated with the items under the column are set.
  • For each column which is being set to a value, the value is stored into every item under the column. Normally there is only one such item per column, but there are more if the item is part of a set of redundant data items.
  • If the table is defined on multiple record types the record must have the same record type after the update operation that it had before the update. If the updated record does not pass the record type filter, an error (−4064) is returned.
  • The driver computes the length of the record just updated.
  • The table filter and record type filter, if any, are evaluated. The updated record must pass both filters, if they exist, before it can be rewritten in the file.
  • If the prime key field of the record was not changed, the record is simply rewritten in the data file. If the prime key was changed, the driver deletes the old record and writes a new record that reflects the modifications made by the UPDATE statement.
  • If value of the current key (the key of reference) in the data file was changed, the driver remembers the prime key value. If the record is seen again while processing this same UPDATE statement, it is skipped.


Waiting for a Record Lock

Warning: The read operation will wait until the file system returns, either with the record locked or with an error. It may not be possible for the user to cancel the wait. If another application on the same machine has the record locked, the other application may not get an opportunity to unlock the record. Both the other application and the ODBC application may hang.


Btrieve Definitions

This page contains the definitions of Btrieve terms.

Btrieve Record Manager | Btrieve Record Manager is a key-indexed record management system designed for high-performance data handling and improved programming productivity.
Btrieve | Btrieve is key-indexed record management software; it is also a trademark of Pervasive Software Inc.
RB Adapter | RB Adapter is a program that acts as an interpreter between the RM File Manager and the Btrieve Interface DLL.
Page | Fixed-length portions of memory used by the RB Adapter.
Index | A storage position, the contents of which represent the identification of a particular record in a file.
Indexed Files | The permanent logical file structure in which each record is identified by the value of one or more keys within that record.
Cache | An area in memory used to store pages of disk files. Caches are used to reduce the number of physical disk I/O requests.
Buffer | An area in memory that is used to temporarily store data.
Ignore | The lock mode value of Ignore means to ignore the WITH LOCK phrase and use the mode indicated in Mode option.
Deny | The lock mode value of Deny means that the WITH LOCK phrase will be denied.
Accept | The lock mode value of Accept means to accept the WITH LOCK phrase. If OPEN WITH LOCK is requested by the application, ignore the open mode indicated in the Mode option.
Accelerated | The open mode value of Accelerated means that Btrieve will not perform transaction logging for the file.
Exclusive | The open mode value of Exclusive means that Btrieve will attempt to open the file for exclusive use by this client.
Read-Only | The open mode value of Read-Only means that Btrieve will allow only read operations on files. No write operations will be allowed.
Normal | The open mode value of Normal means that Btrieve will open the file in the normal mode.
Verify | The open mode value of Verify is ignored by current versions of Btrieve. Older versions interpreted this to mean that write operations were verified.
TCP/IP | Transaction Control Program/Internet Protocol.
IPX/SPX | Novell Netware’s network communications protocol.
Linked Duplicates | For each key value stored in an index page, Btrieve stores two pointers with that key value and two pointers are stored at the end of the record in the data page. The pointers in the index page point to the first and last record with this key value. The pointers in the data page are used to link records with the same key value together. This is the default method of handling duplicates.
Repeating Duplicates | Btrieve stores duplicate key values into the index page and the data pages. Thus, both the key entries in the index pages and the records in the data page are smaller. However, if multiple records have the same value for a key, the value is repeated in the index.
Btrieve Version 5 | Designates Btrieve Version 5.0.
Btrieve Version 6.0 | Designates Btrieve Version 6.0.
Btrieve Version 6.1 | Designates Btrieve Version 6.1.


Relativity Setup - Create Catalog Dialog Box

The Create Catalog dialog box is a Windows common get open file name dialog box that allows selection of the catalog file name, directory and drive where a Relativity catalog should be created. After selecting a valid name and pressing the Open button, the catalog will be created. If the selected file name already exists, an error will occur.

If the catalog is successfully created, a message box will appear to let the user know that a new catalog has been created. After responding to this message box, the created catalog name will be returned to the Relativity setup dialog box as the name of the catalog for the data source.


Relativity Setup - Create Catalog Dialog Box

The Create Catalog dialog box is a Windows common get open file name dialog box that allows selection of the catalog file name, directory and drive where a Relativity catalog should be created. After selecting a valid name and pressing the Open button, the catalog will be created. If the selected file name already exists, an error will occur.

If the Use Fileshare only check box is checked, the user must type the complete server name and file name in the File name text field. The user must specify the name in the format $$FileshareServer\PathToFile, where the FileshareServer must match the name of the server machine (e.g., $$SERVER\test.rcg).

After entering a valid Fileshare server and filename and then pressing the Open button, the server will attempt to create the catalog. If the catalog is successfully created, a message box will appear to let the user know that a new catalog has been created. After responding to this message box, the created catalog name will be returned to the Relativity setup dialog box as the name of the catalog for the data source.

In order to use Micro Focus Fileshare, you must have already configured Fileshare correctly on the client and server. Refer to the Fileshare documentation for details.


Creating a New Relativity Catalog

To create a new Relativity catalog file:

  1. In the DSN tab of the Relativity data source setup dialog box, click Create Catalog. The Create Catalog for Data Source dialog box is displayed. Depending on which file manager options are enabled, either the Generic, or the Setup MF Fileshare Catalog dialog box will be displayed.
  2. In the Look in list, locate the folder where the new catalog should reside.
  3. In the File name field, type or select a file name for the catalog file. This file name should have the .rcg suffix. If you do not type a suffix, the suffix .rcg will be appended to the file name when the file is created.
  4. Click Open. This causes the Relativity Data Manager to create the catalog file and initialize it as a Relativity catalog.
    • If the catalog creation fails, a message box is displayed, showing an error message. Click OK to dismiss the message box. Return to step 1.
    • If the catalog creation succeeds, a message box is displayed, showing that the catalog was created. Click OK to dismiss the message box. The name of the new catalog appears in the Relativity dialog box in the Catalog Name area.


Relativity Setup - Micro Focus Fileshare Catalog Dialog Box

Relativity Setup - Setup Micro Focus Fileshare Catalog Dialog Box

The ODBC Relativity SetUp Micro Focus Fileshare Catalog dialog box allows specification of the Relativity catalog file name on a Micro Focus Fileshare server.

After entering a valid Fileshare server and filename and then pressing the OK button, the catalog name will be validated and returned to the Relativity data source setup dialog box as the name of the catalog for the data source. You must specify the name in the format $$MFFileshareServer\PathToFile, where the MFFileshareServer must match the TCP/IP hostname for the server machine on this client (e.g., $$HOSTNAME\test.rcg).

In order to use Micro Focus Fileshare, you must have already configured Fileshare correctly on both the client and server. Refer to the Micro Focus Fileshare documentation for details.

In order for this dialog to appear, support for Fileshare must be enabled on the Micro Focus Options Page.


Relativity Setup - RM/InfoExpress Catalog Dialog Box

Relativity Setup - Setup RM/InfoExpress Catalog Dialog Box

The ODBC Relativity Setup RM/InfoExpress Catalog dialog box allows specification of the Relativity catalog file name on a RM/InfoExpress server.

After entering a valid RM/InfoExpress server and filename and then pressing the OK button, the catalog name will be validated and returned to the Relativity data source setup dialog box as the name of the catalog for the data source. You must specify the name in the format \\RMInfoExpressServer\PathToFile, where the RMInfoExpressServer must match the TCP/IP hostname for the server machine on this client (e.g., \\HOSTNAME\test.rct).

In order to use RM/InfoExpress, you must have already configured RM/InfoExpress correctly on both the client and server. Refer to the RM/InfoExpress documentation for details.

In order for this dialog to appear, support for InfoExpress must be enabled on the RM/InfoExpress Options Page.


Relativity Setup - Select Catalog Dialog Box

The Select Catalog dialog box is a Windows common get open file name dialog box that allows selection of the catalog file name, directory and drive for a Relativity catalog that already exists.

After selecting a valid name and pressing the Open button, the catalog name will be returned to the Relativity setup dialog box as the name of the catalog for the data source.


Relativity Setup - Select Catalog Dialog Box

The Select Catalog dialog box is a Windows common get open file name dialog box that allows selection of the catalog file name, directory and drive for a Relativity catalog that already exists.

If the Use Fileshare only check box is checked, the user must type the complete server name and file name in the File name text field. The user must specify the name in the format $$FileshareServer\PathToFile, where the FileshareServer must match the name of the server machine (e.g., $$SERVER\test.rcg).

After entering a valid Fileshare server and filename and then pressing the Open button, the server will validate the catalog name. If the catalog exists, the created catalog name will be returned to the Relativity setup dialog box as the name of the catalog for the data source.

In order to use Micro Focus Fileshare, you must have already configured Fileshare correctly on the client and server. Refer to the Fileshare documentation for details.


Selecting an Existing Relativity Catalog

To select an existing Relativity catalog file:

  1. In the DSN tab of the Relativity data source setup dialog box, click Select Catalog. The Select Catalog for Data Source dialog box is displayed. Depending on which file manager options are enabled, either the Generic, or the Setup MF Fileshare Catalog dialog box will be displayed.
  2. In the Look in list, locate the folder where the catalog resides.
  3. In the File name text field, type or select the file name for the catalog file. This file name should have a suffix of .rcg, .rct, or .rcm. If you do not type a suffix, the suffix .rcg will be appended to the file name when the file is opened.
  4. Click Open. This causes the Relativity Data Manager to open the file and verify that it is a valid Relativity catalog. The Data Manager uses the RM/COBOL or Micro Focus COBOL file manager to open the file.
    • If the file is not a valid Relativity catalog, a message box is displayed, showing an error message. Click OK to dismiss the message box. Return to step 1 to select a different file.
    • If catalog verification succeeds, the name of the catalog appears in the Relativity dialog box in the Catalog Name area.


Relativity Setup - Select Query Plan File Dialog Box

Relativity Setup - Query Plan File Dialog Box

The Select Query Plan File dialog box is a common get open file name dialog that allows selection of the query plan file name, directory and drive.

After selecting a valid name and pressing the OPEN button, the file name will be returned to the Relativity setup dialog box and used to store the query plan information for analysis with the Query Plan Viewer.


Relativity Setup Dialog: File Manager Tab

See Also

The Relativity data source setup dialog box contains multiple tabs. If support for both Micro Focus COBOL and RM/COBOL files is installed, the dialog will initially be positioned on the File Manager tab. You must select either RM/COBOL or Micro Focus COBOL before selecting a different tab.

Note: Once you click on Apply or select another tab of this dialog, the File Manager tab will be removed from the dialog. In addition, any tabs specific to the unselected file manager will be removed.

Option | Description
DSN tab | This tab causes the DSN Page to be displayed. The other tabs that are visible from the DSN page is dependent on the file manager selected using the RM/COBOL or Micro Focus COBOL radio buttons on this page.
RM/COBOL tab | This tab causes the RM/COBOL Options Page to be displayed.
RM Btrieve tab | This tab causes the RM Btrieve Options Page to be displayed.
RM/InfoExpress tab | This tab causes the RM/InfoExpress Options Page to be displayed.
Micro Focus tab | This tab causes the Micro Focus Options Page to be displayed.
Micro Focus COBOL radio button | This radio button selects the Micro Focus COBOL file manager for accessing the files used in this data source.
RM/COBOL radio button | This radio button selects the RM/COBOL file manager for accessing the files used in this data source.

See Also

For All Users

Adding, Modifying, and Deleting a Relativity Data Source

Setting Up a New Relativity Data Source


Relativity Setup Dialog Box (MF)

Relativity Setup Dialog Box - DSN Page (MF)

See Also

The Relativity DSN Options page is used to set up options that are independent of Micro Focus COBOL.

Option | Description
Micro Focus tab | This tab causes the Micro Focus Options Page to be displayed.
Data Source Name text field | This text field contains a name that identifies the data source, such as Payroll or Personnel.
Description text field | This text field contains an optional description of the data in the data source; for example, "Hire date, salary history, and current review of all employees."
Catalog Name read-only text field | This read-only text field contains the complete name, including the drive letter, of the catalog associated with the data source. The catalog name may be changed by choosing the Select Catalog or Create Catalog button which will display a dialog box to allow specification. Choose the Select Catalog button if the catalog already exists. Choose the Create Catalog button to create a new catalog.
Select Catalog button | Click this button to select a catalog file if the catalog already exists. This will open the Select Catalog dialog, or, if Fileshare is enabled, the Setup Micro Focus Fileshare Catalog dialog.
Create Catalog button | Click this button to create a new catalog file. This will open the either the Create Catalog dialog, or, if Fileshare is enabled, the Setup Micro Focus Fileshare Catalog dialog.
Generate Query Plan check box | This check box will enable viewing of Query plans from this version of Relativity. The query plan will be placed in the file identified in the Query Plan File read-only field. See Viewing a Query Plan.
Query Plan File read-only text field | This read-only text field contains the complete name, including the drive letter, of the file into which query plan information will be stored. Use the Query Plan Viewer to examine the Query Plan File. Use the Browse button to change the Query Plan File.
Browse button | This button will select or change the file to which the query plan is written. Press this button to display the Query Plan File Dialog Box.
Code Page combo box This combo box contains the code page that the ODBC application is expecting characters. Note that this is not the code page of the characters in the actual data file, that information is associated with the file in the data source’s catalog. Relativity performs the mapping between the characters in the file’s code page and the ODBC application’s code page.

Valid values for the Code Page combo box are:

  • <default>
  • Windows Latin 1
  • Windows Latin 2
  • Windows Latin 5
  • Windows Cyrillic
  • Windows Greek
  • Windows Baltic Rim
  • MS-DOS Latin US
  • MS-DOS Latin 1
  • MS-DOS Latin 2
  • MS-DOS Cyrillic
  • MS-DOS Cyrillic CIS-1
  • MS-DOS Turkish
  • MS-DOS Portuguese
  • MS-DOS Icelandic
  • MS-DOS French Canada
  • MS-DOS US Canada
  • MS-DOS Greek
  • MS-DOS Greek 1
  • MS-DOS Greek 2
  • MS-DOS Baltic Rim
  • EBCDIC US Canada
  • EBCDIC International
  • EBCDIC Greek
  • EBCDIC Latin 5 Turkish

The defined code page number for each code page is displayed to the right in parenthesis.

Translator read-only text field | This read-only text field contains the current translator for the Data Source. This utility intercepts data between the ODBC application and the Data Source and performs translation on the data. To change the current translator, press the Select Translator button.
Select Translator button | This button will select or change the translator DLL that is in use.

See Also

For All Users

Adding, Modifying, and Deleting a Relativity Data Source

Format of Existing Files


Relativity Setup - Micro Focus Options Page

See Also

The Micro Focus Options page is used to configure file manager options that are unique to the Micro Focus COBOL file manager.

Option | Description
DSN tab | This tab causes the DSN Page (MF) to be displayed.
Created Tables Options group | The options in this group are used to configure the file format of indexed files that are created as a result of SQL CREATE TABLE statements.
New File Format list This list box specifies the file format of indexed files that are created as a result of SQL CREATE TABLE statements. The available entries are:
  • <None>
  • Micro Focus
  • Btrieve
  • C-ISAM
  • LEVEL II COBOL
  • IDXFORMAT"4"
  • IDXFORMAT"8"
This entry does not specify the format for existing files. If the New File Format is <None> then the Data File Format specified on the Designer's Data Source Option Page or the DBA's System Option Page is used. If the Data File Format is not present, the Catalog's Format is used.
Data Compression Num numeric field | This numeric field specifies the type data compression number to be used when new files are created as a result of created tables. This field is the same as the Micro Focus COBOL DATACOMPRESS compiler option. The value of 0 indicates that no compression of data records is to take place. The value of 1 indicates that compression algorithm 1 is to be used.
Key Compression check boxes | These three check boxes control compression of keys in new files that are created as a result of creating tables.
Key Compression - Trailing Spaces check box This check box indicates whether compression of trailing spaces is to occur on the keys of new files that are created as a result of creating tables.
  • If checked, the trailing spaces are compressed.
  • If not checked, the trailing spaces are not compressed.
Key Compression - Leading Characters check box This check box indicates whether compression of leading characters is to occur on the keys of new files that are created as a result of creating tables.
  • If checked, the leading characters that are the same as in the previous key are compressed.
  • If not checked, the leading characters that are the same as in the previous key are not compressed.
Key Compression - Duplicates check box This check box indicates whether compression of duplicate keys is to occur on the keys of new files that are created as a result of creating tables.
  • If checked, the repetitions of duplicate keys are compressed.
  • If not checked, the repetitions of duplicate keys are not compressed.
Key Compression - Sparse Character Num numeric field This numeric field specifies decimal number of the sparse character for keys of new files that are created as a result of creating tables.
  • If this field contains a value, keys in the file that contain only this value are suppressed.
  • If this field does not contain a value, the sparse character is disabled.
Catalog Options group | The options in this group are used to configure the file format of the index file that will be used as the catalog. These options are used only when a catalog is selected or created.
Catalog Format list This list box contains the format of the Micro Focus index file that will be used for the catalog. The available entries are:
  • <None>
  • Micro Focus
  • Btrieve
  • C-ISAM
  • LEVEL II COBOL
  • IDXFORMAT"4"
  • IDXFORMAT"8"
The value that is selected in this list box is used when either the Select Catalog or Create Catalog button is pressed. Changing the selection in this list box has no effect on any existing catalog.
Enable Fileshare check box | This check box enables Micro Focus Fileshare access. Setting this check box is necessary in order to create a Relativity catalog on a Fileshare server.

See Also

For All Users

Adding, Modifying, and Deleting a Relativity Data Source

Format of Existing Files


Setting Up a New Relativity Data Source

See Also

When the Relativity data source setup dialog box appears, you must enter information in certain important fields. For most users, the default values will be correct for the remaining fields. The following steps are recommended to set up up a new data source:

  1. In the DSN tab of the dialog box, type the name of a data source in the Data Source Name field.

    Note A data source name must have a length within the range of 1 through 32 characters. The characters [ ] { } ( ) , ; ? * = ! @ \ must not appear in a data source name. The data source name must also begin with a letter.

  2. Optionally, in the Description field, type a description of the data source.
  3. Either create a new catalog or select an existing catalog for the data source.
  4. Click OK to create the data source and dismiss the Relativity data source setup dialog.

See Also

Relativity Setup Dialog: DSN tab

Relativity Setup Dialog: Micro Focus Options


Relativity Setup - RM Btrieve Options Page

See Also

The RM Btrieve Options page is used to set up options needed to access Btrieve files.

Option | Description
DSN tab | This tab causes the DSN Page (RM) to be displayed.
RM/COBOL tab | This tab causes the RM/COBOL Options Page to be displayed.
RM/InfoExpress tab | This tab causes the RM/InfoExpress Options Page to be displayed.
RM File Manager Options group | This group of options configures the behavior of the RM File Manager. The Btrieve Interface DLL is accessed via the RM File Manager.
Enable Btrieve check box | This check box enables Btrieve access. If this box is not checked, no other options may be set. When this box is changed from unchecked to checked, all other options are set to their default values.
Allow File Creation check box | This check box specifies whether you want the RB Adapter to create any new indexed files as Btrieve files. If the box is checked, new indexed files may be created as Btrieve indexed files. If the box is not checked, new indexed files will not created as Btrieve indexed files. This option is checked by default.
Display Initial Message Box check box | This check box specifies whether the initial message box will be displayed when connecting to the data source. This option is used for debugging purposes and should not be used in a production environment. This option is unchecked by default.
Created Files group | This group contains the options used when Relativity creates a Btrieve file.
Duplicates radio buttons These radio buttons control how a Created File's indexes will handle duplicates. The valid values are Linked and Repeating and only one may be selected. The default value for this option is Linked.
  • The Linked radio button causes all keys of all Btrieve files created by Relativity to be linked-duplicatable keys. This is the default method of handling duplicates. Linked-duplicates keys cause Btrieve to store two pointers for each key value stored in an index page and two pointers are stored at the end of the record in the data page. The pointers in the index page point to the first and last record with this key value. The pointers in the data page are used to link records with the same key value together. This is the default method of handling duplicates.
  • The Repeating radio button causes all keys of all Btrieve files created by Relativity to be repeating-duplicatable keys. Repeating-duplicates keys causes Btrieve to store duplicate key values into the index page and the data pages. Thus, both the key entries in the index pages and the records in the data page are smaller. However, if multiple records have the same value for a key, the value is repeated in the index.
Adapter Page Size list | This list specifies the default minimum page size for the RB Adapter. A larger page size transfers more data in a single disk request, requires more time to transfer, and requires more memory to contain the pages. A smaller page size allows more blocks in memory for a fixed amount of memory, but requires more time to randomly access a record by increasing the tree depth of each index for the file. If specified, the value must be a multiple of 512 in the range of 512 to 4096, inclusive. When creating a file, the page size used will be the smallest multiple of 512 sufficient to hold the file overhead, eight keys, the fixed part of the record, or if specified, the RB Adapter Page Size, whichever is greater. The value "Not Set" indicates that Btrieve should determine the minimum page size. The default value for this option is "Not Set".
Btrieve Options group | This group of options configures certain options passed to Btrieve.
Owner text field | This text field specifies the "owner" ID -- actually a security password -- for creating new files and for open requests for existing files. The value is a string of up to 8 characters. The value cannot contain spaces. The field contains no value by default.
Page Size list | This list specifies the maximum page size of any Btrieve file you wish to access. This option determines the size of the buffers allocated in the cache. The maximum page size must be a multiple of 512 bytes and no greater than 4096 bytes. The larger the buffers size, the fewer buffers the client-based Btrieve Record Manager is able to allocate within the memory available. However, after you have started the Btrieve Record Manager, you are not able to open any Btrieve file with a page size larger than the size specified in this option. The value "Not Set" indicates that Btrieve should determine the maximum page size. The default value for this option is "Not Set".
Lock Mode radio buttons These radio buttons are used to specify the manner in which the RB Adapter program is to handle the WITH LOCK phrase on OPEN statements. Valid values are Ignore, Deny, and Accept. The default value for this option is Accept.
  • The Accept radio button causes the WITH LOCK phrase to be accepted. If OPEN WITH LOCK is requested by the application, ignore the open mode indicated in the Mode option.
  • The Deny radio button causes the WITH LOCK phrase to be denied.
  • The Ignore radio button causes the WITH LOCK phrase to be ignored and the mode indicated in Mode option will be used instead.
Mode radio buttons These radio buttons are used to specify an open mode value to RB Adapter at the time each Btrieve file is opened. Valid values are Accelerated, Exclusive, Read-Only, Normal, and Verify. The default value is Normal.
  • The Accelerated radio button causes Btrieve to not perform transaction logging for the file.
  • The Exclusive radio button causes Btrieve to attempt to open the file for exclusive use by this client.
  • The Read-Only radio button causes Btrieve to allow only read operations on files. No write operations will be allowed.
  • The Normal radio button causes Btrieve to open the file in the normal mode.
  • The Verify radio button is ignored by current versions of Btrieve. Older versions interpreted this to mean that write operations were verified.
Debugging Options group | This group of options configures certain debugging features.
Trace File text field | This text field is used to specify the name of a file to which RB Adapter will write a trace. This feature is used when there is a problem with a Btrieve file not being successfully opened. It should not be used in a production environment because it degrades performance and the trace files could become quite large which might exhaust disk space. Specifying the trace file name actually enables the trace feature. If enabled, clear the trace file name to disable tracing. For ease-of-use, a Browse button is available to aid selecting the right trace file. The trace file does not need to exist in order to specify in this option. However, all directories in the specified path need to exist. The field contains no value by default.
Trace File Browse button | This button is used to specify the name of a file to which RB Adapter will write a trace. The located file is placed in the Trace File field.
Version Override list This list box indicates to RB Adapter what version of the Btrieve engine will be used. Sometimes this is necessary because the Btrieve engine may not respond in manner that would distinctly indicate its version. In such cases, the following Engine Version values are available: 5, 60, 61. If no engine override is needed, the value "None" should be selected. The default value is "None".
  • The value 5 designates Btrieve Version 5.0.
  • The value 60 designates Btrieve Version 6.0.
  • The value 61 designates Btrieve Version 6.1.
Undo Changes button | This button restores all RM Btrieve options to the value last saved.
Restore Defaults button | This button resets all RM Btrieve options except Enable Btrieve to their default values.

See Also

For All Users

Adding, Modifying, and Deleting a Relativity Data Source


Relativity Setup - RM/COBOL Options Page

See Also

The RM/COBOL page is used to configure file manager options that are unique to the RM/COBOL file manager.

Option | Description
DSN tab | This tab causes the DSN Page (RM) to be displayed.
RM Btrieve tab | This tab causes the RM Btrieve Options Page to be displayed.
RM/InfoExpress tab | This tab causes the RM/InfoExpress Options Page to be displayed.
Buffer Pool Size numeric field | This numeric field allows setting of the buffer pool size to be used by the RM/COBOL File Manager for buffer allocation. Note that only the value specified for the first data source connection takes effect when a single application connects to multiple data sources. The value specifies an integer number of kilobytes (1024 bytes) of memory to be allocated for the buffer pool. A value of zero indicates that the default value of 256K bytes should be used. If the value is left blank, the value will be determined by the RELDBMS.INI BufferPoolSize entry. Otherwise, the value must be a decimal integer from 16 to 649.

See Also

For All Users

Adding, Modifying, and Deleting a Relativity Data Source


Relativity Setup Dialog Box (RM)

Relativity Setup Dialog Box - DSN Page (RM)

See Also

The Relativity DSN Options page is used to set up options that are independent of RM/COBOL.

Option | Description
RM/COBOL tab | This tab causes the RM/COBOL Options Page to be displayed.
RM Btrieve tab | This tab causes the RM Btrieve Options Page to be displayed.
RM/InfoExpress tab | This tab causes the RM/InfoExpress Options Page to be displayed.
Data Source Name text field | This text field contains a name that identifies the data source, such as Payroll or Personnel.
Description text field | This text field contains an optional description of the data in the data source; for example, "Hire date, salary history, and current review of all employees."
Catalog Name read-only text field | This read-only text field contains the complete name, including the drive letter, of the catalog associated with the data source. The catalog name may be changed by choosing the Select Catalog or Create Catalog button which will display a dialog box to allow specification. Choose the Select Catalog button if the catalog already exists. Choose the Create Catalog button to create a new catalog.
Select Catalog button | Click this button to select a catalog file if the catalog already exists. This will open the Select Catalog dialog, or, if RM/InfoExpress is enabled, the Setup RM/InfoExpress Catalog dialog.
Create Catalog button | Click this button to create a new catalog file. This will open the Create Catalog dialog, or, if RM/InfoExpress is enabled, the Setup RM/InfoExpress Catalog dialog.
Generate Query Plan check box | This check box will enable viewing of Query plans from this version of Relativity. The query plan will be placed in the file identified in the Query Plan File read-only field. See Viewing a Query Plan.
Query Plan File read-only field | This read-only field contains the complete name, including the drive letter, of the file into which query plan information will be stored. Use the Query Plan Viewer to examine the Query Plan File. Use the Browse button to change the Query Plan File.
Query Plan File Browse button | This button will select or change the file to which the query plan is written. Press this button to display the Query Plan File Dialog Box.
Code Page combo box

This combo box contains the code page that the ODBC application is expecting characters. Note that this is not the code page of the characters in the actual data file. That information is associated with the file in the data source’s catalog. Relativity performs the mapping between the characters in the file’s code page and the ODBC application’s code page.

Valid values for the Code Page combo box are:

  • <default>
  • Windows Latin 1
  • Windows Latin 2
  • Windows Latin 5
  • Windows Cyrillic
  • Windows Greek
  • Windows Baltic Rim
  • MS-DOS Latin US
  • MS-DOS Latin 1
  • MS-DOS Latin 2
  • MS-DOS Cyrillic
  • MS-DOS Cyrillic CIS-1
  • MS-DOS Turkish
  • MS-DOS Portuguese
  • MS-DOS Icelandic
  • MS-DOS French Canada
  • MS-DOS US Canada
  • MS-DOS Greek
  • MS-DOS Greek 1
  • MS-DOS Greek 2
  • MS-DOS Baltic Rim
  • EBCDIC US Canada
  • EBCDIC International
  • EBCDIC Greek
  • EBCDIC Latin 5 Turkish

The defined code page number for each code page is displayed to the right in parenthesis.

Translator Name read-only text field | This read-only text field contains the current translator for the Data Source. This utility intercepts data between the ODBC application and the Data Source and performs translation on the data. To change the current translator, press the Select Translator button.
Select Translator button | This button will select or change the translator DLL that is in use. Press this button to display the Select Translator Dialog box.

See Also

For All Users

Adding, Modifying, and Deleting a Relativity Data Source


Relativity Setup - RM/InfoExpress Options Page

See Also

The RM/InfoExpress Options page is used to set up options needed to access files using RM/InfoExpress.

Option | Description
DSN tab | This tab causes the DSN Page (RM) to be displayed.
RM/COBOL tab | This tab causes the RM/COBOL Options Page to be displayed.
RM Btrieve tab | This tab causes the RM Btrieve Options Page to be displayed.
RM File Manager Options group | This group of options configures the behavior of the RM File Manager. The RM/InfoExpress client DLL is accessed via the RM File Manager.
Enable InfoExpress check box | This check box enables RM/InfoExpress access. If this box is not checked, no other options may be set. When this box is changed from unchecked to checked, all other options are set to their default values.
Allow File Creation check box | This check box specifies whether new files are created on the InfoExpress server. If the box is checked, new indexed files may be created on the InfoExpress server. If the box is not checked, new indexed files will not created on the InfoExpress server. This option is checked by default.
Display Initial Message Box check box | This check box specifies whether the initial message box will be displayed when connecting to the data source. This option is used for debugging purposes and should not be used in a production environment. This option is unchecked by default.
Undo Changes button | This button restores all RM/InfoExpress options to the value last saved.
Restore Defaults button | This button resets all RM/InfoExpress options except Enable InfoExpress to their default values.

See Also

For All Users

Adding, Modifying, and Deleting a Relativity Data Source


Relativity ODBC Driver

Users

The following topics discuss the Relativity ODBC driver and how to install it.

Overview
Hardware and Software Requirements
Adding, Modifying, and Deleting a Relativity Data Source
Connecting to a Relativity Data Source
Using the Relativity ODBC Driver

For Advanced Users

The following topics discuss how to use the Relativity ODBC driver directly.

Connection Strings (Advanced)
SQL Grammar (Advanced)
Data Types (Advanced)
Relativity Indexes (Advanced)
Viewing a Query Plan (Advanced)
Error Messages (Advanced)
Write Support (Advanced)
Literal Column Support (Advanced)
Relativity Options (Advanced)

For Programmers

The following topics provide programming information on the Relativity ODBC driver. They are intended for application programmers and require knowledge of the Open Database Connectivity (ODBC) application programming interface (API).

SQLGetInfo Return Values (Programming)
ODBC API Functions (Programming)
SQL_LOCK_TABLES Statement Option (Programming)
SQLConfigDataSource Attribute Strings (Programming)
Implementation Issues (Programming)


CRDM Errors

The CRDM generates the following "native" error codes and error messages.

−1090 | Serialization failure

A record was changed outside of the current transaction. The Relativity transaction manager uses "optimistic concurrency." It assumes that most transactions are short enough that they will not conflict with other transactions, so it does not lock records until a COMMIT. Therefore, if a transaction changes a record that is also being changed in another transaction, one of the two transactions will fail with this error.

−1113 | Too many active sessions

The DBMS was unable to connect to the data source because there are too many connections from this program. For a standalone (not client/server) version of Relativity, there are a limited number of concurrent ODBC connections for each program. This limit defaults to 32, but it may be configured (with the Relativity Control Panel applet) to be as large as 256.

−1311 | Cannot open any more tables

The DBMS was unable to allocate enough memory to process all of the tables referenced in the current SQL statement. The statement fails.

−1804 | Database file can't be opened

The DBMS was unable to open the database catalog because it was created by a newer version of the DBMS.

For a standalone (not client/server) version of Relativity, the DBMS is a DLL on the Windows machine. For a client/server version of Relativity, the DBMS is the server. In either case, this error means you need to upgrade to a newer version of the DBMS.

−1901 | Failed sysdb on beginsession

The DBMS was unable to find the entry name "Catalog" in the data source definition. The name of the catalog file must be specified in the data source definition, which is located in ODBC.INI (for a 16-bit ODBC driver), the registry (for a 32-bit ODBC driver) or the server data source file (for a server).

For a standalone (not client/server) version of Relativity, this error means the data source definition is invalid. Use the Control Panel to select a catalog.

For a client/server version of Relativity, this error means the data source definition on the server is invalid or missing. This error is usually a result of deleting the server data source definition and then attempting to use a client data source that refers to the missing server data source.

−4001 | Column definition in catalog bad

The database catalog is invalid. A column is invalid for one of the following reasons:

  • The data item on which the column is defined is not included in any of the record types on which the table is defined.
  • The column definition record is too short.
  • The column is a numeric type that is defined on a nonnumeric data type other a fixed-length group or a left-justified alphanumeric (ANS).
−4003 | Catalog invalid; no condition item values found

The database catalog is invalid. Each condition item has one or more data values or ranges of values. The CRDM found a condition item without any values.

−4004 | Catalog invalid; no item for item ID

The database catalog is invalid. The catalog contained a reference to a nonexistent item.

−4005 | Catalog invalid; file has no keys

The database catalog is invalid. The definition of an indexed file did not include any keys.

−4006 | Catalog invalid; file key has no parts

The database catalog is invalid. The definition of a key of an indexed file specified zero key segments.

−4007 | Catalog invalid; record type has no filter

The database catalog is invalid. A table refers to two or more record types, at least one of which has no predicate expression (filter). Such a record type is allowed unless a table uses two or more record types.

−4008 | Catalog invalid; table index has no parts

The database catalog is invalid. The definition of a index of an SQL table specified no columns. The catalog contains index definitions only for tables defined with a CREATE TABLE SQL statement. Indexes for tables defined by the Designer are generated automatically by the CRDM.

−4009 | Catalog invalid; table index is bad

The database catalog is invalid. The definition of a index of an SQL table specified a nonexistent column. The catalog contains index definitions only for tables defined with a CREATE TABLE SQL statement. Indexes for tables defined by the Designer are generated automatically by the CRDM.

−4010 | Predicate in catalog bad

The database catalog is invalid. A predicate (filter) is invalid for one of the following reasons:

  • The table predicate is empty, i.e., it does not specify any condition items.
  • A term of the predicate expression refers to an item other than a condition item.
  • A term of the predicate expression refers to a subscripted condition item, but no column in the table uses the same subscript.
  • A condition item occurs twice in the same factor of the predicate expression. A factor is a sequence of condition items separated by AND operators.
  • Two factors of a predicate expression are identical.
  • An internal error in CRDM caused the internal representation of the predicate to be incorrect.
−4011 | Record type predicate in catalog bad

The database catalog is invalid. A predicate (filter) used to distinguish record types is invalid for one of the following reasons:

  • The record type predicate is empty, i.e., it does not specify any condition items.
  • A term of the predicate expression refers to an item other than a condition item.
  • A term of the predicate expression refers to a subscripted condition item or a condition item defined on a subscript item.
  • The table is defined on two or more record types, but at least one has no predicate (filter).
  • The table is defined on two record types which have identical predicate (filter) expressions.
  • An internal error in CRDM caused the internal representation of the predicate to be incorrect.
−4012 | Record type item in catalog bad

The database catalog is invalid. An item specified in a record type is either a condition item, subscript item, or working storage item.

−4013 | Catalog invalid; table has no columns

The database catalog is invalid. The CRDM attempted to open a table but no columns are defined on the table.

−4014 | Bound subscript item outside allowed range

The database catalog is invalid. A bound subscript item is invalid for one of the following reasons:

  • The value of a bound subscript is greater than the maximum value of the subscript (the number of occurrences of the repeating items on this record).
  • The condition item value not either two bytes nor four bytes long.
−4015 | Variable occurrence item outside allowed range

For an (elementary or group) item with a variable number of occurrences (i.e., defined with an OCCURS DEPENDING ON ... clause), the data item which contains the actual number of occurrences in the record has an invalid value. The value is either less than the minimum number of occurrences or greater than the maximum nujber of occurrences.

−4016 | Value of BIT data neither 0 nor 1

The value of a column of type bit is not either zero or one.

−4017 | Value of numeric item conflicts with column range

The value of a numeric item is invalid. The value may result in a column value which is outside the range of possible values for a column of that type. If a date transform is defined on the item, the month or day may be zero or too large.

−4018 | Catalog invalid; transform function is bad

The database catalog is invalid. A tranform is invalid for one of the following reasons:

  • The code which identifies the transform is invalid (unknown).
  • A column which uses a divide by 100 or multiply by 100 transform is not writable.
  • An NLS key bias function is defined on an item which is not type NLS.
  • An NBS key bias function is defined on an item which is not type NBS.
−4019 | Catalog invalid; working storage item not recognized

The database catalog is invalid. A working storage item is not one of the known types or it is not an NBS type.

−4020 | Catalog invalid; data item offset too large

The database catalog is invalid. The record offset of the item is greater than or equal to the maximum record length of the file.

−4021 | Catalog invalid; data item length too large

The database catalog is invalid. The record offset of the item plus the length of the item is greater than the maximum record length of the file.

−4022 | Binary item length not supported

The database catalog is invalid. The length of a binary data item is not one of the known sizes (1, 2, 3, 4, 8, 16 bytes).

−4023 | Item picture string bad

The database catalog is invalid. The encoded PICTURE string for an edited data item is missing or invalid.

−4024 | Bad item description; type not recognized

The database catalog is invalid. The data type of an item is invalid or the item is used in a way which is inappropriate for this class of item.

−4025 | Column value does not fit data item

The value provided for a new column value does not fit the underlying data item. Examples are, providing a value other than 0 or 1 for a column defined on a condition item, passing a TRUE or FALSE for a column defined on a condition item when the condition item does not have a TRUE or FALSE value.

−4026 | Catalog definition size error

There has been a problem initializing one of the arrays that describe the Maui catalog. One of the arrays is not a multiple of the size of a single entry in the array. This is an internal error and would not normally appear in a released product.

−4027 | Item ID not found in OccursInformation array

There has been a problem initializing either the _FD array or the _OCCURS_INFORMATION array. The ItemID of the current entry in the _FD array can not be found in the _OCCURS_INFORMATION array and the _FD entry indicated that the item is a repeating item. This is an internal error and would not normally appear in a released product.

−4028 | Offset control item not found in FD array

There has been a problem initializing either the _FD array or the _OCCURS_INFORMATION array. The ControlItemID of the current entry in the _OCCURS_INFORMATION array can not be found in the _FD array. This is an internal error and would not normally appear in a released product.

−4029 | Subscript item not found in FD array

There has been a problem initializing either the _FD array or the _OCCURS_INFORMATION array. The SubscriptItemID of the current entry in the _OCCURS_INFORMATION array can not be found in the _FD array. This is an internal error and would not normally appear in a released product.

−4030 | Item not found in FD array

There has been a problem initializing either the _FD array, the _VERIFY_FD array or the _ITEM array. The ItemID from the current entry in the VERIFY_FD array could not be located in the _FD array, or the ItemID from the current entry in the _FD array could not be found in the _ITEM array. This is an internal error and would not normally appear in a released product.

−4031 | FD item size does not match the computed size

There has been an error in defining the FD that corresponds to a record in the Maui catalog. The computed size of the FD does not match the size of the C structure that represents the record. This is an internal error and would not normally appear in a released product.

−4032 | Catalog invalid; duplicate table name

The database catalog is invalid. Two tables have the same name (using a case-insensitive comparison).

−4033 | Item not found in Item array

There has been a problem initializing the _ITEM array.

−4034 | Item not found in Data item array

There has been a problem initializing either the _ITEM array or the _DATA_ITEM array. The class of the current entry in the _ITEM array indicates that the item is a data item, however an entry with the entry’s ItemID can not be found in the _DATA_ITEM array. This is an internal error and would not normally appear in a released product.

−4035 | Item not found in Condition item array

There has been a problem initializing either the _ITEM array, the _CONDITION_ITEM array or the _CONDITION_LIST array. The class of the current entry in the _ITEM array indicates that the item is a condition item, however an entry with the entry’s ItemID can not be found in the _CONDITION_ITEM array or the _CONDITION_LIST array. This is an internal error and would not normally appear in a released product.

−4036 | Null Condition Item not found in FD array

There has been a problem initializing either the _DATA_ITEM array or _FD array. The NullConditionItemID of the current entry in the _DATA_ITEM array can not be found in the _FD array. This is an internal error and would not normally appear in a released product.

−4037 | Catalog invalid; file has no items

The database catalog is invalid. The CRDM attempted to open a table but no items are defined on the file on which table is defined.

−4038 | Catalog invalid; item class is bad

The database catalog is invalid. An item in the catalog is not one of the valid classes: condition, elementary, group, subscript, or working storage.

−4039 | Catalog invalid; parent item ID is bad

The database catalog is invalid. An item in the catalog contained a reference to a nonexistent parent data item.

−4040 | Catalog invalid; subscript item ID is bad

The database catalog is invalid. An item in the catalog contained a reference to a nonexistent subscript item.

−4041 | Table Definition not found in Column array

There has been a problem initializing either the _TABLE_DEFINITION or _COLUMN array. There are no columns in the _COLUMN array for the TableDefID of the current table from the _TABLE_DEFINITION array. This is an internal error and would not normally appear in a released product.

−4042 | Item Set ID not found in Item Set array

There has been a problem initializing either the _COLUMN array or the _ITEM_SET array. There are no item set entries in the _ITEM_SET array for the ItemSetID specified in the current column from the _COLUMN array. This is an internal error and would not normally appear in a released product.

−4043 | Column ID not found in Bound Subscript array

There has been a problem initializing either the _COLUMN array or the _BOUND_SUBSCRIPT array. There are no entries in the _BOUND_SUBSCRIPT array for the ColumnID of the current column from the _COLUMN array and the _COLUMN array entry specified that there are bound subscripts. This is an internal error and would not normally appear in a released product.

−4044 | Catalog invalid; item set ID is bad

The database catalog is invalid. The item set ID in a column definition is invalid; there is no such item set.

−4045 | Catalog invalid; primary not in item set

The database catalog is invalid. The primary item was not found in the item set.

−4047 | Catalog invalid; bad item type in bound subs

The database catalog is invalid. A bound subscript is a condition or data item which provides the value for a subscript item for a single column. The CRDM found either a bound subscript which is not a condition or data item or the item to which the bound subscript is bound is not a subscript item.

−4048 | Catalog invalid; bad column length

The database catalog is invalid. The column specifies a length which is either different from the length of a fixed column, too large, or smaller than the minimum length of a variable-length column.

−4049 | Item not found in Value array

There has been a problem initializing either the _VALUE array, _COLUMN array, _CONDITION_ITEM array, _CONDITION_LIST array, or _DATA_ITEM array. A value ID from one of the latter arrays can not be found in the _VALUE array. This is an internal error and would not normally appear in a released product.

−4050 | Catalog invalid; record type has no items

The database catalog is invalid. A record type was found which is empty, i.e., it specifies no items.

−4051 | Catalog invalid; bad table definition

The database catalog is invalid. A table definition record is too short or has no name.

−4052 | Catalog invalid; bad record type ID in table

The database catalog is invalid. A table in the catalog contained a reference to a nonexistent record type.

−4053 | Catalog invalid; record type has no items

The database catalog is invalid. A record type was found which is empty, i.e., it specifies no items.

−4054 | ID not found in Predicate array

There has been a problem initializing either the _TABLE_DEFINITION array, the _RECORD_VARIANT_PART array or the _PREDICATE array. The TableDefID for the current entry in the _TABLE_DEFINITION array can not be located in the _PREDICATE array and the _TABLE_DEFINITION array indicated that there was a predicate defined on this table or the RecordVariantID for the current entry in the _RECORD_VARIANT_PART array can not be located in the _PREDICATE array. This is an internal error and would not normally appear in a released product.

−4055 | New row contents fail predicate

An UPDATE or INSERT operation was rejected because, after including the column values in the record, the resulting record no longer satisfies both the record type predicate and the table predicate.

−4056 | Catalog invalid; bad control record

The database catalog is invalid. The catalog control record is invalid.

−4057 | Item not found in Table Variant array

There has been a problem initializing either the _TABLE_DEFINITION array or the _TABLE_VARIANT array. The TableDefID for the current entry in the _TABLE_DEFINITION array can not be located in the _TABLE_VARIANT array and the _TABLE_DEFINITION array indicated that there are table variants defined for this table. This is an internal error and would not normally appear in a released product.

−4058 | Item not found in Record Variant array

There has been a problem initializing either the _TABLE_VARIANT array or the _RECORD_VARIANT_PART array. The RecordVariantID from the current entry in the _TABLE_VARIANT array can not be located in the _RECORD_VARIANT_PART array. This is an internal error and would not normally appear in a released product.

−4059 | INSERT and DELETE not allowed on table

An INSERT or UPDATE operation was rejected because the table is marked as not being insertable.

−4060 | UPDATE not allowed on table

An UPDATE operation was rejected because the table is marked as not being updatable.

−4062 | INSERT or DELETE not supported if item subscripted

An INSERT or DELETE operation was rejected because the table is defined on subscripted data items.

−4063 | INSERT or UPDATE not allowed on column
−4064 | UPDATE which changes the record type not supported

An UPDATE operation was rejected because the provided column values, when stored in the current record, changed the interpretation of the record as a record type.

−4065 | Variable length group not valid with given text length

The length of text provided when inserting or updating a column defined on a variable length group does not match an integral number of occurrences of the repeating item.

−4066 | Group with multiple varying repeating items not processed

The length of a variable length group could not be modified because the group contains more than one varying length repeating item.

−4067 | No data source defines the named database

No data source could be found with the specified database name. This probably results from specifying a table qualifier which does not correspond to the Catalog value specified for any data source in ODBC.INI.

−4068 | Bad database format specified in data source

The Format specified for the data source in ODBC.INI was not one the valid values.

−4069 | Only one table may be created at a time

There is already a new table being created. This version of Maui only allows one new table to be created at a time.

−4070 | Year value outside the range of the transform function

A year being processed in a transform function is outside the range of the transform.

−4071 | Catalog invalid; catalog value length exceeds item length

The database catalog is invalid. A CATALOG_VALUE for a condition item or an item default exceeds the length of the underlying data item.

−4072 | Delete from sequential organization file not supported

A DELETE operation was rejected on a sequential organization file.

−4073 | Prime record key cannot be updated

An UPDATE operation attempted to update a column defined on the prime record key of an indexed organization file. This is allowed unless the table includes columns that are defined on subscripts.

−4074 | Current key of reference cannot be updated

An UPDATE operation attempted to update a column defined on the current key of reference of an indexed organization file and the prime record key allows duplicates.

−4075 | Record in catalog too short

The database catalog is invalid. When the CRDM attempted to read the catalog, it found a record that was shorter than the minimum length for that type of record.

−4076 | Catalog invalid; no date/time transform for column

The database catalog is invalid. No date or time transform function was found for a date or time column.

−4077 | YY Century Cutoff and Base Year conflict

The database catalog is invalid. Both YYDateBaseYear and YYDateCenturyCutoff variables were specified with values other than 1900.

−4078 | Catalog too old to convert

An attempt to convert a catalog to the latest version failed because the catalog is too old to be converted.

−4079 | Catalog invalid; item date/time transform conflicts with column

The database catalog is invalid. A date or time transform function was found on an item and the associated column is not a date, time, or datetime column or the column already has its own date or time transform function.

−4080 | Catalog invalid; date/time item must have zero scale

The database catalog is invalid. A date or time transform is associated with an item that is not a data item with zero scale.

−4081 | Catalog invalid; tinyint column must be unsigned

The database catalog is invalid. A column is defined as signed tinyint. Simba supports only unsigned columns of type tinyint.

−4082 | Catalog invalid; privilege granted to nonexistent column

The database catalog is invalid. The database administrator has granted (or revoked) a privilege to a column which is not defined in the catalog. This should not be possible.

−4083 | Catalog invalid; configurable date value is bad

The database catalog is invalid. A catalog variable that indicates the type of configurable dates has an invalid value. Configurable dates are dates in which the order of the month, day, and year parts is determined by a catalog variable. This variable is set by the database administrator.

−4084 | Numeric item contains nonnumeric characters

The value of a column could not be retrieved because the underlying data item is a numeric data item and, in the current record, the item contains nonnumeric characters.

−4085 | Catalog invalid; item refers to unknown function

The database catalog is invalid. A function item refers to an unknown function. The bad function ID may refer to either (1) an internal function which is not supported by this version of the CRDM or (2) a registered function which is missing from the catalog.

−4086 | Catalog invalid; missing function item argument

The database catalog is invalid. A function item has fewer function arguments than the function requires.

−4087 | Catalog invalid; extra function item argument

The database catalog is invalid. A function item has more function arguments than the function allows.

−4088 | Catalog invalid; invalid function item argument

The database catalog is invalid. A function item argument is invalid. Either the argument is a nonexistant item or the argument is badly formed.

−4089 | Error computing function item value

An internal error occurred while computing or storing the value of a function item. This should not be possible.

−4090 | Function argument null when null not allowed

A function item is null because one of its arguments is null. However, the use of this particular function item prohibits nulls. This can occur when a column that does not allow nulls is defined on a function item. It can also occur when a function item is used as the control item of an "OCCURS ... DEPENDING ON."

−4091 | Numeric item contains nonnumeric characters

The value of a column could not be retrieved because the underlying data item is a numeric data item and, in the current record, the item contains nonnumeric characters.

−4092 | Catalog invalid; invalid computed ODO argument

The database catalog is invalid. The argument to a GetOccurCount function item is invalid.

−4093 | Record invalid; invalid computed ODO value

The number of occurrences computed for a GetOccurCount function is either less than zero or greater than the maximum occurrence count of the argument.

−4094 | Catalog invalid; duplicate table names

The database catalog is invalid. Two tables have the same name (using a case-insensitive comparison). This can occur when the same table name is used on different table definitions.

−4095 | INSERTs disallowed; some column(s) depend on subscripts

The table is not insertable. One or more columns in the table are defined on subscripted items which have not been bound. The driver does not support INSERTs in tables in which multiple rows may correspond to a single data file record.

−4096 | INSERTs disallowed; record must contain one 01-level item

The table is not insertable. The record definition either does not include any 01-level item or because it includes more than one. The former can only occur if the table is defined on a record type which does not include an 01-level item. The latter can occur if the record type includes multiple 01-level items or if the table is not defined on a record type.

−4097 | INSERTs disallowed; columns refer to overlapping data

The table is not insertable. Two or more columns refer to data items which overlap in the data file record.

−4098 | INSERTs disallowed; overlapping elementary items

The table is not insertable. Two or more elementary items overlap. If the table is defined on a record type, the driver only considers items included in the record type; otherwise it considers all elementary items.

−4099 | Catalog invalid; some items in set are subscripted

The database catalog is invalid. Some items in an item set are subscripted and some are not.

−4100 | INSERT or UPDATE violates condition integrity constraint

An INSERT or UPDATE operation was rejected because a value was provided for a column that violates that column's integrity constraint. The integrity constraint requires that when a value is stored in the item that corresponds to the column, one of the condition items subordinate to the item be true. This error occurs when that constraint is violated.

−4101 | Catalog invalid; column defined on unused subscript

The database catalog is invalid. A column is defined on a subscript item, but the subscript item is not associated with any repeating data item.

−4102 | INSERT disallowed; no matching record type

An INSERT operation was rejected because no record type on which the table is defined (1) supports INSERTs and (2) defines all of the columns for which the INSERT provided a value.

−4103 | Catalog invalid; column default has an invalid type

The database catalog is invalid. The catalog value that represents a column default value has an invalid data type. All column default values should be represented by character strings.

−4104 | Catalog invalid; column default value is invalid

The database catalog is invalid. The catalog value that represents a column default value is badly formed. The value is not appropriate for the column.

−4105 | Numeric string is too long or contains invalid characters

A string that represents a numeric value is invalid. This is probably a floating-point value.

−4106 | Day-of-year value is out of range

A date value is invalid because the day-of-year part of the date is less than one or greater than the number of days in the year.

−4107 | Day-of-month value is out of range

A date value is invalid because the day-of-month part of the date is less than one or greater than the number of days in the month.

−4108 | Month value is out of range

A date value is invalid because the month part of the date is less than one or greater than twelve.

−4109 | Internal error: Item not found in Function item array

There has been a problem processing either the _ITEM array or the _FUNCTION_ITEM array. The class of the current entry in the _ITEM array indicates that the item is a function item, however no entry with the entry's ItemID can be found in the _FUNCTION_ITEM array. This is an internal error and would not normally appear is a released product.

−4110 | Internal error: Function argument not found in function arg array

There has been a problem processing either the _FUNCTION_ITEM array or the _FUNCTION_ARG array. No entry with the current item's ItemID can be found in the _FUNCTION_ARG array. This is an internal error and would not normally appear is a released product.

−4111 | Catalog invalid; bad alphabet ID in file definition

The database catalog is invalid. The catalog contains a reference to a nonexistant alphabet.

−4112 | Catalog invalid; literal alphabets are not supported

The database catalog is invalid. The catalog contains a literal alphabet which does meet the constraints of the current version of the CRDM.

−4113 | Catalog invalid: bad character set ID encountered

The database catalog is invalid. The catalog contains a reference to a nonexistant character set.

−4114 | Catalog invalid: character set has bad character map

The database catalog is invalid. A character set refers to a catalog value which is the wrong length. If a charset specifies its own mapping from a character set to Unicode, the mapping is contained in two catalog values, each of which must be 256 bytes long.

−4115 | Catalog invalid: unsupported code page detected

The database catalog is invalid. The code page specified in the catalog is not supported by the current version of the driver.

−4116 | Catalog invalid; key collating sequence not supported

The database catalog is invalid. A file definition in the catalog specifies a collating sequence alphabet for one or more keys.

This can happen for one of two reasons. Either the database catalog version is too old or the file keys do not all use the same collating sequence.

−4117 | Catalog invalid; program collating sequence not supported

The database catalog is invalid. A file definition in the catalog specifies a program collating sequence alphabet. This indicates that the program from which the file definition was imported specified a program collating sequence. Program collating sequences are not supported in old versions of the database catalog.

−4118 | Catalog invalid; item default has an invalid type

The database catalog is invalid. The catalog value that represents an item default value has an invalid data type. An item default value should be either in the type of the item or represented by a character string. This may indicate a defect in the Designer.

−4119 | Catalog invalid; item default value is invalid

The database catalog is invalid. The catalog value that represents an item default value is badly formed. Use the Designer to examine the default values for all items in the relevant file definition.

−4120 | Catalog invalid; file and item specify CODE-SET

The database catalog is invalid. A file definition in the catalog specifies a code set alphabet and an item in that file also specifies a code set alphabet. An item specifies a code set if the source program contained a CODE-SET clause with the FOR phrase (a MicroFocus extension) referring to the item. A file specifies a code set if the source program contained a CODE-SET clause without a FOR phrase. Both of these may not be true.

−4121 | Catalog invalid; two items specify different CODE-SETs

The database catalog is invalid. Two items in the same file definition refer to different code set alphabets. An item specifies a code set if the source program contained a CODE-SET clause with the FOR phrase (a MicroFocus extension) referring to the item. A single file cannot have two CODE-SET clauses.

−4122 | Catalog invalid; file key refers to nonexistent item

The database catalog is invalid. A segment of a file key refers to a nonexistent item. The item is specified in the SELECT statement in the COBOL source file.

−4123 | Catalog invalid; item has a duplicate presentation sequence

The database catalog is invalid. Two items in the catalog have the same presentation sequence value. Item presentation sequence values are expected to be unique.

−4124 | Bound subscript item exceeds actual number of occurrences

The value of a bound subscript is greater than the actual number of occurrences on the current record, but does not exceed the maximum number of occurrences. If the column were nullable, the column would be reported as null.

−4125 | Limit table conflicts with existing table named _LIMIT

An error occurred when creating or verifying the Limit table. A table named "_LIMIT" exists in the database and does not have the proper attributes.

−4126 | Access name specified for Limit Table file does not exist

The Limit table could not be created by the RDB_LIMIT stored procedure. The file that was specified by the parameter LIMIT_ACCESS_NAME does not exist and the parameter CREATE was 0.

−4127 | SQL statement exceeded user's service unit limit

The current operation failed because it exceeded a limit. This limit may have been set for this user or for all users. At present, the only limit that may be established is a limit of the number of data file records that may be read while processing a single SQL statement.

−4128 | Specified file definition could not be located

No file definition could be found in the catalog with the specified name. List the file definitions and check the name carefully.

−4129 | Specified table definition could not be located

No table definition could be found in the catalog with the specified name. List the table definitions and check the name carefully.

−4130 | Specified file name (instance) could not be located

No file name (instance) could be found in the catalog with the specified identifier. The identifier is a 1-to-8 letter string. List the file names and check the identifier carefully.

−4131 | Specified table connection could not be located

No table connection could be found in the catalog with the specified name. List the table connections and check the name carefully.

−4132 | Specified file name (instance) is in use

The file name (instance) is referenced by a table connection in the catalog. The file name cannot be deleted while it is in use by table connections.

−4133 | Value of numeric item conflicts with column range

The value of a numeric item is invalid. The value may result in a column value which is outside the range of possible values for a column of that type. If a date transform is defined on the item, the month or day may be zero or too large.

The item is part of a file key (or overlaps a file key). If it were not, the column would be reported as null (no value) because the "Invalid Numerics Return Null" option is selected in the Designer preferences for this data source.

−4134 | Numeric item in key contains nonnumeric characters

The value of a column could not be retrieved because the underlying data item is a numeric data item and, in the current record, the item contains nonnumeric characters.

The item is part of a file key (or overlaps a file key). If it were not, the column would be reported as null (no value) because the "Invalid Numerics Return Null" option is selected in the Designer preferences for this data source.

−4135 | Variable occurrence item contains nonnumeric characters

For an (elementary or group) item with a variable number of occurrences (i.e., defined with an OCCURS DEPENDING ON ... clause), the value of the data item which contains the actual number of occurrences in the record could not be retrieved because it contains nonnumeric characters.

−4136 | Error loading File Manager-dependent DLL

The DBMS was unable to connect to the data source. An error occurred when the DBMS attempted to load the Relativity DLL that communicates with the file manager.

−4137 | Incompatible version of File Manager-dependent DLL

The DBMS was unable to connect to the data source. The Relativity DLL that communicates with the file manager was found to be incompatible with the DBMS.

−4138 | Error loading external File Manager DLL

The DBMS was unable to connect to the data source. An error occurred when the DBMS attempted to load the DLL that contains the file manager (e.g., RMFM32.DLL or MFFM32.DLL).

−4139 | Error initializing File Manager

The DBMS was unable to connect to the data source. An error occurred when the DBMS attempted to initialize (or begin communication with) the file manager.

−4140 | Transaction Manager internal error

An internal error occurred in the transaction manager.

−4141 | Attempt to set condition on read-only data item

An INSERT or UPDATE operation was rejected because a value was provided for a column that is defined on a condition item whose parent function item cannot be modified. Some functions, such as ComputeDate, allow a value to be stored in the function item; the appropriate values are propagated to the function's arguments. Some functions do not permit this.

−4142 | Catalog does not support this feature

An SQL statement is attempting to perform an operation this is not supported by this database's catalog. The Designer must be used to upgrade the catalog.

−4143 | Catalog has expired

The catalog contains an expiration date. The expiration has passed. You should contact the vendor from which you obtained the catalog. If you constructed the catalog yourself, you may use the Designer to change or remove the expiration date.

−4144 | Error starting Data Manager Monitor

The Data Manager did not find a running copy of the Monitor program and was not able to start the Monitor program. This may indicate that the Monitor program is missing.

−4145 | Error computing license information

The Data Manager encountered an error while computing information needed to open the license for the product. Contact support for assistance.

−4146 | Error -4146 communicating with Data Manager Monitor

The Data Manager encountered an error while communicating with the Monitor program. Contact support for assistance.

−4147 | Error -4147 communicating with Data Manager Monitor

The Data Manager encountered an error while communicating with the Monitor program. Contact support for assistance.

−4148 | Error -4148 communicating with Data Manager Monitor

The Data Manager encountered an error while communicating with the Monitor program. Contact support for assistance.

−4149 | Error -4149 communicating with Data Manager Monitor

The Data Manager encountered an error while communicating with the Monitor program. Contact support for assistance.

−4150 | Error -4150 communicating with Data Manager Monitor

The Data Manager encountered an error while communicating with the Monitor program. Contact support for assistance.

−4151 | Error -4151 communicating with Data Manager Monitor

The Data Manager encountered an error while communicating with the Monitor program. Contact support for assistance.

−4152 | Error -4152 communicating with Data Manager Monitor

The Data Manager encountered an error while communicating with the Monitor program. Contact support for assistance.

−4153 | Error -4153 communicating with Data Manager Monitor

The Data Manager encountered an error while communicating with the Monitor program. Contact support for assistance.

−4154 | Error -4154 communicating with Data Manager Monitor

The Data Manager encountered an error while communicating with the Monitor program. Contact support for assistance.

−4155 | Error -4155 communicating with Data Manager Monitor

The Data Manager encountered an error while communicating with the Monitor program. Contact support for assistance.

−4156 | Error -4156 communicating with Data Manager Monitor

The Data Manager encountered an error while communicating with the Monitor program. Contact support for assistance.

−4157 | Error -4157 communicating with Data Manager Monitor

The Data Manager encountered an error while communicating with the Monitor program. Contact support for assistance.

−4158 | Transaction Error: Record changed while locked

A record changed between the first time the transaction manager read (and locked) the record (while preparing to COMMIT) and the second time the transaction manager read the record (while actually performing the COMMIT).

−4159 | Data source does not contain catalog keyword

The DBMS was unable to find the entry name "Catalog" in the data source definition. The name of the catalog file must be specified in the data source definition, which is located in ODBC.INI (for a 16-bit ODBC driver), the registry (for a 32-bit ODBC driver) or the server data source file (for a server).

For a standalone (not client/server) version of Relativity, this error means the data source definition is invalid. Use the Control Panel to select a catalog.

For a client/server version of Relativity, this error means the data source definition on the server is invalid or missing. This error is usually a result of deleting the server data source definition and then attempting to use a client data source that refers to the missing server data source.

−4160 | Relativity license is invalid or missing

The Relativity Data Manager was unable to find a valid license. Reinstall the Data Manager.

−4161 | Relativity license has expired

The license for the Relativity Data Manager has expired. Reinstall the Data Manager with a new license.

−4162 | Relativity license limit exceeded

The use count limit specified in the license for the Relativity Data Manager has been exceeded. That is, there are too many copies of the Data Manager running concurrently. Either use fewer copies concurrently or buy a new license with a larger limit.

−4163 | Provided column value length less than minimum column length

An SQL statement failed because the value provided for one of the columns was shorter than the minimum length for that column. This error most commonly occurs on an UPDATE or INSERT statement.

−4164 | Provided column value length exceeds maximum column length

An SQL statement failed because the value provided for one of the columns was longer than the maximum length for that column. This error most commonly occurs on an UPDATE or INSERT statement.

−4165 | Provided column value length exceeds item length

An SQL statement failed because the value provided for one of the columns was too large to store in the data item on which that column is defined. This indicates that the length of a column has (erroneously) been set larger than the length of the data item. Also, this error most commonly occurs on an UPDATE or INSERT statement.

−4166 | INSERT disallowd; inconsistent item set values

An INSERT operation was rejected because, in the resulting record, two items in an item set have different values. This probably indicates an error in the definition of the table. Pay particular attention to the record type and table filters, if any.

−4167 | Invalid response message from File Manager

The DBMS has received an unrecognizable message from the file manager.

−4168 | Error locating key to license

The Data Manager determined that the key to the product license was invalid. Contact support for assistance.

−4169 | Specified file label string is too short

The new file label passed to a stored procedure is invalid because it is too short. The length of the file label string is determined by the length of the file label field defined for the file definition.

−4170 | Specified file label string is invalid

The new file label passed to a stored procedure is invalid because it is not a numeric string. For this file definition, the file label is stored in a numeric field, so the file label string must be a number.

−4171 | Specified file label string duplicates another path for the same file connection

The new file label passed to a stored procedure is already in use with the specified file connection. That is, it already identifies a path name associated with the file connection.

−4172 | Specified path name could not be located

A stored procedure was unable to locate a path name identified by the specified file label.

−4173 | Cannot delete the only path name without deleting file connection

The DBA_DeletePathName stored procedure was called to delete the only path name associated with file connection. That can only be done by deleting file connection.

−4174 | Provided file label column value does not match the label of any of the files

When attempting to store a value in a file label column, the DBMS found that the value did not match the file label identifying any of the path names associated with the table's file connection. This can occur during an INSERT or UPDATE operation, and also while processing a WHERE clause.

−4175 | Error accessing AGPCNF file

The DBMS received an error when it attempted to retrieve information about the AGPCNF file.

−4176 | Day-of-year value is out of range

A date value is invalid because the day-of-year part of the date is less than one or greater than the number of days in the year.

−4177 | Day-of-month value is out of range

A date value is invalid because the day-of-month part of the date is less than one or greater than the number of days in the month.

−4178 | Month value is out of range

A date value is invalid because the month part of the date is less than one or greater than twelve.

−4179 | Day-of-year value in key is out of range

The value of a date or timestamp column is invalid because the day-of-year part of the date is less than one or greater than the number of days in the year.

The underlying data item is part of a file key (or overlaps a file key). If it were not, the column would be reported as null (no value) because the "Invalid Dates Return Null" option is selected in the Designer preferences for this data source.

−4180 | Day-of-month value in key is out of range

The value of a date or timestamp column is invalid because the day-of-month part of the date is less than one or greater than the number of days in the month.

The underlying data item is part of a file key (or overlaps a file key). If it were not, the column would be reported as null (no value) because the "Invalid Dates Return Null" option is selected in the Designer preferences for this data source.

−4181 | Month value in key is out of range

The value of a date or timestamp column is invalid because the month part of the date is less than one or greater than twelve.

The underlying data item is part of a file key (or overlaps a file key). If it were not, the column would be reported as null (no value) because the "Invalid Dates Return Null" option is selected in the Designer preferences for this data source.

−4182 | Year value is out of range

A date value is invalid because the year part of the date is less than one or greater than 9999.

−4183 | Year value in key is out of range

The value of a date or timestamp column is invalid because the year part of the date is less than one or greater than 9999.

The underlying data item is part of a file key (or overlaps a file key). If it were not, the column would be reported as null (no value) because the "Invalid Dates Return Null" option is selected in the Designer preferences for this data source.


Glossary

This page contains a glossary of terms used within this help file.

Buffer

An area in memory that is used to temporarily store data.

Cache

An area in memory used to store pages of disk files. Caches are used to reduce the number of physical disk I/O requests.

CRDM

COBOL Relational Data Manager. The component of the Relativity database management system that presents the COBOL data files as a relational database.

Relativty data source

A Relativity client data source specifies the ODBC driver used to access the data source, the Relativity catalog (for a single-tier driver) or server name and server data source (for a client data source).

Client data source

A Relativity client data source specifies the ODBC driver used to access the server, the server name and the server data source.

Server data source

A Relativity server data source specifies the Relativity catalog which the Relativity Server uses to access your data files, as well as other database and driver information.

TCP/IP

Transaction Control Program/Internet Protocol.


Micro Focus File Format Definitions

This page contains the definitions of Micro Focus file formats.

File Format | FILETYPE | Description
Catalog Default |  | The file format value <None>, when specified for a catalog, indicates that no specific format is requested. This will result in the standard Micro Focus indexed file format being used.
Data File Default |  | The file format value <None>, when specified for a data file, specifies that no specific format is requested. This will result in a new data file having the file format specified by Catalog Default for existing files or, if there is not a Catalog Default, it will be the same file format as the catalog.
Micro Focus | 0 | The value Micro Focus specifies the file as the default Micro Focus file format.
Btrieve | 6 | The value Btrieve specifies the file as a Btrieve file format.
C-ISAM | 1 | The value C-ISAM will specifies the file as a C-ISAM file format.
LEVEL II | 2 | The value LEVEL II COBOL specifies the file as a Micro Focus Level II indexed file format.
IDXFORMAT"4" | 4 | The value IDXFORMAT"4" specifies the file as an optimized form of the format used by this system, for fast duplicate key handling.
IDXFORMAT"8" | 8 | The value IDXFORMAT"8" specifies a large file format.


Format of Existing Files

By default, the Relativity driver assumes that data files are the same Micro Focus file format as the catalog. This can be overridden by explicitly attaching a file format to a file's connection or by assigning a default for the catalog.

File Connections

The primary means of associating a file format with a data file is by placing a format suffix on the file access name using the Relativity Designer or Administrator.

  • In the Relativity Designer, use the Connections menu item under the Security menu.
  • In the Administrator, use the Connections menu item under the Edit menu.

The suffix is a name that identifies the file’s format and is delimited by brackets ([]). The format names can be:

  • Default
  • Btrieve
  • CISAM
  • LEVELII
  • IDX4
  • IDX8
  • The brackets may also contain a decimal number

Catalog Default

Another means of specifying a file format is to choose a default data file format for the catalog via the Relativity Designer or Administrator Options dialog.

  • In the Designer, it is on the Data Source tab.
  • In the Administrator, it is on the System tab.

Selecting a data file format other than none on the Options dialog, specifies the format for all other data files in the catalog that do not have an explicit file format suffix specified by the file connections.


Micro Focus File Suffix Definitions

Micro Focus File Format Suffixes

This page contains the suffixes that can be added to Micro Focus file names to specify the format of the file.

Default File Format Suffix

The format name Default will specify the file as a system-specific default format, which is the standard Micro Focus indexed file format. (FILETYPE "0")

Btrieve File Format Suffix

The format name Btrieve will specify the file as a Btrieve file. (FILETYPE "6")

CISAM File Format Suffix

The format name CISAM will specify the file as a C-ISAM file. (FILETYPE "1")

Level II File Format Suffix

The format name LEVELII will specify the file as a Micro Focus Level II format file. (FILETYPE "2")

IDX4 File Format Suffix

The format name IDX4 will specify an optimized form of the format used by this system, for fast duplicate key handling. (FILETYPE "4")

IDX8 File Format Suffix

The format name IDX8 will specify a large file format. (FILETYPE "8")

Numeric File Format Suffix

If the format is a number, it is the same as the value specified in the Micro Focus COBOL FILETYPE directive. See the Visual COBOL, COBOL System Reference manual for more details.


ODBC Glossary

This page contains a glossary of Open Database Connectivity (ODBC) terms used within this help file.

API

Application programming interface. A set of routines that an application, such as Microsoft Access, uses to request and carry out lower-level services.

Conformance Levels

Some applications can only use drivers that support certain levels of functionality, or conformance levels. For example, an application might require that drivers be able to prompt the user for the password to a data source. This ability is part of the Level 1 conformance for the application programming interface (API).

ODBC drivers conform to one of three API levels (Core, Level 1, or Level 2) and one of three SQL grammar levels (Minimum, Core, or Extended). Drivers may support some of the functionality in levels above their stated level.

For detailed information about what is in the various conformance levels, programmers should see the Microsoft ODBC SDK Programmer's Reference.

DBMS

Database management system. The software used to organize, analyze, search for, update, and retrieve data.

DDL

Data definition language. Any SQL statement that can be used to define data objects and their attributes. Examples include CREATE TABLE, DROP VIEW, and GRANT statements.

DLL

Dynamic-link library. A set of routines that one or more applications can use to perform common tasks. The ODBC drivers are DLLs.

DML

Data manipulation language. Any SQL statement that can be used to manipulate data. Examples include UPDATE, INSERT, and DELETE statements.

ODBC

Open Database Connectivity. A Driver Manager and a set of ODBC drivers that enable applications to access data using SQL as a standard language.

SQL

Structured Query Language. A language used for retrieving, updating, and managing data.

Translation Option

An option that specifies how a translator translates data. For example, a translation option might specify the character sets between which a translator translates character data. It might also provide a key for encryption and decryption.

Translator

A dynamic-link library (DLL) that translates all data passing between an application, such as Microsoft Access, and a data source. The most common use of a translator is to translate character data between different character sets. A translator can also perform tasks such as encryption and decryption or compression and expansion.


Simba Engine Options: BCDMaxPrecision (Advanced)

See Also

A scalar function CONVERT with value_exp argument that is an expression or a numeric literal with an exponent and a data_type argument of SQL_DECIMAL will result in a column of type SQL_DECIMAL with a default size (precision) of 74, and a decimal digits (scale) of 37. This size may be configured using the BCDMaxPrecision option.

The BCDMaxPrecision value resides in the SimbaEngineOptions key or section. The data of the value is a string containing an integer representing the precision or column size of the SQL_DECIMAL column. The scale or decimal digits of the column will be half of the BCDMaxPrecision value data.

The scalar function CONVERT with a value_exp argument that is a numeric literal without an exponent and a data_type argument of SQL_DECIMAL will determine the precision and scale of the column based on the format value_exp argument’s precision and scale.

Rather than using the manual procedure described here, for the Windows Data Manager User Data Sources, this option may be configured using the Relativity control panel applet on the Global tab.

See Also

Relativity options

Simba Engine options

Scalar Function CONVERT


Simba Server Options: ConnectionIdleTimeout (Advanced)

See Also

Idle clients are controlled by the EnableIdleTimeouts and ConnectionIdleTimeout options. Idle clients are ones that have not sent a request to the Relativity Data Server for longer than the timeout value. In general, it is not recommended to leave a connection open and idle for an extended period of time. These values can be used to trim back the timeout so that clients that have been disconnected by reboots will be discarded more quickly.

The ConnectionIdleTimeout value resides in the SimbaServer key or section. Set ConnectionIdleTimeout to the number of idle minutes to wait before disconnecting this client.

The default is to disconnect idle clients after 360 minutes, or six hours.

See Also

Relativity options

Simba Server options


Simba Server Options: ConnectionQueryTimeout (Advanced)

See Also

Runaway queries are controlled by the EnableQueryTimeouts and ConnectionQueryTimeout options. A runaway query is one that has ceased to return result rows to the ODBC application for longer than the timeout value. Note that long running queries that still occasionally return rows are not considered to be runaway queries. Also note that when the Data Server requests that a query terminate, it is not a hard kill of the Data Server process. It is possible for a query to be in such a state that it will not be able to detect the request to terminate, and thus it will not terminate.

The ConnectionQueryTimeout value resides in the SimbaServer key or section. Set ConnectionQueryTimeout to the number of minutes that a query can execute before being terminated.

The default is to terrminate the query after 60 minutes.

See Also

Relativity options

Simba Server options


Simba Engine Options: DefaultLiteralToDecimal (Advanced)

See Also

When numeric-literals appear in a select-list, they result in a column that is the smallest type necessary to contain the literal. For numeric-literals containing a decimal point, the type will either be SQL_REAL or SQL_DOUBLE. If it is desired that this column be of type SQL_DECIMAL, it is necessary to either use the scalar function CONVERT to force the type to be SQL_DECIMAL, or to set the DefaultLiteralToDecimal option to force all numeric-literals containing a decimal point and without an exponent to be treated as decimal columns. The precision and scale of the column will be the precision and scale of the numeric-literal.

The DefaultLiteralToDecimal value resides in the SimbaEngineOptions key or section. If the first character of data’s value is 1, y, Y, or T, then numeric-literals with a decimal and no exponent will result in SQL_DECIMAL columns.

Rather than using the manual procedure described here, for the Windows Data Manager User Data Sources, this option may be configured using the Relativity control panel applet on the Global tab.

See Also

Relativity options

Simba Engine options

Scalar Function CONVERT


Simba Server Options: EnableIdleTimeouts (Advanced)

See Also

Idle clients are controlled by the EnableIdleTimeouts and ConnectionIdleTimeout options. Idle clients are ones that have not sent a request to the Relativity Data Server for longer than the timeout value. In general, it is not recommended to leave a connection open and idle for an extended period of time. These values can be used to trim back the timeout so that clients that have been disconnected by reboots will be discarded more quickly.

The EnableIdleTimeouts value resides in the SimbaServer key or section. Setting EnableIdleTimeouts to the value of 1 will enable the Idle Timeout. Setting it to 0 will disable the Idle Timeout.

The default is to have the Idle Timeout enabled.

See Also

Relativity options

Simba Server options


Simba Server Options: EnableQueryTimeouts (Advanced)

See Also

Runaway queries are controlled by the EnableQueryTimeouts and ConnectionQueryTimeout options. A runaway query is one that has ceased to return result rows to the ODBC application for longer than the timeout value. Note that long running queries that still occasionally return rows are not considered to be runaway queries. Also note that when the Data Server requests that a query terminate, it is not a hard kill of the Data Server process. It is possible for a query to be in such a state that it will not be able to detect the request to terminate, and thus it will not terminate.

The EnableQueryTimeouts value resides in the SimbaServer key or section. Setting EnableQueryTimeouts to the value of 1 will enable the Query Timeout. Setting it to 0 will disable the Query Timeout.

The default is to have the Query Timeout enabled.

See Also

Relativity options

Simba Server options


Relativity options for a UNIX Data Server (Advanced)

Relativity Options on UNIX Data Servers

Setting of the advanced options on UNIX requires the use of a text editor, such as vi. Futher description of the operation of the text editor will not be discussed here. See the online documentation for how to use this tool.

The file to be edited is named odbc.ini, and it resides in the Relativity Data Server’s installation directory, which is named /opt/microfocus/DataServer, by default.

Within the odbc.ini file are groups of lines called sections. Each section is delimited from others by a section name line, which begins in the first column of the line with a left square bracket, followed by the section’s name, and then terminated with a right square bracket. Following the section name line are lines containing individual value name and data pairs. After the last line of a section, there is a blank line.

On a line containing a value’s name and data, the name is delimited from its data by an equal sign. There may not be spaces before or after the equal sign.

An example of the SimbaEngineOptions section in the odbc.ini file is:

[SimbaEngineOptions]
BCDMaxPrecision=50

The instructions in this help file will request that you locate a section in the odbc.ini file. If that section does not already exist, create the section by inserting a new line in the file following a existing section, putting the section name on the line, enclosed by square brackets, and a blank line.

If it is necessary to insert a new value into a section, add a new line to the section. Place the value’s name on the line, followed by an equal sign, and then the value’s data. Note that there are no spaces before or after the equal sign.


Relativity options on Windows (Advanced)

Relativity Options on Windows

Setting of the advanced options on Windows requires the use of the regedit utility. This utility is started from the Start menu by selecting Run, entering regedit in the Run dialog’s Open field and then pressing the OK button. Futher description of the operation regedit will not be discussed here. See the online documentation for how to use this tool. It should be noted that the system registry contains many operating system wide settings, and inappropriate alterations to these settings can cause irreparable harm to your system. Proceed with caution.

The system registry appears as a directory structure when viewed through regedit. The left hand pane contains registry keys. When a registry key is selected in the left hand pane, the right hand pane contains the values of the selected key. Each value has a name, a type, and a data. All key values described in this section will be of type string.

The registry keys are described using a directory nomenclature. That is, each key will be delimited from its subkeys using backslashes. Furthermore, each key will be prefixed by either HKLM or HKCU. These are shorthands for the two uppermost registry keys, HKEY_LOCAL_MACHINE and HKEY_CURRENT_USER. Windows stores settings in HKLM (HKEY_LOCAL_MACHINE) that are not specific to a single user. Windows stores settings in HKCU (HKEY_CURRENT_USER) that are specific to the current single user.

Windows stores user data sources (sometimes called user DSNs) in the registry key HKCU\Software\ODBC\ODBC.INI. Windows stores system data sources (sometimes called system DSNs) in two different registry keys, depending on whether the ODBC driver is 32-bit or 64-bit. This is called registry redirection. A 32-bit program uses the registry key HKLM\Software\ODBC\ODBC.INI to access the 32-bit system data sources. A 64-bit program uses the registry key HKLM\Software\ODBC\ODBC.INI to access the 64-bit system data sources. However, a 64-bit program (such as regedit) must use the registry key HKLM\Software\Wow6432Node\ODBC\ODBC.INI to access the 32-bit system data sources.

For example, the full name for the key SimbaEngineOptions for the current user would be:

HKCU\Software\ODBC\ODBC.INI\SimbaEngineOptions

The instructions in this help file will request using the above nomenclature that you navigate to a particular key in the registry. If not all of the keys are present in the registry, double check that you have navigated properly. After verifying that the keys are not present, it will then be necessary to create the missing keys before creating the key values.


Relativity Options: Simba Engine Options (Advanced)

Simba Engine Options control the semantics or interpretation of SQL statements:

  • For a UNIX Data Server, the Simba Engine Options are in odbc.ini, in a section named SimbaEngineOptions.
  • For a Windows Data Server, the Simba Engine Options are in the registry key HKLM\SOFTWARE\ODBC\ODBC.INI\SimbaEngineOptions, where they will affect system data sources.
  • For a Windows Data Manager, the Simba Engine Options are either in the registry key HKLM\SOFTWARE\ODBC\ODBC.INI\SimbaEngineOptions, in which case they will affect System Data Sources, or HKCU\Software\ODBC\ODBC.INI\SimbaEngineOptions, in which case they will affect only the User Data Sources for the current user.

The currently documented Simba Engine Options are:

BCDMaxPrecision controls the precision and scale of decimal columns that result from the scalar function CONVERT.

DefaultLiteralToDecimal controls the interpretation of numeric-literals in select-lists.


Relativity Options: Simba Server Options (Advanced)

Simba Server Options control the operation of the Data Server:

  • For a Windows Data Server, the Simba Engine Options are in the registry key HKLM\SOFTWARE\ODBC\ODBC.INI\SimbaServer.
  • For a UNIX Data Server, the Simba Server Options are in odbc.ini, in a section named SimbaServer.

The currently documented Simba Engine Options are:

EnableIdleTimeouts controls if the Data Server will disconnect idle clients.

ConnectionIdleTimeout controls the amount of time a client may be idle before it is disconnected.

EnableQueryTimeouts controls if the Data Server will attempt to terminate runaway queries.

ConnectionQueryTimeout controls the amount of time a query may run before it is attempted to be terminated.


Procedure DBA_AddFileConn (Advanced)

See Also

The DBA_AddFileConn stored procedure creates a new file connection in the Relativity catalog and returns the file connection identifier to the catalog.

{?=call DBA_AddFileConn(FileDefinitionName, PathName, CurrencySymbolCharacter, DecimalPointCharacter, Label)}

DBA_AddFileConn returns the file name identifier in parameter 1, which was set up by a previous call to SQLBindParameter. Parameter 1 must be a CHAR(8) parameter. The value returned is used in subsequent calls to associate the PathName to the table connections. The DBA_ListFileConns stored procedure can be used to return the existing file names in the catalog.

FileDefinitionName is a VARCHAR(128) parameter that contains the name of the COBOL file definition with which to associate the file name. The names of all the file definitions are available from the Files tab in the Catalog window.

PathName is a VARCHAR(255) parameter that contains the path name of the underlying COBOL data file. If the path name does not contain a leading forward or back slash, the path name is interpreted by Relativity as relative to the catalog’s directory.

CurrencySymbolCharacter is a CHAR(1) parameter that contains the currency symbol for the file name. If CurrencySymbolCharacter is not blank, it is set as the currency symbol for this file; if blank, the file definition's currency symbol is used.

DecimalPointCharacter is a CHAR(1) parameter that contains the decimal point character for the file name. The valid characters are ".", "," or blank; blank is the same as ".".

Label is a VARCHAR(100) parameter that contains an unique identifier to associate with the path name PathName in the file connection. Supply a value for this parameter only if there are to be more than one path name in the file connection.

This procedure is available only if the user is the owner or a DBA of the database.

See Also

For Advanced Users

Concepts Used in DBA Stored Procedures

Terms Used in DBA Stored Procedures

Sample Programs

Procedure DBA_AddFileName (Obsolete)


Procedure DBA_AddFileName (Obsolete)

See Also

The DBA_AddFileName stored procedure creates a new file name in the Relativity catalog and returns the file name identifier to the catalog.

{?=call DBA_AddFileName(FileDefinitionName, PathName, CurrencySymbolCharacter, DecimalPointCharacter)}

DBA_AddFileName returns the file name identifier in parameter 1, which was set up by a previous call to SQLBindParameter. Parameter 1 must be a CHAR(8) parameter. The value returned is used in subsequent calls to associate the PathName to the table connections. The DBA_ListFileNames stored procedure can be used to return the existing file names in the catalog.

FileDefinitionName is a VARCHAR(128) parameter that contains the name of the COBOL file definition with which to associate the file name. The names of all the file definitions are available from the Files tab in the Catalog window.

PathName is a VARCHAR(255) parameter that contains the path name of the underlying COBOL data file. If the path name does not contain a leading forward or back slash, the path name is interpreted by Relativity as relative to the catalog’s directory.

CurrencySymbolCharacter is a CHAR(1) parameter that contains the currency symbol for the file name. If CurrencySymbolCharacter is not blank, it is set as the currency symbol for this file; if blank, the file definition's currency symbol is used.

DecimalPointCharacter is a CHAR(1) parameter that contains the decimal point character for the file name. The valid characters are ".", "," or blank; blank is the same as ".".

This procedure is available only if the user is the owner or a DBA of the database.

See Also

For Advanced Users

Procedure DBA_AddFileConn (Advanced)

Concepts Used in DBA Stored Procedures

Terms Used in DBA Stored Procedures

Sample Programs


Procedure DBA_AddPathName (Advanced)

See Also

The DBA_AddPathName stored procedure adds a new path to a file connection in the Relativity catalog.

{call DBA_AddPathName(FileConnectionID, PathName, Label)}

FileConnectionID is a CHAR(8) parameter that contains the file connection identifier that was returned from a previous call to DBA_AddFileConn. The DBA_ListFileConns stored procedure can also be used to return the existing file names in the catalog.

PathName is a VARCHAR(255) parameter that contains the path name of the underlying COBOL data file. If the path name does not contain a leading forward or back slash, the path name is interpreted by Relativity as relative to the catalog’s directory.

Label is a VARCHAR(100) parameter that contains an unique identifier to associate with the path name PathName in the file connection.

This procedure is available only if the user is the owner or a DBA of the database.

See Also

For Advanced Users

Concepts Used in DBA Stored Procedures

Terms Used in DBA Stored Procedures

Sample Programs


Procedure DBA_AddTableConn (Advanced)

See Also

The DBA_AddTableConn stored procedure creates a new table connection in the Relativity catalog.

{call DBA_AddTableConn(TableConnectionName, TableDefinitionName, FileConnectionIdentifier, Cardinality, Comment)}

TableConnectionName is a VARCHAR(128) parameter that contains the name of the new table connection. This is the name to be used with SQL statements to reference this table.

TableDefinitionName is a VARCHAR(128) parameter that contains the name of the table definition with which to associate the table connection. The names of all the table definitions are available from the Tables tab in the Catalog window or from the DBA_ListTableDefs stored procedure.

FileConnectionIdentifier is a CHAR(8) parameter that contains the file name identifier that describes the actual path name of the file. The file name identifier is returned from either the DBA_AddFileConn or the DBA_ListFileConns stored procedure.

Cardinality is an INTEGER parameter that contains an estimate of the number of rows in the new table. Use 0 if no better estimate exists. Relativity will automatically update this value as it works with the actual data file.

Comment is a VARCHAR(255) parameter that is stored with the table connection in the catalog and is displayed by the Relativity Designer.

This procedure is available only if the user is the owner or a DBA of the database.

See Also

For Advanced Users

Concepts Used in DBA Stored Procedures

Terms Used in DBA Stored Procedures

Sample Programs


Procedure RDB_CreateFile (Advanced)

The RDB_CreateFile stored procedure creates the physical file on which a table is based. It opens the table, then closes it. If the physical file on which the table is defined does not exist, an empty file is created. (This procedure is useful in creating test cases with small data files.)

{call RDB_CreateFile(TableName)}

TableName is a VARCHAR(128) parameter that contains the name of the table to open.


Concepts Used in DBA Stored Procedures (Advanced)

See Also

The DBA stored procedures deal with several items, including table definitions, table connections, file connections and file names. The following list describes each of these concepts and others in more detail; but briefly, table definitions form the base definition of an SQL table. One or more table connections can be associated with the same table definition, each table connection being associated with a different underlying COBOL file. The table connection is associated with a file connection in the catalog. A file connection is one or more files with which the table connection is associated. If more than one file is associated the records in the file will be treated as if they all came from the same file, using a file label to distinguish the actual source of the record.

Since it is possible for more than one table definition to be built on a COBOL file, a file connection is stored in the catalog separate from the table connection, allowing the table connections to share a common set of file connections.

  • Path Name. A path name is the actual string of characters with which the operating system identifies a file.

  • File Definition. A file definition is similar to an FD and a SELECT statement within a COBOL program, in that it completely describes a file and its attributes, including the minimum and maximum record size, the location of the keys within the file, and the items within the records. A file definition does not, however, describe the actual path name of the file on the disk drive.

  • File Connection. A file connection is an actual instance of the file described by a file definition. This separation allows more than one file to have the same basic file definition, and consequently, to have individual tables reference these common files.

  • Table Definition. A table definition completely describes a table and its columns. A table definition, however, does not describe the actual path name of the file on the disk drive.

  • Table Connection. A table connection is an actual instance of the table described by a table definition. This separation allows more than one table to have the same basic table definition. Consequently, the table connection brings together the table definition and the file connection to create the table name that is used within an SQL statement to access the underlying COBOL data file.

A file connection contains one or more path names to the actual file. The difference between these two items, however, is that the path name is a system-dependent string that describes the location of the actual file on the physical system, and the file connection is an 8-character identifier that represents that path name to the rest of the catalog. This level of indirection is useful since several tables can be built on the same file. If each of the table connections for these tables contains the same file connection identifier, then changing the path name in the file connection would at once, effectively, change the underlying COBOL file for all the tables that use that file name identifier.

Let's examine the SHIRT-3 Sample catalog, which is included with Relativity. The Shipping, Orders, CreditSales, and CashSales tables all have the same file connection identifier, JCBI. JCBI contains the actual path name, ORDERFIL.

Shipping

Shipping

ORDER-FILE

JCBI

ORDERFIL

721

DB3 Owner

<Null>

Orders

Orders

ORDER-FILE

JCBI

ORDERFIL

721

DB3 Owner

<Null>

CreditSales

CreditSales

ORDER-FILE

JCBI

ORDERFIL

721

DB3 Owner

<Null>

CashSales

CashSales

ORDER-FILE

JCBI

ORDERFIL

721

DB3 Owner

<Null>

If the path name for the file connection with the identifier JCBI were changed to something different, such as C:\INSTALL\ORDERFIL, then the Shipping, Orders, CreditSales, and CashSales tables would all refer to the new location of ORDERFIL.

Notice that the first two columns in this table contain the same value in each row. The first column is the table connection name, and the second column is the table definition name. When a new table definition is created in the Relativity Designer, it automatically creates a table connection with the same name.

See Also

For Advanced Users

Terms Used in DBA Stored Procedures


Calling DBA Stored Procedures (Advanced)

See Also

A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. Stored procedures can be compiled and executed with different parameters and results, and they may have any combination of input, output, and input/output parameters.

The DBA stored procedures have a special syntax that identify them as stored procedures. The general syntax is as follows:

{call StoredProcedureName(Arguments)}

For example, the following Relativity DBA stored procedure, DBA_ListTableConns, returns a result set of all the tables defined in a Relativity catalog:

{call DBA_ListTableConns()}

For the Relativity SHIRT-3 Sample catalog, the following rows are returned in the result set:

Shipping

Shipping

ORDER-FILE

JCBI

ORDERFIL

721

DB3 Owner

<Null>

Sales

Sales

SALES-FILE

DGBI

SALESFIL

449

DB3 Owner

<Null>

Product

Product

PRODUCT-2-FILE

CEBI

PROD2FIL

449

DB3 Owner

<Null>

Orders

Orders

ORDER-FILE

JCBI

ORDERFIL

721

DB3 Owner

<Null>

Discontinued

Discontinued

PRODUCT-2-FILE

CEBI

PROD2FIL

1

DB3 Owner

<Null>

Description

Description

DESCRIPTION-FILE

BFBI

DESFILE

449

DB3 Owner

<Null>

Customer

Customer

CUSTOMER-2-FILE

OPAI

CUST2FIL

129

DB3 Owner

<Null>

CreditSales

CreditSales

ORDER-FILE

JCBI

ORDERFIL

721

DB3 Owner

<Null>

CashSales

CashSales

ORDER-FILE

JCBI

ORDERFIL

721

DB3 Owner

<Null>

BackOrder

BackOrder

PRODUCT-2-FILE

CEBI

PROD2FIL

14

DB3 Owner

<Null>

In this result set, the first column is the name of the table; the second column is the name of the table definition; the third column is the name of the file definition; the fourth column is the file name identifier; the fifth column is the actual file name; the sixth column is the cardinality of the table; the seventh column is the name of the user who created the table; and finally, the eight column is a comment about the table.

Stored procedures can also return a single output parameter. In order to retrieve this parameter, it is necessary to use the SQLBindParameter API to set aside space into which the parameter is returned.

For example, the DBA_AddFileConn stored procedure returns an identifier that can be used to associate a table with an actual file. This identifier is returned as a output parameter from the stored procedure, since there will be only one value. Before the actual call to DBA_AddFileConn, a call to SQLBindParameter must be placed to designate the space into which the parameter is to be returned. SQLBindParameter is called with a parameter number argument of 1, a parameter type argument of SQL_PARM_OUTPUT, a C data type argument of SQL_C_CHAR, an SQL type argument of SQL_CHAR, a column precision argument of 8, a column scale argument of 0, the string variable in which to store the file identifier, the length of this variable, and the address of a variable in which to store the actual length of the identifier. After the SQLBindParameter has been placed, the following SQL statement can be executed:

{? = call DBA_AddFileConn('ORDER-FILE', 'ORDERFL2', ' ', ' ', '')}

The DBA_AddFileConn stored procedure creates a new file name, ORDERFL2, and indicates that it is an ORDER-FILE data file. After executing this statement, the string variable indicated by the SQLBindParameter contains the value "EBCI ". This value can be used in subsequent calls to associate one or more tables with the file ORDERFL2.

See Also

For Advanced Users

Concepts Used in DBA Stored Procedures

Terms Used in DBA Stored Procedures

Sample Programs


DBA Procedures Sample Programs

DBA Procedures Sample Programs (Advanced)

Additional samples are available in the SAMPLES directory when the Relativity Designer is installed. The file, editacnm.c, contains a Windows C program that manipulates the file names in a Relativity catalog with a command line interface. The files, StoredProcedures.frm and StoredProcedures.vbp, contain a Windows Visual Basic program that manipulates the file names in a catalog using the ADO interface. Code fragments from StoredProcedures.frm are also used in the samples below.

Creating a new File Name and Table Connection

The following code fragments demonstrate creating a new file name and then associating it with a new table connection.

Function AddFileName(stFDName As String, stPath As String, stLabel As String) As String

    'Declare all of the needed ADO Objects
    Dim rsParm As New ADODB.Parameter
    Dim rsCommand As New ADODB.Command

    'Set up the command object that will execute the query
    Set rsCommand.ActiveConnection = cn
    rsCommand.CommandText = "{?=call DBA_AddFileConn('"
                             & stFDName
                             & "', '"
                             & stPath
                             & "', '', '', '"
                             & stLabel
                             & "')}"

    'Set up the output parameter
    rsParm.Name = "id"
    rsParm.Direction = adParamOutput
    rsParm.Type = adChar
    rsParm.Size = 8
    rsCommand.Parameters.Append rsParm

    'Execute the Query
    rsCommand.Execute

    'The returned parm is now available in rsParm.Value
    AddFileName = rsParm.Value

End Function
Sub AddTableConn(stNewConnectionName As String, stTableDefName As String, stID As String)

    'Declare all of the needed ADO Objects
    Dim rsParm As New ADODB.Parameter
    Dim rsCommand As New ADODB.Command

    'Set up the command object that will execute the query
    Set rsCommand.ActiveConnection = cn
    rsCommand.CommandText = "{call DBA_AddTableConn('"
                            & stNewConnectionName
                            & "', '"
                            & stTableDefName
                            & "', '"
                            & stID
                            & "', 0,'')}"
    'Execute the Query
    rsCommand.Execute
End Sub
Private Sub cmdAddTableConn_Click()
    Dim stSaveID As String
    stSaveID = AddFileName(txtFDName.Text, txtPath.Text, txtLabel.Text)
    AddTableConn txtConnectionName.Text, txtTableDef.Text, stSaveID
End Sub

Modifying all the File Names in a Catalog

The following code fragment demonstrates how to read all the file names in a Relativity catalog and replace them with a new value.

Private Sub cmdUpdate_Click()
    Dim iPos As Integer, stNewValue As String

    Dim rsConnections As New ADODB.Recordset
    Dim rsCommand As New ADODB.Command
    Set rsCommand.ActiveConnection = cn

    With rsConnections
        .Open "{call DBA_ListFileConns()}", cn, adOpenStatic, adLockOptimistic
    End With

    Do While Not rsConnections.EOF
        iPos = InStr(rsConnections.Fields("PathName"), txtPlaceholder)
        If iPos > 0 Then
            stNewValue = Left$(rsConnections.Fields("PathName"), iPos - 1)
                       & txtNewValue.Text
                       & Mid$(rsConnections.Fields("PathName"),
                              iPos + Len(txtPlaceholder))
            rsCommand.CommandText = "{call DBA_ModifyPathName('"
                       & rsConnections.Fields("FileConnID")
                       & "', '"
                       & stNewValue
                       & "', '"
                       & rsConnections.Fields("Label")
                       & "')}"
            rsCommand.Execute
        End If
        rsConnections.MoveNext   ' Move to next Publishers record.
    Loop

    rsConnections.Close

End Sub


Terms Used in DBA Stored Procedures (Advanced)

See Also

Understanding the following terminology is useful when working with the DBA stored procedures:

  • The Currency Symbol is the character defined in COBOL by the CURRENCY SIGN IS clause in the SPECIAL-NAMES paragraph. This character is used by Relativity when scanning leading white space from items whose picture clauses include the currency symbol.

  • The Decimal Point Character is the character defined by COBOL by the DECIMAL-POINT clause in the SPECIAL-NAMES paragraph. This character is used by Relativity when searching for decimal points and commas in items whose picture clauses include the decimals and commas. Note that decimal (‘.’) and comma (‘,’) are the only two valid values for this character.

  • Cardinality is a measure of the number of rows in a table. Relativity uses this figure when deciding how to most efficiently perform joins of two tables.

  • Updateable is a characteristic of a table in that SQL UPDATE statements will be allowed to change existing rows in the table.

  • Insertable is a characteristic of a table in that SQL INSERT statements will be allowed to insert new rows in the table.

See Also

For Advanced Users

Concepts Used in DBA Stored Procedures


Procedure DBA_DeleteFileConn (Advanced)

See Also

The DBA_DeleteFileConn stored procedure deletes an existing file connection from the Relativity catalog. If any table connection in the catalog refers to the specified file connection, the procedure returns an error.

{call DBA_DeleteFileConn(FileConnectionIdentifier)}

FileConnectionIdentifier is a CHAR(8) parameter that contains the file connection identifier of the file name to be deleted. The file name identifier is returned from either the DBA_AddFileConn or the DBA_ListFileConns stored procedure.

This procedure is available only if the user is the owner or a DBA of the database.

See Also

For Advanced Users

Concepts Used in DBA Stored Procedures

Terms Used in DBA Stored Procedures

Sample Programs

Procedure DBA_DeleteFileName (Obsolete)


Procedure DBA_DeleteFileName (Obsolete)

See Also

The DBA_DeleteFileName stored procedure deletes an existing file name from the Relativity catalog. If any table connection in the catalog refers to the specified file name, the procedure returns an error.

{call DBA_DeleteFileName(FileNameIdentifier)}

FileNameIdentifier is a CHAR(8) parameter that contains the file name identifier of the file name to be deleted. The file name identifier is returned from either the DBA_AddFileName or the DBA_ListFileNames stored procedure.

This procedure is available only if the user is the owner or a DBA of the database.

See Also

For Advanced Users

Procedure DeleteFileConn (Advanced)

Concepts Used in DBA Stored Procedures

Terms Used in DBA Stored Procedures

Sample Programs


Procedure DBA_DeletePathName (Advanced)

See Also

The DBA_DeletePathName stored procedure deletes an existing file connection path name from the Relativity catalog.

{call DBA_DeletePathName(FileConnectionIdentifier, Label)}

FileConnectionIdentifier is a CHAR(8) parameter that contains the file connection identifier containing the path name to be deleted. The file name identifier is returned from either the DBA_AddFileConn or the DBA_ListFileConns stored procedure.

Label is a VARCHAR(100) parameter that contains the label associated with the path name when it was added via DBA_AddFileConn or DBA_AddPathName

This procedure is available only if the user is the owner or a DBA of the database.

See Also

For Advanced Users

Concepts Used in DBA Stored Procedures

Terms Used in DBA Stored Procedures

Sample Programs


Procedure DBA_DeleteTableConn (Advanced)

See Also

The DBA_DeleteTableConn stored procedure deletes an existing table connection from the Relativity catalog.

{call DBA_DeleteTableConn(TableConnectionName)}

TableConnectionName is a VARCHAR(128) parameter that contains the name of the table connection to delete. The DBA_ListTableConns stored procedure can be used to return a list of the current table connections.

This procedure is available only if the user is the owner or a DBA of the database.

See Also

For Advanced Users

Concepts Used in DBA Stored Procedures

Terms Used in DBA Stored Procedures

Sample Programs


Procedure RDB_LIMIT (Advanced)

The RDB_LIMIT procedure creates the Limit table (_LIMIT) in the current data source, if the Limit table does not already exist, and connects it with a COBOL indexed data file, which is to be used to store the actual limit data.

{call RDB_LIMIT(LimitAccessName, Create)}

LimitAccessName is a VARCHAR(128) parameter that contains the name of the data file in which the Limit table stores its data.

Create is a BIT parameter that controls if the file indicated by LimitAccessName is to be created if it does not exist. If the data file does not exist and the Create parameter equals 1, the procedure will create the data file. If the Create parameter equals 0, the procedure will fail if the data file does not exist.

If RDB_LIMIT is called on a data source with an existing Limit table, the Limit table and the data file are verified for the correct format. If the data file identified by LimitAccessName is different from the previous data file and the data file is created or verified successfully, the data file identified by LimitAccessName is used as the new data file for the Limit table.


Procedure DBA_ListFileConns (Advanced)

See Also

The DBA_ListFileConns stored procedure returns a list of all the file connections and their path names currently defined in the Relativity catalog.

{call DBA_ListFileConns()}

DBA_ListFileConns returns a result set of seven columns that contains one row for each path name in the catalog:

FileConnID is a CHAR(8) column that contains a string that identifies this file connection Identifier in the catalog.

Sequence is a signed long column that contains a unique identifier for the path names that are in a file connection.

FileDefinition is a VARCHAR(128) column that contains the name of the corresponding file definition.

PathName is a VARCHAR(255) column that contains actual path name of the file.

CurrencySymbol is a CHAR(1) column that contains the Currency Symbol of the file; this may be null.

DecimalPoint is a CHAR(1) column that contains the Decimal Point character of the file, either "." or ",".

Label is a VARCHAR(100) column that contains the file label associated with the path name in the catalog.

This procedure is available only if the user is the owner or a DBA of the database.

See Also

For Advanced Users

Concepts Used in DBA Stored Procedures

Terms Used in DBA Stored Procedures

Sample Programs

Procedure DBA_ListFileNames (Obsolete)


Procedure DBA_ListFileNames (Obsolete)

See Also

The DBA_ListFileNames stored procedure returns a list of all the file names currently defined in the Relativity catalog.

{call DBA_ListFileNames()}

DBA_ListFileNames returns a result set of five columns that contains one row for each file name in the catalog:

AccessNameID is a CHAR(8) column that contains a string that identifies this file name Identifier in the catalog.

FileDefinition is a VARCHAR(128) column that contains the name of the corresponding file definition.

AccessName is a VARCHAR(255) column that contains actual path name of the file.

CurrencySymbol is a CHAR(1) column that contains the Currency Symbol of the file; this may be null.

DecimalPoint is a CHAR(1) column that contains the Decimal Point character of the file, either "." or ",".

This procedure is available only if the user is the owner or a DBA of the database.

See Also

For Advanced Users

Procedure DBA_ListFileConns (Advanced)

Concepts Used in DBA Stored Procedures

Terms Used in DBA Stored Procedures

Sample Programs


Procedure DBA_ListTableConns (Advanced)

See Also

The DBA_ListTableConns stored procedure returns a list of all the table connections currently defined in the Relativity catalog.

{call DBA_ListTableConns()}

DBA_ListTableConns returns a result set of eight columns that contains one row for each table connection in the catalog:

TableName is a VARCHAR(128) column that contains the table connection name.

TableDefinition is a VARCHAR(128) column contains the name of the corresponding table definition.

FileDefinition is a VARCHAR(128) column that contains the name of the corresponding file definition.

FileConnID is a CHAR(8) column that contains a string that corresponding file connection identifier in the catalog. In previous versions of Relativity, this column was named AccessNameID.

PathName is a VARCHAR(255) column that contains the path name associated with the file name identifier in the FileConnID column. In previous versions of Relativity, this column was named AccessName.

Cardinality is an INTEGER column that contains the cardinality of the table.

Owner is a VARCHAR(128) column that is either null or contains the name of the user who created the table connection.

Comment is a VARCHAR(255) column that is either null or contains a user-defined text string.

This procedure is available only if the user is the owner or a DBA of the database.

See Also

For Advanced Users

Concepts Used in DBA Stored Procedures

Terms Used in DBA Stored Procedures

Sample Programs


Procedure DBA_ListTableDefs (Advanced)

See Also

The DBA_ListTableDefs stored procedure returns a list of all the table definitions currently defined in the Relativity catalog.

{call DBA_ListTableDefs()}

DBA_ListTableDefs returns a result set of four columns that contains one row for each table definition in the catalog:

TableDefinition is a VARCHAR(128) column that contains the table definition name.

FileDefinition is a VARCHAR(128) column that contains the name of the corresponding file definition.

IsUpdatable is a BIT column that contains 1 if the table is marked as updateable or 0 if not.

IsInsertable is a BIT column that contains 1 if the table is marked as insertable or 0 if not.

This procedure is available only if the user is the owner or a DBA of the database.

See Also

For Advanced Users

Concepts Used in DBA Stored Procedures

Terms Used in DBA Stored Procedures

Sample Programs


Procedure DBA_ModifyFileName (Obsolete)

See Also

The DBA_ModifyFileName stored procedure replaces the path name in an existing file name with a new value.

{call DBA_ModifyFileName(FileNameIdentifier, PathName)}

FileNameIdentifier is a CHAR(8) parameter that contains the file name identifier of the file name to be replaced. The file name identifier is returned from either the DBA_AddFileName or the DBA_ListFileNames stored procedure.

PathName is a VARCHAR(255) parameter that contains the new path name for the file name.

This procedure is available only if the user is the owner or a DBA of the database.

See Also

For Advanced Users

Procedure DBA_ModifyPathName (Advanced)

Concepts Used in DBA Stored Procedures

Terms Used in DBA Stored Procedures

Sample Programs


Procedure DBA_ModifyPathName (Advanced)

See Also

The DBA_ModifyPathName stored procedure replaces the path name in an existing file connection with a new value.

{call DBA_ModifyPathName(FileConnectionIdentifier, PathName, Label)}

FileConnectionIdentifier is a CHAR(8) parameter that contains the file connection identifier of the file name to be replaced. The file connection identifier is returned from either the DBA_AddFileConn or the DBA_ListFileConns stored procedure.

PathName is a VARCHAR(255) parameter that contains the new path name for the file name.

Label is a VARCHAR(100) parameter that contains the file label of the path name to modify.

This procedure is available only if the user is the owner or a DBA of the database.

See Also

For Advanced Users

Concepts Used in DBA Stored Procedures

Terms Used in DBA Stored Procedures

Sample Programs

Procedure DBA_ModifyFileName (Obsolete)


Procedure DBA_RelabelPathName (Advanced)

See Also

The DBA_RelabelPathName stored procedure replaces the label of a existing path name in a file connection with a new value.

{call DBA_RelabelPathName(FileConnectionIdentifier, OldLabel, NewLabel)}

FileConnectionIdentifier is a CHAR(8) parameter that contains the file connection identifier of the path name to be relabeled. The file connection identifier is returned from either the DBA_AddFileConn or the DBA_ListFileConns stored procedure.

OldLabel is a VARCHAR(100) parameter that contains the current file label of the path name to modify.

NewLabel is a VARCHAR(100) parameter that contains the new file label to give to the path name.

This procedure is available only if the user is the owner or a DBA of the database.

See Also

For Advanced Users

Concepts Used in DBA Stored Procedures

Terms Used in DBA Stored Procedures

Sample Programs


Procedure DBA_RenameTableConn (Advanced)

See Also

The DBA_RenameTableConn stored procedure changes the name of an existing table connection.

{call DBA_RenameTableConn(OriginalTableConnectionName, NewTableConnectionName)}

OriginalTableConnectionName is a VARCHAR(128) parameter that contains the name of the table connection to be renamed.

NewTableConnectionName is a VARCHAR(128) parameter contains the new name for the table connection.

This procedure is available only if the user is the owner or a DBA of the database.

See Also

For Advanced Users

Concepts Used in DBA Stored Procedures

Terms Used in DBA Stored Procedures

Sample Programs


(Untitled)

ODBC Glossary

This page contains a glossary of Relativity terms used within this help file.

Code Page

Code page specifies a mapping between the numbers 0 through 255 and actual characters. It is roughly equivalent to a SQL character set. Different languages have different character requirements and thus different mappings or code pages have been developed, each suited to a specific class of language needs. Futhermore, there are different mappings between the ASCII and EBCDIC standards for the same characters.

Code Page Mapping

It is possible for the data contained within a data file to be in a different code page than the ODBC application which is using Relativity is expecting. This can happen very easily in a Client/Server environment where the data recorded on the server is most likely based on the ISO 8859 standard (because that is the mapping which the terminals employ) and the client to be using a Windows Code Page. It can also happen on a Client only situation where the COBOL application predates Windows, and data is recorded using one of the old DOS code pages, not an ANSI Windows code page.

Relativity provides the ability to map the same characters between two different code pages, so that they are displayed correctly by the ODBC application and data changed by the ODBC application will the represented using the same code page as the other character data in the data file. For characters that are not present in both code pages, a mapping is created between one unmapped character in one code page to another unmapped character in the other. Each such mapping is assigned to the lowest numerically available unmapped character in each code page. This allows unmapped characters to still be displayed and modified consistently.

Default Code Page

This code page will set the Code Page Mapping to be to and from the code page which the Windows operating system is currently running.

Windows Latin 1

This is the usual code page for the United States.

Non Windows Code Pages

These are defined standard code pages, but are not defined for Windows applications. It is not recommended that these be assigned as the code page for a data source. These code pages, in particular MS-DOS Latin US, are more appropriate for the data files themselves.


Relativity Setup - Select Translator Dialog Box

The Select Translator dialog box allow the selection of an ODBC translator which will translate data between an ODBC Application and a Data Source. Press on any control for additional information.

Option | Description
Installed Translators list | This list box contains the ODBC translators which have been installed on this machine. Select the desired translator, or select <No Translator> to specify that a translator is not to be used.


Scalar Function CONCAT (Advanced)

CONCAT(string_exp1, string_exp2)

Returns a character string that is the result of concatenating string_exp2 to string_exp1. If either string_exp1 or string_exp2 is NULL valued, then the CONCAT function returns a NULL value.


Scalar Function CONVERT (Advanced)

CONVERT(value_exp, data_type)

Returns the value specified by value_exp converted to the specified data_type, where data_type is one of the following keywords:

SQL_BIGINT | SQL_LONGVARBINARY
SQL_BINARY | SQL_LONGVARCHAR
SQL_BIT | SQL_REAL
SQL_CHAR | SQL_SMALLINT
SQL_DATE | SQL_TIME
SQL_DECIMAL | SQL_TIMESTAMP
SQL_DOUBLE | SQL_TINYINT
SQL_FLOAT | SQL_VARBINARY
SQL_INTEGER | SQL_VARCHAR

The validity of specific conversions of one data type to another data type can be determined by calling SQLGetInfo with the SQL_CONVERT_type parameter, where type is BIGINT, BINARY, BIT, CHAR, DATE, DECIMAL, DOUBLE, FLOAT, INTEGER, LONGVARBINARY, LONGVARCHAR, NUMERIC, REAL, SMALLINT, TIME, TIMESTAMP, TINYINT, VARBINARY, or VARCHAR. SQLGetInfo then returns a 32-bit bitmask that specifies the allowed conversions for that data type. The returned values for this driver are described in the section SQLGetInfo Return Values (Programming).

When data_type is SQL_DECIMAL, and value_exp is a constant, the precision and scale of the decimal column is determined by the precision and scale of the value_exp constant. If the expression is a single numeric literal (with no exponent), the number of digits in the literal, including leading zeroes, is used as the precision of the column. Similarly, the number of digits to the right of the decimal point in the literal is used as the scale. If the expression is not a single numeric literal, or is a single numeric literal with an exponent, the precision will be 74, with a scale of 37, unless the BCDMaxPrecision option has been specified.


Scalar Function CURDATE (Advanced)

CURDATE()

Returns the current date as a date value.


Scalar Function CURTIME (Advanced)

CURTIME()

Returns the current local time as a time value.


Scalar Function DATABASE (Advanced)

DATABASE()

Returns the name of the database corresponding to the conneciton handle (hdbc). The name of the database is also available by calling SQLGetConnectAttr with the SQL_ATTR_CURRENT_CATALOG connection attribute.


Scalar Function DAYOFMONTH (Advanced)

DAYOFMONTH(date_exp)

Returns the day of the month in date_exp as an integer value in the range 1-31.


Scalar Function DAYOFWEEK (Advanced)

DAYOFWEEK(date_exp)

Returns the day of the week in date_exp as an integer in the range of 1-7, where 1 represents Sunday.


Scalar Function IF (Advanced)

IF(predicate, exp1, exp2)

If predicate is true, exp1 is returned. If predicate is false, exp2 is returned. The predicate argument must not contain a sub-query.

The scalar function IF is not a standard ODBC function.

See Also

SQL Search Condition Syntax (Advanced)


Scalar Function LCASE (Advanced)

LCASE(string_exp)

Returns the string derived from string_exp, where all upper case characters in string_exp have been converted to lower case letters.


Scalar Function LEFT (Advanced)

LEFT(string_exp, count)

Returns the leftmost count of characters from string_exp.


Scalar Function LENGTH (Advanced)

LENGTH(string_exp)

Returns the number of characters in string_exp, excluding trailing blanks and the string termination character.


Scalar Function LOCATE (Advanced)

LOCATE(string_exp1, string_exp2[, start])

Returns the starting position of the first occurrence of string_exp1 within string_exp2. The search for the first occurrence of string_exp1 begins with the first character position in string_exp2 unless the optional argument, start, is specified. If start is specified, the search begins with the character position indicated by start. The first character position in string_exp2 is indicated by the value 1. If string_exp1 is not found within string_exp2, the value 0 is returned.


Scalar Function LTRIM (Advanced)

LTRIM(string_exp)

Returns the characters of string_exp, with leading blanks removed.


Scalar Function MOD (Advanced)

MOD(integer_exp1, integer_exp2)

Returns the remainder (modulus) of integer_exp1 divided by integer_exp2.


Scalar Function MONTH (Advanced)

MONTH(date_exp)

Returns the month in date_exp as an integer value in the range 1-12.


Scalar Function NULL (Advanced)

NULL()

Returns a NULL value.

The scalar function NULL is not a standard ODBC function.


Scalar Function QUARTER (Advanced)

QUARTER(date_exp)

Returns the quarter in date_exp as an integer value in the range 1-4, where 1 represents January 1 through March 31.


Scalar Function RIGHT (Advanced)

RIGHT(string_exp, count)

Returns the rightmost count of characters from string_exp.


Scalar Function RTRIM (Advanced)

RTRIM(string_exp)

Returns the characters of string_exp, with trailing blanks removed.


Scalar Function SUBSTRING (Advanced)

SUBSTRING(string_exp, start, length)

Returns a character string that is derived from string_exp beginning at the character position specified by start for length characters. The first character position in string_exp is indicated by a start value of 1.


Scalar Function UCASE (Advanced)

UCASE(string_exp)

Returns the string derived from string_exp, where all lower case characters in string_exp have been converted to upper case letters.


Scalar Function USER (Advanced)

USER()

Returns the user's authorization name. The user's authorization name is also available via SQLGetInfo by specifying the information type: SQL_USER_NAME.


Scalar Function YEAR (Advanced)

YEAR(date_exp)

Returns the year in date_exp as an integer value.


SQL Base-Table-Name Syntax (Advanced)

The Relativity ODBC driver supports base table names with the following syntax:

base-table-name ::=
base-table-identifier
|qualifier-name qualifier-separator base-table-identifier


Procedure Call Statement (Advanced)

The Relativity ODBC driver supports the ODBC stored procedure extension to standard SQL with the following syntax:

ODBC-procedure-extension ::=
ODBC-std-esc-initiator [?=] call procedure ODBC-std-esc-terminator
|ODBC-ext-esc-initiator [?=] call procedure ODBC-ext-esc-terminator

procedure ::= procedure-name (procedure-parameter-list)

procedure-parameter-list ::=
procedure-parameter
|procedure-parameter, procedure-parameter-list
|empty-string

procedure-parameter ::= dynamic-parameter | literal

empty-string ::=

procedure-name ::=
general-procedure | dba-procedure

general-procedure ::=
RDB_CreateFile
|RDB_LIMIT

dba-procedure ::=
DBA_AddFileConn
|DBA_AddPathName
|DBA_AddTableConn
|DBA_DeleteFileConn
|DBA_DeletePathName
|DBA_DeleteTableConn
|DBA_ListFileConns
|DBA_ListTableConns
|DBA_ListTableDefs
|DBA_ModifyPathName
|DBA_RelabelPathName
|DBA_RenameTableConn

The DBA procedures are available only if the user is the owner or a DBA of the database.


SQL Column-Name Syntax (Advanced)

The Relativity ODBC driver supports column names with the following syntax:

column-name ::= [ { table-name | correlation-name }.]column-identifier


CREATE INDEX Statement (Advanced)

The Relativity ODBC driver supports the SQL CREATE INDEX statement with the following syntax:

create-index-statement ::=
CREATE [UNIQUE] INDEX index-name ON base-table-name
( column-identifier [ASC | DESC] [ , column-identifier [ASC | DESC] ] ...)


CREATE TABLE Statement (Advanced)

The Relativity ODBC driver supports the SQL CREATE TABLE statement with the following syntax:

create-table-statement ::=
CREATE TABLE base-table-name (column-element [, column-element] ...)

column-element ::= column-definition | table-constraint-definition

column-definition ::= column-identifier data-type
[column-constraint-definition [column-constraint-definition] ... ]

column-constraint-definition ::=
NOT NULL

table-constraint-definition ::=
UNIQUE (column-identifier [, column-identifier] ... )
|PRIMARY KEY (column-identifier [, column-identifier] ... )

There is a maximum of 256 columns allowed when a table is created.


CREATE VIEW Statement (Advanced)

The Relativity ODBC driver supports the SQL CREATE VIEW statement with the following syntax:

create-view-statement ::=
CREATE VIEW viewed-table-name [(column-identifier [, column-identifier] ...)]
AS query-specification


SQL Data-Type Syntax (Advanced)

See Also

The Relativity ODBC driver supports specification of data types with the following syntax:

data-type ::=
character-string-type
|exact-numeric-type
|approximate-numeric-type
|bit-type
|binary-type
|date-type
|time-type
|timestamp-type

approximate-numeric-type ::=
SINGLE
|DOUBLE

binary-type ::= LONGVARBINARY (length)

bit-type ::= BIT

character-string-type ::=
CHAR (length)
|VARCHAR (length)
|LONGVARCHAR (length)

date-type ::= DATE

exact-numeric-type ::=
DECIMAL (precision,scale)
|INTEGER
|SMALLINT
|TINYINT

length ::= unsigned-integer

precision ::= unsigned-integer

scale ::= unsigned-integer

time-type ::= TIME

timestamp-type ::= TIMESTAMP

See Also

For Advanced Users

CREATE TABLE Statement

Data Types (Advanced)


SQL Term Definitions

SQL Term Definitions (Advanced)

The following SQL terms are used within this document.

base-table-identifier ::= user-defined-name

column-alias ::= user-defined-name

column-identifier ::= user-defined-name

correlation-name ::= user-defined-name

dynamic-parameter ::= ?

digit ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9

index-identifier ::= user-defined-name

index-qualifier ::= user-defined-name

lower-case-letter ::=
a | b | c | d | e | f | g | h | i | j | k | l | m | n | o | p | q | r | s | t | u | v | w | x | y | z

ODBC-ext-esc-initiator ::= {

ODBC-ext-esc-terminator ::= }

ODBC-std-esc-initiator ::= --(* VENDOR(Microsoft), PRODUCT(ODBC)

ODBC-std-esc-terminator ::= *)--

In a pattern-value, the percent character (%) matches zero or more of any character and the underscore character (_) matches any one character. The backslash (\) character may be used to escape these characters to treat them literally, that is, to search for a percent or underscore character. Two consecutive backslash characters are treated as one backslash character in the search pattern. A backslash character that precedes any other character than percent, underscore, or backslash is taken literally in the search pattern.

qualifier-name ::= user-defined-name

qualifier-separator ::= .

table-identifier ::= user-defined-name

unsigned-integer ::= {digit} ...

digit ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9

user-name ::= user-defined-name

upper-case-letter ::=
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z

viewed-table-identifier ::= user-defined-name


DELETE Statement, Searched (Advanced)

The Relativity ODBC driver supports the SQL searched DELETE statement with the following syntax:

delete-statement-searched ::=
DELETE FROM table-name [WHERE search-condition]

The Relativity ODBC driver does not support a positioned DELETE statement.


DROP TABLE Statement (Advanced)

The Relativity ODBC driver supports the SQL DROP TABLE statement with the following syntax:

drop-table-statement ::=
DROP TABLE base-table-name


DROP VIEW Statement (Advanced)

The Relativity ODBC driver supports the SQL DROP VIEW statement with the following syntax:

drop-view-statement ::=
DROP VIEW viewed-table-name


SQL Expression Syntax (Advanced)

The Relativity ODBC driver supports SQL expressions with the following syntax:

expression ::= term | expression {+|-} term

term ::= factor | term {*|/} factor

factor ::= [+|-]primary

primary ::=
column-name
|dynamic-parameter
|ODBC-scalar-function-extension
|literal
|set-function-reference
|USER
|(expression)

set-function-reference ::= COUNT(*) | all-function

all-function ::= {AVG | MAX | MIN | SUM} (expression)

A set-function-reference is not allowed in an expression that is in the context of a GROUP BY or ORDER BY clause.


INSERT Statement (Advanced)

The Relativity ODBC driver supports the SQL INSERT statement with the following syntax:

insert-statement ::=
INSERT INTO table-name [( column-identifier [, column-identifier] ...)]
VALUES (insert-value [, insert-value] ...)

insert-value ::=
dynamic-parameter
|literal
|NULL
|USER


SQL Index-Name Syntax (Advanced)

The Relativity ODBC driver supports index names with the following syntax:

index-name ::= [index-qualifier.]index-identifier


SQL Literal Syntax (Advanced)

The Relativity ODBC driver supports SQL literals with the following syntax:

literal ::= character-string-literal
| numeric-literal
| bit-literal
| binary-literal
| ODBC-date-time-extension

character-string-literal ::= '{character}'

character ::= <any ASCII character>

numeric-literal ::= exact-numeric-literal | approximate-numeric-literal

exact-numeric-literal ::=
[+|-] {unsigned-integer[.unsigned-integer]
|unsigned-integer.
|.unsigned-integer }

approximate-numeric-literal ::= mantissa E exponent

mantissa ::= exact-numeric-literal

exponent ::= [+|-] unsigned-integer

bit-literal ::= 0 | 1

binary-literal ::= 'hex-byte[hex-byte]...'

hex-byte ::= hex-digit hex-digit

hex-digit ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | A | a | B | b | C | c | D | d | E | e | F | f

Literals can contain a maximum of 1,000 characters.

A character in a character-string-literal may be any ANSI character between 1 and 255 base 10. A single quote (') must be represented as two consectuve single quotes ('').

When a literal appears in a select-list, it will result in an output column containing that literal value in all rows. The type of this output column will be a function of the type of the literal. See Literal Column Support (Advanced) for more information.


SQL ODBC Date Time Extension (Advanced)

The Relativity ODBC driver supports SQL date, time, and timestamp literals with the following syntax:

ODBC-date-time-extension ::=
ODBC-date-literal
|ODBC-time-literal
|ODBC-timestamp-literal

ODBC-timestamp-literal ::=
ODBC-std-esc-initiator ts 'timestamp-value' ODBC-std-esc-terminator
|ODBC-ext-esc-initiator ts 'timestamp-value' ODBC-ext-esc-terminator
|'timestamp-value'

timestamp-value ::= date-value timestamp-separator time-value[.seconds-fraction]

timestamp-separator ::= <the space character>

seconds-fraction ::= unsigned-integer

ODBC-date-literal ::=
ODBC-std-esc-initiator d 'date-value' ODBC-std-esc-terminator
|ODBC-ext-esc-initiator d 'date-value' ODBC-ext-esc-terminator
|'date-value'

date-value ::=
years-value date-separator months-value date-separator days-value

date-separator ::= -

days-value ::= digit digit

months-value ::= digit digit

years-value ::= digit digit digit digit

ODBC-time-literal ::=
ODBC-std-esc-initiator t 'time-value' ODBC-std-esc-terminator
|ODBC-ext-esc-initiator t 'time-value' ODBC-ext-esc-terminator
|'time-value'

time-value ::= hours-value time-separator minutes-value time-separator seconds-value

hours-value ::= digit digit

minutes-value ::= digit digit

seconds-value ::= digit digit

time-separator ::= :


SQL ODBC-Scalar-Function-Extension Syntax (Advanced)

The Relativity ODBC driver supports SQL ODBC scalar function extensions within expressions with the following syntax:

ODBC-scalar-function-extension ::=
ODBC-std-esc-initiator fn scalar-function ODBC-std-esc-terminator
|ODBC-ext-esc-initiator fn scalar-function ODBC-ext-esc-terminator

scalar-function ::= function-name (argument-list)

argument-list ::= expression | expression, argument-list

function-name ::=
string-function | numeric-function | date-function | system-function | conversion-function

conversion-function ::= CONVERT

date-function ::= CURDATE | CURTIME | DAYOFMONTH | DAYOFWEEK | MONTH | QUARTER | YEAR

numeric-function ::= MOD

string-function ::=
CONCAT | LCASE | LEFT | LENGTH | LOCATE
|LTRIM | RIGHT | RTRIM | SUBSTRING | UCASE

system-function ::= DATABASE | IF | NULL | USER


SQL Query Specification Syntax (Advanced)

The Relativity ODBC driver supports query specifications with the following syntax:

query-specification ::= SELECT [ALL | DISTINCT] select-list
FROM table-reference-list
[WHERE search-condition]
[GROUP BY column-name [, column-name] ... [HAVING search-condition]]


SQL Search Condition Syntax (Advanced)

The Relativity ODBC driver supports SQL search conditions with the following syntax:

search-condition ::= boolean-term [OR search-condition]

boolean-term ::= boolean-factor [AND boolean-term]

boolean-factor ::= [NOT] boolean-primary

boolean-primary ::= predicate | (search-condition)

predicate ::=
between-predicate | comparison-predicate | exists-predicate
|in-predicate | like-predicate | null-predicate | quantified-predicate

between-predicate ::= expression [NOT] BETWEEN expression AND expression

comparison-predicate ::= expression comparison-operator {expression | (sub-query)}

exists-predicate ::= EXISTS (sub-query)

in-predicate ::= expression [NOT] IN {(value {, value} ... ) | (sub-query)}

like-predicate ::= expression [NOT] LIKE pattern-value

null-predicate ::= column-name IS [NOT] NULL

quantified-predicate ::=
expression comparison-operator {ALL | ANY} (sub-query)

comparison-operator ::= < | > | <= | >= | = | <>

pattern-value ::= character-string-literal | dynamic-parameter

sub-query ::=
SELECT [ALL | DISTINCT] select-list
FROM table-reference-list
[WHERE search-condition
[GROUP BY column-name [, column-name] ...
[HAVING search-condition] ]

value ::= literal | dynamic-parameter

Maximum number of predicates is 300. This may vary depending upon the stack size allocated by the application.


SELECT Statement (Advanced)

The Relativity ODBC driver supports the SQL SELECT statement with the following syntax:

select-statement ::=
SELECT [ALL | DISTINCT] select-list
FROM table-reference-list
[WHERE search-condition]
[GROUP BY column-name [, column-name] ...
[HAVING search-condition] ]
[UNION [ALL] select-statement]
[ORDER BY sort-specification [, sort-specification] ... ]

sort-specification ::= { unsigned-integer | expression } [ASC | DESC]

The ORDER BY clause may not be specified in a select-statement within a UNION or UNION ALL clause.


SQL Select List Syntax (Advanced)

The Relativity ODBC driver supports select lists with the following syntax:

select-list ::= * | select-sublist [, select-sublist ] ...

select-sublist ::= expression [ [AS] column-alias] | { table-name | correlation-name }.*


SQL Table-Name Syntax (Advanced)

The Relativity ODBC driver supports table names with the following syntax:

table-name ::=
table-identifier
|qualifier-name qualifier-separator table-identifier


SQL Table Reference List Syntax (Advanced)

The Relativity ODBC driver supports table references with the following syntax:

table-reference-list ::= table-reference [, table-reference] ...

table-reference ::=
table-name [correlation-name]
|ODBC-join-extension
|inner-join
|(inner-join)
|outer-join
|(outer-join)

ODBC-join-extension ::=
ODBC-std-esc-initiator oj inner-join ODBC-std-esc-terminator
|ODBC-ext-esc-initiator oj inner-join ODBC-ext-esc-terminator
ODBC-std-esc-initiator oj outer-join ODBC-std-esc-terminator
|ODBC-ext-esc-initiator oj outer-join ODBC-ext-esc-terminator

inner-join ::= table-reference INNER JOIN
table-reference ON search-condition

outer-join ::= table-reference LEFT OUTER JOIN
table-reference ON search-condition

A search-condition in the context of an outer-join must refer to columns within the tables being joined. No literals can be used in the search-condition.


UPDATE Statement, Searched (Advanced)

The Relativity ODBC driver supports the SQL searched UPDATE statement with the following syntax:

update-statement-searched ::=
UPDATE table-name
SET column-identifier = { expression | NULL }
[, column-identifier = { expression | NULL }] ...
[WHERE search-condition]

The Relativity ODBC driver does not support a positioned UPDATE statement.


SQL User-Defined-Name Syntax (Advanced)

The Relativity ODBC driver supports user defined names with the following syntax:

user-defined-name ::= letter [digit | letter | _ ] ...

letter ::= lower-case-letter | upper-case-letter


SQL Viewed-Table-Name Syntax (Advanced)

The Relativity ODBC driver supports viewed table names with the following syntax:

viewed-table-name ::=
viewed-table-identifier
|qualifier-name qualifier-separator viewed-table-identifier