EGL Reference Guide for iSeries

SQL-specific tasks

Setting preferences for SQL database connections

You use the page for SQL database connections for these reasons:

Do as follows:

  1. Click Window > Preferences
  2. When a list of preferences is displayed, expand EGL, then click SQL Database Connections.
  3. In the Connection URL field, type the URL used to connect to the database through JDBC:
  4. In the Database field, type the name of the database.
  5. In the User ID field, type the user ID for the connection.
  6. In the Password field, type the password for the user ID.
  7. In the Database vendor type field, select the database product and version that you are using for your JDBC connection.
  8. In the JDBC driver field, select the JDBC driver that you are using for your JDBC connection.
  9. In the JDBC driver class field, type the driver class for the driver you selected. For IBM DB2 APP DRIVER for Windows, the driver class is COM.ibm.db2.jdbc.app.DB2Driver; for the Oracle JDBC thin client-side driver, the driver class is oracle.jdbc.driver.OracleDriver; and for the Informix JDBC NET driver, the driver class is com.informix.jdbc.IfxDriver. For other driver classes, refer to the documentation for the driver.
  10. In the class location field, type the fully qualified filename of the *.jar or *.zip file that contains the driver class. For IBM DB2 APP DRIVER for Windows, type the fully qualified filename to the db2java.zip file; for example, d:\sqllib\java\db2java.zip. For the Oracle THIN JDBC DRIVER, type the fully qualified filename to the classes12.zip file; for example, d:\Ora81\jdbc\lib\classes12.zip. For other driver classes, refer to the documentation for the driver.
  11. In the Connection JNDI name field, specify the database used in J2EE. The value is the name to which the datasource is bound in the JNDI registry; for example, java:comp/env/jdbc/MyDB. As noted earlier, this value is assigned to the option sqlJNDIName in the build descriptor that is constructed automatically for a given EGL Web project.
  12. If you are accessing DB2 UDB and specify a value in the Secondary authentication ID field, the value is used in the SET CURRENT SQLID statement used by EGL at validation time. The value is case-sensitive.

You can clear or apply preference settings:


Related tasks
Creating a project to work with EGL
Setting EGL preferences


Related reference
sqlJNDIName

Setting preferences for SQL retrieve

At EGL declaration time, you can use the SQL retrieve feature to create an SQL record from the columns of an SQL table. For an overview, see SQL support.

To set preferences for the SQL retrieve feature, do as follows:

  1. Click Window > Preferences, then expand EGL and click SQL Retrieve
  2. Specify rules for naming each structure item that is created by the SQL retrieve feature:
    1. To specify the case of the structure item name, click one of the following radio buttons:
      • Do not change case (the default) means that the case of the structure item name is the same as the case of the related table column name
      • Change to lower case means that the structure item name is a lower-case version of the table column name
      • Change to lower case and capitalize first letter after underscore also means that the structure item name is a lower-case version of the table column name, except that a letter in the structure item name is rendered in uppercase if, in the table column name, the letter immediately follows an underscore
    2. To specify how the underscores in the table column name are reflected in the structure item name, click one of the following radio buttons:
      • Do not change underscores (the default) means that underscores in the table column name are included in the structure item name
      • Remove underscores means that underscores in the table column name are not included in the structure item name
      • Change underscores to hyphens means that underscores in the table column name are rendered as hyphens in the structure item name
  3. If you intend to retrieve data from a table that is part of an Informix system schema, clear the check box for Exclude system schemas. (In this case, "Informix" is the table owner.) In all other cases, select the check box to improve the performance of the SQL retrieve feature.

    The check box is selected by default.


Related concepts
SQL support


Related tasks
Retrieving SQL table data
Setting EGL preferences
Setting preferences for SQL database connections


Related reference
Informix and EGL

Retrieving SQL table data

EGL provides a way to create SQL record items from the definition of an SQL table, view, or join; for an overview, see SQL support.

