SQL data

SQL (pronounced as three separate letters) is a language that communicates with a relational database management system (DBMS), which in turn controls a database that maintains business information in interconnected tables.

EGL provides two ways to deal with SQL data:

If you are performing common and straightforward tasks, use EGL statements to perform all your I/O operations (see Reading and writing records). In this case EGL creates all the actual SQL statements for you.
Use a #sql directive to include your own SQL statements in your EGL code.

With EGL, you can even combine the two styles. You can access the SQL statements that EGL generates from your EGL code and modify them (see Viewing implicit SQL statements).

Topics in this section include the following information:

Instructions for connecting EGL to your SQL database (see Creating an SQL database connection)
Shortcuts for creating SQL records (see Retrieving SQL table data)
Shortcuts for creating complete SQL applications (see Creating a data access application)

The EGL approach

The following table outlines how to use EGL to interact with a relational database.

Table 1. Best practices for EGL SQL
SQL objective	EGL approach
Simple SQL data manipulation (SELECT, UPDATE, INSERT, DELETE). Primary key controls WHERE and ORDER BY.	Use EGL keywords (get, replace, add, delete) and let EGL generate implicit SQL.
Simple SQL data manipulation with reusable custom WHERE clause.	Place the custom WHERE clause in the defaultSelectCondition property.
SQL SELECT statements with custom WHERE clause.	Use explicit SQL through the #sql directive.
SQL table JOIN statement.	Use the Retrieve SQL feature in the workbench, then create use the defaultSelectCondition property to correctly join the tables on primary and foreign keys: `defaultSelectCondition = #sqlCondition{ customer.customer_ID = orders.customer_ID }`
Derived data in SELECT command (such as MAX() or AVG())	Use explicit SQL through the #sql directive, placing the derived fields inside the braces.
Derived data in SELECT command (such as MAX() or AVG())	Create a custom SQLRecord, where the column property for the individual fields specifies the derived or computed expression.
Complex or custom SQL UPDATE, INSERT, or DELETE statement.	Use EGL replace, add, or delete statements with explicit SQL (#sql directive).
Complex or custom SQL UPDATE, INSERT, or DELETE statement.	Use explicit SQL through the execute #sql statement.
SQL statements other than simple data manipulation (such as CREATE TABLE).	Use explicit SQL through the execute #sql statement.
Dynamic SQL (prepared SQL statement).	Use explicit SQL through the execute #sql statement.
Stored procedure.	Use explicit SQL such as the following: `open result_set with #sql{ CALL stored_proc ( :host_var) }`
Processing of individual rows of the result set from a SQL SELECT statement.	Use the EGL open command to open the result set, then initiate a loop with one of the following statements: `forEach (from result_set)` `while (sqlLib.sqlData.sqlcode == 0)`
Programmatic paging for online searches.	Use the Data Access Application wizard.
Addition of data to SQL table.	Use the Table Editor in the workbench Data Perspective.
SQL statement validation	In the EGL editor, select Validate SQL from the context menu.
SQL statement validation	Run interactive SQL using the SQL Editor in the workbench Data Perspective.

Result-set processing

To use a common way to update a series of rows, follow these steps:

Declare and open a cursor by running an EGL open statement with the forUpdate option; that option causes the selected rows to be locked for subsequent update or deletion.
Fetch a row by running an EGL get next statement.
In a forEach loop, take the following actions:
1. Retrieve data from the result set into the host variables. A host variable is a variable in an SQL statement with the same name as a variable in the host language (in this case, EGL hosts the SQL statements), with an additional initial colon character (:).
2. Update or delete the row by running an EGL replace or delete statement.
3. Fetch another row by running an EGL get next statement.
Commit changes by running the EGL commit() function.

The statements that open the cursor and that act on the rows of that cursor are related to each other by a result-set identifier, which must be unique across all result-set identifiers and program variables within the program. You specify that identifier in the open statement that opens the cursor, and you reference the same identifier in the forEach statement that creates the loop. You also reference the identifier in the get next, delete, and replace statements that affect an individual row and on the close statement that closes the cursor.

You can use a specialized type of EGL record, the SQLRecord, to hold the information that you read from or write to a relational database. The following example shows how to update a series of rows when you are coding the SQL yourself:

  try
    open selectEmp forUpdate for emp;
  onException(sqlx SqlException)
    myErrorHandler(sqlx);    // exits program
  end

  foreach(emp)
    emp.empname = emp.empname :: " " :: "III";
    try 
      replace emp;
    onException(sqlx SqlException)
      myErrorHandler(sqlx);   // exits program
    end
  end  // end while; cursor is closed automatically
       // when the last row in the result set is read

  sysLib.commit();

To commit changes periodically as you process an EGL open statement (regardless of whether you use SQL records), use the hold statement option, which maintains cursor position after a commit. However, if a program that is targeted for CICS® is segmented, the hold option has no effect because a converse in a segmented program ends the CICS® transaction and prevents the program from retaining any file or database position.

SQL records and their uses

After you define an SQLRecord part, you declare a variable based on that part. You can use this record variable to access a relational database as though you were accessing a file. For example, if the variable myEmpRecord is based on an SQLRecord part that references the database table EMPLOYEE, for example, you can use myEmpRecord in an EGL add statement:

  add myEmpRecord;

In this case, EGL inserts the data from myEmpRecord into EMPLOYEE. After the EGL statement runs, the record variable contains information about error conditions:

  try
    add myEmpRecord;
  onException(sqlx SqlException)
    if (myEmpRecord is unique) // if a table row had the same key
      myErrorHandler(sqlx);
    end
  end

Use a record variable such as myEmpRecord to interact with a relational database:

Define an SQLRecord part and declare the related record variable.
Write EGL statements that perform I/O using the SQL record.
Accept the default behavior of the EGL statements (which should give you what you want in most cases) or make SQL changes that are appropriate for your business logic.

Define an SQLRecord part and the related record

You define an SQLRecord part and associate each of the fields with a column in a relational table or view. EGL can do this for you automatically; see Retrieving SQL table data.

If the SQLRecord part is not a fixed record part, you can include primitive fields and other variables. You typically include the following kinds of variables:

Other SQL records. The presence of each represents a one-to-one relationship between the parent and child tables.
Arrays of SQL records. The presence of each represents a one-to-many relationship between the parent and child tables.

Only fields of a primitive type can represent a database column.

If level numbers precede the fields, the SQLRecord part is a fixed record part. The following rules apply:

The structure in each SQLRecord part must be flat (without hierarchy)
All of the fields must be primitive fields, but not of type BLOB, CLOB, or STRING
None of the record fields can be a structure-field array

After you define an SQLRecord part, you declare a record variable that is based on that part.

SQL-related EGL statements

You can create a set of EGL statements that each use the record variable as the I/O object in the statement. For each statement, EGL provides an implicit SQL statement, which is not in the source but is implied by the combination of record variable and EGL statement. In the case of an EGL add statement, for example, an implicit SQL INSERT statement places the values of the fields in the given record into the associated table column. If your record variable includes a field for which no table column was assigned, EGL forms the implicit SQL statement on the assumption that the name of the field is identical to the name of the column.

The following EGL statements correspond to the SQL statements shown:

Table 2. Implicit SQL statements
EGL statement	SQL statement
add	INSERT
delete	DELETE
get, open	SELECT
replace	UPDATE

Implicit SELECT statements

When you define an EGL statement that uses a record variable and generates either an SQL SELECT statement or a cursor declaration, EGL provides an implicit SQL SELECT statement. This statement is embedded in the cursor declaration, if any. For example, you might declare a variable based on the following SQLRecord part:

  Record Employee type sqlRecord
    { tableNames = [["EMPLOYEE"]],
      keyItems = ["empnum"] }
    empnum decimal(6,0);
    empname char(40);
  end

Next, declare a variable based on this Record:

myEmpRecord Employee;

Then, you might code a get statement:

  get myEmpRecord;

The implicit SQL SELECT statement is as follows:

  SELECT empnum, empname
  FROM   EMPLOYEE
  WHERE  empnum = :empnum

EGL also places an INTO clause into the standalone SELECT statement (if no cursor declaration is involved) or into the FETCH statement that is associated with the cursor. The INTO clause lists the host variables that receive values from the columns listed in the first clause of the SELECT statement:

  INTO :empnum, :empname

The implicit SELECT statement reads each column value into the corresponding host variable; references the tables specified in the record variable; and has a search criterion (a WHERE clause) that depends on a combination of two factors:

The value that you specified for the defaultSelectCondition record property.
A relationship (such as an equality) between two sets of values:
- Names of the columns that constitute the table keys
- Values of the host variables that constitute the record keys
EGL infers the relationship from the tableNames record property or the usingKeys clause of the EGL statement.

For details on the implicit SELECT statement, see individual keyword topics in the EGL Language Reference.

SQL records with cursors

When you use SQL records, you can relate cursor-processing statements by using the same record variable in several EGL statements, much the same way as you can by using a result-set identifier. However, any cross-statement relationship that is indicated by a result-set identifier takes precedence over a relationship indicated by the record variable; in some cases you must specify a resultSetID.

In addition, only one cursor can be open for a particular record variable. If an EGL statement opens a cursor when another cursor is open for the same record variable, the generated code automatically closes the first cursor.

SQL statement customization

Given an EGL statement that uses an SQL record variable as the I/O object, you can progress in either of two ways:

Accept the implicit SQL statement. In this case, changes to the SQLRecord part affect the SQL statements that are used at run time. For example, if you later indicate that a different field is to be used as the key of the SQL record, EGL changes the implicit SELECT statement used in any cursor declaration that is based on that SQLRecord part.
Choose instead to make the SQL statement explicit. EGL can insert the implicit SQL statements into your code for you so you can modify them. In this case, the details of that SQL statement are isolated from the SQLRecord part, and any subsequent changes to the SQLRecord part have no effect on the SQL statement that is used at run time.
If you remove an explicit SQL statement from the source, the implicit SQL statement (if any) is again available at generation time.

Example of using a record in a record

To allow a program to retrieve data for a series of employees in a department, you can create two SQLRecord parts and a function:

DataItem DeptNo { column = "deptNo" } end

Record Dept type SQLRecord
   deptNo DeptNo;
   managerID CHAR(6);
   employees Employee[];
end

Record Employee type SQLRecord
   employeeID CHAR(6);
   empDeptNo DeptNo;
end

Function getDeptEmployees(myDeptRecord Dept)
   get myDeptRecord.employees usingKeys myDeptRecord.deptNo;
end

NULL testing and setting

If you want a variable to be nullable, that is, to be able to accept a null value, declare the variable with the "?" type extension character:

Record Employee type SQLRecord
   employeeID CHAR(6);
   empDeptNo INT?;
end

To test for a null value, use a standard EGL if statement:

if (myEmpRecord.empDeptNo == null)
   ...
end

You can directly assign a null value to a variable:

myEmpRecord.empDeptNo = null;

The syntax works even if the target is not nullable. In that case, the statement has the same effect as the following statement:

set myEmpRecord.empDeptNo empty;

Compatibility

Table 3. Compatibility considerations for SQL
Platform	Issue
CICS® for z/OS®, z/OS® batch, iSeriesC	The generated code can access DB2® UDB directly
AIX®, HP-UX, iSeriesJ, Linux™, Solaris, z/OS® UNIX™ System Services, Windows™ 2000/NT/XP	JDBC provides access to DB2® UDB, Oracle, Informix®, or Microsoft™ SQL Server