Friday, September 12, 2008

Use Delphi code to create/drop DB2 UDB database tables

Original Source click here


Level: Introductory

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

02 Sep 2004

This article examines the design and implementation of a Borland Delphi application you can use to create or delete/drop IBM® DB2® Universal Database™ tables inside IBM DB2 databases. It provides several examples of sending SQL DDL to the DB2 DBMS using Delphi and the cross-platform dbExpress data access technologies.
Show developerWorks content related to my search: DelphiShow developerWorks content related to my search: Delphi


dbExpress

In this article, I use dbExpress, because it is the cross-platform data access technology available on Windows® (Delphi and C++Builder), Linux (with Kylix for Delphi and C++), and .NET (with Delphi for .NET). It is not difficult, however, to get similar results with the Borland Data Provider for .NET, which is part of Delphi for .NET and C#Builder. You just have to replace some of the calls of TSQLConnection with method calls that are available for the BDPConnection component. Other than that, the generated SQL for the IBM DB2 UDB database is of course the same.

Using sample or demo database

Note that this article shows how to write Delphi code that can manipulate tables inside an existing DB2 database. It does not show how to create a new IBM DB2 UDB database itself, because that's beyond the capabilities of both dbExpress and BDP. You should create your own database, or use an existing one. If you don't want to use the SAMPLE database, you can use the IBM DB2 Control Center (from the General Administration Tools group) to create a new, empty database in order to get some more experience (without the chance of interfering with some production database tables). Just as an example, I've used the IBM DB2 Control Center to create a new IBM DB2 UDB database called "DEMO", as shown in Figure A below.


Figure A. IBM DB2 Control Central - Create Database Wizard
IBM DB2 Control Central - Create Database Wizard

The actual SQL command given is:

CREATE DATABASE DEMO ON C: USING CODESET IBM-1252 TERRITORY US COLLATE USING SYSTEM

Note that you must have SYSADM rights to create a new database. Consult your DBA if you need help with this.

Although I can also use the Control Center to create new tables (and in fact perform all the work on tables that I want to demonstrate this time), I won't be doing that. This article demonstrates the use of Delphi code to generate SQL commands executed by the IBM DB2 UDB database, so you can get a better understanding of SQL, IBM DB2 UDB databases, tables, fields, and the code to manipulate them.

Connect to the new database

Once you've selected the database you want to work with, you can start Delphi (version 6 or higher), Kylix or C++Builder. Note that only the Enterprise edition (or higher) contains the DB2 dbExpress driver. Start a new project and place a TSQLConnection component on your form or data module. If you double-click on the TSQLConnection component, the Connection Editor dialog appears, and you can then specify the database name (DEMO or SAMPLE), the User_Name, and Password if you wish. You can verify that you can make a connection, as Figure B shows.


Figure B. dbExpress Connections Editor
dbExpress Connections Editor

Create DB2 tables

When you've verified that the connection can be made, it's time to consider writing the SQL commands to create some new user tables in this DEMO database. This is done by using the standard SQL "CREATE TABLE" command. However, that's just the start of the command. You must follow that with the name of the table you want to create. This name consists of two parts: the schema name and the actual tablename. As the schema name, you can use the username by which you connect to the database (in my case, I have to admit that's ADMINISTRATOR). For a department-specific table, you can also use the department or company name as schema, of course. To create a table called PERSON in my schema, I have to execute the following SQL command:

CREATE TABLE ADMINISTRATOR.PERSON

This is almost complete, but it doesn't specify any field information (and a table without fields is really not that useful, believe me).

Define DB2 UDB columns

DB2 UDB database table fields (or columns) can be of the following types (in alphabetical order): BIGINT, BLOB, CHARACTER, CLOB, DATE, DECIMAL, DOUBLE, INTEGER, REAL, SMALLINT, TIME, TIMESTAMP, and VARCHAR. For a BLOB or CLOB field, you can specify additional properties like the LENGTH (in Bytes, KBytes, MBytes or even GBytes). For a CHARACTER or VARCHAR field, you can also specify the LENGTH—this time only in bytes of course—with a default value of 10. For a DECIMAL field, you can specify the Precision (default 5) and Scale (default 0) values.

When defining a new column, you have to specify the name of the column followed by the type (and other attributes if relevant). By default, a column can contain a NULL value, but if you don't want that, you can append the "NOT NULL" postfix to the field definition. Finally, all column definitions are separated by commas, and embedded in brackets.

Let's continue the example of the PERSON table. If you want to add a field called PERSONID of type INTEGER, which should not be NULL, and a field NAME of type CHARACTER with a LENGTH of 42, which could be NULL, then the SQL command would be:

CREATE TABLE ADMINISTRATOR.PERSON
(PERSONID INTEGER NOT NULL, NAME CHARACTER (42))

