Microsoft buys Skype for $8.5 billion – not bad for a Delphi application!

Skype was written in Delphi which made it easy to download to users with any Windows machine without having to worry about users having to download MB’s of java or .NET support files.

This ease of configuration from the net was one of the reasons that made Skype so popular.

It will be interesting to see what Microsoft does with it now that Google is also entering into the VOIP marketplace with its Google Voice product which uses SIP addresses (SIP technology is now in some of the new mobile phones) – Google Voice is currently only available in the US.

Posted in Delphi | Tagged , , , | Leave a comment

Microsoft shows Windows 8 and all talk of HTML5 and none of .NET and Silverlight

Following my last post on Microsoft’s announcement that HTML5and javascript is now their preferred cross-platform development technology and apparently leaving .NET and Silverlight for only Windows and Mac development, Microsoft unveiled Windows 8 this week and seems to be pushing for HTML5 apps as their main apps, with no mention of .NET or Silverlight.

Posted in programming languages | Tagged , , , | Leave a comment

Where to now with rich client application development?

In the 1990’s, C/C++ and Java ruled the enterprise application world while Microsoft’s Visual Basic was a very limited Windows application tool, and we had Borland’s Delphi with its far more powerful architecture providing one of the best native Windows Rapid Application Development (RAD) tools.

Sun took Microsoft to court when Microsoft tried to add proprietary extensions to Java which was owned by Sun and this ended Microsoft’s desire to use Java.

Microsoft then hired one of the architects of Delphi to come up with its own equivalent of Java – and this was the C# language which became the basis of Microsoft’s .NET technology and Windows Performance Framework (WPF) which was the .NET graphical engine for rendering user interfaces on Windows operating systems.

Microsoft then disgarded its Winforms-based development tools such as MS Visual Basic and forced VB users to migrate to VB.NET which would work alongside C# as a programming tool in the MS Visual Studio suite.

This change of direction pushed most developers to .NET, and away from native compiled Windows applications and also away from Java.

Borland responded by creating a .NET version of Delphi which has now evolved to be Delphi Prism but in the process meant it lost its way, and eventually sold Delphi to Embarcadero which is now again leveraging its power and versatility, particularly with its re-vitalised Win32 native application development which will hopefully soon also be able to create native compiled Win64, Linux and MacOS apps, but also with its relationships with RemObjects and the furthered development of Delphi Prism .NET to include ports to MacOS.

In the first decade of this millenium, the vast majority of Windows application development was targeting .NET and if you were not making .NET, companies would think twice about your product.

In 2007, Microsoft released Silverlight – a C#, .NET, WPF technology designed to compete with Adobe Flash and provide a graphical plugin environment for web browser which would run on other major operating systems including MacOS. Developers again rushed to embrace this new technology which has a lot of nice features but also a few downsides.

In late 2010, Microsoft created an uproar amongst the developer community by its apparent lack of ongoing commitment to Silverlight which although provides the richest client experience on Windows browsers, does not run on the multitude of new devices such as Android. Microsoft and Apple now seem to have abandoned .NET and Silverlight and are pursuing HTML5 and javascript technology as  the future of cross-platform web browser applications. Adobe have created their Wallaby conversion tool to allow Flash developers an easier way to migrate their work to HTML5 given that Flash developed websites are locked out of the Apple iOS devices.

see:

This leaves many developers wondering about the future of .NET itself when Microsoft does not actually create any major Windows applications using .NET but rather uses native compiled applications for their better performance.

For instance even though Microsoft totally revamped Office apps in 2007, they did not resort to using .NET to architect these.

Likewise, Microsoft Office is not architected with .NET and has a much better user interface than the browser webmail version which demonstrates the limitations of browser-based rich client application development.

If you follow the arguments currently flying around the web, Silverlight and .NET are just not fast enough for major enterprise-wide rich client major applications, and Winforms is still the preferred technology base for such applications.

I know where this leaves me, very happy that I did not change to .NET and abandon Delphi Win32 and its fast natively compiled Windows applications with full access to Windows API’s and memory management, with potential to have native Win64, MacOS compiled apps in the near future.

Furthermore, HTML5 web client solutions need data driven web servers, and these can be created in Delphi 32, Delphi 64, Delphi Prism .NET or Delphi PHP or C++ Builder, depending on your fancy, either with REST/JSON, SOAP/XML, REMObjects or other messaging and with either Azure, Amazon EC2 cloud, DBExpress, ADO, ADO.NET or ASP.NET data technologies, and all with the same RAD Studio programming tool.

