ISAM XDD schema structure

<key>

Defines one key. This element can be repeated to define multiple indexes.

<filters>

Defines the filters to separate the records between the various record schemas. This element will be defined once if needed.

<schema>

Defines a single record schema with its fields, this element can be repeated to define different record schemas.

maxRecLen

Defines the maximum record length in bytes.

minRecLen

Defines the minimum record length in bytes. Use the Btrieve BUTIL -stat command to find out statistics about the original file.

Element

Description

<part>

Describes one of the fields composing the key. This element must be repeated to define all the fields composing the key.

duplicate

Indicates whether the index can contain duplicates. Allowed values are "false" if the index cannot contain duplicates, "true" otherwise. The default value is "false"

primary

Indicates whether the index will act as the table SQL primary key. Set this attribute to "true" to use the index as a SQL primary key. Please note that only one primary key is allowed for each table. The default value is "false."

offset

A number defining the offset of the first byte composing the field.

size

A number defining the size in bytes of the field.

Attribute

Description

name

A string defining the field name. It should match field names defined in the <field> children elements of <schema> elements.

offset

A number defining the offset of the first byte composing the segment.

size

A number defining the size in bytes of the segment.

<field>

This element is used to enumerate the fields used to filter records.

<filter>

Defines the method to filter records.

<eq>

If this tag is used the filter will be based on an equal comparison.

<neq>

If this tag is used the filter will be based on a not equal comparison.

<lt>

If this tag is used the filter will be based on a less than comparison.

<lte>

If this tag is used the filter will be based on a less than or equal comparison.

<gt>

If this tag is used the filter will be based on a greater than comparison.

<gte>

If this tag is used the filter will be based on a greater than or equal comparison.

<other>

If this tag is used the filter will match any record not matching any other filter.

<always>

If this tag is used the filter will always match.

<or>

Logically OR together two filter rules. Only two rules can be OR'd together. Use nested techniques to OR more than two rules.

<and>

Logically AND together two filter rules. Only two rules can be AND'd together.Use nested techniques to AND more than two rules.

<not>

Logically exclude a value from a rule. Applies one per filter.

number

An increasing number to uniquely identify filters.

table

The name of the SQL table which will be generated by this filter rule. It requires a <schema> element with the same name.

name

A string defining the field name. It should match field names defined in the <field> children elements of <schema> elements.

offset

A number indicating the offset of the first byte composing the field.

size

A number indicating the size in bytes of the field.

type

A string defining the data type. Please refer to the type mapping table.

<field>

The string representing the name of the field involved in the filter.

<value>

The value that will be used for the comparison with the field values.

Element

Description

<field>

Describes one of the fields comprising the current schema.

Attribute

Description

name

The name to assign to the current schema. This name will map a SQL table containing all the records selected by this schema.

filter

Specifies which filter to use to generate the current schema, use one of the filter numbers defined in the <filters> element

size

The record length for the current schema specified in number of bytes.

onConvertError

Specifies the default action to take when a value cannot be converted into SQL readable data.

If "error" is specified an error will be returned and the selected table records will not be shown unless the field content matches the bindefault or cbdefault (see <field> schema element) in which case the value will be replaced with a SQL null value.

If "null" is specified, the values that cannot be converted will be replaced with SQL null values.

If "strict" is specified, an error will be returned and the selected table records will not be shown.

name

A string value representing the field name. This will be the field name used by the SQL engine.

size

The number of bytes composing the field.

type

A string defining the data type containing. Please refer to the type mapping table.

scale{numeric attributes}

Used only by numerical field types, specifies the scale of the numeric values.

digits{numeric attributes}

Used only by numerical field types, specifies the number of digits of the numeric values.

dbtype{optional attributes}

Used to force a field to specific SQL types. Current allowed values are:

"date" mapping into DATE or DATETIME depending on format attribute.

"boolean" mapping into BIT. At least one of cbtrue or cbfalse attributes must be specified.

"clob" mapping into LONG VARCHAR. Size should be set to 0. Only one field of this type is allowed, and it must be the last field of the record. This requires another field t to be available with the actual byte value of the "blob" column and is specified with an "sizefield" XML attribute.

"blob" mapping into LONG VARBINARY. Size should be set to 0. Only one field of this type is allowed, and it must be the last field of the record. This requires another field t to be available with the actual byte value of the "blob" column and is specified with an "sizefield" XML attribute.

format{date attributes}

Only for date types. Used to describe how the date will be written into the data file.

COBOL Only: Use the date-format-string used in $XFD.

