Not Insert empty string

[Eden0928]

My temp table structure below:

Code: Select all

CREATE TABLE A_T1(
  F1          integer, 
  F2          varchar(10), 
  F3          varchar(20), 
  Primary Key(F1, F2)
);

Delphi code:

Code: Select all

  ClientDataSet1.Close();
  SQLQuery3.SQL.Text := 'SELECT * FROM A_T1 ';
  ClientDataSet1.Open();
  ClientDataSet1.EmptyDataSet;
  ClientDataSet1.Append();
  ClientDataSet1.Fields[0].AsInteger := 1;
  ClientDataSet1.Fields[1].AsString := '';
  ClientDataSet1.Fields[2].AsString := '';
  ClientDataSet1.Post();
  ClientDataSet1.ApplyUpdates(0);

My SQLMonitor get the logs:

Code: Select all

Execute: SELECT * FROM A_T1

Begin transaction: user "postgres", server "127.0.0.1"
Execute: INSERT INTO "a_t1"
  ("f1", "f2", "f3")
values
  ($1, $2, $3)
:1 (Int32,IN) = 1 
:2 (WideString,IN) = <NULL> 
:3 (WideString,IN) = <NULL> 

Error: Column "f2" in violation of non-Null Null value limit
Rollback: user "postgres", server "127.0.0.1"

Why?

[AlexP]

Hello,

We cannot reproduce the problem. Please specify the IDE version, dbExpress driver version, and SQLConnection parameters.

[Eden0928]

Delphi 2009 V12.0.3420.21218
Devart dbexpress driver for postgreSQL version: 3.3.4

[AlexP]

Hello,

We still cannot reproduce the problem. We have checked this behaviour on the latest driver version, Delphi 2009, and PostgreSQL servers 9.0 and 9.3. Please send a small sample reproducing the problem to alexp*devat*com

[Eden0928]

Hi
My program and pic in:
https://db.tt/MhdT3dB3

[AlexP]

Hello,

We couldn't reproduce the problem even on your project using the same Delphi version and the latest driver version. In addition, please specify the version of the PostgreSQL server (the problem cannot be reproduced on 8.4, 9.0, 9.2). The problem repeats if we explicitly call the Clear method of the f2 field.

[Eden0928]

Can not used PostgreSQL 9.3?

[AlexP]

Hello,

On execution of your application on any server version, the SQLMonitor log shows the following:

Code: Select all

Execute: SELECT * FROM A_T1

Begin transaction: user "postgres", server "db"
Execute: INSERT INTO  a_t1 
  ( f1 ,  f2 )
values
  ($1, $2)
:1 (Int32,IN) = 5 
:2 (WideString,IN) 

Commit: user "postgres", server "db"

I.e., if a parameter is set as an empty string, it is transferred to the server as an empty string; if the parameter is not set at all - it will be missed in the parameters list of the Insert command; if the Clear method of the parameter was called - its value in the parameters list will be 'NULL'. Considering your SQLMonitor log, you have 2 string parameters explicitly set to NULL.

[Eden0928]

Code: Select all

procedure TForm1.ClientDataSet1BeforePost(DataSet: TDataSet);
begin
  if VarIsNull(ClientDataSet1.Fields[1].Value) then
    ClientDataSet1.Fields[1].AsString := '';
end;

I am not input NULL to field.
I am input empty string "''"

If used Devart dbExpress for SQL Server + same example code
1. Create Table A_T1(A varchar(10) primary key, B varchar(10));
A and B is TStringField.

The Error is not exist.

2. Create Table A_T1(A Nvarchar(10) primary key, B Nvarchar(10));
A and B is TWideStringField.

Get same Error message with PostgreSQL.




I don't know why?

My PC Environment in Delphi 2009
Win7 64bit
Delphi 2009
Devart dbExpress driver for PostgreSQL 3.3.4
PostgreSQL 9.0.14 64bit

[Eden0928]

I retry the example part2
In SQL Server 2008 Express:

Code: Select all

CREATE TABLE a_t1
(
  f1 integer NOT NULL,
  f2 nvarchar(10) NOT NULL,
  f3 nvarchar(20),
  f4 datetime,
  CONSTRAINT a_t1_pkey PRIMARY KEY (f1, f2)
)

INSERT INTO a_t1 VALUES (1, '', NULL, NULL)
INSERT INTO a_t1 VALUES (2, '', NULL, NULL)
INSERT INTO a_t1 VALUES (3, '', NULL, NULL)

In PostgreSQL 9.0.14

Code: Select all

CREATE TABLE a_t1
(
  f1 integer NOT NULL,
  f2 varchar(10) NOT NULL,
  f3 varchar(20),
  f4 TimeStamp,
  CONSTRAINT a_t1_pkey PRIMARY KEY (f1, f2)
)

My example project: https://db.tt/ylK2vtbi

[AlexP]

Hello,

Most likely, this problem is due to a bug in Delphi http://qc.embarcadero.com/wc/qcmain.aspx?d=67982 , that was fixed in Update 4 (Database Pack) http://edn.embarcadero.com/article/39392 . Please make sure you have a required update installed.

[Eden0928]

I have update to last Update4 version.
My delphi version: 12.0.3420.21218

(Have Delphi and C++ Builder 2009 Update 4(Database Pack Update))