|Delphi Clinic||C++Builder Gate||Training & Consultancy||Delphi Notes Weblog||Dr.Bob's Webshop|
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:
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).
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).
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).
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.
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).
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:
|BDE / SQL Links||SQL Server||Blackfish SQL|
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.
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.
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.