Migreer je Data met Delphi 2007 en DBX4

Van de BDE (of SQL Links) naar een SQL DBMS met Delphi 2007 en DBX4

Een groot deel van de code in dit artikel werkt met alle Win32 versies van Delphi sinds Delphi 6. Toch zal ik van de meest recente versie Delphi 2007 gebruik maken, en hier en daar melding maken van nieuwe features en mogelijkheden.

Zoals (hopelijk) bekend, is de Borland Database Engine (BDE) een bevroren data access technologie die vanaf Delphi 1 beschikbaar was, maar vanaf Delphi 7 het predicaat “frozen” heeft meegekregen, en waar geen onderhoud meer op gepleegd wordt. Voor de grote broer SQL Links (de BDE uitbreiding om met SQL Server databases te werken) geldt zelfs de toevoeging “deprecated”, en SQL Links wordt ook niet meer geleverd bij versies van Delphi 7 of hoger, waarbij moet worden opgemerkt dat SQL Links nog wel gebruikt kan worden door een Delphi met een versie hoger dan 7 als de BDE/SQL Links op de ontwikkelmachine aanwezig is … maar het gebruik is daarbij volledig “op eigen risico”.

Al vanaf Delphi 6 heeft Borland dbExpress aangemerkt als de data access architectuur die de voorkeur heeft boven de BDE en SQL Links

Al vanaf Delphi 6 (en Kylix) heeft Borland dbExpress aangemerkt als de data access architectuur die de voorkeur heeft boven de BDE en SQL Links. Tot nu toe was er nog niet echt haast geboden om van de BDE over stappen (het zit er immers nog altijd in), maar sinds vorig jaar is de laatste BDE engineer van Borland niet meer werkzaam bij CodeGear. En als straks voor Delphi 2008 de stap naar volledige UNICODE wordt gezet, is het nog maar de vraag of de BDE dit zal meemaken. Om niet voor verrassingen te komen staan zal ik in dit artikel aangeven hoe we BDE aliases kunnen analyseren, hoe we SQL kunnen genereren om vergelijkbare tabellen in SQL DBMSen te creëren, hoe we data uit BDE tabellen kunnen overpompen naar deze SQL DBMSen, en tot slot hoe we met DBX4 deze data uit de SQL DBMSen kunnen bewerken.

BDE Analyse

Laten we beginnen met een kleine GUI om alle mogelijke BDE alias strings op te halen die op een machine aanwezig zijn, en voor een gekozen alias alle tabellen op te sommen die daarbinnen te vinden zijn. Hier hebben we twee TComboBox componenten voor nodig, genaamd cbDatabases en cbTables. Het vullen van de cbDatabases met de aanwezige alias strings gaat met behulp van een TSession component als volgt, bijvoorbeeld in de FormCreate:

procedure TFormSDE.FormCreate(Sender: TObject);
begin
  Session1.GetDatabaseNames(cbDatabases.Items);
end;

Hierdoor zal de dbDatabases TCmboBox gevuld worden met de aanwezige BDE alias strings. Als we eenmaal een alias gekozen hebben, kunnen we aan een TDatabase component vragen welke tables er allemaal bekend zijn voor deze alias. Dat gaat met de GetTableNames functie, en doe ik bij voorkeur in de OnChange event van de cbDatabases combobox, zodat je daarmee meteen de cbTables combobox kunt vullen:

procedure TFormSDE.cbDatabasesChange(Sender: TObject);
begin
  Database1.Close;
  Database1.DatabaseName :=
    cbDatabases.Items[cbDatabases.ItemIndex];
  Database1.GetTableNames(cbTables.Items,
    cbSystemTables.Checked);
end;

Eenzelfde truc kunnen we uithalen in de OnChange event van de cbTables combobox, waarbij we de dan gekozen tablename kunnen gebruiken om deze tabel te analyseren.

procedure TFormSDE.cbTablesChange(Sender: TObject);
begin
  Table1.CLose;
  Table1.DatabaseName := Database1.DatabaseName;
  Table1.TableName :=
    cbTables.Items[cbTables.ItemIndex];
end;

