Delphi – database info

Delphi Datasnap:

links:

• About.com – using database components –
• getting started with dbExpress – by Martin Rudy
• how to use autoinc fields in Datasnap – by John Kaster
• creative solutions using TClientdataset components – by Martin Rudy
• effective ClientDatasets and the Briefcase Model – by Dr Bob
• maximise Datasnap efficiency – by Dr Bob
• Dr Bob’s Datasnap / SOAP / web service tutorials
• Datasnap SOAP Web services –

Json database callbacks in Delphi 2010 Datasnap:

• Adrien Andrei’s blog
• Datasnap review
• Daniele Teti’s blog

database connection options:

• Borland Database Engine (BDE) – now legacy status
• dbGo – VCL for MS ADO
• dbExpress native drivers for cross-platform deployment – now deprecated?
• RemObjects Data Abstraction – 3rd party VCL and .NET component suite with push data and data abstraction layers
• FireDAC – now included in Delphi XE4 and later for cross platform deployment as replacement for dbExpress
• UniDac – 3rd party native drivers
• myDAC – 3rd party native driver and components to access MySQL databases
• Adonis – 3rd party ADO component suite

ADO issues and help:

• cancel an asynchronous ADO query
• accessing MS Excel spreadsheets
• accessing MS Access 2007 and MS Excel 2007 files:
• download ACE engine from Microsoft and install, using TADOConnection set connection string such as Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myAccess2007.accdb;Persist Security Info=False;

TClientDataset issues:

• if you add an InternalCalc field to a ClientDataset and try to CreateDataset you will get a “Name not unique in this context” error – to avoid this error, ensure StoreDefs = false and clear field defs.
• you cannot use Calculated or Lookup fields in an index – using a InternalCalc field which is assigned a lookup field value does NOT work as the lookup field value will be null at the time internal calc is processed!
• if using persistent fields, ensure provider flags are set the same in the ClientDataset as they are in the linked TQuery, otherwise you may get an error on Applyupdates – likewise set UpdateMode the same on both – eg. UpWhereKeyOnly.
• don’t forget to add ApplyUpdates(-1) code to the AfterPost event to ensure edits are saved to the server.
• “Missing Dataset Property” error when opening forms or datamodules in the IDE
• this is usually due to saving a form or datamodule with a TClientDataset set to active = true, but the dataset resides on a different datamodule which has not been opened in the IDE prior. Solution 1 – set TClientDataset.active = false and programmatically set active = true during run time. Solution 2 – always open the referenced datamodule BEFORE the form or datamodule containing the TClientDataset.

dbExpress issues and help:

• does not support ftDateTime fields as does BDE/dbGo VCL components which may cause a migration issue – need to convert all persistent ftDateTime fields to ftSQLTimeStamp type.
• fix for invalid field type error when using ftDateTime parameters – need to use ftTimeStamp and convert using DateTimetoSQLTimeStamp
• from Delphi 2006 and onwards, the DBExpress components now use v4 technology instead of the older COM-based v3 DBExpress
• D2007 version appears to have a memory leak associated with the Sybase ASE driver – see here – apparently fixed in D2010 updates.
• Sybase ASE v 15.5 uses different client dlls, so in the dbxdrivers.ini, you need to change the [ASE] section from VendorLib=libct.dll;libcs.dll which is ASE 12.5 to VendorLib=libsybct.dll;libsybcs.dll which is ASE 15.5
  
• configuring v4: remember to right click on the TSQLConnection component to edit the connection properties – see Dr Bob
• remember to deploy the DBExpress driver dll for the database, and add midas to your uses clause to avoid having to deploy the midas.dll file as well. If you use DBXTraceConnection or DBXPoolConnection, then you will also need to deploy the dbxdrivers.ini and dbxconnections.ini files. But at least you don’t have to worry about editing the registry.
• using dbx4 driver level +/- tracing or pooling – see here

Sybase issues:

• Sybase end of life notification for ODBC driver and OleDB provider for ASE 12.5x and 15.x

.NET data and XML:

• use ADO.NET XML data in Delphi – by Deepak Shenoy