SQLRecord stereotype

The SQLRecord stereotype specializes a Record part for use with a relational database.

The stereotyping (see Stereotypes) provides unique properties at the record level and field level. When you use a variable of this type as the target of an EGL data access statement such as get or replace, it triggers specific behavior.

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 that you base on the SQLRecord stereotype 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 = [["Customer"]] }
  ...
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 #sqlCondition directive; for more information, see #sqlCondition 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 = [["myTable"]] }
  ...
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 determines which table you want to access; you do not need to write new EGL code for each table.

Note that tableNameVariables takes a two dimensional array. Use the second dimension to specify a table label in addition to the variable that holds the table name (see "Table labels" in this topic)):

{ tableNameVariables = [["myTable", "T1"], 
                        ["myOtherTable", "T2"]] }

keyItems

This array identifies one or more fields in the record that make up the key. The key is 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. Remember that in an EGL statement, you can reference a key field in the same way as any other field. You can override these key values by specifying the usingKeys keyword with a get or open statement.

fieldsMatchColumns

Set this property to YES when the type, length, number of decimals, and nullability of every field exactly match those characteristics for the corresponding database columns. Use this property to bypass the consistency checks that EGL programs generated for Java™ perform when retrieving data from a database, thus speeding up the process. The default is NO.

Table labels

Each element in a tableNames or tableNameVariables array can include a table label. DB2® Version 7 supports table labels up to 18 characters long; DB2® Version 8 has no limits on table label length. 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=[["Customer", "T1"]], keyItems=["customerNumber"] }
	customerNumber INT {column = "customer_number"};
	customerName STRING {column = "customer_name"};
	customerBalance DECIMAL(9,2) {column = "customer_balance"};
end

Record OrderRecord type SQLRecord 
{ tableNames=[["Orders", "T2"]], keyItems=["orderNumber"] }
	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 specify which customerNumber the code refers to.