Posts tagged DBExpress

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.