Rational Application Developer 7.0 offers a wide range of tools to work effectively with relational databases. In this article, you will learn how to establish a database connection, set up a data development project, and work with SQL Builder.
Establishing a Connection
Before any development can begin, a database connection must be established. You will use Derby 10.1 as your sample database. RAD 7.0 comes with the derby.jar file (see the com.ibm.datatools.db2.cloudscape plug-in). In case you want to download a newer version, you can do so by going to http://incubator.apache.org/derby/.
Follow these steps to create a Derby connection:
- The first step is to open a data perspective. Go to Window &rarr Open Perspective &rarr Data (see Figure 1).
- Next, locate Database Pane on the screen and right-click Connection &rarr New Connection (see Figure 2).
- In the New Connection Pane, select Derby 10.1 and fill out Connection URL Details:
Figure 1: Data Perspective
Figure 2: Open New Connection Dialog
- JDBC Driver: Other
- Database: Sample
- JDBC Driver Class: org.apache.derby.jdbc.EmbeddedDriver
- Class Location: YOUR_DRIVER_PATH/derby.jar
- Connection URL: jdbc:derby: YOUR_WORKSPACE_PATH.metadata.pluginscom.ibm.datatools.db2. cloudscape.driver/sample
Figure 3: Set up and test Derby Connection
Now that you have created a database connection, you can move ahead to create a Data Development Project.
Create a Data Development Project
A Data Development Project is required to develop the following types of objects:
- DB2® and Derby stored procedures
- DB2 user-defined functions
- SQL scripts
- Open Data Perspective.
- Click File → New → Data Development Project.
- When New Data Development Project Dialog opens, set Project Name, Current Schema Name, and click the “Next” button.
- Select the Derby connection that you set up previously.
- Click Finish.
Figure 4: Data Development Project
Create a SQL Statement with SQL Builder
SQL Builder is a wizard/tool that allows you to create SQL Statements interactively. Please go through an exercise where you join two tables in a simple select.
- Create new SQL Statement by right-clicking SQL Scripts → New → SQL Statement.This is shown in Figure 5.
- When the SQL Statement Wizard opens (see Figure 6), you can choose between among SQL Statement Temples: SELECT, UPDATE, INSERT, DELETE, and FULLSELECT. You will select the “SELECT” Statement template. Also, make sure to select the “SQL Builder” checkbox and enter JOIN_EMPLOYEE_PHOTO as a statement name.
Figure 5: Create new SQL Statement
Figure 6: New SQL Statement
Once the SQL Statement file is created, you can start building your SQL Statement.
- Open the EMPLOYEE_AND_PHOTO.SQL file.
- From the database pane, select Derby Connection → SAMPLE → Schemas → SAMP → Tables (see Figure 7).
- Drag and drop the EMPLOYEE and EMP_PHOTO tables from the database explorer pane to the editor pane (see Figure 8).
- Now, you join the tables by using inner join. Right-click the EMPLOYEE Table and select Inner Join (see Figure 9).
- The next step is to select what columns the select statement shall return. This can be done simply by checking the checkboxes next to the field in the table.
- Finally, you add a where clause in which you specify that you want all employees whose last name starts with A (see Figure 10).
- The last step is to execute the statement and view the results. You can do so by right-clicking the SQL Statement and selecting “Run SQL”. The results can be viewed in the Data Output pane, as shown in Figure 11.
Figure 7: Open Table List
Figure 8: The EMPLOYEE and EMP_PHOTO tables are selected
Figure 9: Create an Inner join between EMPLOYEE and EMP_PHOTO
Figure 10: Add Where Clause
Figure 11: View Execution Results in the Data Output Pane
In this article, you have learned how to create a database connection and use SQL Builder using RAD 7. In the next article, you will learn how to create SQL Stored Procedures and User-Defined Functions using RAD.
About the Author
Aleksey Shevchenko has been working with object-oriented languages for over eight years. For the past four years, he has served as a technical lead and a project manager. Aleksey has been implementing Enterprise IT Solutions for Wall Street and the manufacturing and publishing industries.