Lesson 2: Basic SQL operations in EGL

If you went through the tutorial Introducing EGL (a quick-start guide), you learned about the EGL parts necessary to access a relational database and the commands with which you can read and write data in a database. This lesson covers these concepts in more detail.

When you ran the data access application wizard, either in the previous lesson or in a past tutorial, EGL created data parts and logic parts based on the information in the database.

The most prominent data parts created are the SQLRecord parts. The fields in these Record parts relate to columns in the database. When you create a variable based on this record you can use that variable to represent a new or existing row in the database. Though each SQLRecord created by the data access application wizard matches only the columns in a single table, later in this tutorial you will learn how to merge data from more than one table into a single record (called a table join in SQL).

For example, you will use the Customer SQLRecord throughout this tutorial; you can find this record in the file EGLSource/eglderbyr7/data/customer.egl:

record Customer type sqlRecord { 
  tablenames=[["EGL.CUSTOMER"]],
  keyItems=[CustomerId]
  } 

  CustomerId CustomerId {column="EGL.CUSTOMER.CUSTOMER_ID"};
  FirstName FirstName {column="EGL.CUSTOMER.FIRST_NAME", 
    sqlVariableLen=yes, maxLen=30, isSqlNullable=yes};
  LastName LastName {column="EGL.CUSTOMER.LAST_NAME", 
    sqlVariableLen=yes, maxLen=30, isSqlNullable=yes};
...
  Directions Directions {column="EGL.CUSTOMER.DIRECTIONS", 
    sqlVariableLen=yes, maxLen=255, isSqlNullable=yes};
end

The code tablenames=[["EGL.CUSTOMER"]] specifies the table in the database that this record relates to, and the column properties on each of the fields specify the column in the table that the field relates to. The code keyItems=[CustomerId] indicates that the CustomerId field, and by connection, the EGL.CUSTOMER.CUSTOMER_ID column of the database, is the primary key for this table.

The data access application wizard also created several libraries that perform CRUD (create, read, update, and delete) operations on records and arrays of records. These libraries are more sophisticated ways of performing the CRUD operations directly with EGL commands. In this tutorial, you will use the EGL commands directly, but of course, it's convenient to separate logic into libraries for reuse. The four EGL CRUD commands are:

Table 1. EGL CRUD commands and their SQL equivalents
EGL command	EGL example	SQL command
add	add myRecord;	INSERT
get	get myRecord;	SELECT
replace	replace myRecord;	UPDATE
delete	delete myRecord;	DELETE

When you use one of these commands, EGL generates a default SQL statement from that command. For example, take the following add statement:

add myCustomer;

If you place the cursor on the myCustomer record, right-click, and then click SQL Statement > Add, the EGL editor expands the add statement to show the SQL code, making the SQL code explicit:

The context menu showing how to make a SQL statement explicit

add myCustomer with
  #sql{
    insert into EGL.CUSTOMER
      (EGL.CUSTOMER.CUSTOMER_ID, EGL.CUSTOMER.FIRST_NAME, 
      EGL.CUSTOMER.LAST_NAME, EGL.CUSTOMER.PASSWORD, 
      EGL.CUSTOMER.PHONE, EGL.CUSTOMER.EMAIL_ADDRESS, 
      EGL.CUSTOMER.STREET, EGL.CUSTOMER.APARTMENT, 
      EGL.CUSTOMER.CITY, EGL.CUSTOMER."STATE", 
      EGL.CUSTOMER.POSTALCODE, EGL.CUSTOMER.DIRECTIONS)
    values
      (:myCustomer.CustomerId, :myCustomer.FirstName, 
      :myCustomer.LastName, :myCustomer.Password, 
      :myCustomer.Phone, :myCustomer.EmailAddress, 
      :myCustomer.Street, :myCustomer.Apartment, 
      :myCustomer.City, :myCustomer.State, 
      :myCustomer.Postalcode, :myCustomer.Directions)
  };

Nothing of substance has changed; EGL has merely exposed the SQL code that it generates from the add statement. Now that the code is explicit, you can edit the default SQL generated from the EGL command.

The VALUES clause of the SQL INSERT statement lists the values to insert into the new database row, in this case, fields in the myCustomer record. Each value inside the #sql block is preceded by a colon (:), indicating that the value is not an SQL value but an EGL value. These EGL values used in the explicit SQL code are referred to as host variables.

As another example of host variables at work, here is a function that uses a get statement to remove a series of records from a database:

function getCustFromState(customerState CHAR(2) in, 
  customers Customer[] out)

  get customers with
    #sql{
      select *
      from EGL.CUSTOMER
      where EGL.CUSTOMER."STATE" like :customerState
    };

end

This function receives a two-byte CHAR parameter and uses that parameter as a host variable to retrieve the records that have a STATE field matching those two characters. Careful use of host variables is critical to making EGL work with explicit SQL code.