Do as follows when you are working in the EGL or EGL Web perspective:

  1. Ensure that you have set SQL preferences as appropriate. For details, see and Setting preferences for SQL retrieve.
  2. Decide where to do the task--
  3. If you are working in the EGL source file, you can proceed in this way--
    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 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 context menu, click SQL record > Retrieve SQL.
  4. If you are working in the Outline view, right click on the entry for the SQL record and, in the context menu, click Retrieve SQL.

Note:
You cannot retrieve an SQL view that is defined with the DB2 condition WITH CHECK OPTIONS.

After you create structure items, you may want to gain a productivity benefit by creating the equivalent data item parts; see Overview on creating data item parts from an SQL record part.


Related concepts
Creating data item parts from an SQL record part (overview)
SQL support


Related tasks
Creating data item parts from an SQL record part
Setting preferences for SQL database connections
Setting preferences for SQL retrieve


Related reference
SQL item properties

Creating data item parts from an SQL record part (overview)

After you declare structure items in an SQL record part, you can use a special mechanism in the EGL editor to create data item parts that are equivalent to the structure items. The benefit is that you can more easily create a non-SQL record (usually a basic record) for transferring data to and from the related SQL record at run time.

Consider the following structure items:

  10 myHostVar01 CHAR(3);
  10 myHostVar02 BIN(9,2);

You can request that data item parts be created:

  DataItem myHostVar01 CHAR(3) end
 
  DataItem myHostVar02 BIN(9,2) end

Another effect is that the structure item declarations are rewritten:

  10 myHostVar01 myHostVar01;
  10 myHostVar02 myHostVar02;

As shown in this example, each data item part is given the same name as the related structure item and acts as a typedef for the structure item. Each data item part is also available as a typedef for other structure items.

Before you can use a structure item as the basis of a data item part, the structure item must have a name, must have valid primitive characteristics, and must not point to a typedef.


Related concepts
SQL support


Related tasks
Creating data item parts from an SQL record part


Related reference
DataItem part in EGL source format
SQL record part in EGL source format

Creating data item parts from an SQL record part

After you declare structure items in an SQL record part, you can use a special mechanism in the EGL editor to create data item parts that are equivalent to the structure items. For general information, see Overview on creating data item parts from an SQL record part.

By default, the Outline view is open in the EGL perspective. If you are working in the EGL Web perspective, open the Outline view by selecting Show View > Outline from the Window menu. Do as follows in the Outline view:

  1. For a given SQL record part, hold down Ctrl while clicking on each of the structure items of interest. To select all the structure items in a given record, click the topmost structure item, then hold down Shift while clicking on the bottommost structure item.
  2. Right-click on the selected structure items.
  3. In the context menu, click Create Data Item.

The data-item parts are written at the bottom of the EGL source file, and each structure item is changed to refer to the equivalent part.


Related concepts
Creating data item parts from an SQL record part (overview)
SQL support


Related tasks
Retrieving SQL table data


Related reference
SQL record part in EGL source format

Viewing the SQL SELECT statement for an SQL record

EGL provides an implicit SQL SELECT statement for a given SQL record part. To view the implicit SQL SELECT statement, do as follows:

  1. Open the EGL file that contains the SQL record part. If you do not have the file open, right-click on the EGL file in the Project Navigator, then select Open With > EGL Editor.
  2. Click inside the SQL record part, then right-click. A context menu displays.
  3. Select SQL Record > View Default Select.
  4. To validate the SQL SELECT statement against a database, click Validate.
Note:
Before using the validate function, DB2 UDB users must set the DEFERREDPREPARE option. You can set this option interactively in the CLP (DB2 command line processor) using the db2 update cli cfg for section COMMON using DEFERREDPREPARE 0 command. This command will put the keyword under the COMMON section. Execute the command db2 get cli cfg for section common to verify that the keyword is being picked up.