Uitgaande van een BDE TTable waarvan de DatabaseName en TableName zijn opgegeven, kunnen we alle benodigde informatie ophalen uit de FieldDefs en IndexDefs collecties. Deze informatie wil ik gaan gebruiken om een SQL CREATE TABLE command te produceren dat de structuur van de tabel kan reproduceren in een SQL DBMS. Het voordeel is dat we de inhoud van dit SQL CREATE TABLE command kunnen opslaan in een script en (later) op een andere machine kunnen draaien.

Bij het fieldtype zullen we een voor een DBMS specifieke waarde moeten kiezen

Een probleem van deze benadering is dat de SQL CREATE TABLE voor ieder DBMS type een iets andere inhoud zal hebben, omdat de veld types niet altijd hetzelfde zullen zijn. Zo kent SQL Server bijvoorbeeld de veld types IMAGE en TEXT, terwijl Blackfish SQL die niet kent. En een floating point getal wordt door het ene DBMS als DOUBLE aangegeven, terwijl het andere een FLOAT wil zien. In dit artikel zal ik voor SQL Server en Blackfish SQL een aantal field mappings aangeven, maar laat het aan de lezer over om voor zijn specifeke DBMS de mapping compleet te maken (zie dan de documentatie van de DBMS voor de benodigde details).

SQL CREATE TABLE

De basis syntax van het SQL CREATE TABLE command is kort gezegd als volgt:

CREATE TABLE (
   [size] [[NOT] NULL] )

Hierbij kun je een of meerdere velden opgeven. De size is optioneel (en wordt voornamelijk bij CHAR of VARCHAR velden gebruikt), evenals de NULL of NOT NULL (default is NULL). Zoals eerder aangegeven zullen we bij het fieldtype een voor een DBMS specifieke waarde moeten kiezen, maar de rest DBMS is onafhankelijk.

Als we het fieldtype nog even buiten beschouwing laten, dan kan het DBMS onafhankelijke deel van de SQL CREATE TABLE command met een functie Table2SQL als volgt geproduceerd worden:

function Table2SQL(const Table: TTable): String;
var
  i: Integer;
  Str: String;
begin
  Table.Open;
  Table.FieldDefs.Update;
  Table.IndexDefs.Update;
  try
    Str := 'CREATE TABLE ' +
      ChangeFileExt(Table.TableName, '') + '(';
    for i:=0 to Table.Fields.Count-1 do
    begin
      Str := Str +
        '"' + Table.Fields[i].DisplayName +'" ';
      // fieldtype
      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;

Merk op dat de FieldDefs.Update en IndexDefs.Update meestal niet meer nodig zijn als de Table reeds geopend is (en zonder dat de Table geopend is levert FieldDefs.Update toch niet de gewenste informatie op).

Voor iedere DBMS die we willen ondersteunen bij de migratie, moeten we nu een eigen field mapping schrijven van het BDE/SQL Links type naar het specifieke DBMS type.

Field Mapping

Voor SQL Server en Blackfish SQL heb ik de volgende field mappings gevonden, waarbij ik aan de BDE/SQL Links kant uitga van de waarde van de Table.FieldDefs[i].FieldClass en aan de DBMS kant het corresponderende type heb gekozen:

Deze lijst is niet compleet, maar is voldoende om van de DBDEMOS BDE alias alle tabellen om te zetten naar SQL Server of Blackfish SQL, en daar ging het me in eerste instantie om.

Het stukje ontbrekende code in de voorgaande listing kan dan ook als volgt worden aangevuld voor SQL Server (met de strings voor Blackfish SQL in commentaar daarnaast aangegeven):

if Table.FieldDefs[i].FieldClass = TStringField then
  Str := Str + ' VARCHAR(' +
    IntToStr(Table.FieldDefs[i].Size) + ')'
else
if Table.FieldDefs[i].FieldClass = TSmallIntField then
  Str := Str + ' SMALLINT '
else
if Table.FieldDefs[i].FieldClass = TIntegerField then
  Str := Str + ' INTEGER '
else
if Table.FieldDefs[i].FieldClass = TDAteTimeField then
  Str := Str + ' DATETIME ' // TIMESTAMP '
else
if Table.FieldDefs[i].FieldClass = TMemoField then
  Str := Str + ' TEXT ' // OBJECT '
