Lesson 4: Create parts to access a database

In this lesson, you will create the data and logic parts that allow you to access the sample database.

About this task

The EGL Data Access Application wizard creates the EGL code necessary to access a database. Although you can customize the code when the wizard runs, typically the wizard creates the following EGL parts:
  • For each table you select from the database, the wizard creates a record part representing that table. This Record part is a series of fields, each representing the columns in the table.
  • For each table you select from the database, the wizard creates a library part. This library contains EGL functions that you can use to read from or write to the database.
  • For each row in the database tables you select, the wizard creates a DataItem part. These DataItem parts represent the columns in the database. The Record parts created by the wizard are made of a sequence of these DataItems.

Create parts from the database connection

About this task

In the previous lesson, you created a connection to the database. From that connection, EGL can create the necessary parts to access the database:

Show Me

Procedure

  1. Click File > New > Other.
    The New window opens.
  2. Expand EGL and click Data Access Application.
  3. Click Next.
  4. In the Define project settings window, make sure EGLWeb is displayed in the Project Name list.
  5. In the Database Connection list, select the EGLDerbyDB connection that you created in the previous lesson.
    You might be prompted for a User ID and password for the database. If so, enter any name and password; the database does not require a password, but the workbench data tools might expect a password.
    You have established a connection to the database. All of the tables in the database are listed under Table Name at the bottom of the wizard. You will not create data parts for all of these tables because some contain only metadata.
  6. Under Select Tables, select the check boxes next to the following tables only:
    • EGL.CUSTOMER
    • EGL.ITEM
    • EGL.ORDERITEM
    • EGL.ORDERS
    • EGL.SITEUSER
    • EGL.STATETABLE
    The EGL Data Access Application wizard looks like this:
    Picture of the EGL Data Access Application window with the six tables selected
  7. Make sure the Create web pages check box is clear.
    If this check box is selected, EGL creates web pages directly from the database tables. This feature can save time by creating a simple web data access application for you, but that would defeat the purpose of this tutorial.
    Note:
    Do not click Finish yet. You must change one more setting in this wizard.
  8. Click Next to move to the Define the Fields page.
    This page lets you add key fields to the database tables. Don't change any settings on this page; the database already has a key field in each table.
  9. Click Next again to move to the Define project creation options page.
  10. On the Define project creation options page, select the Qualify table names with schema check box.
  11. Click Finish.

Lesson checkpoint

The Data Access Application wizard has created several EGL artifacts in your EGLWeb project.

Procedure

First of all, there are several new EGL packages in the EGLSource folder of your EGLWeb project, including eglderbydb.data, eglderbydb.access, and eglderbydb.primitivetypes.data. Packages work just like folders: they contain your source code files and organize them into meaningful groups. In this case, the eglderbydb.data package holds the records, the eglderbydb.access package holds the libraries, and the eglderbydb.primitivetypes.data package holds the DataItems.

Here are some of the files that will be useful for this tutorial:
eglderbydb.primitivetypes.data.DataDefinitions.egl
This file lists all of the DataItems that make up the Record parts in the other files. For example, the customer ID number given to each customer record in the database is represented by a DataItem named CUSTOMER_ID: 
dataitem CustomerId INT end
In this case, the ID number field is based on the integer primitive type. The DataItem can have other properties to specify details like its valid range of values and how it should be formatted in the UI.
eglderbydb.data.Customer.egl
This file contains one of the records created from the database tables, in this case the Customer table. This record contains fields to hold information about a customer, such as the customer's first name, last name, address, telephone number, and ID number. The record definition looks like this: 
record Customer type sqlRecord { 
        tablenames=[["EGL.CUSTOMER"]],
        fieldsMatchColumns = yes,
        keyItems=[CUSTOMERID]
    }  

  CUSTOMERID CUSTOMERID {column="CUSTOMERID"};
  FIRSTNAME FIRSTNAME {column="FIRSTNAME", sqlVariableLen=yes, maxLen=30, isSqlNullable=yes};
  LASTNAME LASTNAME {column="LASTNAME", sqlVariableLen=yes, maxLen=30, isSqlNullable=yes};
  ...
end
eglderbydb.access.CustomerLib.egl
This file contains an automatically-generated library of functions that you can use to access the Customers table of the database. For example, the first function is AddCustomer, which adds a new customer record to the database. There are other functions in this library that retrieve, update, and delete records in the database, and each table has similar functions in a separate library. You will use these functions later in the tutorial.