Viewing implicit SQL statements

EGL creates implicit SQL statements whenever you use an EGL data-access statement (such as add or get) that specifies an SQL record variable as its target. This feature enables you to write functions that access a relational database even if you do not know any SQL at all. It also enables you to generate default SQL code that you can customize.

EGL can also display a default SELECT statement based on an SQL Record definition.

The default SELECT statement

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.

Implicit SQL statements

The opposite of an implicit SQL statement is an embedded SQL statement. Here you include explicit SQL code as part of an EGL I/O statement that is introduced by a #sql directive. For details of the #sql syntax, see #sql directive.

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.

Feedback