ODBC Agent

DirX Identity provides two agents to handle the import and export of data from ODBC-based databases:

  • ODBCAgentImp-the DirX Identity agent that handles the import of data into an ODBC database

  • ODBCAgentExp-the DirX Identity agent that handles the export of data from an ODBC database

The ODBC agents run on Windows and Linux systems and can be used to access any database that is accessible through an ODBC driver.

ODBC agents can:

  • Perform a full or a delta export of selected rows from a table or a join of tables

  • Perform a full or a delta import to a single table, with insert (add), update (modify), and delete

  • Perform a full or delta import by calling a Stored Procedure for each record

  • Perform single-step operations, in which one entry is processed at a time, after which user input is required to continue

  • Generate an import error file that records all rows that it fails to import

  • Generate a log file (for tracing)

The following figures show the components of the ODBC agents.

ODBCAgentImp Components
Figure 1. ODBCAgentImp Components
ODBCAgentExp Components
Figure 2. ODBCAgentExp Components

This section describes:

  • ODBCAgentImp and ODBCAgentExp command line format

  • ODBCAgentImp and ODBCAgentExp configuration files

  • The export data file format that ODBCAgentExp generates

  • The import data file format that ODBCAgentImp recognizes

  • ODBCAgentImp import error file format

  • The ODBC agent import procedure

  • The ODBC agent full export procedure

  • The ODBC agent delta export procedure

Sample configuration files and scripts are provided in the ODBC Agent\Samples directory of the DirX Identity installation. See the file OdbcReadme.txt for a description of these files and scripts.

Transactions in ODBC can be in one of two modes: auto-commit mode or manual-commit mode. By default, ODBC transactions are in auto-commit mode. In auto-commit mode, every database operation is a transaction that is committed when performed, but the degree of effective support for transactions is driver-defined. The ODBC agents use the default.

ODBCAgentImp Command Line Format

The command line format to invoke ODBCAgentImp is as follows:

ODBCAgentImp -f configuration_file [-i data_file] [-n name] [-p password] [-s] [-v]
[-Enc encryption_mode -Timeout timeout_value -AuditLevel audit_level -CryptLogLevel crypt_level]

Parameters

-f configuration_file

Specifies the name of the file that contains the specifications for the import procedure. The agent assumes that the file exists in the current working directory unless a pathname is specified. This is a mandatory command line parameter.

-i data_file

Specifies the filename or the full pathname of a source file that contains the data to be imported into the ODBC database. If a file name is specified, the agent assumes that it is relative to the current working directory. If this option is not specified, the agent uses the filename odbc_in.txt relative to the current working directory.

-n name

Specifies a user name, where name is case-sensitive. This parameter may be required in order to access the database.

-p password

Specifies a user password, where password is case-sensitive. This parameter may be required in order to access the database.

-s

Runs the import operation in single-step mode.

-v

Directs ODBCAgentImp to use verbose reporting.

-ENC encryption_mode

Specifies the security mode. Valid modes are ATTRIB_ADMIN_PW or ADMIN_PW.

This functionality only works correctly in an appropriate security environment like in the DirX Identity environment configured in security mode. (See DirX Identity Connectivity Administration Guide).

-Timeout timeout_value

Specifies the timeout value for the security mode. Values must be given in microseconds.

This functionality only works correctly in an appropriate security environment like in the DirX Identity environment configured in security mode. (See DirX Identity Connectivity Administration Guide).

-AuditLevel audit_level

Specifies the audit level value for the security mode. Valid values are in the range of 0 and 4.

This functionality only works correctly in an appropriate security environment like in the DirX Identity environment configured in security mode. (See DirX Identity Connectivity Administration Guide).

-CryptLogLevel crypt_level

Specifies the logging level of the crypt library for the security mode. Valid values are greater or equal to 0.

This functionality only works correctly in an appropriate security environment like in the DirX Identity environment configured in security mode. (See DirX Identity Connectivity Administration Guide).

The following table describes the codes provided when ODBCAgentImp finishes running:

Exit Code Description

0x00

Import successfully done

0x01

Import not done due to errors

0x3C

Import done with warnings

Command Line Description

The ODBCAgentImp command line parameters can be specified in any order. Each parameter can only be specified once.

It is not necessary to provide whitespace between a command line parameter and its argument. For example:

-imy_import_file.txt

is equivalent to

-i my_import_file.txt

Placing a # character where a command line parameter is expected causes ODBCAgentImp to ignore the remainder of the command line.

To display help information about ODBCAgentImp parameters, enter ODBCAgentImp on the command line or follow ODBCAgentImp with the # character to ignore the remaining parameters.

When it is invoked, ODBAgentImp reports any errors found in the command line. For example:

missing configuration file name
cannot open import data file for reading
odbc_in.txt

The -n and -p parameters may be required in order to access the ODBC database. These parameters provide credential attributes to the underlying ODBC access, and must comply with any requirements made by ODBC and the underlying database. Access to Microsoft Access can, but need not be, user-sensitive; it may only require a password, depending on the security arrangements made for the database. You cannot specify username and password in the configuration file; the command line is the only method permitted for specifying them.

The -s parameter invokes single-step mode. In single-step mode, ODBCAgentImp imports one row at a time, and then waits for the input from the keyboard. Possible inputs are:

q<CR> or Q<CR> or n<CR> or N<CR> - to terminate the import procedure (case-insensitive)

g<CR> or G<CR> - to terminate single-step mode and continue the import procedure (case-insensitive)

<CR> - to continue with the next row

The -v parameter directs ODBCAgentImp to write trace information to the display on standard out.

ODBCAgentExp Command Line Format

The command line format to invoke ODBCAgentExp is as follows:

ODBCAgentExp -f configuration_file [-o data_file | +] [-n name] [-p password] [-r [ref_file]] [-s] [-v]
[-Enc encryption_mode -Timeout timeout_value -AuditLevel audit_level -CryptLogLevel crypt_level]

Parameters

-f configuration_file

Specifies the name of the file that contains the specifications for the export procedure. The file is taken to be in the current working directory unless a pathname is specified.

-o data_file | +

Specifies the filename or the pathname of the target export data file that is to contain the entries that ODBCAgentExp extracts from the ODBC database, or directs the agent to write the extracted entries to standard output, if the plus sign (+) is specified. If a filename is specified, the agent assumes it is relative to the current working directory. If this parameter is not specified, the agent writes to the filename odbc_out.txt relative to the current working directory.

-n name

Specifies a user name, where name is case-sensitive. This parameter may be required in order to access the database.

-p password

Specifies a user password, where password is case-sensitive. This parameter may be required in order to access the database.

-r [ref_file]

Specifies the name of a delta export reference file that ODBCAgentExp is to use as the base for a delta export operation (specified by a Mode field of delta or delta-or-full in the export configuration file). The file is taken to be in the current working directory unless a pathname is specified; all name forms acceptable to the operating environment are accepted (for example, fred.ref or reference\fred.ref or ..\reference\fred.ref or \users\myusers\reference\fred.ref..). If the -r flag is present, but ref_file is not specified, ODBCAgentExp performs a full export regardless of the setting in the Mode field and creates a new reference file that represents the full export.

-s

Runs the export operation in single-step mode.

-v

Directs ODBCAgentExp to use verbose reporting.

-ENC encryption_mode

Specifies the security mode. Valid modes are ATTRIB_ADMIN_PW or ADMIN_PW.

This functionality only works correctly in an appropriate security environment like in the DirX Identity environment configured in security mode. (See DirX Identity Connectivity Administration Guide).

-Timeout timeout_value

Specifies the timeout value for the security mode. Values must be given in microseconds.

This functionality only works correctly in an appropriate security environment like in the DirX Identity environment configured in security mode. (See DirX Identity Connectivity Administration Guide).

-AuditLevel audit_level

Specifies the audit level value for the security mode. Valid values are in the range of 0 and 4.

This functionality only works correctly in an appropriate security environment like in the DirX Identity environment configured in security mode. (See DirX Identity Connectivity Administration Guide).

-CryptLogLevel crypt_level

Specifies the logging level of the crypt library for the security mode. Valid values are greater or equal to 0.

This functionality only works correctly in an appropriate security environment like in the DirX Identity environment configured in security mode. (See DirX Identity Connectivity Administration Guide).

Exit Code

The following table describes the codes provided when ODBCAgentExp finishes running:

Exit Code Description

0x00

Export successfully done

0x01

Export not done due to errors

0x3C

Export done with warnings.

Command Line Description

The ODBCAgentExp command line parameters can be specified in any order. Each parameter can only be specified once.

It is not necessary to provide whitespace between a command line parameter and its argument. For example:

-omy_export_file.txt

is equivalent to

-o my_export_file.txt

Placing a # character where a command line parameter is expected causes ODBCAgentExp to ignore the remainder of the command line.

To display help information about ODBCAgentExp parameters, enter just ODBCAgentExp on the command line.

When it is invoked, ODBCAgentExp reports any errors found in the command line. For example:

missing configuration file name

The -n and -p parameters may be required in order to access the ODBC database. These parameters provide credential attributes to the underlying ODBC access, and must comply with any requirements made by ODBC and the underlying database. Access to Microsoft Access can, but need not be, user-sensitive; it may only require a password, depending on the security arrangements made for the database. You cannot specify username and password in the configuration file; the command line is the only method permitted for specifying them.

The -r parameter specified with the ref_file option directs ODBCAgentExp to override its process for selecting a base delta export reference file and to use the file specified in ref_file. The -r parameter specified without the ref_file option overrides the Mode field specified in the export configuration file and can be used to perform a full export that also creates a new base reference file (ODBCAgentExp does not create a new reference file when the Mode field is set to full.)

The -s parameter invokes single-step mode. In single-step mode, ODBCAgentExp outputs one row at a time, and then waits for the input from the keyboard. Possible inputs are:

q<CR> or Q<CR> or n<CR> or N<CR> - to terminate the export procedure (case-insensitive)

g<CR> or G<CR> - to terminate single-step mode and continue the export procedure (case-insensitive)

<CR> - to continue with the next row

The -v parameter directs ODBCAgentExp to write trace information to the display on standard out.

Configuration File Format

Both ODBC agents read control information about the export or import procedure from a common configuration file. You do not need to provide import configuration information when exporting, or export configuration information when importing. If you do provide import configuration information when exporting (or vice-versa), the values that you supply will be checked for syntax, and, if incorrect, will prevent the operation from being executed. If you are uncertain about import (or export) details when doing the other operation, you may find it helpful to "comment out" each import line (including the heading) by inserting a # character at the beginning of the line.