Posted in programming languages | Tagged , , , | Leave a comment

Creating database agnostic and database engine agnostic applications in Delphi

Two major dilemmas arise with database application development:

How to manage the different SQL dialects:

  • whilst sticking to ANSI-SQL 92 syntax will help, particularly with table joins, data conversion or calculation routines and simple data elements such as current date vary with each database vendor making code re-use a nightmare.
  • if you ever plan in supporting multiple vendors such as MS SQL server and Oracle, then you need to consider your application design to factor this in.

How to swap between different database engines:

  • DB Express may be fine for many functions, but it has limitations which are not present in Borland Database Engine (BDE), or you may wish to use ADO or other engine technology.
  • unfortunately, each engine technology has different programming syntax, and indeed different non-visual Delphi components for the database connection and the TQuery component.
  • furthermore, they tend to return different data types, for example DBX uses TSQLTimeStamp while BDE uses TDateTime, while BDE and ADO differ on how they determine which field is a numeric or integer field. This makes using persistent fields problematic.

On my research, there are 5 main Delphi approaches:

The traditional Delphi / MIDAS / Datasnap approach:

  • see here for details on how I use this approach
  • this requires maintaining separate datamodules for each database engine, and potentially separate datamodules for different vendors unless you write code to manually modify SQL statements dynamically.
  • easy to visualise and TClientDatasets work as designed with persistent fields, but not so easy to maintain as you need to remember to add your modifications to each version of datamodule.

The Dimeric Virtual Database approach:

  • in 2002, Dimeric Software created a database abstraction technology (VDB) for Delphi 6 to help address the above issues and add extra functionality such as database connection pooling, enhanced transaction capabilities, enhanced control over prepared queries, etc.
  • it uses interfaces to abstract the Dephi components such as TDatabase, TDataset and TDatasetProvider but leaves TClientDataset unchanged.
  • they support the following database engines BDE, ADO (dbGo), DBX (DataSnap), IBX (Interbase Express), ADS (Advantage database server), DBISAM, and Flash Filer 2.
  • it uses a set of “Function Escapes” to convert SQL statements to vendor specific statements on the fly – ie. in your SQL statement you would use [today()] and the VDB will convert this into the vendor specific SQL command to retrieve today’s date.
  • they have support for the following database vendors or dialects: MS SQL Server, Sybase, Oracle, Interbase, Informix, MySQL, Paradox, DB2, and MS Access.
  • this interface approach is powerful and inexpensive ($US99) but may not be the easiest to use if you wish to use persistent fields in TClientDatasets
  • see their website for more details.
  • Misha Charrett utilises this technology in his CSI Distributed Application Development Framework which makes developing multi-threaded applications easier – see here. This is freely available for use but does require Delphi 2005 or higher.

RemObjects Data Abstract:

  • a more expensive solution (~$US990) which will require you to replace TClientDatasets with their equivalent component as well as all the other non-visual database connection components.
  • you will not be able to use persistent fields but all fields on the client side are declared in the classes which means a change in the way you approach programming.
  • uses Schema to handle different database vendors
  • it does allow for data push functionality
  • there is no easy conversion from a legacy Delphi application.
  • see here for more details.

kbmMW Development Framework:

  • kbmMW is another option which may be considered and new licence for the Professional version is $US478
  • they do have a free, limited functionality version without source code but only for the current version of Delphi.
  • supports different database backends directly without having to write any code.
  • advanced connection pooling on both client to server and server to database, caching of result sets on both client to server and server to database
  • can mimic as a SOAP server, a WEB server, a AJAX server, a AMF3 server
  • only the Enterprise version supports multiple messaging topologies incl. Peer 2 Peer, Hub/Spoke and Broadcast using Publish/Subscribe based messaging via multiple types of fully developer accessible and configurable  message queues
  • supports client side controlled transactions, and supports transactions, even spanning multiple databases.
  • supports most database vendors
  • Automated datastore/database connectivity recovery on connection loss or database breakdown
  • compiled as an assembly, kbmMW can be used, feature complete, from within VS.Net to develop both client and server side code
  • a native PHP extension exists for both Win32 and Linux that allow direct access to a kbmMW based application server
  • an application server can also be accessed from portable devices via a fully portable native C client, a Compact Framework compatible native C# client, a SOAP interface, or an XML interface (only available in Enterprise version).
  • the free version “CodeGear” is available for Delphi versions 2007 and later.
  • unfortunately, attempting to install this D2007 version failed with the latest Indy version installed, it presumably needs the Indy version shipped with Delphi 2007 (ie. Indy 10.1). Design package could not load as procedure entry point @Idtcpclient@TIdTCPClientCustom@SetBoundPortMin$qqrxi could not be located in IndyCore100.bpl.  The difference in the Indy source seems to be that the constant for this procedure is now TIdPort:Word instead of integer;
  • this is an issue as the CSI framework requires the latest Indy version installed.

