Delphi FireDAC, get Database Default values

Question

I am migrating an application written in Delphi XE8 using the newest Advantage Database Server (ADS) to Delphi 12.2 Enterprise but for now staying with ADS. The database tables are configured with field Default values, constraints, Secondary Indexes, ... Of course it works perfectly using the XE8 ADS components back then.

In the FireDAC application when a new record is added those Default values don't show up using FDTable or FDQuery. I thought maybe it was because of the ADS, so I created an ADO MSAccess test app, but it acts the same. I had some fields with default text values. I can add a record, then Post, then Refresh and the Default values appear.

So, can FireDac with the proper Fetch or other settings/options retrieve those Defaults for OnNewRecord?

Thanks,

Mark

Answer 1

If your application is not writing the default values then they will be assigned by the database. However you will need to read the record to get the values. TFDDataSet implements a method RefreshRecord to refresh the current record which you could call in the AfterPost event handler.

It's not clear from the description of your problem how your application is working. I'm not familiar with ADS, but I regularly use FireDAC with other databases.

The database definition gives a default value to columns in newly created records where a value is not specified in the INSERT statement. So if the values are specified in the INSERT statement then the default values will not apply, instead you will get the values supplied in the INSERT statement.

You can check what FireDAC is actually writing by use the built in monitoring. Setup a monitoring component, and set the monitoring on your TFDConnection to use it. You will then be able to see what's actually being written to the database. There is a monitoring application supplied with Delphi that will allow you to see the activity in real time in the monitoring application window.

It's a little difficult to know what your application needs to be doing that it isn't doing from your description. Without knowing how you are actually constructing your INSERT statement ("using FireDAC" is not the answer here, are you using a TFDCommand that you have populated yourself, or are you using something else to built it for you, for example TFDMemTable) it's difficult to advise.

However, if you create a record using an INSERT statement through TFDCommand then any columns that you don't set in the statement will have the default value from the column definition.

This is how I would expect the system to work, it may be that you are expecting something else as you are used to using different components.

If what you are wanting to do is have the system read the record after you have written it then you will need to read the record. I do this myself in several systems, but I have always done it by issuing a SELECT query myself. As pointed out above if you are using something that derives from TFDDataSet you can call RefreshRecord in the AfterPost event handler.

If you are not using them, you may find that using TFDMemTable with TFDTableAdapter and TFDCommand (for the various table actions) helps you. There are various things built into this model which are generally helpful.