julianBase{date attributes|

julianBase='integer-of-date' ('integer-of-date' must be lowercase!) to indicate values stored in the record result from an integer-of-date function.

hidden{optional attributes}

Normally used for the filler fields. Instructs the SQL engine to hide the current field.

bindefault{optional attributes}

bindefault is only used if defaultvalue and cbdefault are not specified.

bindefault specifies the binary value to write to disk when writing a record with null values from SQL.

This option provides more flexibility than cbdefault because it is binary instead of a string.

This value can either be specified as a hexadecimal string filling the entire buffer (e.g., bindefault="\x00\x01") or it can be one of the following predefined values:

low-value - Fills the field with COBOL LOW-VALUE.

high-value - Fills the field with COBOL HIGH-VALUE.

all-spaces - Fills the field with space characters.

For example, to fill the field with the COBOL LOW-VALUE: bindefault="low-value"

When using the hexadecimal string it is possible to specify multiple occurrences of the same byte using \x[#of occurrences]00

For example, for 12 occurrences of \x20 (space):  bindefault="\x[12]20"

When OnConvertError (see below) is set to “error”, the error is propagated to SQ. The query fails unless the field content matches the bindefault, in which case the value will be exposed in SQL as NULL.

COBOL Only: For more details, see SQL/COBOL Data Conversion.

cbdefault{optional attributes}

cbdefault overrides bindefault. It is overridden by defaultvalue (use only one of these three).

cbdefault specifies the value to write to disk when writing a record with NULL values from SQL.

This value is the logical value to assign to the field (as a programmer would write it in a COBOL program). It is always specified as a string in the XDD file (use bindefault if a binary value is required). The SQL engine converts it into the proper representation for the field type before using it. For example, in the case of a PIC 9(4) COMP‑6, which indicates a 4-digit number stored in 2 bytes as an unsigned packed, a cbdefault="15" will be interpreted as a COMP‑6 representing the number 15 (\x00\x15 in hex).

When OnConvertError (see below) is set to “error”, the error is propagated to SQ. The query fails unless the field content matches the cbdefault, in which case the value will be exposed in SQL as NULL.

COBOL Only: For more details see SQL/COBOL Data Conversion.

defaultvalue{optional attributes}

If specified, takes precedence over bindefault and cbdefault.

Specifies the default value for new fields. Can be specified in one of the following formats:

• Hexadecimal string: value is prefixed with ‘\h'. For example: <field defaultvalue="\hA0B1C3D4"/>
• Hexadecimal elements: each value element is prefixed with ‘\x'. For example: <field defaultvalue="\xA0\xB1\xC3\xD4"/>. Multiple occurrences of the same element can be specified within sqare brackets. For example, 12 occurrences of space character: <field default="\x20[12]"/>
• Base64 string: value is prefixed with ‘\6’. For example: <field defaultvalue="MDAwMA=="/>
• COBOL Figurative Constant Values: accepted values are: “LOW-VALUE”, “LOW-VALUES”, “HIGH-VALUE”, “HIGH-VALUES”, “SPACE”, “SPACES”, “ALL-SPACES”. For example: <field defaultvalue="LOW-VALUE"/>
• Character string: value is not prefixed. For example: <field defaultvalue="0"/>

NOTE: To specify a character string that starts with backslash, prefix it with another backslash: <field defaultvalue="\\000"/>

onConvertError{optional attributes}

Specifies the action to take when this field value cannot be converted into SQL readable data.

If "error" is specified an error will be returned and the selected table records will not be shown unless the field content matches the bindefault or cbdefault in which case the value will be replaced with a SQL null value.

If "null" is specified the values that cannot be converted will be replaced with SQL null values.

If "strict" is specified an error will be returned and the selected table records will not be shown.

If "value:?" (where ? is the wanted value in SQL) is specified the value that cannot be converted will be replaced with the specified value

onSignError{optional attributes}

Used to force the sign of the field value in case it cannot be converted from the data on disk. The admitted values are:

"+" Forces to positive sign

"-" Forces to negative sign

cbtrue{optional attributes}

The value used in a Btrieve program that matches the BIT value 1 in SQL.

cbfalse{optional attributes}

The value used in a Btrieve program that matches the BIT value 1 in SQL.

julianBase{date}

Only for date types. Used to set the base date for Julian dates. The Julian date base must be specified using the YYYYMMDD format. It defaults to 17000301 (March 1st, 1700).

sizefield

Required with dbtype "blob and "clob" to define a field that contains the actual length of the LONG portion.