Unable to pass empty string to nonzero database field

Question

I'm at a standstill for something that needs to be very straightforward. I have a SQL Server database and am trying to update a nullchar varchar or nvarchar variable with an empty string. I know this is possible because an empty string '' is not the same as NULL . However, using TADOQuery , this does not allow me to do this.

I am trying to update an existing entry as follows:

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

If there is anything in the line, even just one space, it saves only the penalty, as expected. But, if this is an empty string, it fails:

A column with a null value cannot be updated to Null.

But it is not empty. This is an empty string that should work fine. I swear I skipped blank lines many times, many times in the past.

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

---

Additional Information:

• Database: Microsoft SQL Server 2014 Express
• Language: Delphi 10 Seattle Update 1
• Database Drivers: SQLOLEDB.1
• Field update: nvarchar(MAX) NOT NULL

Answer 1

I can reproduce your reported problem using the following code: SS2014, OLEDB driver and Seattle and the difference in behavior when the table was created with MAX as the column size and specific number (4096 in my case). I thought I would publish this as an alternative because it not only shows how to systematically investigate this difference but also determines why this difference arises (and therefore how to avoid it in the future).

Please review the code below and execute it as indicated, i.e. using UseMAX define active.

Enabling the "Use Debug DCU" parameters in the project parameters before executing the code immediately shows that the described exception occurs in Data.Win.ADODB on line 4920

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

of TCustomADODataSet.InternalPost , and the debug evaluation window shows that Data at this point is Null .

Then notice that

 update jdtest set NonNullFieldName = '' 

is executed in the SSMS2014 request window without complaint ( Command(s) completed successfully. ), so it seems that the fact that Data Null on line 4920 is what causes the problem, and the next question is: “Why?”

Well, the first thing to notice is that ftMemo displayed in the form ftMemo

Next, comment out UseMAX define, recompile, and execute. Result: There are no snd exceptions, notice that ftString now displayed in the form ftString .

And what’s the reason: using a specific number for the column size means that the table metadata received by RTL creates the client side of Field as TStringField , the value of which you can set using the row assignment statement.

OTOH, when you specify MAX, the resulting client side of Field is of type ftMemo, which is one of the BLOB types of Delphi type, and when you assign a string to the ftMemo field, you are at the mercy of the code in Data.DB.Pas that does all the reading ( and write) to the write buffer using TBlobStream . The problem is that, as far as I see, after a lot of experimentation and code tracking, the way TMemoField uses BlobStream does not properly distinguish between updating the contents of the field to '' and setting the field to Null (as in System.Variants).

In short, whenever you try to set the TMemoField value to an empty string, what actually happens is that the field state is set to Null, and that is what causes the exception in q. AFAICS, this is inevitable, so nothing is visible to me.

I did not investigate whether the choice was made between ftMemo and ftString using the Delphi RTL code or the MDAC (Ado) level on which it sits: I would expect it to actually be determined by RecordSet using TAdoQuery.

QED Please note that this systematic approach to debugging revealed problems and causes with very little effort and zero trial and error, which I tried to suggest in my comments on q.

Another point is that this problem could be tracked completely without resorting to server tools, including the SMSS profiler. There was no need to use the profiler to verify that the client was sending to the server because there was no reason to believe that the error returned by the server was incorrect. This confirms what I said about the start of the investigation on the client side.

In addition, using an on-the-fly table using IfDef ed Sql effectively isolates the problem in one step by simply observing two application runs.

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; 

Answer 2

The problem occurs when using MAX in a data type. Both varchar(MAX) and nvarchar(MAX) use this behavior. When you remove MAX and replace it with a large number, such as 5000 , it allows blank lines.