Related concepts
SQL support

Related tasks
Validating the SQL SELECT statement for an SQL record


Related reference
SQL record part in EGL source format

Validating the SQL SELECT statement for an SQL record

EGL provides an implicit SQL SELECT statement for a given SQL record part. To validate the implicit SQL SELECT statement against a database, do as follows:

  1. Open the EGL file that contains the SQL record part. If you do not have the file open, right-click on the EGL file in the Project Navigator, then select Open With > EGL Editor.
  2. Click inside the SQL record part, then right-click. A context menu displays.
  3. Select SQL Record > Validate Default Select.
Note:
Before using the validate function, DB2 UDB users must set the DEFERREDPREPARE option. You can set this option interactively in the CLP (DB2 command line processor) using the db2 update cli cfg for section COMMON using DEFERREDPREPARE 0 command. This command will put the keyword under the COMMON section. Execute the command db2 get cli cfg for section common to verify that the keyword is being picked up.


Related concepts
SQL support


Related tasks
Viewing the SQL SELECT statement for an SQL record


Related reference
SQL record part in EGL source format

Constructing an SQL PREPARE statement

Within a function, you can construct an SQL PREPARE statement based on an SQL record part and an SQL statement coded in a literal. To construct an SQL PREPARE statement, do as follows:

  1. Open an EGL file with the EGL editor. The file must contain a function and a coded SQL statement. If you do not have a file open, right-click on the EGL file in the workbench Project Navigator, then select Open With > EGL Editor.
  2. Click inside the function at the location where the SQL PREPARE statement will reside, then right-click. A context menu displays.
  3. Select Add SQL Prepare Statement.
  4. Type a name to identify the SQL PREPARE statement. For rules, see Naming conventions.
  5. If you have an SQL record variable defined, select it from the drop-down list. The corresponding SQL record part name displays. If you do not have an SQL record variable defined, you can type a name in the SQL record variable name field, then select an SQL record part name using the Browse button. You must eventually define an SQL record variable with that name in the EGL source code.
  6. Select an execution statement type from the drop-down list.
  7. If the execution statement is of type open, enter a result-set identifier.
  8. Click OK. An EGL prepare statement and the appropriate EGL open, get, or execute statement is constructed inside the function.


Related concepts
SQL support


Related tasks
Validating the SQL SELECT statement for an SQL record
Viewing the SQL SELECT statement for an SQL record


Related reference
Naming conventions
SQL record part in EGL source format

Constructing an explicit SQL statement from an implicit one

EGL provides an implicit SQL statement for each SQL-related EGL input/output (I/O) statement. To construct an explicit SQL statement from an implicit one, do as follows:

  1. Open the EGL file that contains the EGL I/O statement. If you do not have the file open, right-click on the EGL file in the Project Navigator, then select Open With > EGL Editor.
  2. Click on the EGL I/O statement, then right-click. A context menu displays.
  3. To construct an explicit SQL statement without an INTO clause, select SQL Statement > Add. To construct an explicit SQL statement with an INTO clause, select SQL Statement > Add with Into. The implicit SQL statement is appended to the EGL I/O statement making it an explicit SQL statement.
    Note:
    The INTO clause is only valid with open, get, and get next statements.


Related concepts
SQL support


Related tasks
Removing an SQL statement from an SQL-related EGL statement
Resetting an explicit SQL statement
Validating an implicit or explicit SQL statement
Viewing the implicit SQL for an SQL-related EGL statement

Viewing the implicit SQL for an SQL-related EGL statement

EGL provides an implicit SQL statement for each SQL-related EGL input/output (I/O) statement. To view the implicit SQL for an EGL I/O statement, do as follows:

  1. Open the EGL file that contains the EGL I/O statement. If you do not have the file open, right-click on the EGL file in the Project Navigator, then select Open With > EGL Editor.
  2. Click on the EGL I/O statement, then right-click. A context menu displays.
  3. Select SQL Statement > View.


