Delphi Clinic C++Builder Gate Training & Consultancy Delphi Notes Weblog Dr.Bob's Webshop
Dr.Bob's Delphi Notes Dr.Bob's Delphi Clinics Dr.Bob's Delphi Courseware Manuals
 Dr.Bob Examines... #98
See Also: Dr.Bob's Delphi Papers and Columns

Migrating Data from TDataSet to SQL DBMS
In this article, I will describe how to use Blackfish SQL, and especially how to migrate BDE data from your dBASE or Paradox tables to a new Blackfish SQL database.
Blackfish SQL is a managed SQL-compliant embedded database that can be deployed with our Delphi applications.There are three different Blackfish SQL for Windows connection drivers: DBXClient – a Win32 DBX4 driver for Delphi and C++Builder to connect to a remote Blackfish SQL database, and as second a Local ADO.NET 2.0 Provider (for a local Blackfish SQL database – the driver and the Blackfish database kernel run in-process), and as third a Remote ADO.NET 2.0 Provider, where you can make a .NET remote connection to a Blackfish SQL database.You can also deploy Blackfish SQL as .NET assembly on a web server for ASP.NET applications.
Because Blackfish SQL is a managed database, written in C# (as direct translation of the Java implementation of JDataStore), the deployment consists of one of the .NET assembly that can be found in the C:\Program Files\Common Files\CodeGear Shared\RAD Studio\Shared Assemblies\5.0 directory, and can be one of the following:

CodeGear RAD Studio 2007 Professional comes with an unlimited Blackfish SQL desktop database deployment license for systems with 1 CPU, 1 user or 4 connections, for databases that have a maximum size of 512 MB.
CodeGear RAD Studio 2007 Enterprise and Architect include an unlimited Blackfish SQL database deployment license for systems with 1 CPU, a maximum of 5 users or 20 connections and databases that can become 2GB in size.
In all cases, it’s possible to purchase additional deployment licenses (for more users, connections, CPUs or bigger database) – contact your local reseller for more details (I’m a reseller for the BeNeLux only).

Blackfish SQL uses the .jds file extension and the same database format as JDataStore, which means that Blackfish SQL the final version is what originally started as NDataStore – the .NET port of JDataStore.The databases can be used in Java as well as .NET.

Blackfish SQL Usage
Using Blackfish SQL databases can be done using the DBX4 driver for Win32 as well as .NET applications (and also from Java, but that’s another story).Just place a TSQLConnection component on your form or data module, right-click on it, and then you can start to specify the Blackfish SQL database properties.

As you see, the default User_Name and Password values are the same as for InterBase, and by default Blackfish SQL uses port 2508 to communicate.
There is also a very helpful create attribute, which can be used to create a new, empty Blackfish SQL database.No more tinkering with an external DBMS tool: you only have to set create to true, and if the database file (specified in Database attribute) doesn’t exist, then it will automatically be created.This is also very useful for application deployment, as you no longer need to deploy an empty database with your application.Note that the create attribute itself is case sensitive (like all Blackfish SQL connection properties).

Data Migration
Once you have a new, empty Blackfish SQL database you can create tables in that database.However, apart from just creating new tables, we could also use the opportunity to migrate existing tables in the BDE format (dBASE, Paradox or FoxPro) to the Blackfish SQL database.Since the BDE is frozen (and SQL Links even deprecated), this doesn’t sound like a bad idea to me at all.Especially considering the next release of Delphi for Win32, which will support Unicode (and it’s unlikely that the BDE will ever support Unicode data).

BDE Analysis
In order to start the migration from BDE data, we first need to write a little BDE analysis code, for example to retrieve all BDE alias strings on a given machine, and for a given alias, to return the tables that are available for that alias.
For this, we need two TComboBox components: cbDatabases and cbTables.Filling the cbDatabases with the list of available aliases can be done using a TSession component in the FormCreate, as shown on the next page. 

  procedure TFormDBX4.FormCreate(Sender: TObject);
  begin
    Session1.GetDatabaseNames(cbDatabases.Items);
  end;
