I have a complex Delphi 2007 multi-user 24×7 MS SQL Server and Sybase ASE database application which uses dozens of TClientDatasets connected to TDatasetProviders which then connect to separate datamodules containing TDatasets (eg. TQuery or TSQLQuery) which link to a connection component (eg. TDatabase or TSQLConnection).

I have 3 almost identical versions with different datamodules:

  • a BDE version using TQuery components and this gives good performance and can do nested SQL statements but the BDE is problematic to install in a network environment, particularly with 64bit windows becoming more common, but more importantly, the BDE truncates varchar fields to 255 characters which is very limiting unless in SQL you convert them to text in which case they become memo fields, but then you need to inform user their input will be truncated to maximum of varchar setting.
  • a DBExpress version using TSQLDatasets which gives good performance but tends to have a few issues and cannot do nested SQL statements for complex reports.
  • an ADO version using TADOQuery components which gives poor performance, presumably as the ADO components also cache the data as do the TClientDatasets. I no longer use this version.

The word from Embarcadero now that they have purchased the excellent 3rd party database components – AnyDAC, is that AnyDAC will be included in Delphi XE4 – presumably late 2013 – but is no longer available for purchase separately, and that the DBExpress technology is likely to be deprecated given they had troubles addressing a few of its issues.

Given this news and not wanting to wait until Delphi XE4, I decided to jump in and buy DevArt’s UniDAC components for Delphi which come highly recommended.

The process of migration and the issues it raises.

After installing the Unidac components – very painless process indeed, you will discover a new item on the Delphi IDE menu bar – UniDAC under which you will find a Migration Wizard tool.

The good news is that the Migration Wizard is very easy to use and seems to work well by converting the BDE components to UniDAC components – just tell it which folder to convert and away it goes (best to back everything up first of course, although the wizard does have an optional backup functionality).

The bad news is that although the wizard will migrate from BDE, ADO and other DevArt components such as SDAC,  there is no option to migrate from DBExpress – this seems like it would require a significant amount of search and replace work in the pas and dfm files.

Obviously some BDE features will not be supported such as aliases and if your database connection or your code relies on this then these aspects will need addressing.

You will have to set the properties on the TUniConnection component (UniDAC database driver, server, database, username, password, then look in SpecificOptions to enter ApplicationName, WorkstationID, etc), plus you will need to reconnect all your dataset.connection properties to the TUniConnection component, plus if you are like me and using TClientDatasets, then you should set all the TUniQuery.UniDirectional = true.

If any of your SQL has double quotes, these will need to be changed to single quotes – easiest way to do this is by opening the datamodule as text file (right click then select “View as text”), then do a search and replace, replacing all ” with two single quotes.

Finally, you will need to drop the UniDAC provider components onto your datamodule – eg. TSQLServerUniProvider, TASEUniProvider.

Issues thus far:

  • certain TClientDatasets with Sybase “text datatype” fields crash the system when active set to true in either IDE or runtime if using the Sybase ASE connection – I am yet to work out what specifically is causing this when other similar datasets function well, and the TUniQuery opens rapidly without issue and the supplied DevArt dbMonitor suggests all is well. This is presumably an issue with the Sybase ODBC driver rather than UniDAC, and I would try changing to the DBExpress Sybase driver via UniDAC if I didn’t have to change all my datetime fields to SQLTimestamp fields.
  • UniDAC TUNiQuery does not allow nested SQL statements even if these are returning a single dataset.
  • Sybase ASE, Access, DB2, DBF databases are accessed via ODBC driver instead of a Direct UniDAC driver as is the case with Oracle, MS SQL Server, PostgreSQL, SQLite, and NexusDB
  • I am getting key violation errors which I didn’t get with record inserts with the BDE version – this may be related to the Sybase ODBC component – will investigate

Benefits of UniDAC:

  • no dll’s or BDE to install on end-user system which allows for a self-installing, self-configuring application to be easily made
  • performance seems good
  • ability to use multiple database engines and even write database-specific SQL with macros allowing clauses such as if Oracle then … etc. “server-independent SQL”
  • built-in debug option – set debug property to true and when dataset is opened, a dialog will display the SQL statement being sent
  • supplied with a dbMonitor which will show each SQL command sent, including parameters and time taken – just drop a TUniDBMonitor component on your datamodule and set it to active to allow this functionality (dbMonitor must be running prior to running your application).
  • it has far more versatility than DBExpress including:
    • ability to do SQL batch scripting to create and delete tables.
    • map data types – this can be particularly useful when using persistent fields and different database drivers
    • encryption functionality
    • assign table update SQL statements – although not needed if using TClientDatasets
    • stored procedure call generator
    • improved transaction control
    • ability to set a server table lock mode for each TUniQuery
    • each UniDAC driver has server specific options which can be set and these as well as the UniDAC driver are compiled within your application
    • ability to use other DevArt drivers such as SDAC if you need even more control over SQL Server or you wish to access SQL Server Compact Edition.
  • Disconnected Model with automatic connection control for working with data offline
  • Connection timeout management
  • Ability to search for installed servers in a local network
  • Local Failover for detecting connection loss and implicitly reexecuting certain operations
  • it seems fairly simple yet versatile