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.

score 5 · Accepted Answer · answered Sep 26 '13 at 16:03

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.

score 3 · Answer 2 · answered Sep 26 '13 at 10:32

3

TField has property for default value, but it is string and unfortunately empty string means that there is no default value, so it doesn't help in your case. But you can catch OnBeforePost event from dataset and check, if field is NULL then set it up with empty string.

answered Sep 26 '13 at 10:32

Andrei Galatyn

3,322
2
24
38

How would I do this? In the OnBeforePost routine I've added this; if IBDataset1.FieldByName('OPTION_TEXT').IsNull then IBDataset1.FieldByName('OPTION_TEXT').Value := ''; – srayner Sep 26 '13 at 11:12
@srayner Yes, it should be enough. – Andrei Galatyn Sep 26 '13 at 11:23
Sorry i forgot to add that this doesn't work because is gives 'validation error for column OPTION_TEXT, value ''*** null ***'' – srayner Sep 26 '13 at 11:31
@srayner Probably you changed property Required of TField to True. Change it back to default value (False). – Andrei Galatyn Sep 26 '13 at 11:43
No it's set to false. If you make it true, then it states OPTION_TEXT must have a value. – srayner Sep 26 '13 at 12:07
@srayner I neverr used TIBStringField, but if there is property EmptyAsNull (as Marcus mentioned), try to set it False from the code (at OnCreate section for example). – Andrei Galatyn Sep 26 '13 at 16:27

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

2 Answers2