Templates of import and export configuration files are provided with the ODBC agent installation. The filenames are:

  • NWAcc70.ini (Northwind ODBC and Microsoft Access Version 7.0)

  • NWAcc97.ini (Northwind ODBC and Microsoft Access 97)

  • NWSQLServer70.ini (Northwind ODBC and SQL Server Version 7.0)

  • hrora.cfg (Unix: Oracle RDBMS)

  • hrtext.cfg (Unix: Text database)

In general, you must customize these files to support the requirements of your ODBC import and export operations.

This section describes:

  • The general structure of a configuration file

  • The configuration file sections

  • The configuration file error reporting

General Structure of a Configuration File

A ODBC agent configuration file consists of sections and fields defined within those sections. An ODBC agent configuration file has the following general structure:

[SectionName]

#comment …​

sectionField=fieldValue
.
.
.
[SectionName]

#comment …​

sectionField=fieldValue
.
.
.

SectionName is a keyword enclosed in square brackets ([ ]) that identifies the purpose of the section. sectionField is a keyword that identifies the field and fieldValue is the value assigned to the section field, preceded by the equal sign (=). Whitespace is allowed on either side of the equal sign. For example:

MaxTraceFileSize=1024, or
MaxTraceFileSize = 1024

SectionName and sectionField are case-insensitive. fieldValue is usually case-insensitive, although text used directly by ODBC (for example, in the Database section) may be case-sensitive. Whitespace is permitted before and after each of these tokens. Comment lines can be inserted anywhere in the configuration file and are identified by a # character at the beginning of the line. Note, however, that the ODBC agents recognize the # character within a non-comment line as real data, and will not, for example, ignore the remainder of the line.

Long fieldValue information can be placed on multiple lines by placing a backslash character (\) at the very end of a line that is to be continued. Line length is unlimited.

Configuration File Sections

The ODBC agent configuration file consists of the following sections:

  • The version section (required)

  • The attributes section (required)

  • The database section (required)

  • The import section (required for import and optional for export; if present, ODBCAgentExp only checks it. Optional for import using Stored Procedures; if present ODBCAgentImp only checks it)

  • The procedures section (required for Stored Procedures; if both import and procedures sections are present, ODBCAgentImp uses Stored Procedures for the import)

  • The export section (required for export and optional on import; if present, ODBCAgentImp only checks it)

  • The control section (optional; all fields have default values)

  • The encrypted attributes section (optional)

The Version Section

The version section consists of a single field that specifies the export configuration file version. The syntax is:

Version=version_number

where version_number is the version number assigned to the configuration file, in the format n*.*nn. The latest version is:

Version=1.01

This is a mandatory field. This document describes the latest version of the ODBC agent configuration file. The ODBC Agent is able to process configuration files with version number 1.00, 1.01 or "old" files that do not contain a Version section. The following table provides information about the differences between configuration file versions and about the support of older configuration file versions for compatibility reasons:

“Old” 1.00 1.01 1.02 1.03 1.04 1.05 1.06

Trace

n.s.

s.

s.

s.

s.

s.

s.

s.

NewReferenceFile

n.s.

n.s.

s.

s.

s.

s.

s.

s.

Autos (on Unix)

n.s.

n.s.

n.s.

n.s.

s.

s.

s.

s.

Autos (additionally on Windows)

n.s.

n.s.

n.s.

n.s.

s.

s.

s.

s.

EncryptedAttributes
Encryption command-line switches
Stored Procedures
User and password in the configuration file

n.s.

n.s.

n.s.

n.s.

n.s.

s.

s.

s.

SQLExecDirect

n.s.

n.s.

n.s.

n.s.

n.s.

n.s.

s.

s.

SetOption

n.s.

n.s.

n.s.

n.s.

n.s.

n.s.

n.s.

s.

n.s. = not supported
s. = supported

The Attributes Section

The attributes section is a required section of the configuration file that defines the attribute abbreviations for ODBC database attributes ("columns", in ODBC syntax) to be imported or exported and maps the abbreviations to the corresponding ODBC table and column. The attributes section must appear before the import or export section.

Each field in the attributes section specifies an attribute definition. The field syntax is:

attribute_abbreviation[qualifier]*=*column_identifier

