Friday, September 12, 2008

Work with IBM DB2 UDB databases and SQL in Delphi for .NET

Original Source click here

Level: Introductory

Bob Swart (drbob@chello.nl), Author, Trainer, Consultant, and Webmaster, Bob Swart Training and Consultancy

30 Sep 2004

This article, a follow-up to the SQL DDL article, Using Delphi code to create/drop DB2 UDB database tables, focuses on the use of SQL queries to build simple and more complex SQL SELECT queries to end up with master-detail relations. The author also examines SQL joins in detail, covering examples of inner joins, left/right outer joins, and the differences between these joins.
Show developerWorks content related to my search: DelphiShow developerWorks content related to my search: Delphi


ADO.NET and BDP

In this article, I use the Borland Data Provider for ADO.NET, which is part of Borland's C#Builder and Delphi 8 for .NET (and future versions of Delphi). The Borland Data Provider offers an ADO.NET interface for the developer with support for live data at design time, and the ability to connect to different database with just one set of components.

Use the SAMPLE database

For this article's examples, I use the IBM® DB2® Universal Database™ (UDB) SAMPLE database again. Specifically, I use the EMPLOYEE, DEPARTMENT, and PROJECT tables. All three tables can be logically linked together using the Department Number field (called DEPTNO in the DEPARTMENT and PROJECT tables, and WORKDEPT in the EMPLOYEE table).

Each Employee works in exactly one Department. And only one Department handles each Project. But a Department can offer work for several Employees and work on several projects at the same time (0 or more in both cases).

Unfortunately, the information is not exact enough to make a mapping between Employee and Project (you can say that a number of Employees from a certain Department are working on a number of Projects, but the SAMPLE database doesn't store information about which Project(s) a certain Employee is working on).

Still, for a given department, you can list the Employees as well as the Projects, and I will do that, as demonstration of different techniques to obtain the same goal.

Connect to the SAMPLE database

For those of you who haven't worked with Delphi 8 for .NET in combination with the IBM DB2 UDB SAMPLE database before (and who haven't read my previous article, "Accessing DB2 universal database tables with Delphi for .NET"), let's get started by connecting to the database.

Start Delphi 8 for .NET, and do File -> New -> Windows Forms Application to create a new WinForms application. Instead of using the Data Explorer, let's now connect manually: Go to the Tool Palette (in the lower-right corner of the IDE) and open up the Borland Data Provider category. Place a BdpConnection component on the WinForm, which will appear in the non-visual component area at the bottom of the WinForms Designer. If you select the BdpConnection component, you can right-click on it to start the Connections Editor, as Figure A shows.


Figure A. Borland Data Provider: Connections Editor
fig a

