TMemoField treats blank as null

[crafty]

Hi,

I've got a serious problem with SDAC and TMemoFields. In SQL Server database I have a field 'some_text' (text, not null) and if I try to set the FieldValue to '' (empty) then it is sent as null and I get an exception.

Steps to reproduce:
The table conatins a record (id,...,some_text,...)=(1,...,'',....)

Code: Select all

MsQuery.SQL.Text := 'select * from my_table where id=1';
MsQuery.Open;

Here we have (in debug):
MsQuery.FieldValues['some_text'] = '' [OK]
MsQuery.FieldByName('some_text').IsNull = false [OK]

Code: Select all

MsQuery.Edit;
MsQuery.FieldValues['some_text'] := '';

Now we have (in debug):
MsQuery.FieldValues['some_text'] = '' [OK]
MsQuery.FieldByName('some_text').IsNull = true [WRONG]

Code: Select all

MsQuery.Post;

Exception:
Cannot insert the value NULL into column 'some_text', table 'mydb.dbo.my_table; column does not allow nulls. UPDATE fails.

The same problem exists also with calling Append/Post but only with TMemoFields, with TStringFields everything is OK.

I see this as some kind of a global DAC issue with TMemoFields, because the same situation occurs in MyDac (IsNull=true) but at least the Post doesn't raise an exception there.

Regards,
Crafty

[Antaeus]

The reason of such behaviour is code in DB.pas unit. Please take a look at the TBlobField.GetIsNull method.

[crafty]

The reason of such behaviour is code in DB.pas unit. Please take a look at the TBlobField.GetIsNull method.

OK, but in that case what is your solution to the problem?
Can you somehow overcome this and allow me to save my empty memo?

[Antaeus]

Try to handle this situation by triggers, or use a stored procedure to insert values into the table.

[crafty]

You call this a solution?
If Delphi doesn't distinguish empty and null then you should implement some changes and post '' (empty) when it's required by database and not backstab SDAC users.

P.S. I tried to edit DB.pas and it now returns false for IsNull but SDAC still tried to post null to database