menu
  Home  ==>  papers  ==>  db  ==>  bde_to_unidac_migration   

BDE To Unidac Migration - Felix John COLIBRI.

1 - Migrating BDE applications

We just migrated several applications using the BDE to the Unidac database access components.

This document presents

  • the steps we used
  • how to use the Devart Unidac migration wizard
  • the differences between the BDE and the UniDac data access component suites
  • how to use dUnit to check that the migration did not modify the application behaviour


2 - BDE to Unidac Migration Using the Wizard

2.1 - The UniDAC BDE conversion Wizard

Using the Wizard is simple and easy.

First use or create some project with BDE data access components :
   create a project with a tDataModule containing
  • a BDE tDatabase initialized to connect to some server
  • a BDE tQuery with some SELECT
Connect tDatabase1 and open Query1

simple_datamodule


Here are the steps to convert this project to Unidac
   on the main menu, open the UniDAC menu and select the "Migration Wizard"

unidac_menu

   the UniDAC conversion Wizard is opened

unidac_conversion_wizard

   click "Next"

   the file location dialog is displayed

file_location_selection

   select "Current Project" and click "Next"

   the list of units is presented

file_confirmation

   Check the files you want to migrate abd click "Next"

   UniDAC offers saving the original and generating a log

backup_before_migration_and_log

   click "Next"

   a summary before conversion is presented

summary_before_conversion

   click "Finish"

   UniDAC asks to confirm the copy of the original

conversion_confirmation

   click "Yes"

   the files will be saved in a local RBackup\ folder, with
  • the files with a ".~PAS" or ".~DFM" extension
  • a Rexpert.REU is an ASCII file containing the original location of each file
NotePad.Exe is opened and a conversion log is presented

conversion_log

An end of conversion dialog is presented

end_of_conversion

   click "Ok"

   hit F9 to RUN the project

   an error is detected :

provider_is_not_defined

   on the Designer select Database1 and double click this component to open the connection dialog

   this dialog allows to input the ProviderName and the DSN

connection_dialog

   in our case we used ODBC with a DSN "my_dsn". So we entered
  • ProviderName ODBC
  • Server my_dsn
  • Username uuu
  • Password ppp
we clicked "Connect" to check the connection and then "Ok"

Run the project again

   an exception tells us that we must include a tODBCUniProvider or add ODBCUniProvider to the datamodule Uses clause

odbc_provider

   from the Tool Palette, we select the tODBCUniProvider

provider_components

and drop it on DataModule1

   we can the connect to the database, open the query, and run the application


Therefore in most cases, you answer "Yes" to all, simply specify the Provider and Server, drop a tODBCUniProvider and the migration is finished.

When you have done it once, the second or third migration is the matter of a couple of minutes.



2.2 - Our application specifics

First of all, our example had the following configuration
  • the database was ORACLE RDB
  • the connection used an ODBC driver (not a native ORACLE driver)
  • to use this driver, an ODBC DSN had been defined using the ODBC Administrator : "Start | Control Panel | Administration Tools | ODBC Manager"

    The following figure displays the definition of such a DSN (not for ORACLE RDB, since the .ZIP of the demo uses Microsoft ACCESS, which is perhaps more common than ORACLE RDB)

    odbc_dsn_definition

  • a BDE alias was defined "delphi menu | Database | Explorer"

    bde_alias

    This "Bde Alias" references the "ODBC Dsn"



2.3 - How the BDE works

Our simple tDataModule contained a tDataBase and a tQuery. Everything was defined at design time, so the .DFM was:

 
Object DataModule1: TDataModule1
  OldCreateOrder = True
  Height = 248
  Width = 605
  Object Database1: TDatabase
    AliasName = 'my_bde_alias'
    DatabaseName = 'my_database'
    Params.Strings = ('USER NAME=my_user'
          'PASSWORD=my_password')
    SessionName = 'Default'
    LoginPrompt = False
    Connected = False
    Left = 40
    Top = 24
  End
  Object Query1: TQuery
    DatabaseName = 'my_database'
    SQL.Strings = ('SELECT *'
          '  FROM customer'
          '  WHERE customer_id< 35000')
    Left = 136
    Top = 24
  End
End

For our discussion we can remove the irrelevant Left, Top etc:

 
Object DataModule1: TDataModule1
  Object Database1: TDatabase
    AliasName = 'my_bde_alias'
    DatabaseName = 'my_database'
    Params.Strings = ('USER NAME=my_user'
          'PASSWORD=my_password')
    SessionName = 'Default'
    LoginPrompt = False
    Connected = False
  End
  Object Query1: TQuery
    DatabaseName = 'my_database'
    SQL.Strings = ('SELECT *'
          '  FROM customer'
          '  WHERE customer_id< 35000')
  End
End



Basically:

bde__database_archtecture_3



It works like this (thick red vertical line)

  • the tDatabase has an AliasName, 'my_alias'
  • this 'my_alias' string is used to lookup the alias record in the BDE IDAPI.CFG file
  • this entry has an "ODBC DSN" with a 'my_ora_dsn' name
  • the ODBC manager has a DSN entry for 'my_ora_dsn', which is used by the ODBC Oracle driver
  • the Oracle driver talks to the Oracle Client, which talks to the Oracle Server
On the tDataSet side (thick black horizontal line)
  • the tDatabase has a DataBaseName, 'my_base'
  • this automatically creates an entry in the DbTables.Session global variable, which contains couples of (DatabaseName, tDatabase reference). A simple lookup in this Session table will return the reference to the tDatabase with this DatabasName
  • the tQuery has also a DatabaseName which is then used to link the tQuery to its tDatabase


2.4 - The Wizard migration result

The wizard transformed our Datamodule into:

 
Object DataModule1: TDataModule1
  Object Database1: TUniConnection
    Username = 'my_user'
    Password = 'my_password'
    LoginPrompt = False
    Connected = False
  End
  Object Query1: TUniQuery
    Connection = DataModule1.Database1
    SQL.Strings = ('SELECT *'
          '  FROM customer'
          '  WHERE customer_id< 35000')
  End
End

We see that the transformation was

  • tDatabase -> tUniConnection
    • conversion of the User / Password Params tStrings into individual UserName and Password Strings
    • abrupt removal of the AliasName, DatabaseName and SessionName properties
  • tQuery -> tUniQuery
    • conversion of DatabaseName into a direct reference DataModule1.Database1


2.5 - Additional corrections

We also had to
  • use the tUniConnection connection dialog to add the ProviderName and the Server
  • add the tODBCUniProvider


The final .DFM became:

 
Object DataModule1: TDataModule1
  Object ODBCUniProvider1: TODBCUniProvider
  End
  Object Database1: TUniConnection
    ProviderName = 'ODBC'
    Server = 'my_alias'
    Username = 'my_user'
    Password = 'my_password'
    LoginPrompt = False
    Connected = False
  End
  Object Query1: TUniQuery
    Connection = Database1
    SQL.Strings = ('SELECT *'
          '  FROM customer'
          '  WHERE customer_id< 35000')
  End
End



2.6 - The Unidac architecture

The Unidac architecture is the following:

unidac_database_archtecture_2



So the organization is

  • the tUniConnection
    • specifies the Providername
    • specifies the ODBC DSN name
    • contains separate propertis for UserName and Password
  • the tUniQuery contains a direct reference to its tUniConnection


3 - Unidac Wizard Missing Features

In addition to the tUniConnection.ProviderName and Server, we also encountered other small problems, essentially the failure to convert the .DFM in case of tDataModule inheritance.

We therefore decided to investigate some current tDataModule configurations, with initialization by code (.PAS) or during design time (.DFM).

Here are our findings



3.1 - Initialization by code (.PAS)

Here is the original code:

Unit u_dm_simple_code;
  Interface

    Uses Windows, Messages, SysUtils, Classes, Graphics, 
      Controls, Forms, Db, DbCtrls, DBTables;

    Type
      TDataModule1 = Class(TDataModule)
        Query1: TQuery;
        Database1: TDatabase;
      Private
      Public
        Procedure connect_database;
        Procedure open_query;
      End;

    Var DataModule1: TDataModule1;

  Implementation

    {$R *.DFM}

    Procedure TDataModule1.connect_database;
      Begin
        With Database1 Do
        Begin
          Close;

          AliasName:= 'my_alias';
          DatabaseName:= 'my_database';
          Params.Add('USER NAME=my_user');
          Params.Add('PASSWORD=my_password');

          LoginPrompt:= False;
        End;
      End;

    Procedure TDataModule1.open_query;
      Begin
        With Query1 Do
        Begin
          Close;
          Sql.Clear;
 
          DatabaseName := 'my_database';
          SQL.Add('SELECT *');
          SQL.Add('  FROM customer');
          SQL.Add('  WHERE customer_id< 35000');
        End;
      End;

  End.

and the result of the wizard migration :

Unit u_dm_simple_code;
  Interface
    Uses Windows, Messages, SysUtils, Classes, Graphics, 
      Controls, Forms, Db, DbCtrls, 
      DBTables, Uni, DBAccess, MemDS;

    Type
      TDataModule1 = Class(TDataModule)
        Query1: TUniQuery;
        Database1: TUniConnection;
      Private
      Public
        Procedure connect_database;
        Procedure open_query;
      End;

// -- the end of the unit is unchanged (= not migrated)