ASTA:

  • ASTA is another data abstraction technology from the 1990’s
  • in Nov 2010, they announced ASTA 4 with Unicode support for Delphi 2009, 2010 and XE.
Posted in Uncategorized | Leave a comment

Running Delphi 6, Delphi 2007 and Borland Database Engine on 64 bit Windows 7

The Borland Database Engine (BDE) and Delphi versions prior to Delphi 2009 were created before 64 bit Windows 7 and thus the default installations need some tweaking to get them to run.

I have created a page explaining the issues and the tweaks needed to get these running nicely in 64 bit Windows 7 – see here.

Most of the issues have to do with UAC and the altered location of System32 files and registry keys.

Before you install any of these, make sure the least you do is change the default install directory of Delphi to somewhere other than C:\Program Files path!

If you are just wanting to run your Delphi 6 or 7 apps on 32 bit Windows Vista or Windows 7, you just need to be aware of a few issues:

  • do not try to write files to protected Windows folders such as any folder derived from Program Files or Windows, or the root folder.
  • Registry preferences should be stored in HKCU
  • If you need Admin rights for your application, you should request this in the manifest XML file – see here
  • You should consider using a TThemeManager to enable XP themes support and give your app a XP feel.
  • Themes and Vista menus are likewise activated through the manifest XML file, but you should probably add some additional Vista/Win7 specific code to handle this.
Posted in Delphi | Tagged , , , | Leave a comment

SMS Texting from Delphi via RedCoal SMS SOAP Server

Many Australian companies use RedCoal SMS texting services to send SMS texts to mobile phones from computers.

RedCoal supplies an API for its MIDA technology with VB and C++ examples but no Delphi code.

I have created a Delphi demo with code using Delphi 2007, which you can download and use free of charge.

Of course, to actually use the SMS text service, you will need to download the SDK from RedCoal which includes a serial number generator which provides the serial number for your application, and you will need to email them for a trial SMS Key (developer’s trial lasts 1 month then there is a one off royalty free developer’s license of $A695).

Finally, your client will need to open an SMS text service account with RedCoal to pay for the actual SMS texts sent (17-22c per SMS for pre-paid).

Post script:

If you deploy this in an organisation which uses a proxy server for internet, then you will need to detect this and set the HTTPWebNode.proxy property which must be in the format server:port (fortunately, this is the format used in Windows registry as outlined below):

for example:

RedCoalRIO.HTTPWebNode.Proxy := GetInternetProxy;

function GetInternetProxy:String;
var reg:TRegistry;
begin
result := ”;
Reg := TRegistry.Create;
try
Reg.RootKey := HKEY_CURRENT_USER;
if Reg.OpenKey(‘\Software\Microsoft\Windows\CurrentVersion\Internet Settings’,False) then
if Reg.readBool(‘ProxyEnable’) then
begin
Reg.CloseKey;
if Reg.OpenKey(‘\Software\Microsoft\Windows\CurrentVersion\Internet Settings’,False) then
Result := Reg.readString(‘ProxyServer’);
end;
finally
Reg.CloseKey;
Reg.Free;
end;
end;

Posted in Delphi | Tagged , | Leave a comment

An approach to client server databases using Delphi

The following is an approach I have used successfully in robust 24×7 multi-user client server database applications using Delphi 6 and upwards and using either Sybase or MS SQL Server databases.

This approach uses the traditional Delphi MIDAS TClientdataset-based approach with emphasis on using persistent fields which allows maximum leverage of the capabilities of TClientDatasets. The main problem with using this persistent field approach is portability of code between different database interfaces (eg. BDE vs ADO vs DBX4), and different database servers with their different data types.