This will fill the cbDatabases TComboBox with the available BDE alias strings.Once we’ve selected an alias, we can ask the TDatabase component which tables exist for this alias.This can be done using the GetTableNames function, for example inside the OnChange event of the cbDatabases combobox: 
  procedure TFormDBX4.cbDatabasesChange(Sender: TObject);
  begin
    Database1.Close;
    Database1.DatabaseName := cbDatabases.Items[cbDatabases.ItemIndex];
    Database1.GetTableNames(cbTables.Items, cbSystemTables.Checked);
  end;
We can perform a similar trick in the OnChange event of the cbTables combobox, extracting the table name to analyse: 
  procedure TFormDBX4.cbTablesChange(Sender: TObject);
  begin
    Table1.Close;
    Table1.DatabaseName := Database1.DatabaseName;
    Table1.TableName := cbTables.Items[cbTables.ItemIndex];
  end;
Unfortunately, the tablename from this list is just a name (like “animals” or “biolife”) but without the extension.And since the BDE supports different kinds of tables, we need to determine the table type before we can continue.This can be done by assigning ttDBase, ttParadox or ttDefault to the TableType property of the BDE TTable component, and for each type trying to open the table, as follows: 
  procedure TFormDBX4.cbTablesChange(Sender: TObject);
  begin
    Table1.Close;
    Table1.DatabaseName := Database1.DatabaseName;
    Table1.TableName := cbTables.Items[cbTables.ItemIndex];
    Table1.TableType := ttParadox;
    try
      Table1.Open;
    except
      Table1.TableType := ttDBase;
      try
        Table1.Open;
      except
        Table1.TableType := ttFoxPro;
        try
          Table1.Open
        except
          Table1.TableType := ttDefault
        end
      end
    end
  end;
Now that we have the table name and type of a BDE table, we can retrieve the internal information (meta data) using the FieldDefs and IndexDefs collections.With this information, I will generate an SQL CREATE TABLE command to reproduce the structure of the BDE table in a real SQL DBMS.The advantage of this approach is that we can store the SQL command itself in a script to be executed later (when needed).
A disadvantage of this approach is the fact that the SQL CREATE TABLE will look (slightly) different for each different DBMS, mainly caused bythe different field types that are supported by the different DBMSs.
SQL Server for example supports the field types IMAGE and TEXT while Blackfish SQL doesn’t recognise these (we have to use VARBINARY for the IMAGE and VARCHAR for the TEXT).And a floating point number is denoted by one DBMS as a DOUBLE, while the other wants to see a FLOAT.
In this section, I will use Microsoft SQL Server as well as CodeGear Blackfish SQL as two example databases to migrate our BDE data to.I leave it as exercise for the readers to extend this with other DBMS types (feel free to e-mail me with a list of field mappings or if you need help).

SQL CREATE TABLE
The general syntax of the SQL CREATE TABLE command is as follows: 

  CREATE TABLE <tablename>
    ( <fieldname> <fieldtype> [size]  [[NOT] NULL] )
where we can add one or more fields.The size attribute is optional, and mainly used by CHAR or VARCHAR fields, just like the NULL or NOT NULL part (default is NULL).For each type, we have to specify a DBMS-specific SQL type, but the remainder of the SQL CREATE TABLE command is DBMS independent.
Skipping the field type issue for now, we can produce the SQL CREATE TABLE skeleton as follows (passing a TDataSet, so we can also use this on ADO or TClientDataSets for example, and are not limited to BDE tables): 
  function DataSet2SQL(const Table: TDataSet; const TableName: String): String;
  var
    i: Integer;
    Str: String;
  begin
    Table.Open;
    try
      Str := 'CREATE TABLE ' + ChangeFileExt(TableName,'') + '(';
      for i:=0 to Table.Fields.Count-1 do
      begin
        Str := Str + '"' + Table.Fields[i].DisplayName + '" ';
        if Table.FieldDefs[i].FieldClass = TStringField then
          Str := Str + ' VARCHAR(' + IntToStr(Table.FieldDefs[i].Size) + ')'
        else
        begin
          ...          // other field types
        end;
        if Table.FieldDefs[i].Required then Str := Str + ' NOT';
        Str := Str + ' NULL';
        if (i < Table.Fields.Count-1) then Str := Str + ', ';
      end;
      Str := Str + ')';
      Result := Str;
    finally
      Table.Close;
    end
  end;
