January 19, 2013

Using ADO to display database schema

In this article, I will show how to get the database schema and display it in a tree view using the ADO components and Delphi (This applies to other languages as well).

ADO stands for ActiveX Data Object and is a software layer that allows programmers a uniform and comprehensive way of developing applications that can access almost any datastore. ADO is actually using MDAC, Microsoft Data Access Components.

In Delphi, ADO is seen as a set of components named "dbGO". They exist in Delphi since Delphi 5.

Using ADO components, you can access database using MDAC, OLE-DB and ODBC. That is almost all well-known databases such as Oracle, Microsoft SQL Server, Access, InterBase, FireBird and many more.

Within Delphi, it is very easy to use ADO components. They are fully integrated in the VCL and all data-aware components. Delphi online documentation (http://docwiki.embarcadero.com/RADStudio/XE3/en/Working_with_ADO_components_Index) explains how to use those components.

Accessing the database schema

A database schema contain the complete description of a database: tables, fields, columns, indices and more. In most applications, you don’t need to worry about the schema because you already know what’s in your database. You probably created it for your application and you know all the tables, fields, columns you have to access to make your application do what it has to do!

But sometimes, you don’t know the database. For example if you are writing a query tool, or if you are writing a code generator, you have to discover what’s in the database. In such software you need to access the schema.

Discovering the tables

Getting the list of tables of a given database is quite easy. You need a TAdoConnection component to connect to the database and the call GetTableNames to have a string list filled with all table names, with or without system tables according to the last argument.

The code looks like this:

  Tables := TStringList.Create;
  AdoConnection1.Connected := TRUE;
  ADOConnection1.GetTableNames(Tables, FALSE);
  Memo1.Lines := Tables;

The code above assumes that you have used the object inspector to setup a connection string to connect to your database. A connection string can be built and tested using the object inspector by clicking on the ellipsis at the right of the ConnectionString property.
Once you get the tables, you can get the field names using a similar call. GetFieldNames will take a table name and a string list and will fill the string list with the table’s field names. The code looks like this:

  FieldNames := TStringList.Create;
  AdoConnection1.Connected := TRUE;
  ADOConnection1.GetFieldNames('Company', FieldNames);
  Memo1.Lines := FieldNames;

The above code is really simple, isn’t it? But there is a problem: you still don’t know each field data type.

To get hand on the data types and other attributes of each field, we have to use slightly more complex code:

  Fields : Recordset;
  FieldLen : Integer;
  Flags : Integer;
  DataType : DataTypeEnum;
  Fields := ADOConnection1.ConnectionObject.OpenSchema(
                     VarArrayOf([Null, Null, ‘Company’]),
  while not Fields.EOF do begin
    DataType := Fields.Fields['DATA_TYPE'].Value;
    FieldLen := StrToIntDef(VarToStr(
    Fields.Fields['CHARACTER_MAXIMUM_LENGTH'].Value), 0);
    Flags := StrToIntDef(VarToStr(
    Fields.Fields['COLUMN_FLAGS'].Value), 0);

This code deserves some explanations.

RecordSet is a data type defined in ADOInt unit. As its name implies, it represents a collection of records, like the result set of a query, each record is made of Fields and each fields has a value.

DataTypeEnum is actually an integer. And there is a bunch of constants identifying the data types supported by ADO. You find constants like adInteger and adChar. See ADOInt unit for all definitions.

ConnectionObject is a property of the TAdoConnection object. It is an interface of the underlying ActiveX and it represents the connection to the database. ConnectionObject interface has a method OpenSchema which will create a RecordSet containing a subset of the database schema.

The subset used here is identified by the constant adSchemaColumns which as you can guess represents the columns of a table.

The second parameter, which is Criteria, the sample code passes in an array that limits the returned information to just ‘Company’ table columns. To achieve this result, the array specifies a nonempty value for the TABLE_NAME, which is the third constraint available for adSchemaColumns. (adSchemaColumns has four available constraints: TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, and COLUMN_NAME).

The third parameter is not used here could be a SchemaId.

The RecordSet returned by OpenSchema contains a record per field, with several columns describing the corresponding field. Fields are named “DATA_TYPE”, “COLUMN_NAME” and similar. See Microsoft documentation for further reference: http://msdn.microsoft.com/en-us/library/ee266174(v=bts.10).aspx

There is nothing special in accessing the columns except the value is a variant which could be empty. This is why I wrote lines such as:

 Flags := StrToIntDef(VarToStr(Fields.Fields['COLUMN_FLAGS'].Value), 0);

Fields.Fields['COLUMN_FLAGS'].Value returns the variant, VarToStr convert it to a string, possibly and empty string, and StrToIntDef convert the string to an integer defaulting to zero in case the string doesn’t represents an integer (for example when empty).

An example application

To better illustrate the use of all those functions, I wrote a little sample program which connects to a database and fetch the schema, displaying the result in a TTreeView component.

I kept the user interface fairly simple (See screen dump below). To avoid a long load time, I have loaded the list of tables when the user clicks on the “Load” button. I load the columns data when the user actually expand the table node, and only on the first click. This is really fast, even if the database is large.

I maintain the information related to each tree node into an object attached to each node. The code handles the node according to the attached object type.

Since handling a TTreeView is not the subject of this article, I won’t further describe the code. Interested people can download the full project from my website:  http://www.overbyte.be/arch/Using_ADO_to_display_database_schema.zip

In the next article, I plan to apply this code to a code generator, AKA “Wizard”, which will create a form according to the structure of a database table. Stay tuned!

Source code

The actual complete source code is available from my website here
The article is here.

Suggested readings

Using a custom attribute for data persistence
Writing an iterator for a container
Original method to iterate thru the bits of an integer
Adding properties to a set
Internet Component Suite (ICS)
MidWare multi-tier framework

Follow me on Twitter

No comments: