Updating from Paradox – Handling field names containing # symbol.

I was asked to upgrade an old database system that used the BDE to interface to Paradox tables to FireDAC and MS Access.

The first job was to use FireDAC to read the Paradox tables and then move the code across to equivalent MS Access tables.

I ran into a problem because some of the paradox tables had the # symbol as part of their field names (for example “House#” for house numbers in an address). Another problem was use of the field name “Date” which caused problems as it was interpreted as an SQL type name.

FireDAC and the ODBC bridge to the paradox tables didn’t seem to handle these names very well. In particular it was impossible to update the field contents with new values.

My working solution was to use the TFDBatchMove, TFDBatchMoveDataSetReader and TFDBatchMoveDataSetWriter components to move the contents of the paradox tables into the equivalent tables in MS Access.

The TFDQuery used as the datasource for the TFDBatchMoveDataSetReader contained the following SQL statement:

SELECT “House#” as HouseNum, StreetName, Town, City, “Date” as DateEntered FROM ADDRESS

Notice the “House#” and the “Date” field names in the paradox table being in inverted commas and being renamed as part of the query, ready for the batch data move.

Making Wrong Code Look Wrong

Here’s a very good article which discusses a coding design principle “Making wrong code look wrong”.

Understanding the contents of this article will make any C++ or Delphi programmer write better code.

Making Wrong Code Look Wrong – Joel Spolsky on Software.pdf

Author Joel Spolsky is a great thinker on how to write good code.

As a C++ programmer I do feel I have to take issue with his arguments against C++ exceptions. You can regard the C++ Exception as a “goto” with an unknown destination. This does sound like a recipe for a “bad smell” in your code. But C++ Exceptions are designed for handling errors that occur in a self contained part of code that can’t know what the correct way (because it depends on the time of usage) to behave when the error is encountered. By passing back an exception it allows the calling code to decide how to handle the error condition. This is an invaluable tool when used correctly. C++ Exceptions are great for solving this design challenge. But please don’t use them for anything else. Any other use immediately drifts into the problem area discussed by Joel Spolsky in his document.