else
if Table.FieldDefs[i].FieldClass = TGraphicField then
  Str := Str + ' IMAGE ' // OBJECT  '
else
if Table.FieldDefs[i].FieldClass = TBLobField then
  Str := Str + ' IMAGE ' // OBJECT '
else
if Table.FieldDefs[i].FieldClass = TFloatField then
  Str := Str + ' FLOAT ' // DOUBLE '
else
  raise Exception.Create('Unsupported field type ' +
    Table.FieldDefs[i].FieldClass.ClassName);

Let op de raise Exception.Create aan het eind die ons zal vertellen wanneer er een ander field type is gevonden, zodat we dan daarvoor ook op zoek moeten naar een bijbehorende SQL mapping.

Voor de Country tabel uit de DBDEMOS alias, is hiermee de volgende CREATE TABLE geproduceerd:

CREATE TABLE country("Name"  VARCHAR(24) NULL,
  "Capital"  VARCHAR(24) NULL,
  "Continent"  VARCHAR(24) NULL,
  "Area"  FLOAT  NULL,
  "Population"  FLOAT  NULL)

We moeten nu alleen nog de data zelf meenemen, wat de laatste stap is in dit migratieverhaal.

Copy Copy

En dan nu de laatste stap: het overpompen van de data uit de BDE/SQL Links tabel naar de SQL DBMS. Dat kan door gebruik te maken van een aantal DBX4 componenten die de BDE records eerst inlezen in een TClientDataSet, en daarna alles naar het SQL DBMS sturen.

Allereerst is daar de dbExpress SQLConnection component die een verbinding met het SQL DBMS moet maken: hiertoe moet je de juiste connection properties opgeven. Dan is er een SQLDataSet die via zijn SQLConnection property aan de SQLConnection component verbonden is, en een SELECT * FROM uitvoert op de betreffende (nog lege) tabel die we eerder met het CREATE TABLE command hebben aangemaakt. Een TDataSetProvider verbonden met de SQLDataSet en een TClientDataSet verbonden met de DataSetProvider maken het plaatje compleet.

En dan nu de laatste stap: het overpompen van de data uit de BDE/SQL Links tabel naar de SQL DBMS

Bij het kopiëren hoeven we nu alleen nog maar door de BDE/SQL Links TTable heen te lopen en per record een Append te doen in de ClientDataSet. Met Append komt ieder volgend record achter het vorige, zodat we dezelfde volgorde aanhouden als in de BDE table.

Vervolgens kunnen we van ieder veld de waarde kopiëren door de Fields[i].Value te gebruiken. Wat nog iets beter is (ook voor memo velden) is om Assign te gebruiken om de waarde van de BDE velden toe te kennen aan die van de DBX4 velden. Hierbij ga ik wel uit van het feit dat beide tabellen de velden in exact dezelfde volgorde hebben staan, zodat we niet via FieldByName hoeven te zoeken, maar kunnen volstaan met de mapping van veld i uit de BDE tabel naar veld i van de SQL DBMS tabel.

Omdat de SQLDataSet met een SELECT * FROM werd gestart, zal bij de aanroep van ApplyUpdates van de ClientDataSet automatisch een bijbehorende SQL INSERT worden gegenereerd.

procedure TFormSDE.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;

In de code van de laatste listing wordt deze ApplyUpdates voor ieder record aangeroepen (direct na de Post), maar dit kun je ook na iedere X records doen om het proces een klein beetje te versnellen.

Op het eind van de code laat ik de ClientDataSet op Active staan. Dat doe ik met opzet, zodat een eventueel DBGrid op het conversie form dan meteen de inhoud kan laten zien, zoals in onderstaand figuur:

Conclusie

In dit artikel heb ik laten zien hoe we vanuit een BDE/SQL Links een SQL CREATE TABLE command kunnen produceren, en dit kunnen gebruiken om een nieuwe vergelijkbare tabel in een SQL DBMS te maken, gevolgd door het kopiëren van de data uit de BDE tabel naar de corresponderende tabel in het SQL DBMS. Hiermee kan op eenvoudige wijze BDE data naar een SQL DBMS gemigreerd worden.