specialising in clinical database solutions for Australian hospitals
Posts tagged DBExpress
Migrating to DevArt’s UniDAC database connection components
Feb 13th
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
Delphi – migrating BDE app to DBX4
Jun 24th
Migrating a sophisticated BDE-based database application to DBExpress is not as trivial as it is to migrate to an ADO-based application, but the benefits are substantial – single dll deployment, fast, and cross-platform capability (eg. Linux, Mac compatibility in Delphi 2011) .
In order to aid this migration, your BDE app should be architected to use TDatasetProvider and TClientDatasets as unlike TQuery, the DBExpress TSQLQuery is unidirectional. One should avoid use of TTable for C/S databases although there is a corresponding TSQLTable.
To convert components, in the .pas file do a search and replace for the component types, and then right click on the datamodule and select View as Text, then search and replace for the component types and any associated property types that need converting, then right click and select View as Form to return to stand design mode – you just then select IGNORE ALL for properties which no longer exist in the new component.
All numeric values that will not fit in a double precision floating-point value (eg. numeric 8 ) will be returned to your application in a TFMTBCDField object. The TFMTBCDField object stores the value as type TBCD which is a true BCD value with a maximum precision of 32 digits. To
perform mathematical operations on the value in a TFMTBCDField, use the AsVariant property to store the value in a variant variable.
TSQLTimeStamp is a Delphi record with separate fields for the year, month, day, hour, minute, second, and fraction. Fraction is
the number of milliseconds. TSQLTimeStamp allows date-time values to be stored with no loss of precision. You can still use asDateTime to return the datetime value from a field. There are some new conversion routines to assist with this new data type.
DBExpress does not support the following BDE functions or components:
- TBatchmove
- TUpdateSQL
- TField.origin – although this property is present it returns a null value in DBExpress
- ftDateTime fields
- native driver support for dBase or Paradox tables
Steps to migrate:
- either add or convert TDatabase component to TSQLConnection component (as above) – you can use the same component name for ease of migration but you will of course need to edit the Connection properties by right clicking on the TSQLComponent, Note that default driver parameters are populated from the dbxdrivers.ini file in your Shared Documents/RADStudio/DBExpress folder, while saved Connection properties are stored in the dbxConnections.ini file in the same folder. These ini files only need to be deployed if you use driver spooling or tracing.
- convert all TQuery components to TSQLQuery components as above and whilst viewing the datamodule as text, also search and replace all ParamData with Params, and TDateTimeField with TSQLTimeStampField (also do this in the .pas file).
- convert TClientDataset persistent fields in other datamodules or forms – replace TDateTimeField with TSQLTimeStampField.
- search and replace “as TDateTimeField” with “as TSQLTimeStampField” in your .pas files.
- you may need to search and replace for ftDateTime as well – eg. in params
- consider setting Required = false for all persistent fields in the TSQLQuery and TClientDataset otherwise you will get a “Field value required” error when you try to apply updates and one of your fields is null – this assumes of course the database was set a default value for fields which cannot be null, and that you will have provided values for fields that cannot have a default value such as the key index field.
- ensure each TSQLQuery component has its Connection property assigned – if your TSQLConnection components reside in another datamodule, you will need to add that datamodule to the USES clause (this was not needed in BDE as system-wide BDE alias could be used).
- remove DBTables from the Uses clause (Delphi will automatically add SqlExpr, FMTBcd for you)
- ensure any string values in the SQL statements are enclosed in single quotes NOT double quotes as is allowed in BDE and ADO. You may be able to get around this when using Sybase ASE by sending “set quoted_identifier off” as ASE has a default setting pr session setting these to OFF, but DBexpress sets it to ON. The SQL standard is that double quotes refer to an object not a string, so it is good practice to use single quotes for strings in SQL anyway.
- ensure all fields returned by an SQL statement have field names ie. you can’t do Select Count(*) from mytable but you must do Select Count(*) AS MyFieldName from mytable.
- for compatibility between BDE and ADO, I use Convert(int, fldname) as fldname for integer type fields declared in the database as Numeric 3, this can be retained for DBX4
- use Convert(float, fldname) as fldname for Numeric 8 fields otherwise DBX4 will assume these are TFMTBcdFields and attempting to access the data will result in a BCD overflow error.
- you may need to use Convert(text, fldname) to ensure you get a memo field not a string field for varchar fields.
- check that you can open each TSQLQuery – you may need to flush the fielddefs property if it holds TDateTime fields still.
- check your code particularly for use of datetime fields as the new TSQLTimeStamp fields may require avoidance of .value :=, and use of asDateTime instead, and you may need to use the new SQLTimeStamp conversion routines in the unit SqlTimSt such as SQLTimeStampToStr, SQLDayOfWeek, DateTimeToSQLTimeStamp, SQLTimeStampToDateTime, TryStrToSQLTimeStamp or StrToSQLTimeStamp.
- Unfortunately Delphi does not automatically convert ftTimeStamp variant values to a TSQLTimeStamp. If your code accesses field.value or field.newValue and that field.datatype = ftTimeStamp, you will need to first convert the variant value into a SQLTimeStamp using VarToSQLTimeStamp(dataset.fields[i].newValue) for example, and then you may need to convert this into a string or a tDateTime value using the above conversion routines.
- if you use dataset parameters of type ftDateTime then setting its value by using asDateTime in D2007 returns an error ‘No value assigned to parameter’, as the internal DBExpress code has a bug. To get around this, use the ftTimeStamp parameter type, and use .value := aDateTimevalue. WARNING: this ONLY applies to parameters, not to fields – use fields[i].asDateTime := now -1 and NOT fields[i].value := now -1 ;
- set TSQLConnection.SQLHourGlass := true; if you want to automatically have the screen cursor set to crSQLWait during query execution.
- when using MS SQL Server, the MS OleDB driver only allows 1 transaction per connection and you may get an error on applying updates of “Cannot create new transaction because capacity was exceeded”. To get around this, add SQLConnection.CloseDatasets in the TDatasetProvider BeforeApplyUpdates event.
- CommandText containing sequential SELECT statements do not seem to be supported. You may need to break the CommandText into single SELECT statements per execution.
- the TSQLConnection using Sybase ASE driver does not appear to send the Client HostName and Client AppName parameters to the server which means you will have trouble debugging server problems as you cannot work out which machine or app is causing the problems – I am trying to find a workaround for this one. These values ARE sent to MS SQL Server though using the Delphi driver.
- test application – but if there are issues, you may need to analyse one component at a time searching for the bug to fix
- deploy the appropriate DBX4 driver dll with your application (but you don’t need to deploy midas.dll if you add midas to the USES clause)
- avoid deploying dbxconnections.ini with passwords by manually assigning params and ensure you set LoadParamsOnConnect = false so it does not look for an ini file entry and ignore your manual params – this way you do not have to have anything much in the ini file.
- better still, don’t deploy the dbxdrivers.ini or dbxconnections.ini files at all as these may cause confusions with your parameters resulting in “Multi-step OLEDB errors“, are not compatible with the versions of these files for Delphi XE, and create other issues – just add the appropriate files to your Uses clause (eg. DBXxxxx where xxxx = ORACLE or MSSQL or SybaseASE, etc).
“Parameter invalid” exception when trying to open TClientDataset at design time or run time:
- this appears to be often the result of a presumably corrupted TSQLDataset component despite the fact you can set it to active without error, and the error occurs when you set the linked TClientdataset to active.
- try creating a new TSQLDataset component and deleting the offending one.
Useful resources:
- Bill Todd’s white paper (pdf)
- Ethea’s InstantDBExpress components – I have not used these!
Other issues with Delphi dbExpress and MS SQL Server:
Delphi 2007 uses dbxMSS30.dll driver and the oledb.dll MS SQL Server client dll which is available on Windows XP and higher machines by default – older machines will need MDAC(Microsoft Data Access Components) 2.8 SP1 installed.
Delphi 2007 officially supports MS SQL Server 2000 and 2005 editions but does seem to work well with 2008 edition as well.
Delphi 2007 DBX appears to have a bug in which memo fields are truncated to 1024 characters if more than one memo field is present in a table – this is fixed in Delphi XE.
Delphi 2010 and later drops MS SQL oledb.dll support and requires that client machines have MS SQL Native Client v2008 installed, even if accessing a MS SQL Server 2005 database. All the dbx drivers have been updated and renamed. The dbx driver is now dbxmss.dll and the MS client dll is now sqlncli10.dll.
Note that the native client dll for SQL server v2005 was sqlncli.dll but this is not compatible with Delphi 2010’s dbxmss.dll but apparently may be compatible with Delphi 2009’s version of dbxmss.dll.
If you are getting Multi-step OLEDB error message (or “cannot find procedure DBXRow_GetUInt8” when you run in debug), you probably have either the wrong DBX driver being called for your version of Delphi (note that DBXDrivers.ini file may be used from Users folder in preference to the file in your app folder – avoid confusion by adding DBXxxxx to your Uses clause!), or you have incorrectly set the connection parameters, or the database client software has not been installed correctly.
Delphi XE TClientdataset has a new bug – you can no longer call RefreshRecord if there are joined tables – hopefully Embarcadero will release a hotfix for this as it is caused by the MIDAS code no longer creating the table join when it dynamically creates the SQL statement to do get the data for the refresh.
Recent Comments