This is in CONTRAST to, and is not directly compatble with or easily modified to the approach taken by RemObjects Data Abstract which does NOT use persistent fields but instead uses strongly typed data tables – see here for the RemObjects approach.

To ensure greater portability between databases, I have avoided use of stored procedures, etc in the database.

You may also wish to read my earlier post about my experiences in migrating a Delphi 6 BDE app to DBX4 in Delphi 2007 – see here.

A database connection datamodule

On this datamodule,  place a TDatabase component(s) if using the old but still handy Borland Database Engine (BDE) technology, or TSQLConnection component if using new DBExpress (DBX) technology.

Generally requires minimal coding – just create a BDE alias in SQL Explorer and set the BDE alias name if using TDatabase, or create an SQLConnection string and set the TSQLConnection to this if using DBX.

Optionally, you can use the datamodule.onCreate event to write code to automatically copy any needed files (eg. midas.dll, DBX driver(s), dbxdrivers.ini, and dbxconnections.ini if using DBX) to the program folder from a central repository on a server to minimise installation work – be sure the user as sufficient security rights in Windows to write to that folder!

If using BDE on a network, my preferred approach is to place the BDE files on an accessible server folder and create BDE registry files which point to this folder (eg. export your own BDE registry records using regedit, then use Notepad to replace all the folder locations with your server folder), then you can just install this .reg file on each workstation (as long as you have administrator rights to do so).

Note, although I have also used dbGo components which access ADO technology, I have found this to be slower than either of the above for complex database applications when identical SQL and programming is used. However, some may prefer to use dbGo and it will work almost as well except for some data type differences with BDE and a bug in returning datetime values which leaves out the time component – but there is a workaround for this issue too.

A main SQL query datamodule

This holds all your SQL statements to access the database via TQuery (if using BDE) or TSQLQuery (if using DBX) components which are linked to the database (make sure you add the database datamodule to this datamodule’s uses clause).

The TQuery component is linked via the databasename property – and I also set the update property to UpWhereKey (but if using reconciliation handling, you may wish to use UpWhereAll – in any case, it should be set the same as your TClientDataset – see below).

The TSQLQuery component is linked to the database via the SQLConnection property.

Once you have written your SQL statement and tested to make sure it works, if it uses parameters, then make sure you go to the params property and set the params Datatype and I usually set ParamType to ptInput.

Then, double click on the component to bring up the field list and right click and select Add All Fields as we generally NEED to use PERSISTENT FIELDS to ensure we can write data back to the database, and to use nice features such as calculated fields and lookup fields.

Set the provide flags for each field to pfInUpdate, and for the key field, also add pfInKey. If you are going to use reconciliation dialogs and handling (which I don’t use as it tends to confuse most end users), then you will also need to add pfInWhere.

In general, you will not need to write ANY code in this datamodule.

A lookup table datamodule

In complex applications, I prefer to place all my lookup tables in their own datamodule,for ease of use.

Detail is as for the main datamodule.

TClientDataset datamodules for each SQL datamodule

I like to have a main clientdataset module and one for my lookup clientdatasets.

If you do this, you will need to add the lookup datamodule to the main datamodule Uses clause, and create it BEFORE the main datamodule.

You will also need to place the relevant SQL datamodule in the Uses clause.

First I place a TDatasetProvider on the datamodule and set the Dataset property to the TQuery or TSQLQuery component and set the UpdateMode to UpWhereKeyOnly (or UpWhereAll if using reconciliation handling).

If you will be writing dynamic SQL statements in your application to parse through this component, you will need to set poAllowCommandText.

Then I place a TClientDataset component and link it to the TDatasetProvider via the ProviderName property (under Linkage tab in D2007).

If params were used, the right click the component and click on Fetch Params to automatically set these.

Then double click the component and create PERSISTENT fields as was done above and set the provider flags as above.

Now you can also create NEW persistent fields such as Internal Calc fields (this can be used for sorting the table by including them in the IndexFieldnames property or filtering the table), calculated fields or lookup fields (the latter two cannot be used in indexes or filters, nor can they be referenced by an Internal Calc field). Obviously, changes in this new fields will NOT be sent back to the database.

If using lookup fields, I add some code in the BEFOREOPEN event of the TClientDataset to ensure the lookup table being referenced is opened first, for example:

