Lesson 2: Connect to a new Derby database

Use the Derby open source database manager to handle the data store for the application.

About this task

This tutorial uses the open source Derby database. In this chapter, you connect to a Derby database and create the table to be accessed. Alternatively, you can connect to a database of one of the following kinds: Cloudscape, DB2® UDB, Informix®, Oracle, or SQL Server. If you prefer to use one of those databases, review the following help topic: Creating an SQL database connectionCreating an SQL database connection at http://publib.boulder.ibm.com/infocenter/rbdhelp/v8r0m0. In any case, create the table described in this lesson.

Follow these steps to set up the Derby database:

Create an SQL database connection through the EGL Preferences.
Use the Data perspective to create and connect to the database.
Write an SQL script to create a table within the database.
Disconnect from the database, as is necessary because Derby allows only one connection, which you will need during code development.

Create an SQL database connection

Procedure

In the top menu of the EGL workbench, click Window and then click Preferences > EGL > SQL Database Connections.
Next to the list of connection details, click New.
In the Connection Profile window, complete these steps:
1. Under Connection Profile Types, click Derby.
2. In the Name field, type the following string:
```
Derby Database Connection
```
3. Click Next.
In the Specify a Driver and Connection Details window, specify the following information:
1. From the Drivers list, select Derby Embedded JDBC Driver 10.1 Default.
2. For the Database location field, enter a simple path:
```
C:\databases\PaymentDB
```
  The final element in the path is the name of a folder that does not yet exist.
3. Specify generic login information:
  - In the User name field, enter admin
  - In the Password field, also enter admin
4. Select the Create database (if required) check box.
5. Select the Save password check box.
  When you work with live data, you might prefer not to select this option, but it simplifies the tutorial.
6. Make sure that Connect when the wizard completes is selected and that Connect every time the workbench is started is cleared.
7. Click Test Connection.
  You should see a message that says Ping succeeded! Click OK to close the message window. If the test failed, get more information by clicking Details on the failure message.
8. Click Finish.
In the Preferences window, make sure that Derby Database Connection is highlighted, then click OK.

Switch to the Data perspective

About this task

To set up the Derby database for your application, use the Data perspective, which is a workbench perspective and different from the EGL Data view.

To connect to the database:

Procedure

Change to the Data perspective as follows:
1. Click the Open Perspective button, which is located by default in the right side of the navigation bar.
2. If the Data perspective is not shown on the menu, click Other.
3. If you still do not see the Data perspective, select Show All at the bottom of the wizard. Click Data and then click OK.
Locate the Data Source Explorer view, by default in the lower left corner of the workbench; and under Database Connections, right-click Derby Database Connection. Click the Connect option.
The option was enabled because you set the following check boxes when you created the connection: Create database (if required) and Connect when the wizard completes.

Create a table

Procedure

While in the Data perspective, you can write an SQL script to create a table in the database.

In the Data Source Explorer view, expand Derby Database Connection. Right-click the PaymentDB database name and click New SQL Script.

A new script file opens in the editor.
Copy the following SQL code into the script file:
```
CREATE TABLE PAYMENT(
   PAYMENT_ID INT PRIMARY KEY NOT NULL 
      GENERATED ALWAYS AS IDENTITY
      (START WITH 1, INCREMENT BY 1),
   CATEGORY INT,
   DESCRIPTION CHAR(30),
   AMOUNT DECIMAL(10,2),
   FIXED_PAYMENT SMALLINT,
   DUE_DATE DATE,
   PAYEE_NAME CHAR(30),
   PAYEE_ADDRESS1 CHAR(30),
   PAYEE_ADDRESS2 CHAR(30));
```
In the next step, you run this code to create a table named PAYMENT.
Note:
1. The PAYMENT_ID column is an identity column, which means that Derby will place a unique value into that column whenever the user creates a record. Each value is one more than the last.
2. The names of Derby tables and columns are always in uppercase regardless of the case of names that are in the CREATE TABLE statement.
Right-click anywhere in the background of the editor pane, and then click Run SQL.
The SQL Results view, which is by default at the bottom center of the workbench, should show the create table operation and a status of Succeeded. You can now expand the PaymentDB entry in the Data Source Explorer and see the columns for the new table:
Close the script file. You do not need to save the file, as you will not need it again.
You cannot access the database from EGL source code while the Data view is using the connection. Right-click Derby database connection and click Disconnect.

Lesson checkpoint

About this task

In this lesson, you completed the following tasks:

Created an EGL database connection
Created a database named PaymentDB
Created a database table named PAYMENT

In the next lesson, you start writing application code.