Lesson 1: Set up the database connection

First, you need a new EGL project to hold the data access code.

1. Switch to the EGL perspective:
   1. Click Window > Open Perspective > Other.
   2. In the Open Perspective window, click EGL. If you don't see EGL in the list of perspectives, select the Show all check box.
   3. Click OK.
2. Create a new EGL project (not an EGL Web project) named EGLSQL:
   1. Click File > New > Project.
   2. In the New Project window, expand EGL and then click EGL Project Wizard.
   3. Click Next.
   4. In the Project Name field, enter EGLSQL.
   5. Under EGL Project Types, click General Project. The New EGL Project window looks like this:
      
   6. Click Next.
   7. On the second page, make sure that Java is selected under Target Runtime Platform and that Create a new build descriptor is selected under Build Descriptor Options. The New EGL Project window looks like this:
      
   8. Click Finish.
   The new project is created and appears in the Project Explorer view.

This tutorial uses the sample Derby database that is used in the tutorial Introducing EGL (a quick-start guide). In these steps, you unzip another copy of this database to a location on your computer. You also use the Data Access Application wizard to connect to the database and create data parts and logic parts based on that database. For more information on these parts, see Introducing EGL (a quick-start guide).

1. Click the following link and download the sample database to a temporary folder on your computer, such as your desktop:

   Sample database

   It doesn't matter where you save the database, as long as you can find it again later.

   Alternately, you can find this sample database in your product installation directory in the following location:

   shared_resources/plugins/com.ibm.etools.egl.tutorial0001.doc_version/resources/EGLDerbyR7.zip

   shared_resources

   The shared resources directory for your product, such as C:\Program Files\IBM\SDP70Shared on a Windows system or /opt/IBM/SDP70Shared on a Linux system. If you installed and kept a previous version of an IBM product containing EGL before installing your current product, you may need to specify the shared resources directory that was set up in the earlier install.

   version

   The installed version of the plugin, including three numbers separated by periods, a string separator, and the date and time that the plugin was built; for example, 7.0.0.RFB_20070120_1300. If more than one is present, use the one with the most recent version number, unless you have a reason to use an older version.

2. Unzip the database to a location on your computer, such as C:\databases. Again, it isn't important where you put the database as long as you can find it again later.
3. Click File > New > Other. The New window opens.
4. Expand EGL and click EGL Data Access Application.
5. Click Next.
6. On the Define project settings page, select EGLSQL in the Project Name list.
7. Next to the Database Connection list, click New.
8. In the New Connection window, under Select a database manager, expand Derby and click 10.1.
9. In the Database location field, browse to the EGLDerbyR7 folder, which was in the ZIP file you unzipped earlier. For example, if you unzipped the EGLDerbyR7.zip file to C:\databases, the Database location field should be C:\databases\EGLDerbyR7

   You do not need to change the User ID or Password fields.

10. In the Class location field, enter the path to the file derby.jar.
    There are several ways you may be able to find this file:

    • If you have installed IBM^® WebSphere^® Application Server, version 6.1, you can use the version of Derby that is included with the server. Look for derby.jar in the following folder:

      install_location/runtimes/base_v61/derby/lib

    • If you have installed database tools along with your product, you may be able to find the file in the following location:

      shared_resources/plugins/com.ibm.datatools.db2.cloudscape.driver_version/driver

      shared_resources

      The shared resources directory for your product, such as C:\Program Files\IBM\SDP70Shared on a Windows system or /opt/IBM/SDP70Shared on a Linux system. If you installed and kept a previous version of an IBM product containing EGL before installing your current product, you may need to specify the shared resources directory that was set up in the earlier install.

      version

      The installed version of the plugin, including three numbers separated by periods, a string separator, and the date and time that the plugin was built; for example, 7.0.0.RFB_20070120_1300. If more than one is present, use the one with the most recent version number, unless you have a reason to use an older version.

    • If you can't find the file in the previous two locations, try searching for the derby.jar file in your product installation directory. Different installations may have the file in different locations.
    • If you can't find the file on your computer, you can download the file directly from the Derby Web site: http://db.apache.org/derby/. You will need to download the most recently released version of Derby and extract the derby.jar file to a place on your computer.
    The New Connection window looks like this, with your own workspace and location information in the Database location and Class location fields:
    
