Insert data with Null value instead of default value

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
galit
Posts: 18
Joined: Mon 12 Feb 2007 11:07

Insert data with Null value instead of default value

Post by galit » Tue 13 May 2014 14:14

Hello,

Using Delphi7 with SDAC 6.9.17
Recently we've noticed some change in behavior between SDAC & DBExpress
We have a table and a field with decimal(28,9) type & default 0 value & allow null values
when we create a new row for this table with the SDAC components and we set the value of the field to be Null

for example:
DataSet.FieldByName('field1').Value := Null;
or
DataSet.FieldByName('field1').Clear();

in both cases after the Dataset.Post() action the field is being ignored in the insert query which is incorrect because we've set the value to be Null
this results with a new row being inserted with the default value which is 0 and not Null as it was intended

this behavior works fine when we are using the DBExpress components
we've also noticed that in DBExpress the field's new value property is set to unassigned and when we set it to Null it is included in the insert query as it should.

Thanks,

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Insert data with Null value instead of default value

Post by azyk » Wed 14 May 2014 14:31

Hello,

Such behavior is due to that fields with NULL value are not included to the INSERT SQL query.
To solve the problem, it is enough to set the TMSQuery.Options.UpdateAllFields option to True.
In this case, all the fields will be included to the INSERT SQL query.

You can find more details in the SDAC help http://www.devart.com/sdac/docs/index.h ... fields.htm

dupe
Posts: 20
Joined: Wed 15 Jun 2016 13:37

Re: Insert data with Null value instead of default value

Post by dupe » Fri 17 Jun 2016 13:27

Hi,

we have the same problem. We are migrating from ADO to SDAC and in generally fields with NULL value shouldn't be in the INSERT-Statement, because we are using SQL Server constraints to set default values.
But in some cases we explicitly want to set the value to NULL.
So we can not use TMSQuery.Options.UpdateAllFields.

ADO collects all modified fields in TCustomADODataSet.SetFieldData FModifiedFields.
Could you extend SDAC's behaviour with such a mechanism?

ViktorV
Devart Team
Posts: 2299
Joined: Wed 30 Jul 2014 07:16

Re: Insert data with Null value instead of default value

Post by ViktorV » Mon 20 Jun 2016 07:41

We will consider the possibility to change this behavior of SDAC.
If you want us to implement the feature faster, please post it at our user voice forum: https://devart.uservoice.com/forums/104 ... sql-server If the suggestion gets a lot of votes, we will consider the possibility to implement it.

Post Reply