with (Dataset as TClientDataset) do
if fieldCount > 0 then
for i := 0 to fieldCount-1 do
if fields[i].LookupDataset <> nil then
with (fields[i].LookupDataset as TDataset) do
if not active then
open;

If using internal calculated fields or calculated fields, you will need to write code in the OnCalcFields event for the ClientDataset to set values for these fields – but remember this event will get called very frequently so avoid long duration code.

Dealing with primary key fields without using auto-incrementing fields

In order to allow use across multiple database types, I prefer to avoid using auto-incrementing fields and instead manage these manually.

This needs to be done on two levels:

  1. within the TClientDataset so that additional records created prior to applying updates and refreshing the clientdataset does not result in a local key violation – here I use local variables set to a unique negative value within the datamodule.
  2. on the database itself – there are a variety of techniques one can use, but my preference is on the TDatasetProvider.BeforeUpdateRecord to write code to retrieve the next available key value, and then set the key field’s value to this (see below for some code).

Prevent TClientDataset key violations:

Create a global private variable in the datamodule such as Table1ID_Temp: Integer (or one could use a TClientDataset to manage these if there are a lot of tables);

In the ClientDataset.AfterOpen event set the variable to zero;

In the ClientDataset.OnNewRecord event decrement the variable (so it becomes negative and unique) and set the table’s key field value to this variable;

Manual updating a key field:

TDatasetProvider.BeforeUpdateRecord event:

case UpdateKind of
ukInsert:

begin

newKey := IncrementPrimaryKey(Tablename); //this runs a function which sends SQL to the database to get the max(PrimaryKeyValue) and then increments this

DeltaDS.FieldByName(IndexFieldName).newValue := newkey;

end;

ukDelete:  if  DeltaDS.FieldByName(IndexFieldName).newValue < 0 //this is a temporary clientdataset index so has not yet been sent to server
then Applied := true; //so skip delete

end;

Minimising server side cursors and locking

In the TDatasetProvider.OnGetData event, write the following code which will close the server cursor once the data is retrieved:

with (Sender as TDatasetProvider).dataset do
if active then close;

What we do NOT want to do is allow the BDE to maintain table cursors, as this will cause problems with many concurrent users.

Try to keep your SQL statements short running and accessing as few tables as possible to reduce chances of deadlocks occurring – don’t forget you can use lookup fields in your TClientdatasets so you may not need to use them in your SQL statements.

Whatever you do, DON’T use TTable components or similar for a server database – they are fine for Paradox, dBase and MS Access databases but NOT for MS SQL Server, Oracle, Sybase, etc.

Avoiding the MS SQL Server issue of only one transaction at a time:

MSSQL OLEDB can only have 1 transaction open per connection and there is a risk ApplyUpdates will fail with error: “Cannot create new transaction because capacity was exceeded”  – the workaround when using DBX is to do the following:

TDatasetProvider.BeforeApplyUpdates event:

((Sender as TDatasetProvider).DataSet as TSQLQuery).SQLConnection.CloseDatasets;

Saving edits to the database:

One of the wonderful features of TClientDatasets are that user edits are initially performed on the local data and your design can either allow these edits to be saved to local disk files for delayed updating back to the server, or even allow a user to undo an edit or cancel all edits before they are sent to the server.

A simple method to send the edits back to the server is to place the following code in the TClientDataset.AfterPost event:

with Dataset as TClientDataset do
if changeCount > 0 then
applyUpdates(-1);

Note that the -1 means you are not going to handle any errors.

This same event could be used for AfterDelete and AfterInsert events as well.

You need to ensure the post event is called prior to shutting the application down, so in the TClientDataset.BeforeClose event just add:

with Dataset as TClientDataset do

if Dataset.state in [dsEdit,dsInsert] then Dataset.post;

And in the datamodule.OnDestroy event you could add:

for i := 0 to ComponentCount -1 do
if Components[i] is TClientDataset then
if (Components[i] as TClientDataset).active then
(Components[i] as TClientDataset).close;

Creating audit trails and historical data trails:

Place code in the TDatasetProvider.BeforeUpdateRecord event to look at the DeltaDS to access which data items are being changed before they are sent to the server using code such as (NB. audit1 is a string variable, delim is a char variable to act as a field delimiter in audit1):

for i := 0 to DeltaDS.FieldCount-1 do

if DeltaDS.fields[i].fieldkind = fkData then

if not VarIsEmpty(DeltaDS.fields[i].newvalue) then //if changed

