SQL record part in EGL source format

You declare a record part of type sqlRecord in an EGL file, which is described in EGL source format. For an overview of how EGL interacts with relational databases, see SQL support.

An example of a SQL record part is as follows:

  Record mySQLRecordPart type sqlRecord
    { 
      tableNames = (mySQLTable T1),
      keyItems = myHostVar01,
      defaultSelectCondition = 
        #sql{ // no space between #sql and the brace
          hostVar02 = 4 -- start each SQL comment 
                        -- with a double hypen
            }
     }

    // The structure of an SQL record has no hierarchy
    10 myHostVar01 myDataItemPart01 
       {
         column = column01,
         isNullable = no,
         isReadOnly = no
       };
    10 myHostVar02 myDataItemPart02
       {
         column = column02,
         isNullable = yes,
         isReadOnly = no
       };
     end

The syntax diagram for an SQL record part is as follows:

Record recordPartName sqlRecord

Identifies the part as a record part of type sqlRecord and specifies the name. For rules, see naming conventions.

tableNames = (name label, ...., name label)

Lists the table or tables that are accessed by the SQL record. If you specify a label for a given table name, the label is included in the default SQL statements that are associated with the record.

You may include a double quote mark (") in a table name by preceding the quote mark with the escape character (\). That convention is necessary, for example, when a table name is one of these SQL reserved words:

• CALL

• COLUMNS

• FROM

• GROUP

• HAVING

• INSERT

• ORDER

• SELECT

• SET

• UPDATE

• VALUES

• WHERE

Each of those names must be embedded in a doubled pair of quote marks. If the only table name is SELECT, for example, the tableNames clause is as follows:

  tableNames=("\"SELECT\"")

(A similar situation applies when one of those SQL reserved words is used as a column name.)

tableNameVariables = (varName label, ...., varName label)

Lists one or more table-name variables, each of which contains the name of a table that is accessed by the SQL record. The name of a table is determined only at run time.

The variable may be qualified by a library name and may be subscripted.

If you specify a label for a given table-name variable, the label is included in the default SQL statements that are associated with the record.

You may use table-name variables alone or with table names; but the use of any table-name variable ensures that the characteristics of your SQL statement will be determined only at run time.

You may include a double quote mark (") in a table-name variable by preceding the quote mark with the escape character (\).

keyItems = = (item, ...., item)

Indicates whether the column associated with a given record item is part of the key in the database table. If the database table has a composite key, the order of the record items that are defined as keys must match the order of the columns that are keys in the database table.

defaultSelectCondition = #sql { sqlCondition }

Defines part of the search criterion in the WHERE clause of an implicit SQL statement. The value of defaultSelectCondition does not include the SQL keyword WHERE.

EGL provides an implicit SQL statement with a WHERE clause when you code one of these EGL statements:

• get

• open

• execute (only when you request an implicit SQL DELETE or UPDATE statement)

The implicit SQL statements are not stored in the EGL source code. For an overview of those statements, see SQL support.

level

Integer that indicates the hierarchical position of a structure item.

structureItemName

Name of a structure item. For rules, see naming conventions.

primitiveType

The primitive type assigned to the structure item.

length

The structure item's length, which is an integer. The value of a memory area that is based on the structure item includes the specified number of characters or digits.

decimals

For a numeric type (BIN, DECIMAL, NUM, NUMC, or PACF), you may specify decimals, which is an integer that represents the number of places after the decimal point. The maximum number of decimal positions is the smaller of two numbers: 18 or the number of digits declared as length. The decimal point is not stored with the data.

"dateTimeMask"

For items of type INTERVAL or TIMESTAMP, you may specify "dateTimeMask", which assigns a meaning (such as "year digit") to a given position in the item value. The mask is not stored with the data.

dataItemPartName

Specifies the name of a dataItem part that acts as a model of format for the structure item being declared. For details, see typeDef.

embed sqlRecordPartName

Specifies the name of a record part of type sqlRecord and embeds the structure of that record part into the current record. The embedded structure does not add a level of hierarchy to the current record. For details, see typeDef.

property

An item property, as described in Overview of EGL properties and overrides. In an SQL record, the SQL item properties are particularly important.

Related concepts
EGL projects, packages, and files
Overview of EGL properties and overrides
Parts
References to parts
Record parts
SQL support
Typedef

Related tasks
Syntax diagram

Related reference
Arrays

DataItem part in EGL source format
EGL source format
Function part in EGL source format
Indexed record part in EGL source format
MQ record part in EGL source format
Naming conventions
Primitive types
Program part in EGL source format
References to variables and constants
Relative record part in EGL source format
Serial record part in EGL source format
SQL item properties