11. Make sure that the Use default naming convention check box is selected.
12. Click Finish. Now 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 won't create data parts for all of these tables because some contain only metadata.
13. Under Select Tables, select the check boxes next to only the following tables:
    • EGL.CUSTOMER
    • EGL.ITEM
    • EGL.ORDERS
    • EGL.ORDER_ITEM
    • EGL.SITEUSER
    • EGL.STATETABLE
    The EGL Data Access Application wizard looks like this:
    
14. Note the name of the connection in the Database Connection field. You will need the name of the connection in a later step. Normally, the connection is named EGLDerbyR7, after the name of the database. However, if you completed the tutorial Introducing EGL (a quick-start guide) in this workspace, you have already created a database connection with this name, so EGL names the new connection EGLDerbyR71.
15. Clear the Create Web pages check box.
16. Make sure that your project EGLWeb is listed in the Project Name field.
    Note: Don't click Finish yet! You need to change one more setting in this wizard.
17. 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.
18. Click Next again to move to the Define project creation options page.
19. On the Define project creation options page, select the Qualify table names with schema check box.
20. Click Finish. EGL creates a variety of data parts and logic parts that you can use to access the database. You will use these parts extensively in the rest of this tutorial.

    You must still configure the build descriptor to use this database connection at run time.

21. Open the project's default build descriptor, which is located in EGLSQL/EGLSource/EGLSQL.eglbld. Double-click this file to open it in the EGL Build Parts Editor.
22. In the EGL Build Parts editor, next to Load DB options using Connection, select the name of your database connection, which was the name of the database connection that you noted in a previous step. The build descriptor options are set to the necessary values to use that connection:
    
23. Save and close the build descriptor.
24. Add the Derby driver to the project's Java™ build path:
    1. Right-click the EGLSQL project and then click Properties.
    2. In the Properties window, click Java Build Path.
    3. On the Java Build Path page, go to the Libraries tab.
    4. On the Libraries tab, click Add External JARs.
    5. In the JAR Selection window, select the Derby.jar file and click Open.
    6. Click Finish.
    7. Click OK.

To make sure you have the database connection working, follow these steps to run a test program and print the rows of the database to the console.

1. Click File > New > Program.
2. In the New EGL Program part window, type programs in the Package field.
3. Type databaseTest in the EGL source file name field. The New EGL Program part window looks like this:
   
4. Click Finish. The new program is created and opens in the editor.
5. Replace all the code in the new program with this code:

   package progams;
   
   import eglderbyr7.data.Customer;
   
   program databaseTest type BasicProgram {}
     
     function main()
       customer Customer;
       open allResults for customer;
       
       SysLib.writeStdout("#"::" Customer name");
       forEach (customer)
         SysLib.writeStdout(customer.CustomerId::" "
           ::customer.FirstName::" "::customer.LastName);
       end
       
     end
     
   end

6. Save the program.
7. Generate the entire project by right-clicking the EGLSQL project in the Project Explorer view and then clicking Generate.

   Before you can run the program, you must disconnect the database tools from the database. Derby allows only one connection at a time, and the tools EGL used to create the data parts and set the values for hte build descriptor remain connected until you close the connection.

8. Close the database connection so the database will be available to the program at run time:
   1. Open the Database Explorer view by clicking Window > Show View > Other, selecting Data > Database Explorer from the list of views, and then clicking OK.
   2. In the Database Explorer view, expand Connections. Your database connection is listed under Connections, with the same name as you selected in the EGL build descriptor, such as EGLDerbyR7.
   3. Right-click your database connection and then click Disconnect.
   4. Close the Database Explorer view. You will learn more about data tools, including this view, later in the tutorial.
9. Run the test program:
   1. Expand EGLSQL/JavaSource/programs. The JavaSource folder holds the generated output of your programs.
   2. Right-click the databaseTest.java file and then click Run As > Java Application.

When you run the test program, you should see the contents of the database's CUSTOMER table printed to the Console view:

If you see an error message instead of the output in the Console view, see Troubleshooting.