Desiderata software® and consultancy blog
specialising in clinical database solutions for Australian hospitals
specialising in clinical database solutions for Australian hospitals
Jun 3rd
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.
Mar 7th
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.
Feb 18th
Two major dilemmas arise with database application development:
How to manage the different SQL dialects:
How to swap between different database engines:
Jan 31st
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:
Dec 21st
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;
Dec 17th
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.
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.
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.
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.
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:
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;
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;
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.
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;
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;
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;
Aug 13th
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:
Delphi “Fulcrum” Project:
64bit command line compiler:
Delphi “Pulsar” Project:
Delphi “Wheelhouse” Project:
Delphi “Commodore” Project:
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:
Steps to migrate:
“Parameter invalid” exception when trying to open TClientDataset at design time or run time:
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.
Feb 27th
The TIOBE Programming Community Index for Feb 2010 shows that the most popular programming languages are:
See here
Jun 7th
Desiderata software® and consultancy is based in Melbourne, Australia and specialises in clinical database systems and clinical decision making algorithms.
Desiderata:
L. desiderata – something desired
in decision analysis – the desired characteristics of a decision making algorithm
and, of course, the famous poem by Max Ehrman – go placidly amid the noise and haste ...
some excerpts from the poem which reflect the ideals of Desiderata Software:
Speak your truth quietly and clearly; and listen to others,
even to the dull and ignorant; they too have their story.
Exercise caution in your business affairs,
for the world is full of trickery.
But let this not blind you to what virtue there is;
many persons strive for high ideals,
and everywhere life is full of heroism
And whatever your labors and aspirations,
in the noisy confusion of life, keep peace with your soul.
With all its sham, drudgery and broken dreams,
it is still a beautiful world.
Be cheerful. Strive to be happy.
Desiderata Software is a Registered Trademark ®
Recent Comments