Documentation

The SQLRecord stereotype (see Stereotypes) specializes a Record part for use with a relational database. The stereotyping provides unique properties at the record level and field level, and it also triggers specific behavior when you use a variable of this type as the target of an EGL data access statement like get or replace.

EGL can automatically create SQLRecord parts based on information from your database; for more information, see "Creating a data access application" in the EGL Programmer's Guide.
Each custom record type you base on the SQLRecord type has the following optional properties:

tableNames
A two-dimensional array where each element identifies an SQL table that you want to associate with the record definition. You can optionally specify a table label (SQL alias) for that table (see "Table labels" in this topic). If you do not specify any table names, the property defaults to the name of the SQL record. The code in the following example tells EGL to assume you are using the database table named "Customer" whenever you use a CustomerRecord variable in a data access statement:

Record CustomerRecord type SQLRecord
{ tableNames = [ }
...
end

defaultSelectCondition
Specifies conditions that become part of the WHERE clause in default SQL statements. The WHERE clause is meaningful when an SQL record is used in an EGL execute, open or get statement.

In most cases, the SQL default select condition supplements a second condition, which is based on an association between the key-field values in the SQL record and the key columns of the SQL table.

Specify the conditions using the #sqlConditions directive; for more information, see "#sqlConditions directive."

tableNameVariables
You can specify one or more variables whose content at run time determines what database tables to access, as in the following example:

myTable STRING;
Record CustomerRecord type SQLRecord
{ tableNameVariables = [ }
...
end

myCustomer CustomerRecord;

function main()
myTable = requestTableName();
get myCustomer;
displayCustomer(myCustomer);
end

The EGL statement get myCustomer reads data from the database table that is named by the variable myTable. By using the myTable variable, you can have several tables with similar columns. The logic decides which table you want to access, and you do not need to write new EGL code for each table.
Note that tableNameVariables takes a two dimensional array. This allows you to specify a table label in addition to the variable that holds the table name (see "Table labels" in this topic):

{ tableNameVariables = [,
}
keyItems
This array identifies one or more fields in the record that make up the key, used to match corresponding fields in the database table. You must use an unqualified reference to specify each of these fields; for example, use myItem rather than myRecord.myItem. (In an EGL statement, however, you can reference a key field in the same was as any other field.) You can override these key values by specifying the usingKeys keyword with a get or open statement.

Table labels
Each element in a tableNames or tableNameVariables array can include a two-character table label. By convention the label is usually the letter "T", followed by a single digit. You can use this label, which SQL calls an alias, to distinguish between two columns with the same name when you use a SQL JOIN statement to combine tables. The following example shows a tableNames array:

package com.CompanyB.CustomerPackage;

Record CustomerRecord type SQLRecord
{ tableNames=[, keyItems= }
customerNumber INT {column = "customer_number"};
customerName STRING {column = "customer_name"};
customerBalance DECIMAL(9,2) {column = "customer_balance"};
end

Record OrderRecord type SQLRecord
{ tableNames=[, keyItems= }
orderNumber INT {column = "order_number"};
customerNumber INT {column = "customer_number"};
orderTotal DECIMAL(9,2) {column = "order_total"};
end

program CustomerTest type BasicProgram
myCustomer CustomerRecord;
myOrder OrderRecord;

function main()
myCustomer.customerNumber = 1001;
get myCustomer with
#sql{
select
customer_name, order_total
from Customer T1, Orders T2
join Orders on T1.customer_number = T2.customer_number
where
T1.customer_number = :myCustomer.customerNumber
}
into myCustomer.customerName, myCustomer.customerBalance;
end

end

The customerNumber field is a primary key in the Customer table and a foreign key in the Order table. However, after you join the two tables, you must use the T1 or T2 designation to make clear which customerNumber the code refers to.