Related concepts
SQL support


Related tasks
Constructing an explicit SQL statement from an implicit one
Removing an SQL statement from an SQL-related EGL statement
Resetting an explicit SQL statement
Validating an implicit or explicit SQL statement

Validating an implicit or explicit SQL statement

To validate an implicit or explicit SQL statement against a database, do as follows:

  1. Open the EGL file that contains the SQL-related EGL statement or explicit SQL statement. If you do not have the file open, right-click on the EGL file in the Project Navigator, then select Open With > EGL Editor.
  2. Click the EGL statement or SQL statement, then right-click. A context menu displays.
  3. Select SQL Statement > Validate.
Note:
Before using the validate function, DB2 UDB users must set the DEFERREDPREPARE option. You can set this option interactively in the CLP (DB2 command line processor) using the db2 update cli cfg for section COMMON using DEFERREDPREPARE 0 command. This command will put the keyword under the COMMON section. Execute the command db2 get cli cfg for section common to verify that the keyword is being picked up.


Related concepts
SQL support


Related tasks
Constructing an explicit SQL statement from an implicit one
Removing an SQL statement from an SQL-related EGL statement
Resetting an explicit SQL statement
Viewing the implicit SQL for an SQL-related EGL statement

Removing an SQL statement from an SQL-related EGL statement

EGL provides an implicit SQL statement for each SQL-related EGL input/output (I/O) statement. An implicit SQL statement can be appended to an EGL I/O statement making it an explicit SQL statement (see Constructing an explicit SQL statement from an implicit one). To remove the appended SQL statement, do as follows:

  1. Open the EGL file that contains the explicit SQL statement. If you do not have the file open, right-click on the EGL file in the Project Navigator, then select Open With > EGL Editor.
  2. Click on the explicit SQL statement, then right-click. A context menu displays.
  3. Select SQL Statement > Remove. The EGL I/O statement remains.


Related concepts
SQL support


Related tasks
Constructing an explicit SQL statement from an implicit one
Resetting an explicit SQL statement
Validating an implicit or explicit SQL statement
Viewing the implicit SQL for an SQL-related EGL statement

Resetting an explicit SQL statement

EGL provides an implicit SQL statement for each SQL-related EGL input/output (I/O) statement. An implicit SQL statement can be appended to an EGL I/O statement making it an explicit SQL statement. If you change the explicit SQL statement, do as follows to return to an SQL statement based on the implicit SQL:

  1. Open the EGL file that contains the explicit SQL statement. If you do not have the file open, right-click on the EGL file in the Project Navigator, then select Open With > EGL Editor.
  2. Click on the explicit SQL statement, then right-click. A context menu displays.
  3. Select SQL Statement > Reset.


Related concepts
SQL support


Related tasks
Constructing an explicit SQL statement from an implicit one
Removing an SQL statement from an SQL-related EGL statement
Validating an implicit or explicit SQL statement
Viewing the implicit SQL for an SQL-related EGL statement

Resolving a reference to display an implicit SQL statement

Consider what happens when you specify the following EGL statement:

  open myRecord;

When the EGL editor tries to create a default SQL statement, the editor attempts to find a variable named myRecord and to identify the SQL record part on which that variable is based. If the variable is unavailable at development time or if the variable is undeclared, the editor attempts to use an SQL record part named myRecord as the basis for the default SQL statement. The editor assumes that you intend to create a variable whose name is the name of the SQL record part.

If you wish to store an SQL-related function in a file that does not include the variable myRecord, you can do as follows:

  1. In the program part, declare the global variable
  2. Create the function as a nested function in the program part
  3. Create the default SQL statement and modify it as appropriate; then, save the file
  4. Move the function to the other file

After the function is moved from the program part, the record name cannot be resolved at development time, and the editor cannot display any default SQL statements that are based on that record.


Related concepts
SQL support


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]