where:

  • attribute_abbreviation consists of one or more alphanumeric characters, including the underscore () and hyphen (-) that represent the short form or convenient notation for the column of information. The number of characters that can be specified for _attribute_abbreviation is unlimited. Attribute abbreviations are case-insensitive in terms of matching.

  • qualifier is an optional syntax that specifies any special data type and control information for the attribute, in the format:
    (data_type[[[minimum]-maximum]])[#]
    where data_type is the name of the attribute’s data type (only text is currently permitted), minimum and maximum are non-negative integers that specify a lower (optional) and upper limit of attribute length, and the # character is a flag that, if present, causes the import of an entry to fail when the row to be imported contains an oversize attribute. The definition of "oversize" can be specified explicitly in maximum, but the ODBC database itself will often specify a maximum length value for the attribute in the relevant table, and the user-supplied value will be reduced to this value (if necessary).

  • column_identifier identifies the column and generally has the form:
    table_name.column_name
    For example:

             Employees.LastName

    or

             HR.Employees.LastName

    but can be in any form permitted by a SELECT statement. (Please read the notes concerning Oracle below first.) Specifically, the table_name value and the following period (.) can be omitted when exporting from a single table. (Import is normally performed on a single table, although update-modification, row insertion and row deletion-of joined tables is possible with some restrictions.) column_identifier can for export only take the value of an expression or a subquery (that is, joining up the strings formed by two or more columns, or adding up arithmetical values). In this case put parenthesis around the expression like in the following example. (Note that the MaxPrice line is splitted here into 3 lines with continuation symbol (\) just for better reading):

    …
[attributes]
orderID=Orders.OrderID
MaxPrice=(SELECT MAX(OrdDet.UnitPrice) FROM \
     Northwind.dbo.[Order Details] AS OrdDet WHERE \
     Orders.OrderID = OrdDet.OrderID)
orderDate=Orders.OrderDate
[database]
DSN=…
[export]
SELECT=orderID, MaxPrice, orderDate
…

    Microsoft Access or SQL Server: Table names and column names that contain the hyphen character (-) or the space character ( ) must be enclosed in square brackets [ ], for example, [My Table].[First-Name].

    Oracle: Table names and column names that contain the hyphen or space character or other special characters must be enclosed in double quotation marks.

The following example illustrates an attribute definition that uses the optional qualifier:

SN(text[1-64])#=Employees.LastName

  • For Oracle, import can be performed on a single table. It is possible to perform import on a „view“ which has been created to represents a join of two or more tables, but there are significant restrictions. (See the Oracle documentation for details.)

  • For Oracle, only referring objects in the own schema is supported. In the syntax you omit the schema name.

An attribute definition field must exist for each ODBC attribute to be imported or exported. Each attribute_identifier must be unique, but a specific column_identifier can be mapped to more than one attribute_identifier. To obtain the column identifiers for ODBC attributes, it is recommended that you use the ODBC tools to access the ODBC database, and then copy the column identifiers as they appear in the resulting data file, for example, using a simple select statement like:

SELECT * FROM Employees

The ODBC agents convert attributes being supplied to the ODBC database to the column identifier using the first matching abbreviation.

You can use the abbreviations in the From and Where fields. Be careful in the naming of abbreviation because the substitution is a simple text substitution. You can see the substitution result in the SQL statements if you set the tracelevel SQL.

The Database Section

The database section is a required section that provides information that the ODBC agents need to access the ODBC database. The fields in the database section represent the information that is required by the ODBC driver for access to the ODBC databases it manages and differ depending on the ODBC driver in use. Specifying the DSN (Data Source Name) alone or with credentials should be adequate; otherwise, it will be necessary to consult the ODBC driver documentation to determine which fields are required and what values should be used.

The fields described in the next section vary depending which RDBMS you are using. At least DSN is necessary and in many cases sufficient. See the manual of the ODBC distribution you use for further information.

DSN

The DSN field specifies the name of the ODBC database as set up by the ODBC Data Source Administrator, which is available in the Windows 2003 Control Panel. On Unix this is configured in the .odbc.ini file in the HOME directory of the account in which the agent runs. The syntax is:

DSN=name

where name is the name of an ODBC database. For example:

DSN=my_database

This is the only mandatory field, and using it by itself will usually be sufficient.

If the DirX Identity Server is to start the agent and the Server is running as a service under a local system account, you must set up a System DSN (this DSN is shared by all users and services on the machine) or a User DSN.

For simplest operation, use the ODBC Data Source Administrator to bind a specific name, such as my_database, to a specific database, such as C:\MSOffice\Access\Samples\Northwind.mdb. You can also use the Administrator to select a class of databases (not selecting a specific database); in this case, running the agent causes a selection window to pop up to do the selection at run time.

User and Password Fields

The Access ODBC driver specifies username and password fields as credentials for authentication to the ODBC driver during ODBC database access. In some cases, it may be necessary to supply user and password credentials in order for the ODBC agents to gain access to the ODBC database.

Starting from version 1.04 user and password fields are allowed in the configuration file because passwords can now be written in scrambled or encrypted format into the file.

It is still possible to use the -n and -p parameters on the ODBCAgentImp or ODBCAgentExp command line to specify the credentials.

Other Driver-Specific Fields

Using the ODBC Data Source Administrator to establish an ODBC Data Source Name (DSN) also establishes a number of other driver-specific fields, which are used for internal purposes. These fields include the Driver field, which specifies the type of ODBC driver, and some other fields. You can obtain the values of these fields using the TraceLevel facility quoting ODBC, but their use in the context of Windows is not material.

The configuration file can be used to contain default values for these fields. Including values for these fields in the configuration file other than those supplied by the TraceLevel facility will cause the system default values to be replaced, but this should only be done with detailed knowledge of the effect on the database.

For example, if you use Microsoft SQL Server the fields DATABASE and SERVER are needed additionally.

The Export Section

The Export section consists of fields that define the parameters of an export operation for ODBCAgentExp. The next sections describe these fields. If present when importing, the values supplied here should be syntactically and semantically correct. If in doubt, deactivate the entire section by prefixing each line with the # character.

Mode

The Mode field specifies the type of export operation that ODBCAgentExp is to perform. The syntax is:

Mode=mode

where mode is one of the following keywords:

  • delta - Perform a delta export and fail if delta processing cannot be performed

  • delta-or-full - Perform a delta export, or perform a full export if delta processing cannot be performed

  • full - Perform a full export

ODBCAgentExp cannot perform delta processing when:

  • The -r ref_file parameter has been specified on the command line, and it cannot find or cannot open the specified reference file

  • The -r parameter without the ref_file option has been specified on the command line

  • It detects a change in the export specification; almost any change in configuration will cause the reference files no longer to match in their basic characteristics

If the Mode field is set to delta-or-full and the second and third cases occur, ODBCAgentExp performs a full export and creates a new reference file that represents it.

This is a mandatory field if delta exporting is to be performed and is optional for full exporting.

Select

The Select field specifies the set of entry attributes that ODBCAgentExp is to export from the ODBC database. The syntax is:

Select=attribute_list

where attribute_list specifies the attributes to export, in the format:

abbreviation[,abbreviation …​]

For example:

Select=GN, SN, T, TOC, BD, HD, A, CITY, REG, C

Keys and save-attributes (see SaveAttr below) are included in the exported attributes.

This is a mandatory field.

Keys

The Keys field specifies the set of attributes that ODBCAgentExp is to use to uniquely identify each entry to be exported from the ODBC database. The syntax is:

Keys=attribute_list

where attribute_list specifies the attributes to be used as unique identifiers, in the format:

abbreviation[*,*abbreviation …​]

For example:

Keys=EID

The set of attributes specified in the Keys field should correspond to the set of primary keys defined for the ODBC database since the export procedure assumes that the combination of key values is unique in the selected export table. Any combination for which this is true will work properly. The attribute that represents the most significant primary key for ordering should appear as the first attribute in the list; the remainder should follow in order of precedence. The attributes specified in the Keys field can overlap the attributes specified in Select, but they cannot overlap the attributes specified in the SaveAttr field. This is a mandatory field if delta exporting is to be performed. It is mandatory for full export with the option to generate a delta export reference file.

SaveAttr

The Keys field is used to relate delta reference information to the ODBC database. The SaveAttr information enables the delta reference information to be related to information in the target database. For example, if an entry is removed from the ODBC database, it may be required to remove the corresponding entry in the target database; the SaveAttr field is used to specify any additional attributes that may be used to identify the entry in the target database that is to be removed. Otherwise, removal is impossible.

For example, an ODBC table may have a simple integer as a primary key. When synchronizing to a database which does not store the key, information such as surname, given-name, and initials may be required to identify the correct entry in the target database.

The SaveAttr field therefore specifies the set of entry attributes that ODBCAgentExp is to store in the delta export reference file that it creates as part of the delta export process. The syntax is:

SaveAttr=attribute_list

where attribute_list specifies the attributes to be saved in the reference file, in the format:

abbreviation[,abbreviation …​]

For example:

SaveAttr=GN, SN, T, TEL

By default, ODBCAgentExp does not store complete entries in the delta reference files it creates. Instead, it stores the attributes defined as keys (with the Key field) to uniquely identify each entry, and stores the complete contents of the entry as a hash value which cannot be used to reconstruct the complete entry. Use the SaveAttr field to store attributes that are important to the directory synchronization process in the reference file. The attributes specified in the SaveAttr field cannot overlap the attributes specified in the Key field but can overlap the attributes specified in the Selection field.

This is an optional field.

From

The From field specifies the table or tables in the ODBC database from which ODBCAgentExp is to extract entries. The syntax is:

From=tables

where tables is a valid SQL-like expression that can be used in a FROM expression in a SQL SELECT statement. The value in tables can specify a simple table, for example:

/From=Employees

It can also specify a single or multiple union of tables, for example:

From=HR, PABX

or

From=Employees LEFT OUTER JOIN "org-units" on UnitId = EmpUnit

or

From=HR INNER JOIN PABX ON PNR = PID) AND (HR.EmployeeId = PABX.EmployeeId

You can also use a self-join to obtain further information from the same table. For example:

From=Employees INNER JOIN Employees AS Employees_1 ON Employees.ReportsTo = Employees_1.EmployeeID;

In the previous example, Employees 1 is a correlation name used to distinguish multiple uses of an object. If correlation names are used, they must be specified in the attributes section of the configuration file; for example:

[attributes]
GN=Employees.FirstName
SN=Employees.LastName
BName=Employees_1.Title
BTitle=Employees_1.LastName

When using a join of tables in the From field, the appropriate table name should be used to prefix a column name; for example, Employees.LastName. You can use column name abbreviations in JOIN predicates (as PNR and PID were used in an earlier example).

This is a mandatory field.

If you are not completely familiar with SQL SELECT statements, when developing the SQL statement that defines tables to be exported, it is recommended that you use Access or another tool to design the query, then use the tool to view the FROM component of the resulting SQL statement. The agent should work with any FROM component that works in an SQL statement for the target database.

Where

The Where field controls whether or not ODBCAgentExp searches for and exports specific entries ("rows" in ODBC terminology). The syntax is:

Where=predicate

where predicate is a valid SQL expression that can be used in a WHERE expression in a SQL SELECT statement. For example:

Where=Employees.LastName LIKE 'D%'

The LIKE element in this case selects last-names that start with D, and is part of standard SQL. Examples of SQL predicates are:

ProductID>2
ProductName='Chai'
ProductName LIKE 'C%'

The repertoire of supported predicates varies with ODBC database, and some databases provide extensions to the SQL standard. Refer to the database documentation for details.

As for the From field above, you may find it convenient to develop a working SELECT statement using Access or another tool, and then "lift" the predicate from it.

This is an optional field. If it is not specified, ODBCAgentExp exports all of the rows in the selected table or join of tables.

MaxRows

The MaxRows field controls the number of entries that ODBCAgentExp writes to the export data file. The syntax is:

MaxRows=number

where number specifies the maximum number of entries ("rows" in ODBC terminology) to be output.

This is an optional field. If it is not specified, ODBCAgentExp exports a theoretical maximum of 231-1 rows (a little more than 2 billion).

ReferencePath

The ReferencePath field specifies the pathname to the directory in which ODBCAgentExp is to store delta export reference files. The syntax is:

ReferencePath=directory_pathname

For example:

ReferencePath=D:\Program Files\DirX Identity\ODBC\Data\myrefdir

This is an optional field; if it is not specified (or is not present in the configuration file), ODBCAgentExp stores the delta reference files it creates in the current working directory.

NewReferenceFile

The NewReferenceFile field stores the name of a reference file that ODBCAgentExp is to use as the base for a delta export operation. The syntax is:

NewReferenceFile=ref_file

For example:

NewReferenceFile=fred.ref

ODBCAgent writes a new reference file name into this field each time it performs an export operation using the naming convention described in "ODBCAgentImp Delta Export Process". See that section for a complete description of the reference file generation process and format.

SortControl

When the ODBCAgentExp creates a reference file for delta export, it sorts the records in the file by ordering the key fields. This ordering then permits fast analysis of changes between the previous state of the database and the present one, and allows the modified information to be selected. The process of sorting and extraction is much faster if the sorting of the reference file information corresponds to the order in which the ODBC database is sorted. The SortControl facility enables the sorting to be optimized where necessary. In many cases, the sorting will be correct anyway. (You can usually determine how sorting is done by the database by inspection of a full export data file.)

Use this field if you are exporting a large database and seek to optimize export times. A problem that may be resolved by using the field may be indicated by a delta export that takes a much longer time than a full export.

Thus, the SortControl field controls how ODBCAgentExp sorts attributes in the reference file when exporting from an ODBC database and can be used to override the agent’s default sorting algorithm. The syntax is:

SortControl=[form_list]

where form_list specifies the matching rule form to apply to each key specified in the Keys field, in the format:

form[,form …​]

And form is one of the following (case-insensitive) keywords:

  • Integer (or Int) - the key is to be interpreted as an integer and the first four characters are to be taken as a binary 32-bit number. For example, '0A2B' in memory is 0x42324130 in hex or is 1,110,589,744 in decimal format (Windows NT is a little-endian system). The sorting is endian sensitive.

  • Numeric (or Num) - the key is to be right-justified before sorting and taken as a number. Numeric is handled as a lexical comparison based on the encoding value. It is not a "real" numeric sorting, which ignores leading zeros. For example, the agent sorts "39" < "0040"; as "0040" < "39".

  • CaseIgnore (or CI) - the key is to be left-justified before sorting, ignoring case

  • CaseSensitive (or CS) - the key is to be left-justified before sorting and case is significant

  • Any or empty - the key is to be matched using the default sorting algorithm (see below)

For example:

SortControl=CI,,Num

specifies that the sorting for first three keys are to be specified as case-ignore, default, numeric.

The ordering of the keywords in form_list must correspond to the ordering of the Keys attributes; these must be ordered in accordance with the desired sort key precedence. If the SortControl field specifies Any or is empty, ODBCAgentExp uses the default sorting algorithm, where ODBCAgentExp sorts the ODBC database according to the data type of the column, ignoring case where relevant. The following table shows the ODBCAgentExp default sorting algorithm.

ODBC Code Matching Rule Form

SQL_BIGINT

integer form

SQL_BINARY

numeric form

SQL_BIT

numeric form

SQL_CHAR

case-sensitive form

SQL_WCHAR

case-ignore form

SQL_DATE

case-ignore form

SQL_DECIMAL

numeric form

SQL_DOUBLE

fail

SQL_FLOAT

fail

SQL_INTEGER

integer form

SQL_LONGVARBINARY

fail

SQL_LONGVARCHAR

fail

SQL_WLONGVARCHAR

fail

SQL_NUMERIC

numeric form

SQL_REAL

fail

SQL_SMALLINT

integer form

SQL_TIME

case-ignore form

SQL_TIMESTAMP

case-ignore form

SQL_TINYINT

integer form

SQL_VARBINARY

integer form

SQL_VARCHAR

case-sensitive form

SQL_WVARCHAR

case-ignore form

This is an optional field.

The Import Section

The Import section consists of fields that define the parameters of the import operation for ODBCAgentImp. The next sections describe these fields.

If present when exporting, the values supplied here should be syntactically and semantically correct. If in doubt, deactivate the entire section by prefixing each line with the # character.

The procedures section has precedence over the import section.
Table

The Table field specifies the ODBC name of the table (or joined set of tables) into which entries are to be imported. The syntax is:

Table=table_name

or

Table=joined-table

An example of Table=table_name syntax is:

Table=Employees

With the Table=joined-table syntax, it is only possible to use the method of join that uses the JOIN keyword. For example, the following SQL statement block is permitted:

Table=Categories INNER JOIN (Suppliers INNER JOIN Products ON
Suppliers.SupplierID = Products.SupplierID)
ON Categories.CategoryID = Products.CategoryID

However, the following legal SQL statement block cannot be used as a basis for import because the predicate ("WHERE") as part of the SQL SELECT construct is already controlled by the SelectBy field:

SELECT ... FROM Categories, Suppliers, Products
WHERE (Suppliers.SupplierID = Products.SupplierID) AND
(Categories.CategoryID = Products.CategoryID);

The use of joined tables is permitted with certain limitations. The first table that is specified is called the "primary table" and must contain all of the columns specified by the SelectBy field. The primary table will be the only table whose rows are removed by a "delete" changetype entry, or inserted by an "add" changetype entry.

Both inner and outer joins are possible. If inner join is specified, ODBCAgentImp only evaluates the rows in the primary table that satisfy a join to the other table (or tables). If outer join is specified, all rows in the primary table are evaluated.

In the case of modification of a row in a joined table, only the columns specified by the Modify field will potentially be changed. You should note that two "rows" from a join of tables are not necessarily independent. For example, changing a Products.SupplierName from Organic Growers Ltd to OGL in one row has the effect of changing the name for all products that have the same supplier. If a subsequent row includes a setting of Products.SupplierName to the original value, the change will be undone for all rows. To control this effect, you must use the Modify field carefully to select the columns to be changed.

Note that the target ODBC database may refuse to carry out modifications if "referential integrity" will be violated; see "Import Procedure" for further details. Referential integrity problems can occur with delete commands, even when a single table is involved.

The Table field is a mandatory field.

SelectBy

The SelectBy field specifies one or more naming attributes that ODBCAgentImp is to use as selection criteria during the import procedure. The syntax is:

SelectBy=predicate

where predicate is built up in a natural way from abbreviations, ampersand (&) characters representing logical ANDs, vertical bar (|) characters representing logical ORs, exclamation point (!) characters representing logical NOT, and parentheses ( ) to coerce an order of evaluation.

For example:

A&!(B|C|!(D&E&(!F))))