Note that you need to specify the name of the Database (SAMPLE) as well as the UserName and Password (obviously ******** isn't my real password). If you want the end user of the application to specify the UserName and Password information, then you can set the LoginPrompt value to True.

Before you close this dialog, make sure you click on the Test button to verify that you've entered the correct information to connect to and use the IBM DB2 UDB SAMPLE database.

Build simple SELECT commands

Once you configure the BdpConnection component, you can start to write SQL commands to select tables from the SAMPLE database. Place two BdpDataAdapter components and call them BdpDADEPT and BdpDAPROJ (to work with the DEPARTMENT and PROJECT tables, respectively).

Then select BdpDADEPT, right-click on it, and select the Configure Data Adapter dialog. Here, you can configure the BdpDataAdapter. Specifically, you can write the SELECT command to select all fields from the DEPARTMENT table. Uncheck the checkboxes for the Insert, Update, and Delete options, click on the ADMINISTRATOR.DEPARTMENT table, and select all columns (or * if you wish—the result is the same), and then click on the Generate SQL button. Figure B shows the result.


Figure B. BdpDADEPT Data Adapter Configuration dialog
fig b

The SQL SELECT command you produced is:

SELECT DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION
FROM ADMINISTRATOR.DEPARTMENT

Now, move to the Preview Data tab of the Data Adapter Configuration dialog, where you can take a preview of the data that will be the result of this SELECT command.


Figure C. BdpDADEPT Data Adapter Configuration - Preview Data
fig c

Notice that only a small number of Departments in the IBM DB2 UDB SAMPLE database exist, but they are connected to a larger number of Projects (and Employees), as you'll see shortly.

Finally, move to the last tab in the BDP Data Adapter Configuration dialog, where you can specify the .NET DataSet you will use to hold the results from the SQL SELECT command (in a DataTable). I've specified a new DataSet called SampleDataSet here, as Figure D shows.


Figure D. BdpDADEPT Data Adapter Configuration - DataSet

Now you can close the Data Adapter Configuration dialog. The non-visual area of the WinForms designer will display a new component: the .NET DataSet called SampleDataSet. To fill this SampleDataSet with the result of the SQL SELECT command from the BdpDADEPT component, you must set the Active property of BdpDADEPT to True.

Repeat the same for BdpDAPROJ

Once this is done, you can turn to the BdpDAPROJ component. You must perform the same steps as you did with the BdpDADEPT component. The difference is that this time you should select all fields from the PROJECT table of course.

The resulting SQL COMMAND for the BdpDAPROJ component is:

SELECT PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF,
PRSTDATE, PRENDATE, MAJPROJ
FROM ADMINISTRATOR.PROJECT

Also, at the DataSet tab of the Data Adapter Configuration dialog for the BdpDAPROJ data adapter, you should specify that you want to use an existing DataSet called SampleDataSet—the one you just created.

Finally, don't forget to set the Active property of BdpDAPROJ to True as well.

Show the results in DataGrids

As a result of the two SQL SELECT commands in BdpDADEPT and BdpDAPROJ, there will be two DataTables inside the SampleDataSet. You can view them in the Delphi 8 for .NET IDE by selecting the SampleDataSet component and clicking on the ellipsis for the Tables property. Figure E shows a dialog with the two DataTables, called DEPARTMENT and PROJECT (named after the original tablenames in the IBM DB2 UDB SAMPLE database).


Figure E. SampleDataSet Tables Collection Editor

The easiest way to show the contents of these two DataTables is to use two DataGrid controls. Place a first DataGrid on the WinForm and open up the drop-down combobox for the DataSource property. This shows a list with DataTable1, DataTable2, and SampleDataSet. You can either directly connect to one of the DataTables or connect to the SampleDataSet. In the latter case, you must also specify a value for the DataMember property, which can then be either DEPARTMENT or PROJECT. I've bound the DataSource to SampleDataSet, and the DataMember to DEPARTMENT, in my case.

You can use a second DataGrid to bind its DataSource to SampleDataSet again, but this time, bind the DataMember to PROJECT.


Figure F. DataGrids with live data at design time

You can see the result in Figure F: two DataGrids with records that remain unrelated at this time. You will now create a master-detail relationship between the two DataTables so a more useful display of the data can be given.

Connect master and detail DataSets

Where the Tables property of the SampleDataSet can be used to view the available DataTables, you can use the Relations property to define relations between DataTables in the SampleDataSet. Select the SampleDataSet and click on the ellipsis for the Relations property to start the Relations dialog.

Using this dialog, you can click on the Add button to define a new relationship. This gives a new popup dialog where you can specify the name of the relation (that I called DepartmentProjects), the Parent table (DEPARTMENT), and the Child table (PROJECT), and the columns that are used as key columns (from the DEPARTMENT table) and foreign key columns (from the PROJECT table). In both cases, the column can be DEPTNO, as Figure G shows.


Figure G. Master-detail relation between DEPARTMENT and PROJECT

When you click on OK to close Figure G's dialog, the Relations Collection Editor will list the new relation.

To display the effects of the new relation, select the second DataGrid component. This time, instead of binding the DataMember property to the PROJECT DataTable, bind it to the DEPARTMENT.DepartmentProject DataRelation. At design time, the second DataGrid will remain empty (because no PROJECTs are related to the first DEPARTMENT); but at runtime, you can walk through the top DataGrid with the DEPARTMENT records, showing only the related PROJECT records in the second DataGrid (see Figure H).


Figure H. Master-detail DataRelation

Consider SQL JOINs

This kind of master-detail relation is perfect if you have a one-to-many relationship. However, in some cases, you have a one-to-one relationship (like the records in the EMPLOYEE, EMP_RESUME and EMP_PHOTO tables), or you just want to add information from one table to another. An example that illustrates this is the binding between the EMPLOYEE and DEPARTMENT tables—not from the DEPARTMENT point of view, which indeed is a one-to-many relation, but from the EMPLOYEE point of view. This is a one-to-one relation (each Employee works in exactly one Department). You do not want to display the Department details in a detail table. Moreover, if you already know which Employee you are looking for, then there is no need to retrieve the entire Department table only to make the DataRelation at the client side and find out which Department should be displayed along with this Employee. Sure, it's fine for a handful of records, but you don't want to retrieve all books from a bookstore to find only the ones written by Bob Swart.

In those cases, it's more efficient to express the relation between the tables at the DBMS server side—in the SQL query being executed by IBM DB2 UDB, and let the DBMS handle the relation. For this, you need to extend your SQL SELECT command using a JOIN.

Build SQL JOIN commands

Building an SQL JOIN command starts as a regular SQL SELECT command, but instead of specifying fields from just one table, you can specify fields from more than one table. In my example with the Employee and Department tables, you can specify the EMPNO, FIRSTNME, LASTNAME, WORKDEPT, and JOB fields from the Employee table, as well as the DEPTNAME field from the Department table. This will result in JOINed records that contain both the Employee fields and the Department field—in a single record, and not in separate DataTables like you saw with the DEPARTMENT and PROJECT example.

A good place to experiment with SQL commands is the BdpDataAdapter's Data Adapter Configuration editor, where you can build your SQL command in the SQL field, and go to the Preview tab to get a preview of the result (or an error message if you've made a syntax error). The next few screenshots use the Data Adapter Configuration dialog to demonstrate this.

