CODEWITHSUNDEEP
sql-serverdelphisql-server-2014adodelphi-10-seattle
Jerry Dodge
Jerry Dodge

Reputation: 27276

Unable to pass empty string into non-null database field

I'm stumped on something which should be very straight-forward. I have a SQL Server database, and I'm trying to update a non-nullable varchar or nvarchar field with an empty string. I know it's possible, because an empty string '' is not the same thing as NULL. However, using the TADOQuery, it is not allowing me to do this.

I'm trying to update an existing record like so:

ADOQuery1.Edit;
ADOQuery1['NonNullFieldName']:= '';
//or
ADOQuery1.FieldByName('NonNullFieldName').AsString:= '';
ADOQuery1.Post; //<-- Exception raised while posting

If there is anything in the string, even just a single space, it saves just fine, as expected. But, if it is an empty string, it fails:

Non-nullable column cannot be updated to Null.

But it's not null. It's an empty string, which should work just fine. I swear I've passed empty strings many, many times in the past.

Why am I getting this error, and what should I do to resolve it?

Additional details:

Upvotes: 7

Views: 2681

Answers (2)

MartynA
MartynA

Reputation: 30715

I can reproduce your reported problem using the code below with SS2014, the OLEDB driver and Seattle and the difference in behaviour when the table has been created with MAX as the column size and a specific number (4096 in my case). I thought I would post this is as an alternative answer because it not only shows how to investigate this difference systematically but also identifies why this difference arises (and hence how to avoid it in future).

Please refer to and execute the code below, as written, i.e. with the UseMAX define active.

Turning on "Use Debug DCUs" in the the project options before executing the code, immediately reveals that the described exception occurs in Data.Win.ADODB at line 4920

Recordset.Fields[TField(FModifiedFields[I]).FieldNo-1].Value := Data

of TCustomADODataSet.InternalPost and the Debug evaluation window reveals that Data at this point is Null.

Next, notice that

update jdtest set NonNullFieldName = ''

executes in an SSMS2014 Query window without complaint (Command(s) completed successfully.), so it seems that the fact that Data is Null at line 4920 is what is causing the problem and the next question is "Why?"

Well, the first thing to notice is that the form's caption is displaying ftMemo

Next, comment out the UseMAX define, recompile and execute. Result: No exception snd notice that the form's caption is now displaying ftString.

And that's the reason: Using a specific number for the column size means that the table metadata retrieved by the RTL causes the client-side Field to be created as a TStringField, whose value you can set by a string assignment statement.

OTOH, when you specify MAX, the resulting client-side Field is of type ftMemo, which is one of Delphi's BLOB types and when you assign string values to an ftMemo field, you are at the mercy of code in Data.DB.Pas , which does all the reading (and writing) to the record buffer using a TBlobStream. The problem with that is that as far as I can see, after a lot of experiments and tracing through the code, the way a TMemoField uses a BlobStream fails to properly distinguish between updating the field contents to '' and setting the field's value to Null (as in System.Variants).

In short, whenever you try to set a TMemoField's value to an empty string, what actually happens is that the field's state is set to Null, and this is what causes the exception in the q. AFAICS, this is unavoidable, so no work-around is obvious, to me at any rate.

I have not investigated whether the choice between ftMemo and ftString is made by the Delphi RTL code or the MDAC(Ado) layer it sits upon: I would expect it is actually determined by the RecordSet TAdoQuery uses.

QED. Notice that this systematic approach to debugging has revealed the problem & cause with very little effort and zero trial and error, which was what I was trying to suggest in my comments on the q.

Another point is that this problem could be tracked down entirely without resorting to server-side tools including the SMSS profiler. There wasn't any need to use the profiler to inspect what the client was sending to the server because there was no reason to suppose that the error returned by the server was incorrect. That confirms what I said about starting investigation at the client side.

Also, using a table created on the fly using IfDefed Sql enabled the problem effectively to be isolated in a single step by simple observation of two runs of the app.

Code

uses [...] TypInfo;
[...]
implementation[...]

const
   //  The following consts are to create the table and insert a single row
   //
   //  The difference between them is that scSqlSetUp1 specifies
   //  the size of the NonNullFieldName to 'MAX' whereas scSqlSetUp2 specifies a size of 4096

   scSqlSetUp1 =
  'CREATE TABLE [dbo].[JDTest]('#13#10
   + '  [ID] [int] NOT NULL primary key,'#13#10
   + '  [NonNullFieldName] VarChar(MAX) NOT NULL'#13#10
   + ') ON [PRIMARY]'#13#10
   + ';'#13#10
   + 'Insert JDTest (ID, [NonNullFieldName]) values (1, ''a'')'#13#10
   + ';'#13#10
   + 'SET ANSI_PADDING OFF'#13#10
   + ';';

   scSqlSetUp2 =
  'CREATE TABLE [dbo].[JDTest]('#13#10
   + '  [ID] [int] NOT NULL primary key,'#13#10
   + '  [NonNullFieldName] VarChar(4096) NOT NULL'#13#10
   + ') ON [PRIMARY]'#13#10
   + ';'#13#10
   + 'Insert JDTest (ID, [NonNullFieldName]) values (1, ''a'')'#13#10
   + ';'#13#10
   + 'SET ANSI_PADDING OFF'#13#10
   + ';';

   scSqlDropTable = 'drop table [dbo].[jdtest]';

procedure TForm1.Test1;
var
  AField : TField;
  S : String;
begin

//  Following creates the table.  The define determines the size of the NonNullFieldName

{$define UseMAX}
{$ifdef UseMAX}
  S := scSqlSetUp1;
{$else}
  S := scSqlSetUp2;
{$endif}

  ADOConnection1.Execute(S);
  try
    ADOQuery1.Open;
    try
      ADOQuery1.Edit;

      // Get explicit reference to the NonNullFieldName
      //  field to make working with it and investigating it easier

      AField := ADOQuery1.FieldByName('NonNullFieldName');

      //  The following, which requires the `TypInfo` unit in the `USES` list is to find out which exact type
      //  AField is.  Answer:  ftMemo, or ftString, depending on UseMAX.  
      //  Of course, we could get this info by inspection in the IDE
      //  by creating persistent fields

      S := GetEnumName(TypeInfo(TFieldType), Ord(AField.DataType));
      Caption := S;  // Displays `ftMemo` or `ftString`, of course

      AField.AsString:= '';
      ADOQuery1.Post; //<-- Exception raised while posting
    finally
      ADOQuery1.Close;
    end;
  finally
    //  Tidy up
    ADOConnection1.Execute(scSqlDropTable);
  end;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  Test1;
end;

Upvotes: 13

Jerry Dodge
Jerry Dodge

Reputation: 27276

The problem occurs when using MAX in the data type. Both varchar(MAX) and nvarchar(MAX) exploit this behavior. When removing MAX and replacing it with a large number, such as 5000, then it allows empty strings.

Upvotes: 2

Related Questions