Watch, Follow, &
Connect with Us
Public Report
Report From: Delphi-BCB/Database/TField/TBlobField/TMemoField    [ Add a report in this area ]  
Report #:  98568   Status: Open
TClientDataSet treates MSSQL varchar (max) with empty string as NULL
Project:  Delphi Build #:  2010, ... XE2
Version:    16.0 Submitted By:   David Pedrelli
Report Type:  Basic functionality failure Date Reported:  9/5/2011 12:11:43 PM
Severity:    Serious / Highly visible problem Last Updated: 3/20/2012 2:24:39 AM
Platform:    All versions Internal Tracking #:   287519
Resolution: None (Resolution Comments) Resolved in Build: : None
Duplicate of:  None
Voting and Rating
Overall Rating: No Ratings Yet
0.00 out of 5
Total Votes: None
Description
I get a SQL error that NOTES does not allow NULLs.

Firstly, the RAD Studio about box shows that Delphi and C++ Builder 2010 Update 4 and Update 5 are both installed.

I have an MSSQL 2008 table with an optional column called NOTES of type varchar (max).  I am using dbExpress TSQLQuery, TDataSetProvider, and TClientDataSet to access the table.  Delphi imports the varchar (max) column as a TMemoField.

1)  If I add a new row, do not set any value for NOTES, and ApplyUpdates, MSSQL returns an error that column NOTES does not allow NULLs.

2)  If I add a new row, default NOTES to '' (empty string), and ApplyUpdates, MSSQL returns an error that column NOTES does not allow NULLs.

3)  If I edit an existing row and delete the value for NOTES and ApplyUpdates, MSSQL returns an error that column NOTES does not allow NULLs.

If I create NOTES as a varchar (150), Delphi imports it as a TEditField, and I do not have the above problem.

I tried stepping through the code to trace the problem.  I can step through everything, except for units that are in RAD Studio\7.0\source\database.  The library path is set, but breakpoints that I set in those units are ignored.  The breakpoints have white X's on them.  If I set a breakpoint in my code and try to step into one of those units, the code executes as if I clicked step over.
Steps to Reproduce:
Added by Sysop
<<<<<<<
Please see [Comments] of QC for more detailed [Steps] to reproduce this issue.
I get a SQL error that NOTES does not allow NULLs.
>>>>>>>


Run MSSQL script to create table.

CREATE TABLE tbl_TEST (

ID INT IDENTITY (1, 1) NOT NULL CONSTRAINT PKC_TEST PRIMARY KEY  CLUSTERED ,

[NAME] VARCHAR (40) NOT NULL CONSTRAINT CK_TEST_NAME CHECK (LEN([NAME]) > 1) ,

NOTES VARCHAR (MAX) NOT NULL CONSTRAINT DF_TEST_NOTES DEFAULT ('') ,
)
GO

I wrote a test app and I will include an attachment.  Here is the PAS code from the form.

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, WideStrings, DBXMsSQL, FMTBcd, ExtCtrls, DBCtrls, StdCtrls, DB, Mask,
  DBClient, Provider, SqlExpr;

type
  TForm1 = class(TForm)
    dbXSQL: TSQLConnection;
    SQLQuery1: TSQLQuery;
    SQLQuery1ID: TIntegerField;
    SQLQuery1NAME: TStringField;
    SQLQuery1NOTES: TMemoField;
    DataSetProvider1: TDataSetProvider;
    ClientDataSet1: TClientDataSet;
    ClientDataSet1ID: TIntegerField;
    ClientDataSet1NAME: TStringField;
    ClientDataSet1NOTES: TMemoField;
    Label1: TLabel;
    DBEdit1: TDBEdit;
    DataSource1: TDataSource;
    Label2: TLabel;
    DBMemo1: TDBMemo;
    DBNavigator1: TDBNavigator;
    CheckBox1: TCheckBox;
    procedure FormShow(Sender: TObject);
    procedure ClientDataSet1AfterPost(DataSet: TDataSet);
    procedure ClientDataSet1NewRecord(DataSet: TDataSet);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.ClientDataSet1AfterPost(DataSet: TDataSet);
begin
  ClientDataSet1.ApplyUpdates(-1)
end;

procedure TForm1.ClientDataSet1NewRecord(DataSet: TDataSet);
begin
  if CheckBox1.Checked then
    ClientDataSet1NOTES.Value := ''
end;

procedure TForm1.FormShow(Sender: TObject);
begin
  ClientDataSet1.Open
end;

end.
Workarounds
None
Attachment
Project.zip
Comments

Tomohiro Takahashi at 9/6/2011 8:59:12 PM -
With attached GUI client, could you please write [Steps] to confirm your issue?
For example,
1. launch attached client
2. you will see existing rows in DBGrid
3. turn on 'Default Notes to empty string?'
4. press [+] button
5. enter some data into [NAME] field
6. press [post] button
7. you will see ...
expected result: ?
actual result: ?

David Pedrelli at 9/7/2011 6:31:37 PM -
Steps to run program
1)  Run the script to create the MSSQL table.
2)  The TSQLConnection points to my server and my database.  Edit the properties to point to your server and database.  It will prompt for user name.
3)  Compile and run program.

Steps to produce problem, after program is running.
1)  Click <add> on the DBNavigator.
2)  Enter a value for NAME, but leave NOTES blank.
3)  Click <save> on the DBNavigator.
In this case, NOTES is never given a value.  Since delta does not change from data, I would expect that column to no be included in the INSERT statement.


1)  Click "Default Notes to empty string?"  In this case, OnNewRecord defaults NOTES.Value = ''.  
2)  Click <add> on the DBNavigator.
3)  Enter a value for NAME, but leave NOTES blank.
4)  Click <save> on the DBNavigator.

In both cases, I get a SQL error that NOTES does not allow NULLs.

Tomohiro Takahashi at 9/7/2011 8:18:36 PM -
> ...  I get a SQL error that NOTES does not allow NULLs.
I confirmed the error with Delphi 2010 + MS SQL Server 2008.
Thanks.

David Pedrelli at 9/3/2012 7:32:37 AM -
This problem exists for Delphi XE2, SQL Server 2012 Express, and SQL Server Native Client 2008 (10) as well.  Is there a fix for it?  I tried to use Native Client 11, but DataSnap seems to expect version 10.

Server Response from: ETNACODE01