When the predicate is evaluated, the values to be used for a particular abbreviation are taken from the row information being imported at any one time. The predicate is used to select (if possible) a single row from the database.

The use of the SelectBy field is in strong contrast to the Where field for export, in which fixed values are used for every row to be selected for export. As an example (FirstName & LastName) would be used by a row to be imported that specified FirstName as Joe and LastName as Bloggs to select the single row that used this combination of FirstName and LastName. If there is more than one such row in the target database, the import of that particular row will fail because the import operation is ambiguous for the supplied data.

The precedence of AND, OR, and NOT items is as specified for SQL (NOT binds tightest, then AND, then OR). When in doubt, you should use parentheses ( ) to group these items.

This is a mandatory field.

Modify

The Modify field specifies the entry attributes in the ODBC database that ODBCAgentImp is to modify. The syntax is:

Modify=attribute_list

where attribute_list specifies the attributes to import, in the format:

abbreviation[,abbreviation …​]

For example:

Modify=T, TOC, BD, HD, A, CITY, REG, C

Naming attributes specified in the SelectBy field cannot be specified in the Modify field, nor can attributes that correspond to expressions in an export table (that is, which compute a value by combining more than one column value).

This is a mandatory field. ODBCAgentImp modifies only the columns that correspond to the attributes specified in attribute_list.

When using joined tables, remember that Modify field attributes can be selected to alter the column values in several tables at the same time.

CreateIfAbsent

The CreateIfAbsent field controls whether or not ODBCAgentImp creates a new ODBC entry ("row" in ODBC terminology) in the ODBC database if it does not find a matching entry using the naming attributes supplied in SelectBy. The syntax is:

CreateIfAbsent=boolean

where boolean is one of the following values:

  • TRUE - Create a new entry using the attribute values supplied in the import data file (default)

  • FALSE - Do not create a new entry

This is an optional field. If it is not present in the configuration file, ODBCAgentImp creates new entries if possible. If the Table field specifies a join of tables, ODBCAgentImp creates an entry (inserts a row) in the primary table only.

Exceptions

The Exceptions field specifies the import error file that ODBCAgentImp is to create and write error information about entries that cannot be imported into the ODBC database. The syntax is:

Exceptions=filename

For example:

Exceptions=Exceptions.txt

The filename is taken as within the current working directory unless it includes a relative or absolute pathname, such as:

Exceptions=..\except\Exceptions.txt
Exceptions=\users\fred\odbc\Exceptions.txt

ODBCAgentImp saves the complete row information, with diagnostic and other information, for each row that it is unable to import into this file. You can use this file as input to ODBCAgentImp and re-run the import operation, after first fixing the reported errors.

This is an optional field. If it is not specified in the configuration file, ODBCAgentImp creates the file except.txt in the current working directory.

InsertOnly

The InsertOnly field controls whether existing entries in the ODBC database are updated with attribute values from the import data file. The syntax is:

InsertOnly=boolean

where boolean is one of the following values:

  • TRUE - Do not modify existing entries, and create new entries if there are no matches in the database using the naming attributes specified in the SelectBy field.

  • FALSE - Modify existing entries with the attribute values supplied in the import data file (default) unless otherwise permitted.

An entry is created when no matching entry is found when any one of the following is true:

  • The import entry (record) explicitly specifies that a new entry is to be added.

  • CreateIfAbsent is set to TRUE

  • InsertOnly is set to TRUE.

Thus, the InsertOnly field overrides a CreateIfAbsent field that is set to FALSE and vice versa.

This is an optional field. If it is not present in the configuration file, ODBCAgentImp only modifies existing entries unless otherwise directed.

ChangeType

The ChangeType field specifies the alphanumeric string used in the import data file to indicate the "changetype" for ODBC entries. The syntax is:

ChangeType=string

For example:

ChangeType=change_it

This is an optional field. If it is not present in the configuration file, ODBCAgentImp recognizes the string ChangeType as the "changetype" identifier. The value supplied to the "changetype" identifier must be one of add, delete, or modify.

If the ChangeType field is not defined, the control of the imported rows in the import data file is done by quasi-attributes using ChangeType such as:

ChangeType: delete

If it is defined (for example, to change_it), the control of imported rows is done by:

change_it: delete

Relationships

The Relationships field specifies references from one table to another for which referential integrity enforcement can be handled by nullifying the reference. Use the Relationships field to permit entries ("rows" in ODBC terminology) to be deleted when entries in other tables affected by referential integrity point to them, or when it is unacceptable for the reference to a deleted entry to continue to exist.

In order for ODBCAgentImp to implement this function:

  • The reference that points to the entry to be removed must be nullifiable

  • The access control that permits ODBCAgentImp to nullify the reference must be in force

The syntax is:

Relationships=relationship[,relationship …​]

where relationship is a string in the format:

abbreviation⇒abbreviation

The first abbreviation specifies the column in the table that contains a reference; this is the table that is affected by referential integrity. The second abbreviation specifies the column in the table that supplies the value of the reference. For example, Employees.[org-unit-id] could be a reference in the Employees table to an entry ("row", in ODBC terminology) in the OrgUnit table, using the value of OrgUnit.Id as the value used in the reference. In this case, the relationship would be specified as:

Employees.[org-unit-id]⇒OrgUnit.Id

Removing an OrgUnit entry (if successful) invalidates all the pointers Employees.org-unit-id that point to the OrgUnit entry. If referential integrity enforcement is switched on in the database for this relationship, removing the OrgUnit entry is impossible. However, if referential integrity enforcement is switched off, the OrgUnit entry can be deleted, leaving the Employees.[org-unit-id] references pointing into "thin air".

You must create abbreviations for all related table elements whose referential integrity you want to override. Thus, an entry, or "row", that is referenced by another row with referential integrity policing cannot be deleted. However, when the Relationships field has been used to specify a referential integrity override and ODBCAgentImp detects a failure to remove a row for this reason, it nullifies the column value for all rows that would otherwise refer to the row specified by the relationship. For example, consider two tables-"Clients" and "Websites"-with a set of row values as follows: 
Clients
Id Surname Given Name Email Address
16 Smith Fenella fenella@mysp.net
Websites
Id Owner WWW Site
32 16 (to be nullified) www.fenella.mysp.net
45 16 (to be nullified) www.fenella-import.com

Referential integrity between these two tables is defined by Websites.Owner ⇒ Clients.Id. Row 16 in the Clients table cannot be removed while rows 32 and 45 in the Websites table point to it and the database applies referential integrity enforcement to the relationship. However, the referential integrity link can be broken by setting the Owner value to null. The Relationships field permits this action to occur automatically.

When the database does not apply referential integrity enforcement to the relationship, an attempt to remove Row 16 will succeed, and the references in the Websites table will stay set to the same (now-nonsensical) value. Use the AlwaysFollowReferences field to cause the relationships to be followed in the absence of referential integrity enforcement, or, better, establish referential integrity enforcement. This is an optional field.

AlwaysFollowReferences

The AlwaysFollowReferences field controls whether ODBCAgentImp follows the references defined in the Relationship field if referential integrity enforcement has not been configured in the database for the specific relationships specified. The syntax is:

AlwaysFollowReferences=boolean

where boolean is one of the following values:

  • TRUE - always follow the references defined in the Relationships field even if referential integrity is not enforced for the references specified by Relationships

  • FALSE - do not follow references if referential integrity is not configured in the database for the references specified by Relationships (default)

Using the database referential integrity enforcement mechanism is a more efficient solution than leaving referential integrity unenforced and using AlwaysFollowReferences. This is because AlwaysFollowReferences always checks for references, whether one exists or not for the particular row being removed. Thus, more operations are typically carried out when using AlwaysFollowReferences by comparison with using referential integrity enforcement.

This is an optional field.

ModifyAnyway

The ModifyAnyway field controls whether ODBCAgentImp performs a comparison operation before modifying an ODBC entry. The syntax is:

ModifyAnyway=boolean

where boolean is one of the following values:

  • TRUE - compare the ODBC entry with the import data entry before modifying the entry

  • FALSE - modify the ODBC entry without performing a comparison operation first (default)

When ModifyAnyway is set to TRUE, ODBCAgentImp compares each attribute value in a "modify" import data entry with the corresponding entry in the ODBC database. If all of the values match, ODBCAgentImp does not modify the ODBC entry. The following table shows the matching rules that ODBCAgentImp uses depending on the corresponding column data type. If <none> is specified then no comparison is performed.

SQL Data Type Matching Rule Form

SQL_BIGINT

integer form

SQL_BINARY

numeric form

SQL_BIT

numeric form

SQL_CHAR

case-sensitive form

SQL_DATE

case-ignore form

SQL_DECIMAL

numeric form

SQL_DOUBLE

none

SQL_FLOAT

none

SQL_INTEGER

integer form

SQL_LONGVARBINARY

none

SQL_LONGVARCHAR

none

SQL_NUMERIC

numeric form

SQL_REAL

none

SQL_SMALLINT

integer form

SQL_TIME

case-ignore form

SQL_TIMESTAMP

case-ignore form

SQL_TINYINT

integer form

SQL_VARBINARY

integer form

SQL_VARCHAR

case-sensitive form