To execute the SQL Command constructed so far, you don't have to use any of the other special dbExpress components. In fact, the TSQLDataSet or TSQLQuery components expect an SQL command that returns a result set of records. And obviously, the CREATE TABLE doesn't return anything.

The TSQLConnection component is equipped with an ExecuteDirect method to execute an SQL command that doesn't return a result set (also called cursor). According to the online help, ExecuteDirect returns 0 if the SQL command is successful, or a dbExpress error code otherwise.

In your Delphi project, place a TMemo component (called MemoSQL) on the form, as well as a TButton (called btnSQL), which looks like this on my system:


Figure C. Delphi SQL application at design-time
Delphi SQL application at design-time

In the OnClick event handler of the button, you need to write this code:

procedure TForm1.btnSQLClick(Sender: TObject);
var
ErrorCode: Integer;
begin
try
// if not SQLConnection1.Connected then SQLConnection1.Open;
ErrorCode := SQLConnection1.ExecuteDirect(MemoSQL.Text);
if ErrorCode <> 0 then // only 0 should be correct
raise Exception.Create('Error: code = ' + IntToStr(ErrorCode))
except
on E: Exception do
ShowMessage(E.Message)
end
end;

This will execute the ExecuteDirect method of the TSQLConnection component, and report any error if the ErrorCode is not equal to 0. In practice, however, it appears that the error code is -1 for a successful execution of the SQL command.

SQL errors are in fact reported as exceptions raised by the DB2 dbExpress driver. Specifically, if I enter the CREATE TABLE SQL command that I've constructed so far, and hit the SQL button, I first get a messagebox that tells me the error code is -1, which also indicates success since the table is created without problems. If you click on the SQL button twice, you don't get another error code, but an exception instead (as Figure D shows), raised by the [IBM][CLI Driver][DB2/NT], which is the IBM DB2 vendor client reporting an error to the DB2 dbExpress driver.


Figure D. SQL Server Error Exception
SQL Server Error Exception

This is why I used the try-except block: I can catch my own SQL errors and respond to them if needed.



Back to top


Construct SQL

So far, I've built a little Delphi application that can execute an SQL command, but I also had to enter the example "CREATE TABLE" SQL command in the Memo field by hand. This is a sure way to result in accidental typing mistakes or other errors, so instead of forcing the user to type in pure SQL, I now want to focus on generating SQL from the Delphi application.

Create tables (again)

When you create a new table, the tablename is the easy part. It's the list of field names, types, and optional additional information (like the size, nullable, etc.) that adds some complexity. To support the user when defining a new table, you can design a special dialog to define a new field. You can use this dialog to build a subsection of the SQL CREATE TABLE command.

For each new field you need a TEdit to hold the fieldname, a drop-down ComboBox to hold the field type, another TEdit for some optional additional information, and finally a TCheckBox to specify if the field can be NULL or not.

At design time, you can place these four controls on a single row, with two additional labels. In fact, this is a combination of controls that just asks to be put on a TFrame, as Figure E shows.


Figure E. New Field Frame
New Field Frame

The Style property of the TComboBox is set to DropDownList, and the Items list if filled with the possible field types I mentioned earlier. You can use this Frame multiple times on the form I worked on earlier. To add a little bit of intelligence to the frame, you should initially hide the label and TEdit control that belong to the optional information, and only show them if the selected field type in the drop-down combobox has a particular value (like BLOB, CLOB, CHARACTER, VARCHAR, or DECIMAL). If you really want to make it work well, you should even add some code to ensure that only digits can be entered in the second editbox (for length or precision).

I leave the input checking as exercise for you, but to dynamically toggle the visibility of the label and the second TEdit control, you can write the following code in the OnChange event handler of the drop-down combobox:

procedure TFrame1.ComboBox1Change(Sender: TObject);
begin
if (ComboBox1.ItemIndex >= 0) then
begin
if (ComboBox1.Items[ComboBox1.ItemIndex] = 'BLOB') or
(ComboBox1.Items[ComboBox1.ItemIndex] = 'CLOB') or
(ComboBox1.Items[ComboBox1.ItemIndex] = 'CHARACTER') or
(ComboBox1.Items[ComboBox1.ItemIndex] = 'VARCHAR') then
begin
Label2.Caption := 'Length';
label2.Visible := True;
if Length(ComboBox1.Items[ComboBox1.ItemIndex]) > 4 then // no BLOB
edtSize.Text := '10';
edtSize.Visible := True
end
else
if (ComboBox1.Items[ComboBox1.ItemIndex] = 'DECIMAL') then
begin
Label2.Caption := 'Precision';
label2.Visible := True;
edtSize.Text := '5';
edtSize.Visible := True
end
end
end;

With this frame ready for use, you can return to the original form and drop a btnAddField on the form. When you click on this button, a new instance of the Frame will be created and shown on top of the form—or actually on the tabsheet on the form. Here is the code for the OnClick event handler to create and display these frame instances (note that I use the TabSheet1 as Parent here, and not the Form itself):