if (DeltaDS.fields[i].dataType = ftTimeStamp) then
audit1 := audit1+ DeltaDS.fields[i].fieldName+delim+SQLTimeStampToStr(‘dd/mm/yyyy hh:nn’,VarToSQLTimeStamp(DeltaDS.fields[i].newvalue))+delim
else audit1 := audit1+ DeltaDS.fields[i].fieldName+delim+VarToStr(DeltaDS.fields[i].newvalue)+delim;


The above covers most of the database management logic, leaving you to deal with the business logic and the user interface:

  • data validation eg. using the persistent field’s OnValidation event
  • default values eg. using the persistent field’s OnChange event to set values for other fields
  • master-detail tables eg. I prefer to not embed these in a TClientDataset but manually open and close the detail perhaps triggered by a TDatasource event which sets the param of the detail clientdataset then opens it.
  • user interface – put your datamodule in the Uses clause of your form, then place a TDatasource component on your form and link its dataset property to a TClientDataset. Then you have a multitude of data aware components which will link to the TDatasource component.
Posted in Delphi, Uncategorized | Tagged | Leave a comment

Embarcadero announces new Delphi roadmap

see here – view with IE as not all slides show in Firefox!

Very brief summary of the revised roadmap as at August 2010:

RAD Studio XE:

  • coming late 2010
  • adds Delphi for PHP into the RAD Studio bundle
  • latest .NET, ASP.NET and MONO support for Delphi PRISM
  • support for cloud computing including Microsoft AZURE integration
  • deploy to Amazon EC2 infrastructure

Delphi “Fulcrum” Project:

  • as above, plus a few extras including NativeInt and NativeUInt types in readiness for forthcoming 64bit compiler
  • RESTful server creation with Datasnap

64bit command line compiler:

  • projected to be available 1st half of 2011

Delphi “Pulsar” Project:

  • 64bit compiler built in – for 64bit Win compilation only
  • cross-platform 32bit compiler for Win, Mac OSX and Linux

Delphi “Wheelhouse” Project:

  • extended support for 64bit – adds 64bit compiler for C++ Builder for 64bit Win compilation only
  • cross-platform native Windows, Mac OSX and Linux servers with clients based on dbExpress and DataSnap
  • +/- cross-platform VCL-like components
  • new data binding architecture

Delphi “Commodore” Project:

  • full support for 64bit compilers with RTL and VCL library support in Win, Mac OSX and Linux
  • etc.
Posted in Delphi | Tagged | Leave a comment

Delphi – migrating BDE app to DBX4

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:

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.

Posted in Delphi | Tagged , | Leave a comment

TIOBE Programming Community Index Feb 2010

The TIOBE Programming Community Index for Feb 2010 shows that the most popular programming languages are:

  • Java (17% – strongly trending downwards from 26.5% in 2001 – seems governments are NOT interested in java solutions anymore)
  • C (17% trending downwards from 21% in 2001)
  • PHP (10% – rapidly rose from 1.5% 2001 to 5% in 2002 and since has been trending upwards – website only)
  • C++ (9% and rapidly falling from its peak of 17.5% in 2003),
  • VB (7% and falling from its peaks of 11.5% in 2004 an 2008 – a legacy language which will be superceded by C# in the .NET world)
  • C# (5% and strongly trending upwards from 0.5% in 2001 – the main language for .NET)
  • Python (4% trending up from 1% in 2003 – web scripting – fast development but slow running)
  • Perl(3.6% strong down trend from 10.7% in 2004)
  • Delphi (2.7% and trending up from 1.2% in 2002, although has hit higher peaks in between – the only RAD native compiled language for Win32 and soon to be Linux, MacOS and Win64 – fast development and fast running)
  • Javascript (2.6% trending up fro 1.2% in 2002 – scripting for websites and AJAX)
  • Ruby (2.4% – static after a rapid rise to 2.5% in 2007 – scripting for websites)
  • Objective-C (1.8% a big rise from 0.2% a year ago – the main language for Apple Cocoa API thus mainly for MacOS and iPhone apps)
  • Go (1.8% also a big rise from ~zero a year ago – introduced in 2003 as an multi-threaded agent-based language and taken over by Google in late 2009 as an experimental language combining the fast development of Python with the safety and fast running of C++).

See here

Posted in Delphi, programming languages | Tagged , | 1 Comment