The SELECT part of the SQL command so far is:

SELECT
EMPNO, FIRSTNME, LASTNAME, WORKDEPT,
JOB, DEPTNAME
FROM
ADMINISTRATOR.EMPLOYEE E,
ADMINISTRATOR.DEPARTMENT D

But this needs a WHERE clause to specify the relation between the EMPLOYEE and DEPARTMENT tables; otherwise, you get a combined resultset with the combinatorics explosion of EMPLOYEE and DEPARTMENT records. Obviously, you only want the DEPARTMENT fields that "belong" to the EMPLOYEE, and for that you need to "match" the WORKDEPT field of the EMPLOYEE table with the DEPTNO field of the DEPARTMENT.

With that expression in the WHERE clause, the SQL SELECT command becomes:

SELECT
EMPNO, FIRSTNME, LASTNAME, WORKDEPT,
JOB, DEPTNAME
FROM
ADMINISTRATOR.EMPLOYEE E,
ADMINISTRATOR.DEPARTMENT D
WHERE
E.WORKDEPT = D.DEPTNO

Optionally, you can also append an ORDER BY clause to the query, for example, to order the result by Department number (E.WORKDEPT or D.DEPTNO), as Figure I shows.


Figure I. Building SQL JOIN Command

Note that this JOIN uses two tables: ADMINISTRATOR.EMPLOYEE (the left or outer table) and ADMINISTRATOR.DEPARTMENT (the right or inner table). By default, a JOIN is performed as a INNER JOIN, meaning that you only get (combined) records if you have at least a matching record on the left side—from the ADMINISTRATOR.EMPLOYEE table (for example if the condition mentioned in the ON is met).

You could also write the SQL Command to explicitly mention the INNER part of the JOIN:

SELECT 
EMPNO, FIRSTNME, LASTNAME, WORKDEPT,
JOB, DEPTNAME
FROM
ADMINISTRATOR.EMPLOYEE E
INNER JOIN
ADMINISTRATOR.DEPARTMENT D
ON
E.WORKDEPT = D.DEPTNO
ORDER BY
E.WORKDEPT

Note that an ON part replaces the WHERE part now. Figure J displays the result in both cases.


Figure J. INNER JOIN of EMPLOYEE and DEPARTMENT

The fact that this is also called an INNER JOIN might also indicate that an alternative called OUTER JOIN exists. In fact, there are two kinds of OUTER JOINs, and each has its own advantage and specific use.

OUTER JOIN comparison

Where an INNER JOIN only returns a record if the matching condition is met, an OUTER JOIN will return null fields if a matching record in one of the tables is not found; which table depends on the prefix: you can have LEFT OUTER JOINs and RIGHT OUTER JOINs.

With a LEFT OUTER JOIN, the LEFT table is still the OUTER table, and if the condition specified in the ON field doesn't result in a matching record from the RIGHT table, then null values are returned (for the RIGHT table, which is the INNER table). This is useful for situations where you have the EMPLOYEE table as well as a smaller table with some EMP_PHOTO records, but not all EMPLOPYEEs have a corresponding EMP_PHOTO record.