Note that we can use the FieldDefs array to find the FieldClass, and we need to map the native Delphi TField type to a SQL type.For the TStringField, I’ve already hardcoded the solution as a VARCHAR with a specific length (found in the Size property of the FieldDefs array).
Also note that while VARCHAR works fine for now, we could also use this migration as a good moment to start preparing for Unicode by defining the VARCHAR as a NVARCHAR field (but this is only true for SQL Server).
For each DBMS that you want to support, you need to write a mapping between the TField type and the SQL type string (for that DBMS).

Field Mapping
For SQL Server and Blackfish SQL, I’ve found the following field mapping table, mapping the Table.FieldDefs[i].FieldClass to the DBMS specific SQL type:

Although this list is not complete, it’s good enough to convert the DBDEMOS BDE alias from dBASE and Paradox to SQL Server or Blackfish SQL. The table with the field mappings can be translated to a Delphi data source, and a new implementation for the DataSet2SQL function as follows: 
  unit DS2SQL;
  // (c) 2008 by Bob Swart Training & Consultancy - Freeware "as-is"
  // Suggestions for Field Mappings for other DBMS types are welcome
  // Send feedback by e-mail to Bob@eBob42.com - thanks in advance!!
  interface
  uses
    SysUtils, DB;

  type
    TDatabase = (dbSQLServer, dbBlackfishSQL);

  function DataSet2SQL(const Table: TDataSet; const TableName: String;
    Database: TDatabase = dbSQLServer): String;

  implementation

  function DataSet2SQL(const Table: TDataSet; const TableName: String;
    Database: TDatabase = dbSQLServer): String;

  type
    TFieldMapping = record
      FieldClass: TFieldClass;
      SQL: Array[TDatabase] of String;
    end;

  const
    Fields = 13;
    FieldMappings: Array[0..Fields-1] of TFieldMapping =
      ((FieldClass: TStringField;   SQL: (' VARCHAR(', ' VARCHAR(')), // + size
       (FieldClass: TIntegerField;  SQL: (' INT ', ' INTEGER ')),
       (FieldClass: TAutoIncField;  SQL: (' INT ', ' INTEGER ')),
       (FieldClass: TSmallIntField; SQL: (' SMALLINT ', ' SMALLINT ')),
       (FieldClass: TFloatField; SQL:    (' FLOAT ', ' DOUBLE ')),
       (FieldClass: TDateTimeField; SQL: (' DATETIME ', ' TIMESTAMP ')),
       (FieldClass: TDateField; SQL: (' DATETIME ', ' DATE ')),
       (FieldClass: TTimeField; SQL: (' DATETIME ', ' TIME ')),
       (FieldClass: TCurrencyField; SQL: (' DECIMAL ', ' DECIMAL ')),
       (FieldClass: TBooleanField; SQL: (' BIT ', ' BOOLEAN ')),
       (FieldClass: TMemoField; SQL: (' TEXT ', ' VARCHAR ')),
       (FieldClass: TGraphicField; SQL: (' IMAGE ', ' VARBINARY ')),
       (FieldClass: TBlobField; SQL: (' IMAGE ', ' VARBINARY '))
      );

  var
    i: Integer;
    Str: String;
    FieldMapping: TFieldMapping;
    found: Boolean;

  begin
    Table.Open;
    try
      Str := 'CREATE TABLE ' + ChangeFileExt(TableName,'') + '(';
      for i:=0 to Table.Fields.Count-1 do
      begin
        Str := Str + '"' + Table.Fields[i].DisplayName + '" ';
        if Table.FieldDefs[i].FieldClass = TStringField then
          Str := Str + ' VARCHAR(' + IntToStr(Table.FieldDefs[i].Size) + ')'
        else
        begin
          found := False;
          for FieldMapping in FieldMappings do if not found then
          begin
            if Table.FieldDefs[i].FieldClass = FieldMapping.FieldClass then
            begin
              Str := Str + FieldMapping.SQL[Database];
              found := true
            end
          end;

          if not found then
            raise Exception.Create('Unsupported field type ' +
              Table.FieldDefs[i].FieldClass.ClassName)

        end;
        if Table.FieldDefs[i].Required then Str := Str + ' NOT';
        Str := Str + ' NULL';
        if (i < Table.Fields.Count-1) then Str := Str + ', ';
      end;

      Str := Str + ')';
      Result := Str;
    finally
      Table.Close;
    end
  end;

  end.
Note that raise Exception.Create at the end of the for-loop that walks through the FieldMappings, to raise an exception in order to tell us that an unsupported field type has been found.Which will then have to be added to the TFieldMappings data structure, which is left as exercises as mentioned earlier. Using the Country table from the DBDEMOS alias, we will get the following CREATE TABLE command: 
  CREATE TABLE country("Name" VARCHAR(24) NULL, "Capital" VARCHAR(24) NULL,
    "Continent" VARCHAR(24) NULL, "Area" FLOAT NULL, "Population" FLOAT NULL)
Finally note that instead of placing the field names in double quotes, we could also place them in square brackets. In my example BDE to DBX4 conversion application, the resulting SQL CREATE TABLE command is first placed in a TMemo component (called mSQL), so we can make manual modifications (when needed), and with the click on another button can execute the SQL using the ExecuteDirect method of the TSQLConnection component. 
  SQLConnection.ExecuteDirect(mSQL.Text);

Wrap up
The final step of migrating BDE data to Blackfish SQL or SQL Server consists of actually copying the data from the BDE table to the new SQL database.For this, we need to use a couple of DBX4 components, reading the BDE records in a TClientDataSet and then sending them to the SQL DBMS using calls to the ApplyUpdates method.
In source code, this can be implemented as follows (assuming the corresponding BDE table is already created in the SQL DBMS): 

  procedure TFormDBX4.btnCopyClick(Sender: TObject);
  var
    i: Integer;
  begin
    ClientDataSet1.Close;
    Table1.Open;
    SQlDataSet1.CommandText :=
     'SELECT * FROM ' + ChangeFileExt(Table1.TableName,'');
    ClientDataSet1.Active := True;
    while not Table1.Eof do
    begin
      ClientDataSet1.Append;
      for i:=0 to Table1.Fields.Count-1 do
        ClientDataSet1.Fields[i].Assign(Table1.Fields[i]);
      ClientDataSet1.Post;
      ClientDataSet1.ApplyUpdates(0);
      Table1.Next;
    end;
    Table1.Close;
  end;
I’ve used this to migrate the complete DBDEMOS set of tables to a SQL Server and Blackfish SQL database.Feel free to extend it for our own uses.

Summary
In this article, I’ve demonstrated how to migrate data from VCL datasets (like BDE tables, but from any VCL TDataSet, so also from TClientDataSets) to an SQL DBMS using CREATE TABLE commands and DBX4 to generate the INSERT commands.The resulting code has been used on a number of BDE table collections, including the DBDEMOS alias that ships with Delphi.
This article is an abstract from my 132-page book “Delphi Win32 VCL Database Development” which can be purchased for 24 Euro at Lulu.com in printed format.

This webpage © 2008-2011 by Bob Swart (aka Dr.Bob - www.drbob42.com).All Rights Reserved.