Retrieving SQL table data

With EGL, you can create SQL record fields from the definition of an SQL table, view, or join.

To create SQL record fields from the definition of an SQL table:
  1. Ensure that you have set SQL preferences as appropriate. For details, see Setting preferences for SQL retrieve.
  2. Set the default database connection, which is the connection that EGL will use to retrieve the table data:
    1. Click Window > Preferences.
    2. Expand EGL and click SQL Database Connections.
    3. Select your database connection from the Connection list or create a new database connection. The connection selected in the Connection list is the default database connection.
  3. Decide where to do the task:
    • In an EGL source file, as you develop each SQL record; or
    • In the Outline view, as may be easier when you already have SQL records.
  4. If you are working in the EGL source file, proceed in this way. If you are working in the Outline view, skip to the next step.
    1. If you do not have the SQL record, create it:
      1. Type R, press Ctrl+Space, and in the content-assist list, select one of the SQL table entries (usually SQL record with table names).
      2. Type the name of the SQL record, press Tab, and then type a table name, or a comma-delimited list of tables, or the alias of a view.
      You also can create an SQL record by typing the minimal content, as appropriate if the name of the record is the same as the name of the table, as in this example: 
        Record myTable type sqlRecord
  end
    2. Right-click anywhere in the record.
    3. In the menu, click SQL record > Retrieve SQL.
  5. If you are working in the Outline view, right click the entry for the SQL record and, in the pop-up menu, click Retrieve SQL.
Note:
You cannot retrieve an SQL view that is defined with the DB2® condition WITH CHECK OPTIONS.
After you create record fields, you may want to gain a productivity benefit by creating the equivalent DataItem parts for the fields in the record:
  1. Open the record in the EGL editor and then open the Outline view. The Outline view shows a hierarchical view of the parts and other EGL code on the page.
  2. In the Outline view, right-click the field and then click Create DataItem Part. EGL creates a dataItem part based on the field in the record and uses that dataItem as the field type.

Compatibility of SQL data types and EGL primitive types

An EGL host variable (see Host variables) and the corresponding SQL table column are compatible in any of the following situations:
  • The SQL column is any form of character data, and the EGL host variable is of the type CHAR with a length less than or equal to the length of the SQL column.
  • The SQL column is any form of DBCHAR data, and the EGL host variable is of the type DBCHAR with a length less than or equal to the length of the SQL column.
  • The SQL column is any form of number and the EGL host variable is of one of these types:
    • BIN(4,0)/SMALLINT
    • BIN(9,0)/INT
    • BIN(18,0)/BIGINT
    • DECIMAL, with a maximum length of 18 digits, including decimal places. The number of digits for a DECIMAL variable should be the same for the EGL host variable and for the column.
  • The SQL column is of any data type, the EGL host variable is of type HEX, and the column and host variable contain the same number of bytes. No data conversion occurs during data transfer.

    EGL host variables of type HEX support access to any SQL column of a data type that does not correspond to an EGL primitive type.

If character data is read from an SQL table column into a shorter host variable, content is truncated on the right. To test for truncation, use the reserved word trunc in an EGL if statement.

Default mapping

EGL uses a default mapping when it creates records with the Retrieve SQL feature. The following table shows the default mapping.

Table 1. EGL variable characteristics
SQL data type EGL variable characteristics
Primitive type Digits/characters Number of bytes
BIGINT BIGINT n/a 8
BIT SMALLINT n/a 2
BLOB BLOB n/a n/a
BOOLEAN BOOLEAN n/a 1
CHAR CHAR 1-32767 1-32767
CLOB CLOB n/a n/a
DATE DATE n/a 8
DECIMAL DECIMAL 1-18 1-10
DOUBLE FLOAT n/a 8
FLOAT FLOAT n/a 8
GRAPHIC DBCHAR 1-16383 2-32766
INTEGER INT n/a 4
LONG VARBINARY HEX 65534 32767
LONG VARCHAR CHAR >4000 >4000
LONG VARGRAPHIC DBCHAR >2000 >4000
NUMERIC DECIMAL 1-18 1-10
REAL SMALLFLOAT n/a 4
SMALLINT SMALLINT n/a 2
TIME TIME n/a 6
TIMESTAMP TIMESTAMP n/a 14
VARBINARY HEX 2-65534 1-32767
VARCHAR CHAR 4000 4000
VARGRAPHIC DBCHAR 2000 4000

The definition of an SQL table column of the type VARCHAR or VARGRAPHIC includes a maximum length, and the retrieve command uses that maximum to assign a length to the EGL host variable. The definition of an SQL table column of the type LONG VARCHAR or VARGRAPHIC, however, does not include a maximum length, and the retrieve command uses the SQL-data-type maximum to assign a length.

Compatibility

Table 2. Compatibility considerations for SQL data
Platform Issue
Java™ generation If numeric data is read from an SQL table column into a shorter host variable, EGL treats the situation as it would an overflow on an assignment statement.
COBOL generation If numeric data is read from an SQL table column into a shorter host variable, leading zeros are truncated on the left. If the number still does not fit into the host variable, fractional parts of the number (in decimal) are deleted on the right, with no indication of error. If the number still does not fit, a negative SQL code is returned to indicate an overflow condition.