procedure TForm1.btnAddFieldClick(Sender: TObject);
begin
Inc(Fields);
with TFrame1.Create(Self) do
begin
Name := 'MyFrame' + IntToStr(Fields);
Parent := TabSheet1;
Top := 32 + (Fields * 28);
Left := 86;
Width := 429;
Height := 28
end
end;

Note that a variable called "Fields" is used to maintain the number of fields (and hence frames) that have been added to the form, and must be used in the SQL command.

Figure F shows the result after two fields have been added, one of type INTEGER and one of type CHARACTER (with an additional editbox to specify the Length).


Figure F. Defining Table PERSON
Defining Table PERSON

The only remaining task now is the implementation of the OnClick event handler of the SQL button, where the actual CREATE TABLE SQL command is put together, based on the specified tablename, and all individual field definitions. Here is the code (note that you can replace the ExecuteDirect with a call to MessageBox to see the intended SQL before actually running it):

procedure TForm1.btnSQLClick(Sender: TObject);
var
Frame: TFrame1;
SQL: String;
i: Integer;
begin
try
SQL := 'CREATE TABLE ' + edtTableName.Text;
if Fields > 0 then
begin
SQL := SQL + ' (';
for i:=1 to Fields do
begin
Frame := FindComponent('MyFrame'+IntToStr(i)) as TFrame1;
if Assigned(Frame) and (Frame.ComboBox1.ItemIndex >= 0) then
begin
SQL := SQL + Frame.edtFieldName.Text + #32 +
Frame.ComboBox1.Items[Frame.ComboBox1.ItemIndex];
if Frame.edtSize.Visible then
SQL := SQL + '(' + Frame.edtSize.Text + ')';
if Frame.cbNotNull.Checked then SQL := SQL + ' NOT NULL';
if i < Fields then SQL := SQL + ','
end
end;
SQL := SQL + ' )'
end;
SQLConnection1.ExecuteDirect(SQL);
except
on E: Exception do
ShowMessage(E.Message)
end
end;

I leave it as an exercise for you to include additional checks to see if the TableName and FieldNames are left empty by mistake (or deliberate), but that shouldn't be a significant issue.

Delete tables

Apart from the ability to create new tables, you might also want to delete tables—only if you've made an accidental typing mistake, or when you no longer need a particular table. The SQL syntax to delete a table is DROP TABLE followed by the tablename. In my case, after I've created the ADMINISTRATOR.PERSON table, I can delete it again with the DROP TABLE ADMINISTRATOR.PERSON command. However, instead of forcing the user to type the literal name of the database table, it would be easier to select one of the existing tables.

To fill a TListBox or TComboBox with the list of existing tablenames, you can call the GetTableNames method. Place a TComboBox (with Style set to DropDownList) and two TButton components on a new page of the form, as Figure G shows:


Figure G. Delete/Drop Table
Delete/Drop Table

The Refresh button refreshes the list of tables in the cbTable combobox, and its OnClick event handler is implemented as follows:

procedure TForm1.btnRefreshClick(Sender: TObject);
begin
SQLConnection1.GetTableNames(cbTables.Items, False)
end;

Then use the Drop Table button to drop the table from the database, by adding the selected tablename to the "DROP TABLE " string, as follows:

procedure TForm1.btnDropTableClick(Sender: TObject);
begin
try
if cbTables.ItemIndex >= 0 then
SQLConnection1.ExecuteDirect('DROP TABLE ' +
cbTables.Items[cbTables.ItemIndex]);
SQLConnection1.GetTableNames(cbTables.Items, False) // Refresh
except
on E: Exception do
ShowMessage(E.Message)
end
end;

Note that after the ExecuteDirect, you need to call the GetTableNames again to refresh the contents of the ComboBox (if dropping the table failed, then you won't get there anyway, and the exception will be caught and shown).

With these two buttons and the drop-down ComboBox, you can retrieve the list of tablenames from the IBM DB2 UDB database you're connected to, and drop the selected table at will.



Back to top


Summary

In this article, I examined the design and implementation of a Delphi application you can use to create or delete/drop IBM DB2 UDB tables inside IBM DB2 databases. I showed several examples of sending SQL DDL to the DB2 DBMS using Delphi and the cross-platform dbExpress data access technologies.

I've included full source code for the Delphi dbExpress project, and it runs fine on Windows, as well as .NET (and you can migrate it to Kylix on Linux without problems).



Back to top


Next time

Next time I'll continue with an article on the use of SQL queries to build complex queries such as joins, inner joins, master-detail relations, and updates on these joins. After that, in a third and concluding article, I'll focus on IBM DB2 stored procedures and show what they can do and how they work, and demonstrate the stored procedures in the IBM DB2 UDB DEMO database I've built so far.




Back to top


0 Comments: