How do i get a TDataset to store empty string instead of null?

Question

I have a required field in my database (NOT NULL), but empty strings are allowed.

How do I get a delphi TDataset to work with this? With the required property of the field object set to either true or false it still seems to be trying to store null instead of an empty string.

For info im using a TIBDataset and a TIBStringField.

Answer 1

Normally, you can set the value in the OnBeforePost like this:

if IBDataSet1.FieldByName('OPTION_TEXT').IsNull then
begin
  IBDataset1.FieldByName('OPTION_TEXT').Value = '';
end;

However, TIBStringField has an unpublished property EmptyAsNull which you must set to False. The default value is True. When this feature is enabled, the dataset does you a favor and converts empty strings to NULL:

You can turn it off like this:

if IBDataSet1.FieldByName('OPTION_TEXT').IsNull then
begin
  TIBStringField(IBDataset1.FieldByName('OPTION_TEXT')).EmptyAsNull := False;
  IBDataset1.FieldByName('OPTION_TEXT').Value = '';
end;

Alternatively, you could set the EmptyAsNull property on the string fields in your form's OnCreate if you are using static (design time) fields, or wherever your create your fields.