SQL_WCHAR

case-sensitive form

SQL_WLONGVARCHAR

case-sensitive form

SQL_WVARCHAR

case-sensitive form

This is an optional field.

Autos

The Autos field specifies named sequences to be used with Oracle databases.

To generate an automatically incremented field for new rows a named sequence has to be used (comparing to autoNumber fields in Microsoft Access). This helps to generate unique primary keys. The syntax is:

Autos=attribute_abbreviation(sequence_name)[, attribute_abbreviation(sequence_name) …]

Use comma-separated values in case there are multiple columns which require sequences are allowed.

An example for the attributes ID and SER, and the sequences employeeid and serial would be as follows:

Autos=ID(employeeid), SER(serial)

The presence of this field modifies the INSERT statements in the following way:

  • If id is present, its value is automatically derived from the nextval value of the given sequence.

Internally the next value is derived from the Oracle-internal table “dual”.

  • This feature is only supported with Oracle databases.

  • You must provide values for the fields in the import data file although these values are irrelevant.

This is an optional field.

SQLExecDirect

The SQLExecDirect field controls whether ODBCAgentImp uses the ODBC function SQLExecDirect or SQLPrepare/SQLExec in stored procedure mode. The latter is the default. It has the advantage, that a repeatedly-used SQL statement is interpreted just once on the data base side. With SQLExecDirect in each cycle the statement has to be interpreted again. But SQLExecDirect avoids the issue with open cursors in some ODBC driver implementations, e.g. in the Microsoft SQL Server driver.

The syntax is:

SQLExecDirect=boolean

where boolean is one of the following values:

  • TRUE – use SQLExecDirect in stored procedure mode

  • FALSE – use a combination of SQLPrepare/SQLExec in stored procedure mode (default).

This is an optional field.

SETOPTIONS

The SETOPTIONS can be used to execute SQL SET statements one time before the import procedure starts. For example, if on the table a trigger is defined that starts a stored procedure it might be necessary to set some options. On Microsoft SQL Server data bases, certain options can not be set in the stored procedure but must be set in the client.

The statement is executed once after the structure of the targeted primary table has been evaluated.

The syntax is:

SETOPTIONS=set_statement [ ; …​ ]

where set_statement is a valid SQL SET statement for the target data base.

In the following example the options ARITHABORT and ANSINULLS are specified:

SETOPTIONS = SET ARITHABORT ON; SET ANSI_NULLS OFF

This is an optional field.

The Procedures Section

The Procedures section consists of fields that define the parameters of the import operation using Stored Procedures for ODBCAgentImp. The next sections describe these fields.

Procedure

The Procedure field specifies a list of configured procedures, of which the first is the active one. The syntax is:

Procedure=proc-id [, proc-id2 ]

Example:

Procedure=Full_SP

This is an mandatory field for import using stored procedures.

procedure declaration

The procedure declaration field specifies the stored procedure by name proc-id including response codes. This name must correspond precisely to the stored name of the Stored Procedure/Function in the RDBMS which is to be invoked.

proc-id = [return-tag] CALL procedure-name ( [RETURN] tag-1 [= preset] [,[RETURN] tag-2 [= preset] ] …​ ) RETURNING range-item [, range-item]

where

  • preset = “ literal-string “ | number

  • range-item = ( range-list ) [range-output]

  • range-list = ( number | ( number : number ) ) [, range-list]

  • range_output = [ ! ] (“ literal-string “ | token )

This is a required field for import.

The identifiers (proc-id, return-tag, tag-1 etc.) are arbitrary combinations of :

  • Alphanumerics

  • Underscore

  • Hyphen

In the range-output production, the initial exclamation mark distinguishes between a warning and an error. The exclamation mark can be placed within a quoted string rather than preceding it, without change in effect; for example:

FullSP =res call SP_BENUTZERROLLE (op = 2, rname, mitarb) \
returning (0:9999),(-1)!"make it a good error!", \
(-2) ! "quite extraordinary", (-3) "!amazing"
DeltaSP =result call ADD_FUNC (base=2, addend = 5 )\
returning (0 , 1: 6 ),(7:10)"Too many for comfort", \
(11:1000 )"!far too many altogether"

Tag names tag-1 etc. correspond directly to record attribute names in the input (and output) files. They match in sequence the return (if there is one) and the parameters of the stored procedure. Output tags may be involved in future developments to produce export files, but are not currently supported except as potential providers of a return value.

If there is a return tag (i.e. the procedure is in fact a function), the RETURN indicator cannot be used (since there already is a return). Otherwise, RETURN is only permitted with an argument that is defined as OUT or IN/OUT in the underlying function or procedure definition; only one RETURN is permitted per procedure definition, and the value returned must resolve to a numeric value.

The names of the parameters (tag-1, tag-2 …) must be the same (without case sensitivity) as the names of lines in the input file to which they are potentially to correspond (removing the need for an [attributes] section). Setting preset allows a tag to be defaulted to a particular value if a value is not set in an input record. Note, that the tag “ChangeType” the value of the ChangeType field of the import section is allowed.

Presets must be of a format that corresponds to the nature of the target parameter, e.g.:

  • Numeric values (format depending on the precise data-type)

  • String values

  • Dates

Presets can only be checked against the ruling stored procedure characteristics after connection to the database, and not when the configuration file is analysed. Thus, a value that is accepted with a particular stored procedure definition could cause an error return after a modification to the stored procedure.

The tags need not be the same as those (formal parameter names) stored in the stored procedure definition. For example, Oracle9i defines procedure creation, with some simplifications, as:

CREATE [OR REPLACE] PROCEDURE procedure-name (arg1-name [ IN | OUT | IN OUT ] data-type , … ) ( AS | IS ) pl-sql-or-other;

but tag-1 need not be the same as arg1-name. The parameter characteristics (data-type, IN/OUT values, etc.) are not specified in the configuration file.

The stored procedure mechanism makes use of a returned value. This is the value provided by the return value of the procedure/function when return-tag is provided. This value is optional, although desirable. If absent, the key word RETURN must prefix the tag corresponding to the return value, which must be:

  • Designated as an OUT parameter in the definition of the stored procedure

  • Of value mapping to an integer.