Reviewing the CRUD operations

In this section, you use get, add, replace, and delete to perform basic database operations with EGL. This section is optional; you can skip it if you are comfortable with the four basic EGL data access statements and their SQL equivalents.

Create a new EGL program named CRUDTest.egl:
1. In the Project Explorer view, right-click the EGLSQL project and then click New > Program The New EGL Program part window opens.
2. In the Package field, type the name programs.
3. In the EGL source file name field, type the name CRUDTest.
4. Click Finish.
The new EGL program is created and opens in the editor.

Replace the default EGL code with the following code:

package programs;

import eglderbyr7.data.*;

program CRUDTest type BasicProgram {}
  
  function main()
    
    try
      // Print the starting records in the database.
      SysLib.writeStdout("Contents of database before any SQL operations:");
      printAllCustomers();
      
    // What to do if an error happens.
    onException (exception SQLException)
      handleDBException(exception);
    end
    
  end
  
  // This function prints out the contents of the CUSTOMER
  // table in the database.
  function printAllCustomers()
    
    allCustomers Customer[0];

    get allCustomers;
    for (counter int from 1 to allcustomers.getSize())
      SysLib.writeStdout(allCustomers[counter].CustomerId::" "::
        allCustomers[counter].FirstName::" "::
        allCustomers[counter].LastName);
    end
    SysLib.writeStdout("\n");

  end
  
  //This function responds to errors accessing the database.
  function handleDBException(exception SQLException)
    SysLib.writeStdout("Error accessing database. "::
      "See Troubleshooting section");
    SysLib.writeStdout(exception.message);
  end
  
end

Right now, this program merely prints out the complete list of customers in the database. It also includes a function named handleDBException to process any problems EGL encounters when working with the database. (You'll learn more about handling errors later in this tutorial.) You can run this program now to see how the database looks before you have changed it.

Save and generate the program.
Run the program:
1. In the Project Explorer view, expand EGLSQL/JavaSource/programs.
2. Right-click the CRUDTest.java file and then click Run As > Java Application.
If the program runs without any errors, the Console view shows the contents of the database. The results in the Console view look similar to this:
```
Contents of database before any SQL operations:
1 Fred Filibuster
2 Andy Lundquist
3 Billie Kingman
4 Francine Liebowitz
5 Ornette Coleman
6 Ellen Springsteen
7 Martin St. Louis
8 Sergey Sharov
9 Melodie Hudak
```
If the results in the console include errors, make sure you have copied the program accurately, and then see Troubleshooting.

Add a function to insert a new row into the database, before the final end statement:

function addCustomer()
  customerToAdd Customer;
  customerToAdd.CustomerId = 50;
  customerToAdd.FirstName = "John";
  customerToAdd.LastName = "Doe";
  
  // Insert the record into the database
  try
    add customerToAdd;
    SysLib.writeStdout("Contents of database after adding a new record:");
    printAllCustomers();
  onException(exception SQLException)
    handleDBException(exception);
  end
  
end

This function uses add to insert a record into the database. To see the implicit SQL code behind the add statement, place the cursor directly on the keyword customerToAdd in the line add customerToAdd;, right-click, and then click SQL Statement > Add. The statement now looks like this:

try
  add customerToAdd with
    #sql{
      insert into EGL.CUSTOMER
        (EGL.CUSTOMER.CUSTOMER_ID, EGL.CUSTOMER.FIRST_NAME, 
        EGL.CUSTOMER.LAST_NAME, EGL.CUSTOMER.PASSWORD, 
        EGL.CUSTOMER.PHONE, EGL.CUSTOMER.EMAIL_ADDRESS, 
        EGL.CUSTOMER.STREET, EGL.CUSTOMER.APARTMENT, 
        EGL.CUSTOMER.CITY, EGL.CUSTOMER."STATE", 
        EGL.CUSTOMER.POSTALCODE, EGL.CUSTOMER.DIRECTIONS)
      values
        (:customerToAdd.CustomerId, :customerToAdd.FirstName, 
        :customerToAdd.LastName, :customerToAdd.Password, 
        :customerToAdd.Phone, :customerToAdd.EmailAddress, 
        :customerToAdd.Street, :customerToAdd.Apartment, 
        :customerToAdd.City, :customerToAdd.State, 
        :customerToAdd.Postalcode, :customerToAdd.Directions)
    };

You can also preview the SQL code without making it explicit by placing the cursor directly on the record variable, right-clicking, and clicking SQL Statement > View. To work with SQL code within EGL code like this, the cursor in the EGL editor must be somewhere in the statement.

Call the new function from the program's main() function:

function main()
  
  // Print the starting records in the database.
  SysLib.writeStdout("Contents of database before any SQL operations:");
  printAllCustomers();
  
  // Add a record.
  addCustomer();
  
end

Save, generate, and run the program. The output shows the contents of the table before and after you add a record to it:

Contents of database before any SQL operations:
1 Fred Filibuster
2 Andy Lundquist
3 Billie Kingman
4 Francine Liebowitz
5 Ornette Coleman
6 Ellen Springsteen
7 Martin St. Louis
8 Sergey Sharov
9 Melodie Hudak


Contents of database after adding a new record:
1 Fred Filibuster
2 Andy Lundquist
3 Billie Kingman
4 Francine Liebowitz
5 Ornette Coleman
6 Ellen Springsteen
7 Martin St. Louis
8 Sergey Sharov
9 Melodie Hudak
50 John Doe

Add a function to delete the record from the database, before the final end statement:

function deleteCustomer()
  customerToDelete Customer;
  customerToDelete.CustomerId = 50;
  
  // Delete the record.
  try
    delete customerToDelete noCursor;
    SysLib.writeStdout("Contents of database after deleting the record:");
    printAllCustomers();
  onException(exception SQLException)
    handleDBException(exception);
  end
  
end

In the main() function, replace the call to the function that adds the record with a call to the function that deletes the record. Because the CUSTOMER_ID column is the primary key for the table, and the table already has a row with CUSTOMER_ID set to 50, trying to add the record again will cause an error.
```
function main()
  
  // Print the starting records in the database.
  SysLib.writeStdout("Contents of database before any SQL operations:");
  printAllCustomers();
  
  // Delete a record.
  deleteCustomer();
  
end
```

Save, generate, and run the program. The output shows the contents of the table before and after you delete the record from it:

Contents of database before any SQL operations:
1 Fred Filibuster
2 Andy Lundquist
3 Billie Kingman
4 Francine Liebowitz
5 Ornette Coleman
6 Ellen Springsteen
7 Martin St. Louis
8 Sergey Sharov
9 Melodie Hudak
50 John Doe


Contents of database after deleting the record:
1 Fred Filibuster
2 Andy Lundquist
3 Billie Kingman
4 Francine Liebowitz
5 Ornette Coleman
6 Ellen Springsteen
7 Martin St. Louis
8 Sergey Sharov
9 Melodie Hudak

Add a function to retrieve and update the record in the database, before the final end statement:

function updateCustomer()
  customerToUpdate Customer;
  customerToUpdate.CustomerId = 50;
  
  // Retrieve the row.
  get customerToUpdate;
  
  // Change values in the record variable.
  customerToUpdate.FirstName = "Johnny";
  customerToUpdate.LastName = "Five";
  
  // Update the row in the database.
  try
    replace customerToUpdate noCursor;
    SysLib.writeStdout("Contents of database after updating the record:");
    printAllCustomers();
  onException(exception SQLException)
    handleDBException(exception);
  end
  
end

In the program's main() function, call the add, replace, and delete functions in that order:

function main()
  
  // Print the starting records in the database.
  SysLib.writeStdout("Contents of database before any SQL operations:");
  printAllCustomers();
  
  // Add a record.
  addCustomer();
  
  // Update the record.
  updateCustomer();
  
  // Delete a record.
  deleteCustomer();
  
end

Save, generate, and run the program. The output shows the contents of the table at the beginning of the program, after you add a record, after you update the record, and after you delete the record:

Contents of database before any SQL operations:
1 Fred Filibuster
2 Andy Lundquist
3 Billie Kingman
4 Francine Liebowitz
5 Ornette Coleman
6 Ellen Springsteen
7 Martin St. Louis
8 Sergey Sharov
9 Melodie Hudak


Contents of database after adding a new record:
1 Fred Filibuster
2 Andy Lundquist
3 Billie Kingman
4 Francine Liebowitz
5 Ornette Coleman
6 Ellen Springsteen
7 Martin St. Louis
8 Sergey Sharov
9 Melodie Hudak
50 John Doe


Contents of database after updating the record:
1 Fred Filibuster
2 Andy Lundquist
3 Billie Kingman
4 Francine Liebowitz
5 Ornette Coleman
6 Ellen Springsteen
7 Martin St. Louis
8 Sergey Sharov
9 Melodie Hudak
50 Johnny Five


Contents of database after deleting the record:
1 Fred Filibuster
2 Andy Lundquist
3 Billie Kingman
4 Francine Liebowitz
5 Ornette Coleman
6 Ellen Springsteen
7 Martin St. Louis
8 Sergey Sharov
9 Melodie Hudak

Here is the complete code of the CRUDTest.egl file. If you see any errors marked by red X symbols in the file, make sure your code matches the code in this file:Completed CRUDTest.egl file after lesson 2.

Lesson 2: Basic SQL operations in EGL

Reviewing the CRUD operations

Lesson checkpoint