If you use an INNER JOIN, then you only get EMPLOYEE records that have a EMP_PHOTO, which is not what you want. For a LEFT OUTER JOIN, you still get all EMPLOYEE records, with the EMP_PHOTO fields where available. The syntax for this LEFT OUTER JOIN is:

SELECT 
E.EMPNO, FIRSTNME, LASTNAME, WORKDEPT,
JOB, PHOTO_FORMAT, PICTURE
FROM
ADMINISTRATOR.EMPLOYEE E
LEFT OUTER JOIN
ADMINISTRATOR.EMP_PHOTO P
ON
E.EMPNO = P.EMPNO
AND
P.PHOTO_FORMAT = 'bitmap'

Note that you must prefix the EMPNO in the SELECT part with the tablename (or alias) because this fieldname occurs in both the EMPLOYEE and EMP_PHOTO tables.

Also note that the ON part also has an additional condition to check for PICTURES in the bitmap PHOTO_FORMAT only. You can see the result on the Preview tab of the Data Adapter Configuration dialog, as Figure K shows.


Figure K. LEFT OUTER JOIN of EMPLOYEE and EMP_PHOTO

Using a master-detail relationship, this would have resulted in a detail DataTable that would have been empty most of the time. The combined record is much easier to use—you only have to check the PICTURE field to see if it's not null (which was the result of the LEFT OUTER JOIN).

RIGHT OUTER JOIN

A RIGHT OUTER JOIN, on the other hand, will use the RIGHT table as OUTER table; and when the condition is verified and no matching column(s) in the LEFT (INNER) table is found, then null values are used instead. Compared to a LEFT OUTER JOIN, the difference is that the LEFT OUTER JOIN produces null values for missing "detail" (RIGHT) records, while the RIGHT OUTER JOIN uses null values for missing "master" (LEFT) records. As such, the RIGHT OUTER JOIN is used less frequently than a LEFT OUTER JOIN, but it's an excellent way of finding "orphan" child records, such as records in the EMP_PHOTO table that no longer have a corresponding EMPLOYEE record.

The syntax for that particular example is:

SELECT 
E.EMPNO, FIRSTNME, LASTNAME, WORKDEPT,
JOB, PHOTO_FORMAT, PICTURE
FROM
ADMINISTRATOR.EMPLOYEE E
RIGHT OUTER JOIN
ADMINISTRATOR.EMP_PHOTO P
ON
E.EMPNO = P.EMPNO

For each record in the EMP_PHOTO table, the condition in the ON clause is checked to find a matching EMPLOYEE record. If one is not found, null values are used, which would indicate a missing EMPLOYEE record for this EMP_PHOTO record. Figure L shows the result of this RIGHT OUTER JOIN on the IBM DB2 UDB SAMPLE database.


Figure L. RIGHT OUTER JOIN of EMPLOYEE and EMP_PHOTO

You can use RIGHT OUTER JOINs to look for orphan records and LEFT OUTER JOINs in situations where not every "master" record has a detail (such as EMPLOYEEs who don't have an EMP_PHOTO in the database yet).

Use the INNER JOIN in situations where you are only interested in the combined records that indeed have an INNER part (such as a DEPARTMENT for a EMPLOYEE or PROJECT). In all cases, the result of a JOIN is a single record that holds all values together, while a master-detail relationship (as defined in the beginning of this article) still consists of two or more tables that are related but not merged together. Especially in cases where you have multiple detail records (EMPLOYEEs that work in a DEPARTMENT for example), it would be more efficient to use a master-detail relationship compared to a JOIN (that would copy the DEPARTMENT fields for every EMPLOYEE in that DEPARTMENT), unless you are only interested in a few of the OUTER table fields (which would reduce the redundancy).



Back to top


Summary

In this article, I used Delphi 8 for .NET and the Borland Data Provider to design a Delphi application and execute numerous SQL SELECT commands on the IBM DB2 UDB SAMPLE database. I built master-detail relations with the result of SQL SELECT commands, showing when it can be more convenient to use SQL JOINs instead. Finally, I discussed the different possible SQL JOIN commands and provided examples of where and when you can use them.



Back to top


Next time

Next time I'll continue with an article that introduces working with stored procedures in IBM DB2 UDB databases. That article will focus on DB2 Stored Procedures, show what they can do and how they work, and demonstrate the stored procedures in the IBM DB2 UDB SAMPLE database in a Delphi 7 for Win32 and/or Delphi 8 for .NET client application.




Back to top



0 Comments: