| Delphi Clinic | C++Builder Gate | Training & Consultancy | Delphi Notes Weblog | Dr.Bob's Webshop |
 |
 |
 |
| Delphi Clinic | C++Builder Gate | Training & Consultancy | Delphi Notes Weblog | Dr.Bob's Webshop |
|
|
|
| ||||||
Moving from the BDE to ADS
Even Delphi Developers who still work with Delphi 7 (or older) should know by now that the Borland Database Engine (BDE) has been frozen since Delphi 7, and that the SQL Links part of the BDE (to connect to SQL databases like InterBase, SQL Server, Informix, Sybase and DB2) is deprecated and even removed from later version of Delphi. For a long time, it was still possible to use the BDE, but it's getting more and more problematic to install and use the BDE with recent versions of Windows like Windows Vista and Windows 7 (not to mention the 64-bits editions of these). In short: it's not fun anymore to use the BDE, and it's about time we should move away from it.
In this article I will cover one of the potential alternatives for the BDE: the Advantage Database Server (of which version 10 has just been released).
Advantage Database Server
The Advantage Database Server (ADS) is not the only alternative for the BDE. People can also consider to use InterBase/Firebird, BlackfishSQL, SQL Server or sometimes even Access. In my view, Advantage Database Server has a number of advantages (pun not intended) compared to the other alternatives, making it an ideal replacement for the BDE and Paradox or dBASE tables:
Advantage Database Server
You can download the Advantage Database Server from the Advantage website. There is a 30-day trial version of the Client/Server DBMS, and also a free Advantage Local Server (specifically for local connections – on the same machine as the application itself). The non-Local Advantage Database Server is the one to use in Client/Server (or internet) situations where the Advantage Database Server does not reside on the same machine as the application. In those cases, the ADS deployment license is not free, but starts at 207 Euro (for a 1-User Windows Server) to 845 or 1614 Euro for a 5- or 10-User Server edition. These are prices for the deployment server only – for development you can stick with the free local server.
Compared to the BDE, it may be a bit more expensive to use the Advantage Database Server (except when you use the free Local version), but it's a complete DBMS with full transaction support, speed that often exceeds that of SQL Server, and with hardly any maintenance need. The few tasks we need to perform can be done with the free Advantage Data Architect tool, which comes complete with source code. Written in Delphi, by the way, to highlight the strong connection between Advantage and Delphi once more.
Migration from the BDE to ADS
In order to migrate BDE data to the ADS format, we can use the free Advantage Data Architect tool. In the main menu of that tool, we can select Tools | Import Data, and then in the Advantage Data Import Wizard dialog that follows we can select among the supported data types, including Paradox/dBASE files or just a whole BDE alias.
For the example in this article, I want to migrate all tables from the BDE alias DBDEMOS to the ADS format, and then use these tables with help of the Advantage TDataSet components.
So, in the list of import data types, select the BDE Alias, and then in the next drop-down list (of available BDE aliases on this machine) we can select the DBDEMOS alias.
In the next window, I can either use an existing ADS database connection, or create a new one. When creating a new connection, we can specify where the database or converted tables should be placed on disk (the ConnectionPath), and if the server is a local, remote or internet server. For tests on the local machine (and with the free Advantage Database Local Server) we should special local for the ServerTYpe.
For our example, I've specified that the data must be placed in the c:\adsdata directory in the ADS format. I can also specify a databasename, for which I've just used DBDEMOS again, but you're free to specify something else of course.
By clicking on the OK button now, the new connection is created, and we can then start the actual migration. The migration progress is shown in a progress page where each table is moving along, sometimes taking a little time (depending on the amount of data in the table).
Finally, we can view the resulting connection and the migrated tables in the Advantage Data Architect. As an example, let's consider the contents of the biolife table, now with the ADS extension.
This was a very easy way to migrate the data from local BDE tables to the ADS format. We would also have created a new database connection with a data dictionary to use as destination for the converted tables, offering extra abilities (with the data dictionary), but that's something we won't be using this time, so I will save that part of the story for another day.
Advantage Delphi Components
After the data is converted to the ADS format, we need to start using it in Delphi. And that's the area where the Advantage Database Server really shows its attention and focus for Delphi developers: we can use special TDataSet Descendant components. These data access components are available in two editions: for Delphi 7 and older, or for Delphi 2006 and later, including Delphi 2009 and 2010 with Unicode support. The Advantage TDataSet Descendant components are free, and consist of7 components on the Tool Palette, as can be seen in the following screenshot:
The TAdsConnection component can be compared to the old BDE TDatabase component. The TadsTable, TAdsQuery and TAdsStoredProc are the obvious counterparts of the BDE TTable, TQuery and TStoredProc components (although the BDE doesn't really support stored procedures for the local dBASE or Paradox tables of course). The TAdsDictionary and TAdsSettings are specific ADS components that will be covered in more detail next time. And finally the TAdsBatchMove component can be used to quickly move data from one table (format) to another, for example from dBASE or Paradox to the ADS format (similar to what the Advantage Data Architect is doing of course).
Note that there is no TAdsSession component in this set. We don't need one, since the Advantage TDataSet components are already thread-safe, and do not need an explicit TSession component.
TAdsConnection
The TAdsConnection component is the one to start the connection with our ADS database or collection of ADS tables. To start with out freshly migrated tables from DBDEMOS, we should first tell ADS that the directory c:\adsdata is the location where the tables reside, by defining this location as an alias. The definition of ADS aliasis can be done in the ADS.INI file, which by default resides in the Windows directory.
Assuming we've installed the ADS example data tables, there will be a single line in the [Databases] section of the ADS.INI file in the Windows directory, mentioning the location for the ADTDemoData. We can add a line to the ADS.INI file, defining the ADS alias DBDEMOS in the directory c:\adsdata, so we can use this alias also at design-time. The contents of the ADS.INI will then be as follows:
[Databases] ADTDemoData=C:\Program Files\Advantage 9.10\Help\ADS_DATA\;A DBDEMOS=C:\ADSDATA;A
Note that the ADS.INI doesn't have to be placed in the Windows directory. We can also put it in the same directory as the compiled executable, which is much easier when deploying the application of course.
Using the AliasName property is at least a little bit more flexible than specifying the full path in the ConnectionPath property of the TAdsConnection component. With the modified ADS.INI file, Delphi will offer us two choices for the AliasName property: ADSDemoData and DBDEMOS.
After the AliasName property has been set, I always also set the LoginPrompt property to False. Note that this is not really required, since the LoginPrompt property has no effect when it turns out that the database doesn't need a password (quite handy, because it doesn't ask for a username/password when none is needed).
Finally, we can check if we can connect to the tables by setting the IsConnected property to True.
The TAdsConnection component has a few more interesting properties, worthy of a mention. I often get in trouble when I open a Delphi project from machine X on machine Y, and the database (from machine X) cannot be found on machine Y. Using other data access technologies, with an active table, query or database connection at design-time, this usually means that I have to wait for a connection timeout before I can continue. With the TAdsConnection component, the value of the IsConnected property – used at design-time - doesn't have to be stored. The property StoreConnected can be used to control the saving of the IsConnected value to the DFM file. Quite handy, since this will avoid these time-out errors. Note that we need to set StoreConnected to False (since it's set to True by default), but at least we only need to do that once, and we don't need to remember to close any database connections before we save or close the project.
Another helpful property is the CompressionType property, by default set to ccAdsCompressionNotSet, but we can also assign values ccAdsCompressionInternet (to indicate that data transfer to the internet should be compressed) or ccAdsCompressionAlways (to indicate that all data transfers should be compressed).. Compression results in less data that needs to be transferred, but takes a little longer to process (the data needs to be compressed and decompressed). For that reason, it's not very useful to use compression on a Local connection. For real Client/Server of web application this is a nice property to help minimize the bandwidth usage.
The TAdsConnection component also has UserName and Password properties, in case the database needs a connection. We can also pass this information using the OnLogin event handler, where we can pass the UserName and Password that they user has entered on screen for example.
Another nice property is ReadOnly, which we can use in situations where it's clear in advance that no changes to the data should (and will) be made, but only read (SELECT) operations are allowed. By setting the ReadOnly property to True, we are assured that no UPDATE, INSERT or DELETE commands will be performed, and that the data will indeed not change.
TAdsTable
The TAdsTable component can be compared to the good-old BDE TTable component. When using a TAdsTable component, we should point the AdsConnection property to a TAdsConnection component, and then we can select a suitable table by picking one from the list we get for the TableName property. The Active property works just like the normal TTable, and can be used to view live data at design-time. As an example, I've pointed the TableName to the new biolife table, which results in the following view at design-time using a TDBGrid, TDBNavigator, TDBMemo and TDBImage:
Note that the TAdsTable also has a nice StoreActive property to help us when we save the project (if you set StoreActive to False, then the value of the Active property will not be stored, and you can then open the project on another machine – even if the biolife table doesn't exist there). Very useful, since not all my machines contain the same set of local database tables.
So far, I've shown you how we can create new Delphi applications using the Advantage TDataSet descendant components, in a quick and easy way. However, most BDE applications will be legacy applications, for which we should migrate existing code and BDE component usage to the ADS usage.
Migration
The migration of the data from the BDE Paradox and dBASE tables to the Advantage Database Server format can be done using the Advantage Data Architect, like we've seen before. For existing BDE application, however, we must also replace the BDE components with their ADS counterparts. This doesn't take a lot of effort, although it may be helpful to have clear instructions how to proceed with this task.
Keep in mind that the TAdsTable component behaves exactly like the TTable component, and the TAdsQuery behaves like the TQuery, in a similar way that the TAdsStoredProc behalves like the TStoredProc component. We no longer need the TDatabase and TSession components, and instead we have to use a TAdsConnection.
The steps for a quick migration are as follows:
The last step will result in one or more warnings (one for each modified component) to indicate that a component is defined in the .pas file with an incorrect type, and that Delphi can correct the declaration for you if you click on Yes:
Make sure to create a backup of your source files before (!) you start the migration. It's easy to make mistakes with steps like these, and it would be a shame if you ruin a previously working project.
Developers who want a little help with the migration can always contact me – as Delphi and Sybase reseller (for Advantage Database Server), I always offer my customers personal support with the migration. My personal goal is to help as many people as possible to get rid of the BDE, and ADS is a good means to this goal.
Summary
The BDE is old, frozen and due for replacement. Overdue in fact. The Advantage Database Server is one of the possible database environments to migrate your BDE dBASE and Paradox table data to. Converting the data is a piece of cake with the Advantage Data Architect, and the migration of the BDE application by using the Advantage TDataSet Descendant components is not trivial but no rocket science either.
Apart from an ideal replacement for local BDE tables, the Advantage Database Server can also perform in a Client/Server and Multi-Tier environment.
References
Advantage Local Server free
ADS Deployment Prices from eBob42.com
