provided by: 
Originally published at Internet.comIntroduction
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: 1. The first step is to open a data perspective. Go to Window &rarr Open Perspective &rarr Data (see Figure 1).
Figure 1: Data Perspective 2. Next, locate Database Pane on the screen and right-click Connection &rarr New Connection (see Figure 2).
Click here for a larger image.
Figure 2: Open New Connection Dialog 3. In the New Connection Pane, select Derby 10.1 and fill out Connection URL Details: * 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\.plugins\com.ibm.datatools.db2. cloudscape.driver/sample 4. Test your connection. Note that the user name and password can be any value.
Click here for a larger image.
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 1. Open Data Perspective. 2. Click File → New → Data Development Project. 3. When New Data Development Project Dialog opens, set Project Name, Current Schema Name, and click the "Next" button. 4. Select the Derby connection that you set up previously. 5. 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. 1. Create new SQL Statement by right-clicking SQL Scripts → New → SQL Statement.This is shown in Figure 5.
Figure 5: Create new SQL Statement 2. 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.
Click here for a larger image.
Figure 6: New SQL Statement
Once the SQL Statement file is created, you can start building your SQL Statement. 1. Open the EMPLOYEE_AND_PHOTO.SQL file. 2. From the database pane, select Derby Connection → SAMPLE → Schemas → SAMP → Tables (see Figure 7).
Figure 7: Open Table List 3. Drag and drop the EMPLOYEE and EMP_PHOTO tables from the database explorer pane to the editor pane (see Figure 8).
Click here for a larger image.
Figure 8: The EMPLOYEE and EMP_PHOTO tables are selected 4. Now, you join the tables by using inner join. Right-click the EMPLOYEE Table and select Inner Join (see Figure 9).
Click here for a larger image.
Figure 9: Create an Inner join between EMPLOYEE and EMP_PHOTO 5. 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. 6. Finally, you add a where clause in which you specify that you want all employees whose last name starts with A (see Figure 10).
Click here for a larger image.
Figure 10: Add Where Clause 7. 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.
Click here for a larger image.
Figure 11: View Execution Results in the Data Output Pane
Conclusion
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.
Author: Aleksey Shevchenko
Read article at Internet.com site