So

  • Uni, DBAccess and MemDS were added to the Uses clause (we still have to add the ODBCUniProvider Unit
  • the data access component types were translated (tDatabase -> tUniConnection etc)
  • no code statement was touched


We had to modify the Implementation:

  Implementation
    Uses UniProvider, ODBCUniProvider;

    {$R *.DFM}

    Procedure TDataModule1.connect_database;
      Begin
        With Database1 Do
        Begin
          ProviderName := 'ODBC';
          Server := 'my_alias';

          Username := 'my_user';
          Password := 'my_password';

          LoginPrompt:= False;
        End;
      End;

    Procedure TDataModule1.open_query;
      Begin
        With Query1 Do
        Begin
          Close;
          Sql.Clear;

          Connection := Database1;

          SQL.Add('SELECT *');
          SQL.Add('  FROM customer');
          SQL.Add('  WHERE customer_id< 35000');
        End;
      End;

    End



It is possible that the Wizard did not change the implementation code because our example uses With. This is more difficult to translate but could have been done. To check this, we could build another sample including

  • a local tQuery
  • modification of the tDatabase.Params with a statement
  • a local creation of a tQuery

      With tQuery.Create(Nil) Do
    ...



3.2 - Separate tDatabase / tQueries datamodules

Next we used separate tDatamodules
  • for the tDatabase
  • for the different tQueries groups
This separation makes sense to factorize the connection in one module, and organize the queries according to the business domains (Sales, Manufacturing etc)



Here is the connection tDataModule .DFM

 
Object dm_database: Tdm_database
  Object Database1: TDatabase
    AliasName = 'my_alias'
    DatabaseName = 'my_database'
    Params.Strings = ('USER NAME=my_user'
          'PASSWORD=my_password')
    LoginPrompt = False
    SessionName = 'Default'
  End
End

and an example of a query tDataModule

 
Object dm_query: Tdm_query
  Object Query1: TQuery
    DatabaseName = 'my_database'
    SQL.Strings = ('SELECT *'
          '  FROM customer'
          '  WHERE customer_id< 35000')
  End
End



Here is the result

 
Object dm_database: Tdm_database
  Object Database1: TUniConnection
    LoginPrompt = False
    Username = 'my_user'
    Password = 'my_password'
  End
End

and

 
Object dm_query: Tdm_query
  Object Query1: TUniQuery
    Connection = dm_database.Database1
    SQL.Strings = ('SELECT *'
          '  FROM customer'
          '  WHERE customer_id< 35000')
  End
End



So the wizard was able to link tUniQuery.Connection to a tUniConnection located on another tDataModule



3.3 - tDatabase tDataModule hierarchy

When a user connects to a database, his credential must be checked. If there are many users, this process might become rather complex.

When the application uses several databases, like a Sales database and an Employee database, the user / password checking code should not be duplicated for each database.

One solution is to gather the credential checking in an ancestor tDatabase, datamodule with several tDataModule descendents dedicated to each database:

The organization then looks like this:

database_datamodule_inheritance



The ancestor connection module is standard:

 
Object dm_ancestor_database: Tdm_ancestor_database
  Object Database1: TDatabase
    LoginPrompt = False
    SessionName = 'Default'
  End
End

and the descendent is

 
Inherited dm_child_database_client: Tdm_child_database_client
  OldCreateOrder = False
  Object Database1: TDatabase
    AliasName = 'my_alias'
    DatabaseName = 'my_database'
    Params.Strings = ('USER NAME=my_user'
          'PASSWORD=my_password')
  End
End

with the corresponding .PAS:

Unit u_dm_child_database_client;
  Interface
    Uses Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, 
      System.Classes, Vcl.Graphics, Vcl.Controls, Vcl.Forms, Vcl.Dialogs, 
      Data.DB, Bde.DBTables, u_dm_ancestor_database;

    Type
      Tdm_child_database_client = Class(Tdm_ancestor_database)
        Private
        Public
      End;

    Var dm_child_database_client: Tdm_child_database_client;

  Implementation

    {$R *.dfm}

  End.

Please note that

  • the child u_dm_child_database_client .PAS does not contain any data access component: no tDatabase
  • the child .DFM on the contrary contains the User / Password for each specific connection
  • in addition, the imported units have qualifying namespaces since we created those examples using Delphi Xe3


The only interesting part is the migration of the child datamodule :

 
Inherited dm_child_database_client: Tdm_child_database_client
  OldCreateOrder = False
  Object Database1: TDatabase
    AliasName = 'my_alias'
    DatabaseName = 'my_database'
    Params.Strings = ('USER NAME=my_user'
          'PASSWORD=my_password')
  End
End

And

  • it has not been touched by the wizard, even the tDatabase type is unchanged
  • we suspect the Wizard to only analyze the .DFM when a .PAS contains some BDE component definition (some tDatabase or some tQuery), which is not the case for descendent tDataModules


As for the query tDataModule
  • the BDE version is

     
    Object dm_client_query: Tdm_client_query
      Object Query1: TQuery
        DatabaseName = 'my_database'
        SQL.Strings = ('SELECT *'
              '  FROM customer'
              '  WHERE customer_id< 35000')
      End
    End

  • the wizard conversion is

     
    Object dm_client_query: Tdm_client_query
      Object Query1: TUniQuery
        SQL.Strings = ('SELECT *'
              '  FROM customer'
              '  WHERE customer_id< 35000')
      End
    End

So
  • the usual tQuery, UserName, Password transforms were performed, bu the link with the dm_child_database_client having the same DatabaseName could not be established
  • we tried to give the Wizard a hint by dropping another tDatabase on the child connection datamodule, but this did not help the wizard to convert the descendent DataBase1 to a tUniConnection type


Since the wizard does not analyze the descendent tDatabase, il is also unable to replace the tQuery.DatabaseName with the reference to the tUniConnection. In a typical medium size application, you might have 3 or 4 tDatabase, but over 100 tQueries. Connecting each of those to their correct tUniConnection manually (using NotePad or some other ASCCI text Editor) is possible, but is error prone. So we used yet another utility (analyze the datamodules, find the BDE types, analyze their properties etc) to make this replacement.



4 - Other Bde Unidac Wizard migration problems

4.1 - Properties not present in Unidac

We found two items no present in the tUniQuery component
  • tQuery.Text, wich can easily be replaces with tUniQuery.Sql.Text
  • tQuery.Fetchall. In Unidac this is changed to a boolean, which in this case should be set to True.

    Please note the Fetchall should be analyzed in the context of possible tClientDatasets with RecordCount, and must take into account the size of the returned dataset (checking the WHERE clause). Remember that the BDE did an outstanding job on caching, and a direct translation to ADO did degrade the performance when loading huge table with no row filtering.



4.2 - Unidac difference

We could have encountered other BDE properties without exact match in Unidac.

We used a small RTTI utility which returned the exhaustive list of those exclusive BDE properties (BDE properties without those present in UniDac). Here is the list for the tDatabase:

 
tdatabase - tUniConnection
  aliasname
  closedatasets
  databasename
  directory
  drivername
  exclusive
  execute
  flushschemacache
  handle
  handleshared
  issqlbased
  keepconnection
  locale
  params
  readonly
  session
  sessionalias
  sessionname
  temporary
  traceflags
  transisolation
  validatename

This list contains some properties we saw previously
    AliasName
    DatabaseName
    DriverName
    Params
    Session



And now for the tQuery

 
tquery - tUniQuery
  autorefresh
  cacheblobs
  checkopen
  closedatabase
  constrained
  constraintcallback
  constraints
  constraintsdisabled
  database
  databasename
  dbhandle
  dblocale
  dbsession
  disableconstraints
  enableconstraints
  expindex
  fetchall
  flushbuffers
  getindexinfo
  handle
  keysize
  local
  locale
  opendatabase
  requestlive
  sessionname
  sqlbinary
  stmthandle
  text
  updatemode

where
    DatabaseName
    FetchAll
    Text
were present in our projects



For all our BDE migrations, our first task was to gather all the properties from the tDataBase and the tQuery (collected from both the .DFM and the .PAS), to be prepared to adapt the missing properties.

In this particular example, it turned out that our customer was very conservative concerning the use of exotic BDE members, and only tQuery.Text and tQuery.FetchAll was flagged (by our tool as well as by the Delphi compiler).



4.3 - BDE vs Unidac Integer field mapping

The compiler also detected some tField.DataType divergence
  • Oracle RT has some big integer that the BDE mapped to ftDouble
  • Unidac complained that the type should be tLargeIntField
We quickly build a small application using a tUniMetaData component that confirmed the Unidac field type for those fields.



Our replacement tool then replaced :

  xxx_query_n_fff_field: tFloatField

with

  xxx_query_n_fff_field: tLargeIntField

in the .PAS and the .DFM



Note that

  • the field type was flagged for persistent fields. For dynamic fields, the BDE might consider the field as a tFloatField and Unidac as a tIntegerField.

    Since the AsInteger, AsString denote the desired conversion (not the native type of the field), the difference might stay unnoticed, which is fine.

  • for the replacement, it is possible to drop the diverging persistent field, and recreate then using the Field Editor. This technique might
    • modify the presentation order some related grids (or in the requests using Field[ppp] with a predefined hard coded ppp position)
    • remove any persistent properties (DisplayFormat, EditFormat etc) or events references (OnGetText, OnValidate)


4.4 - BDE vs Unidac Blob field mapping

Another field type mismatch was a tMemo / String difference.

A BDE tBlobField, with property BlobType= ftMemo was interpreted as an Unidac ftString.

Therefore same checking of the metadata and replacement of the field type as well at the removal of the "BlobType = ftMemo" from the .DFM for those persistent fields

The result of the transformation was checked by inspecting the display of those fields.



5 - Summary of the Unidac Wizard BDE migration

  • we knew, from previous investigation that DevArt had a migration tool and offered to try it
  • this migration tool
    • replaces the BDE types (tDatabase, tQuery in our case) with their Unidac counterparts (tUniConnection and tUniQuery), in the .PAS and the .DFM, if their is no tForm / tDataModule hierarchy (in this case setting properties in the descendent component is NOT performed)
    • the replacement of the tDataBase connection params with their Unidac correspondants is done correctly in simple cases (single module, or even separate module for the tDataBase and the tQuery) but not when form inheritance is involved
    • the properties without an Unidac matching properties are removed from the converted .DFM without any warning or log. AliasName is an example
    • the nature of the connection (ODBC in our case) is not detected (a quick lookup in IDAPI.CFG could fix this problem). And the tODBCUniProvider had to be manually added
    • the dbTables BDE Unit was not removed from the Uses list, and some syntactic errors were detected when the Uses list uses qualified names
  • those small points were quickly isolated and could be easily fixed
  • our migrations were quite easy because
    • On the DevArt side
      • the base Unidac hierarchy closely follows the BDE organization: tDatabase / tQuery
      • for those elementary components, the properties were nearly the same on both side
    • and our customer
      • used a true Sql Engine (Oracle) and not some desktop data like dBase or Paradox which in some bad case are used like old DOS files, with a lot of directory creation, shuffling of files between those etc. we had to tackle some of those beasts a couple of time
      • the BDE alias name were the same as the ODBC DSN name
      • only used tQueries (no tTable, tBatchMove, tNestedTable, tBdeClientDataSet)
      • only the basic properties of the tDataBase and tQuery were used. No tQuery.RequestLive, cached updates etc


Therefore we started to use the Unidac Wizard, adding some custom built utilities to finish the transform.

If finally turns out that, given the small difference between the BDE and Unidac, and the conservative use of the BDE by our customer, adding the simple changes made by the Wizard to our complementary tools resulted in a full fledged one step conversion tool. Our tool will not convert any BDE project to UniDac 100 %, but did the complete job in our case with the benefit of allowing reproductible conversion (if the customer wants to apply the migration to projects still under development). It also has far more detailed analysis and reporting.



Two points remain

  • why did the customer chose UniDac and not ADO ?. They maintain several hundreds of projects, and more than 90 % already use ADO. They somehow liked UniDac since they used the DevArt products for Java projects and were satisfied whith the DevArt performance and the outstanding support.

    ADO migration was another option, with some more effort on the performance side (loading complete datasets) or parameters. But then with only one data access component suite for all their Delphi projects and no license.

    A third possibility would have been FireDac, which also seems to have some migration wizard, and, beeing now an Embarcadero product, has no license.

  • the other point is how can we check that our migration does not alter the application behaviour ?


6 - Checking the BDE migration

6.1 - Build, Run, Load, Execute

Obviously the customer (or you) would like to be assured that the transformation did not change the behaviour of the application.

The only way to do this is performing some test on the connection and the Sql requests.



The absolute minimum is to be able to Build the application without compiler error.

For Delphi, the "Buidl" of the project does the compilation of all the units where the compiler can

  • either compile and generate the .DCU from the .PAS, if the .PAS is available
  • or locate a .DCU
If the .DPR imports all the units and the "Search Pathes" are correct, "Build" will recompile all reachable .PAS and check their correctness.

If your project contains some units with BDE components with an accessible .DCU but no available source then "Build" will not faithfully guarantee that all BDE reference have been removed. This might also be the case for .DLLs or packages.

To be sure, one recommendation would be

  • to centralize all .DCU in one directory and check that all corresponding .PAS are reachable by the compiler
  • examine all .DLLs and Packages


Finally, to be sure that Build flags any remaining BDE references, the BDE units (DbTable, DbiTypes, DbiProcs, DbiErrs) must have been removed from all the Uses clauses.



The Build might success, but the Run still fail.

The reason is that Build essentially generates the processor binary, but does not initialize the components using the initial values from the .DFM.

Here is an example:

  • our project contains a tForm and a tDataModule created dynamically
  • on the tDataModule a tDatabase, a tQuery and a tDataSource referencing Query1

     
    Object DataModule2: TDataModule2
      Object Database1: TDatabase
        DatabaseName = 'bbbbbbbbbbbbbbbb'
        SessionName = 'Default'
      End
      Object Query1: TQuery
      End
      Object DataSource1: TDataSource
        DataSet = Query1
      End
    End

  • now we use Notepad to replace a string with an illegal string (missing end quote of 'bbbb')

     
    Object DataModule2: TDataModule2
      Object Database1: TDatabase
        DatabaseName = 
        DatabaseName = 'bbbbbbbbbbbbbbbb

    the resource linker called by the "Build" complains:

      [Error] RLINK32: Unsupported 16bit resource in file "C:\...\_bde_modify_dfm\u_dm.dfm"

  • but if we change an identifier with any legal but meaningless identifier, the error goes unnoticed. In our case, we replaced the
      DataSource1.DataSet1 = Query1
    with
      DataSource1.DataSet1 = bogus_query

     
    Object DataModule2: TDataModule2
      Object Database1: TDatabase
        DatabaseName = 'bbbbbbbbbbbbbbbb'
        SessionName = 'Default'
      End
      Object Query1: TQuery
      End
      Object DataSource1: TDataSource
        DataSet = bogus_query
      End
    End

    and any resource editor displays this identifier now contained in the .EXE Resources :

    build_vs_run_

    So Build generated a .EXE with a wrong ressource.



This is especially worrisome if we change the .DFM manually, using Notepad for instance. Any trivial identifier misspelling will not be detected by "Build".



Is our bogus query reference detected by "Run" ?

Not quite

  • the "Run" of the dynamic query does not load the tDataModule .DFM
  • if we replace the DataModule2 dynamic creation by a static creation, or create DataModule2 by code, still nothing happens. The DataSource1.DataSet is set to Nil:

    undetected_reference_error

    (the Debug Inspector shows the situation on the breakpoint line before the assignment). Either the exception is somehow masked, or a call to some "FindComponent" returned Nil when it could not find our bogus_query.

  • if we now try to use the DataSet reference, then we get an access violation

    Procedure TForm1.Button2Click(Sender: TObject);
    Begin
      Datamodule2:= tDatamodule2.Create(Nil);
      Caption:= Datamodule2.DataSource1.DataSet.Name;
    End;



6.2 - Run might not detect an error

If we change any type of the .DFM and directly hit "Run" (without performing a Build), the error also is not reported:

 
Object DataModule2: TDataModule2
  Object Database1: TDatabase
    DatabaseName = 'bbbbbbbbbbbbbbbb'
    SessionName = 'Default'
  End
  Object Query1: t_bogus_query
    Left = 112
  End
  Object DataSource1: TDataSource
    DataSet = Query1
  End
End

If we create this Datamodule by code:

Procedure TForm1.Button2Click(Sender: TObject);
Begin
  Datamodule2:= tDatamodule2.Create(Nil);
End;

the runtime rightly complains with and EClassNotFound exception:

class_not_found_exception

Performing a Build before the Run does not help. Touching the .PAS (using NotePad to add a space and change the .PAS date) does not help. Loading the datamodule in the IDE (forcing the IDE to analyze the .DFM triggers the "Class Not Found" error :

ide_class_not_found_exception



6.3 - Build, Run, Create, Execute

The bottom line is to always exercise all the database related components after the migration: open the connections, open or execute the queries, start and commit the transactions etc

And the simplest way to do it is using unit test.



7 - Unit test of Database components

7.1 - Is database testing difficult ?

It often happened that we could not perform a migration beyond the syntactic transformation :
  • we had no connection to the server, be it a development server or a production version
  • we did not know how to navigate the application, short of having some minimal training on the product (or product family) or reading some user manual
And therefore very often we did the transformation and the customer would perform the tests. Some back an forth communication did usually fix the remaining quirks.



In the present migration job, our customer had a very neat architecture with a clean Model / View separation:

  • a couple of tDataModules each containing a single tDataBase
  • a dozen of tDataModules with the Queries (plus the persistent tFields, the tDataSetProviders and tClientDataSets)
  • the user interface was built using frames and forms
  • maybe here or there a couple of Classes to handle some dates, rates, costs and other arithmetic computations
It is then very easy to perform unit tests: we simply have to create the datamodules, and call the database connection and open or execute the queries.



Looking backward, this could also have been performed on the previous migrations. Even when the data access components are placed on forms, exporting them to datamodules is reasonably easy

  • create a datamodule for each form containing data access component definitions.
  • transfer the definitions and the associated code from the tForm and tFrame to the tDataModule
  • transform the database initialization of properties and statements into public methods of the Datamodule
  • all constants, types and global variables used by both the unit and the datamodule should be transfered to the datamodule
  • replace all database references to the tForm / tFrame into references to the tDataModule
Those transforms are a simple transfer of computations from one place to another, and should not cause any behaviour change. And most of the work can be handled by some utility.

The datamodule has then no link to the form, and can be tested separately.

Therefore there is no need to learn which buttons should be clicked, nor understand how the handling is performed percolating from some tActions or triggered by the creation of a dynamic form or the display of some frame.



7.2 - Perform the test on the original project

The unit tests should be implemented on the original project.

If we build the tests after the migration, the migration might be held responsible for errors caused by some wrong test (or application bugs !).

And migrating the unit test from the BDE version to the UniDac version is a no brainer, since they essentially call the project datamodules.

So the unit tests should be added to the version control system (SVN) and to any continuous integration server (Continua, Hudson, Jenkins etc)



7.3 - Testing the connection

Usually the login is presented when the project is executed. Once this step has succeeded, the user usually has access to the database(s).

This first step often was the most frustrating part of the whole migration. Hours trying to follow the code, creating missing registry entries, phoning to the Security Officer to get some development credentials, bypassing some sophisticated security schemes using Corba etc.



The connection unit test is then very simple:

  • create the Datamodule containing the tUniConnection(s)
  • open those connections to check that all properties are present and correct


7.4 - Testing the queries

If the Sql query is hard coded in the .DFM and does not contain any parameter, a simple call to Open or Execute is easy to test.

If the query contains parameters, or is built by code, the test can still be added to the test suite. The actual check will require the parameter values or the execution, or the SQL construction steps. Some spelunking in the application code might help, but of course the support of the customer is often required.

A simple .DFM analyzer can find all the Sql statement and prepare the test methods.



7.5 - database unit testing - Single Setup / Teardown

Creating the Datamodules and connection to the server might take a long time. So for query testing, we usually factor those operations in a special Setup called once for all the tests. For dUnit, this is done using a TTestSetup child which has Setup and TearDown called once for the complete test suite, whereas the tTestCase descendent have their Setup and TearDown methods called before and after each Public test method.



Here is a simple tDataModule with a static and a parameterized query:

Object DataModule1: TDataModule1
  Object Query1: TQuery
    DatabaseName = 'my_database'
    SQL.Strings = (
      'SELECT *'
      '  FROM customer')
  End
  Object Database1: TDatabase
    AliasName = 'DBDEMOS'
    Connected = True
    DatabaseName = 'my_database'
    LoginPrompt = False
    Params.Strings = (
      'USER NAME=my_user'
      'PASSWORD=my_password')
  End
  Object Query2: TQuery
    DatabaseName = 'my_database'
    SQL.Strings = (
      'SELECT *'
      '  FROM employee'
      '  WHERE Empno= :emp_no')
    ParamData = <
      item
        DataType = ftUnknown
        Name = 'emp_no'
        ParamType = ptUnknown
      End>
  End
End

and here is the generated test unit:

// 001 u_zzz
// 04 mar 00

(*$r+*)

Unit u_c_test_datamodule_queries;
  Interface
    Uses TestFrameWork
        , GUITesting, TestExtensions, StdCtrls
        , u_dm_simple_2;

    Type tDataModuleSetup=
             Class(TTestSetup)
               Protected
                 Procedure SetUp; Override;
                 Procedure TearDown; Override;
               Public
             End; // tDataModuleSetup

         tQueryTests=
             Class(TGUITestCase)
               Protected
                 Procedure TearDown; Override;
               Published
                 Procedure Hookup;
                 Procedure test_u_dm_simple_2_DataModule1_Query1;
                 Procedure test_u_dm_simple_2_DataModule1_Query2;
             End; // tQueryTests

    Var g_c_datamodule: tDataModule1= Nil;

  Implementation
    Uses Forms, SysUtils
        , GUITestRunner
        , dbTables
        ;

    // -- tDataModuleSetup

    Procedure tDataModuleSetup.SetUp;
      Begin
        g_c_datamodule:= tDataModule1.Create(Application);

        Inherited;
      End; // SetUp

    Procedure tDataModuleSetup.TearDown;
      Begin
        g_c_datamodule.Free;
        Inherited
      End;

    // -- tQueryTests

    Procedure tQueryTests.Hookup;
        // -- check that everything is ok
      Begin
        Check(True);
      End; // Hookup

    Procedure tQueryTests.test_u_dm_simple_2_DataModule1_Query1;
      Begin
        With g_c_datamodule.Query1 Do
        Begin
          Open;
          Check(Active);
        End;
      End; // test_u_dm_simple_2_DataModule1_Query1;

    Procedure tQueryTests.test_u_dm_simple_2_DataModule1_Query2;
      Begin
        With g_c_datamodule.Query2 Do
        Begin
          Params[0].AsInteger:= 2;
          Open;
          Check(Active);
        End;
      End; // test_u_dm_simple_2_DataModule1_Query2;

    Procedure tQueryTests.TearDown;
      Begin
        GUI:= Nil;
        Inherited;
      End; // TearDown

    Initialization
      RegisterTest(tDataModuleSetup.Create(tQueryTests.Suite));
    End.



Of course the g_c_datamodule global is not very palatable. Our code uses Delphi 6, but in Delphi Xe3 we would use a Class Var instead.



8 - Summary of the migration

We used the following steps to perform the migration
  • build and run the project. If there are missing units, wrong pathes etc, this is the time to find about it
  • check that the database access components are isolated in datamodules or Classes totally separated from the graphical user interface. If this is not the case, do the separation. The time spent will be easily recovered during the test phase and later use of the project
  • generate unit test for the connection and for each queries. If the query is not deterministic (has parameters or is built by code), comment out the test until the required parameters are available. Run those unit tests. Add the unit tests to the project's SVN and continuous integration system.

  • use an automatic tool to migrate to Unidac. Using the DevArt Unidac Wizard is an option, but be prepared to add other modifications to the Wizard outputs

  • run the unit tests on the migrated product


In our case, migrating 4 small to medium size applications (40 Forms / Datamodules, 30.000 lines of code, 1 to 5 Datamodules, around 100 queries) took around 2 to 3 days per application, once the connection to the database was established.

For a 1 million lines of code project, migration should take a week or so, and testing 4 or 5 weeks.



We are also available for consulting and help for Delphi migration jobs.



9 - Download the Sources

Here are the source code files: The .ZIP file(s) contain:
  • the main program (.DPR, .DOF, .RES), the main form (.PAS, .DFM), and any other auxiliary form
  • any .TXT for parameters, samples, test data
  • all units (.PAS) for units
Those .ZIP
  • are self-contained: you will not need any other product (unless expressly mentioned).
  • for Delphi 6 projects, can be used from any folder (the pathes are RELATIVE)
  • will not modify your PC in any way beyond the path where you placed the .ZIP (no registry changes, no path creation etc).
To use the .ZIP:
  • create or select any folder of your choice
  • unzip the downloaded file
  • using Delphi, compile and execute
To remove the .ZIP simply delete the folder.

The Pascal code uses the Alsacian notation, which prefixes identifier by program area: K_onstant, T_ype, G_lobal, L_ocal, P_arametre, F_unction, C_lass etc. This notation is presented in the Alsacian Notation paper.



As usual:

  • please tell us at fcolibri@felix-colibri.com if you found some errors, mistakes, bugs, broken links or had some problem downloading the file. Resulting corrections will be helpful for other readers
  • we welcome any comment, criticism, enhancement, other sources or reference suggestion. Just send an e-mail to fcolibri@felix-colibri.com.
  • or more simply, enter your (anonymous or with your e-mail if you want an answer) comments below and clic the "send" button
    Name :
    E-mail :
    Comments * :
     

  • and if you liked this article, talk about this site to your fellow developpers, add a link to your links page ou mention our articles in your blog or newsgroup posts when relevant. That's the way we operate: the more traffic and Google references we get, the more articles we will write.


10 - References and links

  • DevArt Unidac
  • we used many utilities to automate the migration. Early versions of those utilities have been published:
    • DFM parser : ebnf syntax, parser and dfm object tree. An early .DFM parser - Felix Colibri - Nov 2004
    • DFM binary to text converter : convert .DFM files from binary to text format - Felix Colibri - Nov 2004
  • some other articles were published about Delphi migration:
    • BDE migration : a more general BDE migration article, presenting in more detail the tools we are using to perform more difficult migrations (BDE to ADO) used for more important migrations (a family of projects counting 2600 units) - John Colibri - March 2013
    • Delphi Unicode Migration : for those who want to jump from pre Delphi 2009 versions to Unicode versions. Also a throrough presentation of Unicode - John Colibri - Nov 2010
  • for unit test with a single setup we can recommend
    • GUI testing with DUnit : a site about Delphi extreme programming. The site might no longer be reachable - 2009
    • Unit Test Framework : to better undertand the dUnit Test framework, we created our own simpler test framework. This could be used to have a "single test suite Setup and Teardown".


11 - The author

Felix John COLIBRI works at the Pascal Institute. Starting with Pascal in 1979, he then became involved with Object Oriented Programming, Delphi, Sql, Tcp/Ip, Html, UML. Currently, he is mainly active in the area of custom software development (new projects, maintenance, audits, BDE migration, Delphi Xe_n migrations, refactoring), Delphi Consulting and Delph training. His web site features tutorials, technical papers about programming with full downloadable source code, and the description and calendar of forthcoming Delphi, FireBird, Tcp/IP, Web Services, OOP  /  UML, Design Patterns, Unit Testing training sessions.
Created: jul-15. Last updated: dec-15 - 99 articles, 220 .ZIP sources, 1068 figures
Copyright © Felix J. Colibri   http://www.felix-colibri.com 2004 - 2015. All rigths reserved
Back:    Home  Papers  Training  Delphi developments  Links  Download
the Pascal Institute

Felix J COLIBRI
+ Home
  + articles_with_sources
    + database
      + interbase
      – firebird_trans_simulator
      + sql_server
      + bdp
      – db_refactoring
      – sql_parser
      – sql_to_html
      – sniffing_interbase
      – eco_tutorial
      – dbx4_programming
      – blackfishsql
      – rave_pdf_intraweb
      – rave_reports_tutorial
      – rave_reports_video
      – embarcadero_er/studio
      + firedac
      – bde_unidac_migration
    + web_internet_sockets
    + oop_components
    + uml_design_patterns
    + debug_and_test
    + graphic
    + controls
    + colibri_utilities
    + colibri_helpers
    + delphi
    + firemonkey
    + compilers
  + delphi_training
  + delphi_developments
  + sweet_home
  – download_zip_sources
  + links
– Contacts
– Site Map
– search :

RSS feed  
Blog