The return tag is mapped to a string value as defined by RETURNING. Returned values must be numeric, but can be positive, negative, or zero. The result of the RETURNING process is a string (if specified) defined by range-output (if specified). Evaluation is left to right. The strings have the following semantics:

  • No string: OK

  • String not preceded by "!" - warning described by the string

  • String preceded by "!" - failure described by the string (e.g. "!ERROR!! - review record!"

  • Return is out of range - failure.

In both the latter two cases, an error notification is made to the exceptions file. This also applies if no return is made (null response) or if the return is not an integer. In the warning case, the trace file contains a warning message if the appropriate trace-level settings are made.

The returned value of data-type must be numeric, as given in the following table:

Allowed Not supported

SQL_DECIMAL
SQL_NUMERIC
SQL_SMALLINT
SQL_INTEGER
SQL_DOUBLE
SQL_TINYINT
SQL_REAL
SQL_FLOAT

SQL_CHAR
SQL_VARCHAR
SQL_LONGVARCHAR
SQL_BITSQL_BINARY
SQL_VARBINARY
SQL_LONGVARBINARY
SQL_TYPE_DATE
SQL_TYPE_TIME
SQL_TYPE_TIMESTAMP
SQL_BIGINT
Interval types

The data-types associated with a particular function or procedure can be determined by the Agent by using the "PC" facility in the configured TraceLevel value.

Dates

Date values (in the configuration file and in input files) are supported in a single configured form selected from one of the following general forms (see method of specification below):

…YYYY…MM…DD
…YYYY…MON…DD
…DD…MM…YYYY
…DD…MON…YYYY

Specific examples are:

"YYYY/MM/DD" which accepts dates like "2003/4/1"

"date-of-birth: DD-MON-YYYY" which accepts dates like "date-of-birth: 1-Apr-2003"

Here:

  • YYYY means a sequence of four digits

  • DD, MM means a sequence of one or two digits representing day and month;

  • MON means one of jan feb etc (the first three letters of the month), case insensitively;

  • … means any string of letters not in %YMD.

The default format is:

YYYY-MM-DD

which permits dates of form "2003-04-02".

In all cases, only the date itself is passed to the database.

Changes to the default may be specified in the [control] section of the configuration file in the following form:

DateFormat=format

Where format is one of the form s given above. Values are accepted with the following relaxations:

  • Single-digit days and months are accepted

  • The value in an input file can take the form of an ODBC escape; for example:
    {d '1995-01-15'}

Dates support arithmetic in stored procedures, so that if date1 and date2 are dates, date1-date2 is the positive or negative time between them.

The EncryptedAttributes Section

The EncryptedAttributes section is an optional section that lists attributes which are encrypted in the import data file and have to be decrypted by the agent before they are passed to the ODBC Interface. This functionality only works correctly in an appropriate security environment like in the DirX Identity environment configured in security mode. (See DirX Identity Connectivity Administration Guide). The attributes are listed in the format:

abbreviation=1

where abbreviation specifies the attributes to be imported.

For example:

[EncryptedAttributes]
Password=1

The Control Section

The control section is an optional section that consists of fields that provide control information that is common to both export and import procedures. The next sections describe these fields.

RecordSeparator

The RecordSeparator field specifies the record separator that distinguishes between successive import or export entries. The syntax is

RecordSeparator=string

where string is a value that can contain a form-feed, expressed as "\f". No other escapes are permitted; backslashes are not permitted other than as a prefix to "f" (not even to "F").

This is an optional field. If it is not specified in the configuration file, the ODBC agents use a default string row:.

Trace

The Trace field controls whether the ODBC agents perform program flow tracing on an export or import operation. The syntax is:

Trace=[switch]

where switch is one of the following values:

  • 0 - Do not perform program flow tracing on the export or import operation (default)

  • 1 - Perform program flow tracing on the export or import operation

If 1 is specified, the ODBC agents write information about the export or import operation to the pathname specified in the TraceFile field.

TraceLevel

The TraceLevel field controls the amount of program event information that is written to the trace file during import and export operations. The syntax is:

TraceLevel=trace_string_list

where trace_string_list is one or more of the following strings or abbreviations, separated by whitespace:

  • ConnectAttributes (CA) - include connection "attributes" (fields)

  • FailSummary (FS) - include a summary of failed entries sent to the error file

  • ODBC - report ODBC versions

  • SQL - include SQL statements that are to be executed

  • Summary (S) - include a summary of all entries imported or exported (mark failed entries with a trailing # character

  • Warnings (W) - include ODBC warnings (ODBC errors are always written to the trace file)

  • Columns (Cols) - include information on columns stored as part of the database schema

  • RefData (Ref) - include information on the reference data used for delta export

  • Statistics (Stats) - include statistics about the import operation, such as the number of creates, updates, and deletes, the number of entries unprocessed because of errors, the total of all entries handled with or without error but not skipped, and skipped entries

  • AllProcedures (AP) – provides summary details of all functions and procedures

  • ProcedureCharacteristics (PC) - provides details of all functions and procedures configured into the configuration file

  • ProcedureReturns (PR) - provides details of (numerical) procedure returns if available

ConnectAttributes

When trace_string_list includes ConnectAttributes, the ODBC agents write the connection "attributes" that fully define the connection to the ODBC database to the trace file (and to standard output) in the format they would have as fields in the configuration file (username and password are not included). You can copy the connection attributes from the trace file into the configuration file if you wish to change any of the parameters at agent startup. For example:

database connection attributes:
DSN=my_database
DBQ=C:\MSOffice\Access\Samples\Northwind.mdb
DriverId=25
FIL=MS Access
MaxBufferSize=512
PageTimeout=5
FailSummary

When trace_string_list includes FailSummary, the ODBCAgentImp provides a summary of each entry that it fails to process on an import operation, in the format:

entry_introducer attribute_1 / attribute_2 …​ line-number-text disposition-text error-sign
diagnostic-event
diagnostic-event
…​

Here is an example:

record Annette/Dodsworth at line 129 (absent) #

warning: empty import attribute
REG

insertion forbidden
ODBC

When trace_string_list includes ODBC, the ODBC agents write the version of the ODBC subsystem and the driver in use to the trace file. For example:

ODBC version info
03.00.0000

ODBC driver version
02.50
SQL

When trace_string_list includes SQL, the ODBC agents write to the trace file the SQL statements that they execute at various stages of the import or export procedure. Because the ODBC agents can only run successfully if the SQL is correct, you can use this information to determine the precise location at which an import or export operation has failed by running the SQL statement in a native SQL environment for the database being accessed.

ODBCAgentExp makes only one SQL call which obtains all matching records sequentially. This is the form of the output:

SELECT statement
SELECT Employees.FirstName, Employees.LastName, Employees.Title, Employees.TitleOfCourtesy, Employees.BirthDate, Employees.HireDate, Employees.Address, Employees.City, Employees.Region, Employees.PostalCode, Employees.Country, Employees.HomePhone, Employees.Extension, Employees.Notes FROM Employees ;

All of the SELECT statement is on a single line.

ODBCAgentImp makes several SQL calls which prepare it for the various import scenarios it may encounter. The agent performs this preparation phase before it carries out any importing, and must complete this preparation phase successfully. Here is an example:

dummy SELECT statement
SELECT Employees.FirstName, Employees.LastName, Employees.PostalCode, Employees.Title, Employees.TitleOfCourtesy, Employees.BirthDate, Employees.HireDate, Employees.Address, Employees.City, Employees.Region, Employees.Country, Employees.HomePhone, Employees.Extension, Employees.Notes FROM Employees
SELECT statement
SELECT Employees.FirstName, Employees.LastName, Employees.Title, Employees.TitleOfCourtesy, Employees.BirthDate, Employees.HireDate, Employees.Address, Employees.City, Employees.Region, Employees.PostalCode, Employees.Country, Employees.HomePhone, Employees.Extension, Employees.Notes FROM Employees WHERE (Employees.FirstName=?) AND (Employees.LastName=?)
UPDATE statement
UPDATE Employees SET Employees.Title=?, Employees.TitleOfCourtesy=?, Employees.BirthDate=?, Employees.HireDate=?, Employees.Address=?, Employees.City=?, Employees.Region=?, Employees.PostalCode=?, Employees.Country=?, Employees.HomePhone=?, Employees.Extension=?, Employees.Notes=? WHERE (Employees.FirstName=?) AND (Employees.LastName=?)
INSERT statement
INSERT INTO Employees (FirstName, LastName, PostalCode, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, Country, HomePhone, Extension, Notes) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
DELETE statement
DELETE FROM Employees WHERE (Employees.FirstName=?) AND (Employees.LastName=?)

ODBCAgentImp uses the dummy SELECT statement to "prepare" an execute statement; the preparation enables the table characteristics to be extracted. The SQL is never executed.

ODBCAgentImp uses the SELECT statement to test each supplied entry to see how many of its kind there are in the database, using the predicate that follows WHERE, in this case:

(Employees.FirstName=?) AND (Employees.LastName=?)

This condition would have been the result of a SelectBy field of:

SelectBy = GN & SN

where GN maps to Employees.FirstName and SN maps to Employees.LastName.

The question mark (?) characters are used internally to indicate entry-specific arguments, as supplied. This construct is also used in each of the following SQL statements.

ODBCAgentImp uses the UPDATE statement to modify rows according to the specification of the incoming entry; that is, each of the '?' signs are substituted for with the incoming information.

ODBCAgentImp uses similar statements for INSERT (to add a new row) and DELETE (to remove a row).

Summary

When trace_string_list includes Summary, ODBCAgentImp provides a summary of each entry processed in the import operation, in the format:

entry_introducer attribute_1 / attribute_2 …​ line-number-text disposition-text [error-sign]

each followed by diagnostic information if necessary. For example:

record Nancy/Davolio at line 1 (updated)

record Andrew/Fuller at line 17 (updated)

record Janet/Leverling at line 33 (updated)

record Margaret/Peacock at line 49 (updated)

record Steven/Buchanan at line 65 (updated)

record Michael/Suyama at line 81 (updated)
Warnings

When trace_string_list includes Warnings, the ODBC agents write warning messages into the trace file. The messages have the following format:

  • The first line announces a warning.

  • The second and third lines provide information generated by the underlying ODBC support. Consult the ODBC Help for explanations of the meaning.

  • The fourth line is provides information generated by the ODBC driver manager. This is occasionally useful, particularly in conjunction with the SQL statements included by using the SQL trace string.

  • The fifth line is occasionally generated, and is based on experience. (The generation is an open-ended capability.)

Here is a sample (benign) warning that occurs on agent startup:

connection with information
state=01000
native error=0
error message=[Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).
this error is probably irrelevant.

Here is a warning generated as the result of misspelling the table-name in an export operation:

SQLExecDirect failure
state=42S02
native error=-1305
error message=[Microsoft][ODBC Microsoft Access 97 Driver] The Microsoft Jet database engine cannot find the input table or query 'xEmployees'. Make sure it exists and that its name is spelled correctly.

Here is a warning generated as the result of misspelling the table-name in an import operation:

SQLDescribeCol failure
state=42S02
native error=-1305
error message=[Microsoft][ODBC Microsoft Access 97 Driver] The Microsoft Jet database engine cannot find the input table or query 'xEmployees'. Make sure it exists and that its name is spelled correctly.
state=07009
native error=53
error message=[Microsoft][ODBC Microsoft Access 97 Driver]Invalid column number
Columns

When trace_string_list contains Columns, the ODBC import agent writes information about the columns to which it is about to import. For example:

column info
attribute=GN
column-id=FirstName
column-expansion=Employees.FirstName
configured-data-type=SQL_CHAR
C-data-type=SQL_C_CHAR
configured-minimum=1
configured-maximum=10
fail-if-too-big=false
SQL-data-type=SQL_VARCHAR
precision=10
scale=0
nullable=SQL_NULLABLE
column info
attribute=SN
column-id=LastName
column-expansion=Employees.LastName
configured-data-type=SQL_CHAR
C-data-type=SQL_C_CHAR
configured-maximum=20
fail-if-too-big=false
SQL-data-type=SQL_VARCHAR
precision=20
scale=0
nullable=SQL_NULLABLE
etc.

In this information:

  • attribute gives the abbreviation;

  • column-id defines the name of the column within the table;

  • column-expansion defines the full column definition as defined in the attributes section;

  • configured-data-type and C-data-type give information about the representation of the ODBC data-type within the agent;

  • configured-maximum defines the maximum size specified for the column, either by configuration or by taking information from the ODBC database;

  • SQL-data-type give information about the native ODBC data-type for the column;

  • precision gives (for text information) the field length within the ODBC database; scale is not relevant at present;

  • nullable indicates whether the column information is permitted to be absent (TRUE), or whether it must always be present (FALSE).

RefData

When trace_string_list includes RefData, ODBCAgentExp writes the following information to the trace file:

  • When the Mode field in the export configuration file is set to full, ODBCAgentExp writes a message indicating that full export is to take place

  • When the Mode field in the export configuration file is set to delta-or-full or delta, ODBCAgentExp writes:

    • A list of reference files that are currently available (possibly none)

    • The name of the file identified as the reference file to be used in the delta export procedure (possibly none)

    • The information contained in the reference file header (if present)

    • A message indicating that delta export is to take place

If the -v parameter has been specified on the command line, ODBCAgentExp also displays this information on the user’s console.

ODBCAgentImp ignores the RefData option.

Here is an example trace file for a full export when the RefData option is used:

reference data files:
none
no previous reference file found
full export with reference file:
R9122200

Here is an example trace file for a delta export when the RefData option is used:

reference data files:
R9122200
reference file found:
.\R9122200
reference info:
time-stamp=Wed Dec 22 16:47:42 1999
record count=17
key data width=92
support-8-bits=TRUE
data hash size=8
selection=Employees.FirstName, Employees.LastName, Employees.Title, Employees.TitleOfCourtesy, Employees.BirthDate, Employees.HireDate, Employees.Address, Employees.City, Employees.Region, Employees.PostalCode, Employees.Country, Employees.HomePhone, Employees.Extension, Employees.Notes
keys=Employees.FirstName, Employees.LastName, Employees.HomePhone, Employees.Country, Employees.City
from=Employees
where=null

delta export with new reference file:

R9122201
RefData

When trace_string_list includes Stats, ODBCAgentImp writes statistical information into the trace file. ODBCAgentExp ignores the Stats option. Statistics are provided on entries created and updated, entries deleted, entries that could not be processed as the result of an error, the total number of entries processed, and the total number of entries skipped (these entries are not included in the total processed). The number of updated attributes and the number of attributes on which an update was unnecessary are also listed. Items of statistics for which the value is zero are not included. For example:

import statistics:
creates: 21
updates: 1105
updates (attr): 995
updates (not necess.)2320
deletes: 112
errors: 6
total imports: 1244
skips: 2
ProcedureCharacteristics

When trace_string_list contains ProcedureCharacteristics, the ODBC import agent writes information about the parameters of the stored procedure to which it is about to import. For example:

column info
procedure=test
procedure-cat = ""
procedure-schema = "SCOTT"
procedure-identifier = "SP_BENUTZERROLLE"
parameter-name = "RETURN_VALUE"
parameter-type = SQL_RETURN_VALUE
data-type = SQL_DECIMAL
type-name = "NUMBER"
column-size = 38
buffer-length = 39
decimal-digits = 0
num-prec-radix = 10
nullable = SQL_NULLABLE
remarks = "null"
column-def = "null"
sql-data-type = SQL_DECIMAL
sql-datetime-sub = SQL_LONGVARCHAR
char-octet-length = -1
ordinal-position = 0
is-nullable = "YES"
etc

parameter bind info
parameter-number = 1
parameter-type = SQL_PARAM_OUTPUT
c-data-type = SQL_C_CHAR
sql_type = SQL_DECIMAL
precision = 18
scale = 0
buffer-width = 20
pvd->slip = 0
etc

The output gives detailed information about data type mapping between the target database and the ODBC subsystem and the ODBC Agent:

  1. The native data-type of procedure parameters (NUMERIC, VARCHAR2 etc).

  2. The data-type supported by and visible within the ODBC subsystem (SQL_DECIMAL, SQL_CHAR, etc)

  3. The C-data-type into which the ODBC data-type is mapped (SQL_C_FLOAT, etc.).

Column info covers mapping 1. to 2.,bind info 2. to 3.

ProcedureReturns

When trace_string_list contains ProcedureReturns, the ODBC import agent writes information about the procedure returns if available. For example:

procedure return value at line n
result=0.000000
AllProcedures

When trace_string_list contains AllProcedures, the ODBC import agent writes information about all the defined procedures in the target database. For example:

procedure-catalog = "mydatabase" +
procedure-schema-identifier = "dbo" +
procedure-identifier = "sp_benutzerrolle;1" +
remarks = "null" +
procedure-type = function

This trace level can be used to check the names of the defined procedures to which the agent has access.

This should be used only for test purposes because the access costs a lot of performance if many stored procedures are defined in the target database.
TraceFile

The TraceFile field specifies the name of the trace file to which the ODBC agents are to write information about their execution. The syntax is:

TraceFile=filename

where filename can be a pathname or a file name. When a pathname is specified, the entire path must pre-exist. If filename has a suffix, the ODBC agents use this file to write tracing information. If filename does not have a suffix (that is, it does not contain a period (.)), the ODBC agents use filename as a prefix for a multiple trace file naming scheme of the form:

filenamennn.txt

where filename represents the supplied prefix and nnn is a three-digit decimal string that starts at 000 and has a maximum value of the MaxTraceFiles field minus 1. For example:

imp_trace038.txt

The ODBC agents write multiple trace files up to one less than the maximum specified in the MaxTraceFiles field and use the MaxTraceSize field to determine when to create a new trace file in the series. You can supply a pathname to specify where the trace files are to be located.

This is an optional field. If it is not specified in the configuration file, the ODBC agents direct program event output to the trace file trace.txt in the current working directory.

MaxTraceFiles

The MaxTraceFiles field determines the number of trace files that the ODBC agents are permitted to create, in rotation. The syntax is:

MaxTraceFiles=number

where number is a non-negative integer between 2 and 1000. Each trace file name has a suffix ranging from 000.txt (first trace file created) to nnn*.txt*, where nnn is one less than the value of the MaxTraceFiles field.

This field is optional. If it is not specified, the ODBC agents use a maximum number of 1000.

MaxTraceFileSize

The MaxTraceFileSize field determines the maximum size of a trace file. The syntax is:

MaxTraceFileSize=size

where size is a value between 1024 and 231-1. Actual trace files will always be a little smaller than this size (by up to 256 bytes).

The ODBC agents use the value in the MaxTraceSize field to determine when to create a new trace file. The change of file to the next in the series occurs when the ODBCAgentImp or ODBCAgentExp determines that the report to be written could cause the size of the trace file to exceed the maximum size set by the MaxTraceFileSize field.

TraceFlow

The TraceFlow field specifies the level of tracing information written to the trace file. The syntax is:

TraceFlow=level

where level is an integer from 0 to 9. The higher the number, the more tracing information is written. Currently, only trace level 1 is implemented; at this level, the ODBC agents give an indication of entrance and exit for main functions. For example:

...
entering CONVERT()
exiting CONVERT()
entering SQLFetch()
exiting SQLFetch()
exiting odbc_export()
8bit

The 8bit field controls whether or not ODBCAgentExp accepts characters larger than 7 bits without escaping them to hex notation (\xhh). The syntax is:

8bit=boolean

where boolean is one of the following values:

  • TRUE - Accept characters where bit 8 is non-zero

  • FALSE - Escape characters where bit 8 is non-zero (default)

On export, ODBCAgentExp changes characters in the text that is output, if necessary, to the escaped hex code. This conversion always occurs for non-text characters like NEWLINE. If the 8bit field is set to TRUE, ODBCAgentExp transmits characters with encodings in the range \xa0 to \xfe unchanged; if set to FALSE, it converts the characters to the hex notation. A backslash \ is encoded as two backslashes \\. On import, ODBCAgentImp reverses the encoding to reproduce the original text without encoding.

This is an optional field. If it is not specified in the configuration file, ODBCAgentExp escapes characters where bit 8 is non-zero. ODBCAgentImp ignores the 8bit field on import.

DataHash

The DataHash field specifies the number of octets that ODBCAgentExp is to use when creating the hash value for an entry’s attributes in the delta reference file. The syntax is:

DataHash=number

where number is a non-negative integer between 4 and 16. The hash values for entry attributes that ODBCAgentExp creates are not guaranteed to be unique for every entry. Therefore, the larger the number of octets used by ODBCAgentExp to create a hash value, the more unlikely it is that ODBCAgentExp will create duplicate hash values for different entries.

This is an optional field. If it is not specified in the configuration file, ODBCAgentExp uses 8 octets to create the hash value for the entry.

Configuration File Error Reporting

The ODBC agents attempt to pinpoint the cause of any errors in the configuration file. For example, the output:

E:\transfer\run>h:\development\exporter\exporter\ODBCAgentExp -v
-ftransfer.cfg
missing equals sign at line 34
From ??Employees

detects an error in the export section in the configuration file. Where errors occur (as in this case) in simple analysis of the characters, the "??" marker occurs at the point where the error is detected. Where the error requires some context, it will normally follow the offending token. For example, a second SelectBy creates this error:

duplicate SelectBy setting at line 45
SelectBy=??GN

Import and Export Data File Format

The ODBC agent import and export data files use a tagged file format with the following characteristics:

  • The only supported encoding is ISO 8859-1.

  • Each entry attribute is contained on one line; line continuation is permitted using the backslash (\) as the line continuation character.

  • The representation of each attribute is: attribute_name: attribute_value(s)

  • Leading and trailing whitespace between attribute_name and attribute_value is ignored. For example, in the attribute:

           SN:   Lowell Jr.

    the whitespace between the colon (:) and the start of the attribute value is ignored, but the whitespace within the attribute value is returned

  • The record (entry) separator is either the default string row: or the string defined in the RecordSeparator field in the configuration file. For an import data file, the ODBC agent requires that a record separator be present at the end of each entry. However, it does not require the presence of a record separator at the start of the first entry in the file or at the end of the last entry in the file.

  • An optional file termination indicator string end: can be placed anywhere in an import data file to direct ODBCAgentImp to ignore data that occurs after the end: terminator during import processing. The file terminator string is never present in an export data file.

  • Comment lines can be placed anywhere in the file and are identified by a # character at the beginning of the line. A non-comment line that would otherwise start with a # character starts instead with \#.

  • An optional skip record indicator string skip: can be placed in a record to direct ODBCAgentImp to ignore the record during import processing. The skip record string is never present in an export data file.

  • The data file format supports a per-entry "changetype" attribute that specifies the type of modification indicated for the entry in the ODBC database. The value for "changetype" is one of "add", "modify", or "delete". The changetype attribute name and its values are case-insensitive. The attribute name is either ChangeType or the name specified in the ChangeType field.

  • The export data file format permits <CR> or <LF> to be used in attribute values.

  • Boolean attribute values are represented as 0 (for FALSE) and 1 (for TRUE).

  • Characters in import data file entries can be specified by their hex value in the format \xhh.

  • Backslash characters in import data file entries must be specified as \\.

Here is an example (CR LF is represented as \x0d\x0a in line eight):

row:
GN:  Nancy
SN:  Davolio
T:   Sales Representative
TOC:  Ms.
BD:  1948-12-08 00:00:00
HD:  1992-05-01 00:00:00
A:   507 - 20th Ave. E.\x0d\x0aApt. 2A
CITY:  Seattle
REG:  WA
PC:  98122
C:   USA
TEL:  (206) 555-9857
EXT:  4109
DESC:  Education includes a BA in psychology from Colorado State University in 1970. She also completed "The Art of the Cold Call." Nancy is a member of Toastmasters International.

row:
...

Import Error File Format

An import error file log entry generated by ODBCAgentImp on a failed import of an entry has the following format:

#entry_identifier
source_entry
#error_messages
#

where entry_identifier specifies the line number in the import data file at which the failed entry (record) exists, source_entry is the original entry that ODBCAgentImp was unable to import, and error_messages describe the error. Here is an example of an error log for an import operation in which the configuration file sets InsertOnly to TRUE:

# record at line 17
row:
GN: Nancy
SN: Davolio
T: Sales Representative
TOC: Ms.
BD: 1948-12-08 00:00:00
HD: 1992-05-01 00:00:00
A: 507 - 20th Ave. E.\x0d\x0aApt. 2A
CITY: Seattle
REG: WA
PC: 98122
C: USA
TEL: (206) 555-9857
EXT: 5467
DESC: Education includes a BA in Psychology from Colorado State University in 1970. She also completed "The Art of the Cold Call." Nancy is a member of Toastmasters International.

# error: row already exists
#

If the entry itself is invalid, ODBCAgentImp records it as a comment. For example, in the following entry, "Surname" was used in place of the abbreviation "SN":

# record at line 1
row:
GN: Nancy
Surname: Davolio
T: Sales Representative
TOC: Ms.
BD: 1948-12-08 00:00:00
HD: 1992-05-01 00:00:00
A: 507 - 20th Ave. E.\x0d\x0aApt.
CITY: Seattle
REG: WA
PC: 98122
C: USA
TEL: (206) 555-9857
EXT: 5467
DESC: Education includes a BA in Psychology

# warning: unrecognized attribute in import data file
# Surname
#
# unset naming attributes
# SN
The text for the failed entry is always as supplied; that is, no attempt is made to correct it. To use the entries, manual correction will probably be necessary to remove the problem.

ODBCAgentImp places a condensed version of this error message in the trace file (and also to the display if verbose mode was specified on the command line):

record Nancy at line 1 (error) #

warning: unrecognized attribute in import data file
Surname

unset naming attributes
SN

Import Procedure

Import takes place using data from an import data file. This file has the format described in "Import and Export Data File Format" and comprises a series of records, each stored on separate lines and consisting of an introducer string and a set of attribute lines. By default, the introducer string is a line containing row:. However, you can specify a different value for the introducer (for example, a form-feed) using the RecordSeparator field in the Control Section of the configuration file.

Each attribute is one of the following:

  • A naming attribute (identified as on the SelectBy attribute list, and never modified);

  • An attribute to be modified (identified by the Modify attribute list);

  • An attribute that is ignored except when a new row (record) is to be created (other attributes listed in the attributes block);

  • All other attributes - ignored (present in the import entry (record), but not specified in the attributes block).

In addition, the quasi-attribute ChangeType or an attribute substituted for it using the ChangeType field in the Import section of the configuration file can be used to modify the behavior for the entry within which the attribute is placed.

ODBCAgentImp does not handle multi-valued attributes; attributes in the first three categories are not permitted to occur twice in a single import entry. If the agent encounters a multi-valued attribute, it discards the second and subsequent values creates a warning message in the error file.

The introducer end: may be used where row: would otherwise be used, and terminates the process.

The introducer skip: may be used anywhere following a row: introducer, and causes the complete record information to be ignored. It can be used to blank off a record which requires special attention. The record information does not appear in the error file, and is not reported upon in the trace file.

For each ODBC entry, ODBCAgentImp does the following:

  • Checks the entry for compliance with the constraints specified for the attributes (or defined by the database). If constraints are broken for non-naming attributes and the attribute is not marked with the "fail-if-too-big" flag, the agent truncates the values (or pads them), and continues its processing. If constraints are broken for naming attributes or for any attribute when the "fail-if-too-big" flag is TRUE, the agent discards the entire entry and writes an explanatory message into the error file (and, if required, to the trace file).

  • Using the naming attributes, SELECTs a row (record) that matches the resulting predicate in the specified table or join of tables. All naming attributes used in the predicate must be present in the import entry.

  • If zero rows are found, and either ChangeType is set to add, or CreateIfAbsent is TRUE or InsertOnly is TRUE, creates a new entry that contains all the relevant attributes. If ChangeType is absent or is not set to add, or if both CreateIfAbsent and InsertOnly are FALSE, discards the entry and writes an explanatory message into the error file.

  • If a join of tables is specified, and a new entry is to be created, this creates a new row in the primary table only (that is, the table first mentioned in the JOIN specification). Attributes matching rows in tables other than the primary table are ignored.

  • If just one row is found, and ChangeType is set to delete, removes the row (just the row in the primary table, if a join of tables is selected). If ChangeType is set to add or InsertOnly is set, discards the entry and writes an explanatory message to the error file. Otherwise, modifies the attributes specified or removes them (using a blank line after the attribute); in both cases, attributes to be modified must be defined within the Modify list; others are ignored for modification.

    The database can refuse to remove an entry if it detects that referential integrity would be broken. This means that a row which is pointed to by another row in the same or a different table cannot be removed by ODBC (or even by SQL tools in general). The referencing components must be removed first, or the reference must be nullified. To make this happen automatically, you can use the Relationships control to specify where referential integrity enforcement is configured.

  • If multiple rows are found, discards the entry and writes an explanatory message to the error file.

  • If the -s parameter has been specified on the command line, ODBCAgentImp imports one row at a time, displaying an invitation to continue after each import. Possible inputs are:

q<CR> or Q<CR> or n<CR> or N<CR> - terminates the import procedure (case-insensitive)

g<CR> or G<CR> - terminates single-step mode and continues the import procedure (case-insensitive)

<CR> - continues with the next row

Export Procedure

ODBCAgentExp transcribes all or selected rows (records) from a table of an ODBC-accessible database to an export data file. The column information is either the stored value, or may be derived from expressions within the SQL language.

In the export section, the Select field defines attribute abbreviations that map exactly to those used in SELECT statements. The From field specifies the tables or combination of tables. These two fields normally need to be designed together.

The attribute abbreviations must be specified in the attributes section. Table names can be omitted in the specification, but it is recommended that table names are always included when the From table is a union of tables.

Here is an example of the relationship between the attribute definitions in the attributes section and the From and Select fields in the export section.

[attributes]
GN=Employees.FirstName
SN=Employees.LastName
PC=Employees.PostalCode
T=Employees.Title
TOC=Employees.TitleOfCourtesy
BD=Employees.BirthDate
HD=Employees.HireDate
.
.
.
[export]
Select=GN, SN, T, TOC, BD, HD, A, CITY, REG, PC, C, TEL, EXT, DESC
From=Employees

...

If the -s parameter has been specified on the command line, ODBCAgentExp exports one row at a time, displaying an invitation to continue after each row is exported. Possible inputs are:

q<CR> or Q<CR> or n<CR> or N<CR> - terminates the export procedure (case-insensitive)

g<CR> or G<CR> - terminates single-step mode and continues the export procedure (case-insensitive)

[any_other input*]<CR>* - continues with the next row

If the -v parameter has been specified on the command line and RefData has been specified as an option to the TraceLevel field in the export configuration file, ODBCAgentExp displays a message indicating that the export procedure is to take place.

Delta Export Procedure

The ODBCAgentExp delta export procedure exports only those entries that have changed since the last export operation using a delta reference file mechanism to determine which entries have changed. This section describes:

  • The delta export process that ODBCAgentExp follows

  • The configuration file fields and command line parameters that you can use to configure and control the delta export process

ODBCAgentExp Delta Export Process

A delta reference file is a snapshot of the ODBC database contents. It is a binary file that contains a header and a sorted array of elements that represents the entries in the ODBC database. Each element in the array contains:

  • Key values that uniquely identify the record (entry) within the database

  • A hash value of the contents of the entry (its attribute values)

  • Any key values that are necessary to uniquely identify the entry to the target database during the directory synchronization process

ODBCAgentExp creates an initial reference file on the first full export. On a subsequent delta export operation, ODBCAgentExp performs the following steps:

  • Exports the information from the ODBC database into a temporary export data file

  • Creates a temporary reference file that represents the temporary export data file contents in condensed form

  • Sorts the temporary reference file (according to default ordering criteria or according to the criteria specified by the SortControl field in the export configuration file)

  • Evaluates the temporary reference file against the reference file it previously created and builds the delta export data file as follows:

    • Each entry that is present in both reference files and for which the hash is the same is ignored

    • Each entry that was absent from the previous reference file but which is present in the temporary reference file is exported as an "add", sending the complete set of data

    • Each entry that was present in the previous reference file but which is absent in the temporary reference file is exported as a "remove", specifying only its identity using the key information in the reference file

    • Each entry that is present in both reference files, but for which the hash has changed in the temporary reference file, is exported as a "modify"

  • Creates a new reference file from the temporary reference file, retains the "old" (previous) reference file, and removes all temporary files

For modified entries, ODBCAgentExp writes only the new values of attributes to the delta export data file. If the value of an attribute specified in the Key field has changed, ODBCAgentExp treats this change as the deletion of the entry and the creation of a new entry. Consequently, the delta export data file contains a "delete" changetype entry and an "add" changetype entry.

For deleted entries, ODBCAgentExp writes only the values of the attributes specified in the Key field and the SaveAttr field to the delta export data file. Values of attributes that are not specified in either the Key field or the SaveAttr field are not written to the delta export data file.

ODBCAgentExp names reference files in the format:

Rymmddnn

where:

  • y represents the last digit of the year; for example, 9 for 1999

  • mm represents the month (01 through 12)

  • dd represents the day (01 through 31, calculated using GMT, not local time)

  • nn is a sequence number that ODBCAgent calculates for the file

For example:

R9061015

When creating a new reference file based on a previous reference file that has a sequence number, ODBCAgentExp calculates the sequence number for the new file as follows:

  • If the day (dd) of new and previous reference files are the same, it increments the sequence number by one (nn+1) for the new reference file, to a maximum of 99 for the previous reference file. If a new reference file is then needed, the sequence number re-starts at 00, and the day is incremented (taking into account all the usual month-length and leap-year rules).

  • If the day (dd) of the previous reference file is older, it assigns the sequence number 00 to the new reference file (any previous file of this name is overwritten)

  • If the day (dd) of the previous reference file is newer (which could possibly occur around midnight GMT but otherwise indicates a serious problem), it uses the day of the previous reference file for the new reference file, and assigns it the sequence number of the previous file incremented by 1. This provision ensures that the agent always has a monotonic view of time, and that clock adjustments do not confuse the mechanism.

When creating a new reference file based on a previous reference file that does not have a sequence number (for example, because a reference filename that does not use the agent’s naming format has been explicitly specified on the command line), ODBCAgent uses the next sequence number above the largest for the day, and uses 00 if no reference files are available (any previous file of this name is overwritten).

ODBCAgentExp interprets the year (y) in Ry0101nn as the last year or next year relative to Rzmmddnn depending on how close y and z are. For example, if y is 8, it is considered to be before z=9, 0, 1, 2 (the 1 cells with vertical stripes in the column 8 in the table below) but after z=3,4,5,6,7 (the -1 cells with horizontal stripes in column 8):

ODBCAgentExp

ODBCAgentExp creates delta reference files in the current working directory unless the configuration file specifies otherwise.

Configuration File Fields and Command Line Parameters for Delta Export

The following fields in the export configuration file are relevant to the delta export procedure:

  • The Mode field in the Export section - use this field to select the delta export operation

  • The Keys field in the Export section - use this field to establish the keys that ODBCAgentExp is to use for uniquely identifying each ODBC record (entry)

  • The SaveAttr field in the Export section - use this field to establish the attributes required for directory synchronization that ODBCAgentExp should store in the reference file

  • The ReferencePath field in the Export section - use this field to specify the directory in which ODBCAgentExp is to create delta reference files

  • The SortControl field in the Export section - use this field to set up a specific ordering criteria that ODBCAgentExp is to use in place of its default ordering scheme when sorting the elements in the reference file

  • The DataHash field in the Control section - use this field to specify the length of the hash value that ODBCAgentExp is to use when hashing a record (entry) attribute values for the reference file

See "Configuration File Format" for further details about these fields.

Use the -r parameter on the ODBCAgentExp command line to generate a full export data file and the initial reference file; the -r parameter overrides the delta operation specification in the Mode field of the export configuration file, but generates a reference file (if the Mode field is set to full, no reference file is generated). See the section "ODBCAgentExp Command Line Format" for more information about ODBCAgentExp command line parameters.