Viewing implicit SQL statements

Right-click within the first line of an SQL Record definition and select SQL Record. A second menu offers you the following choices:

Retrieve SQL

If you are in the process of defining the record, this option asks EGL to construct the record definition for you, based on fields in the database table. See Retrieving SQL table data.

View Default Select

This option pops up a window that contains the SQL SELECT statement that returns all information in the current record. You can copy the contents of this window by highlighting it and pressing Ctrl+C.

Validate Default Select

This option compares the information in the SELECT statement to the structure of the referenced SQL database and makes sure that such a query would work correctly.

To deal with the transformation of implicit SQL code to embedded SQL code, right-click anywhere in an EGL I/O statement that references a record variable based on a Record part with the SQLRecord stereotype. Choose SQL Statement from the menu. The examples in this section all use the following Record part:

record CustomerRecord type SQLRecord  
  {tableNames = [["ADMINISTRATOR.CUSTOMER", "L1"]], 
   keyItems = [customerNumber]}
  
  customerNumber STRING     {column="C_NUMBER", maxLen=6};
  customerName STRING       {column="C_NAME", isSQLNullable=yes, maxLen=25};
  customerAddr1 STRING      {column="C_ADDR1", isSQLNullable=yes, maxLen=25};
  customerAddr2 STRING      {column="C_ADDR2", isSQLNullable=yes, maxLen=25};
  customerAddr3 STRING      {column="C_ADDR3", isSQLNullable=yes, maxLen=25};
  customerBalance MONEY     {column="C_BALANCE", isSQLNullable=yes};
  
end

The following options are available from the SQL Statement menu:

Add

This option converts implicit SQL code to embedded SQL code and adds it to your program. The Add option converts the simple I/O statement get myCustomer to the following:

get myCustomer with #sql{
    select
      C_NUMBER, C_NAME, C_ADDR1, C_ADDR2, C_ADDR3, C_BALANCE
    from ADMINISTRATOR.CUSTOMER L1
    where
      C_NUMBER = :myCustomer.customerNumber
  };

Add with Into

This option functions the same as Add, but includes an EGL into clause for the field names in the EGL record variable. This is useful if you want to update only some of the fields; you can remove the field names you do not want to update from the into and select clauses:

get myCustomer
  into myCustomer.customerNumber, myCustomer.customerName, 
    myCustomer.customerAddr1, myCustomer.customerAddr2, 
    myCustomer.customerAddr3, myCustomer.customerBalance with
  #sql{
    select
      C_NUMBER, C_NAME, C_ADDR1, C_ADDR2, C_ADDR3, C_BALANCE
    from ADMINISTRATOR.CUSTOMER L1
    where
      C_NUMBER = :myCustomer.customerNumber
  };

View

This option displays the implicit SQL code without adding to the code. You can, however, highlight the code in the pop-up display and copy it by pressing Ctrl+C. From the View dialog you also have the option to Add, Add with Into, Reset, and Validate the SQL statement.

Validate

This option checks to see whether the implicit SQL code is well-formed and will work correctly.

Remove

This option removes the embedded SQL code and returns you to your original I/O statement.

Reset

If you have edited the embedded code that EGL added to your program, this will undo all of your edits and restore the original embedded code.