You
declare a record part of type sqlRecord in an EGL source 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 =
#sqlCondition{ // no space between #sqlCondition and the brace
myHostVar02 = 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
- FROM
- GROUP
- HAVING
- INSERT
- ORDER
- SELECT
- SETâ„¢
- UPDATE
- UNION
- 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 that 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 = #sqlCondition { 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 field.
If you exclude this value, the part is a record part; if you include this
value, the part is a fixed-record part.
- structureFieldName
- Name of a structure field. For rules, see Naming conventions.
- primitiveType
- The primitive type assigned to the structure field.
- length
- The structure field'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 present with the data at run time.
- 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 field properties are particularly important.