Page 1 of 1

Not Insert empty string

Posted: Fri 08 Nov 2013 05:18
by 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?

Re: Not Insert empty string

Posted: Fri 08 Nov 2013 12:00
by AlexP
Hello,

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

Re: Not Insert empty string

Posted: Fri 08 Nov 2013 14:54
by Eden0928
Delphi 2009 V12.0.3420.21218
Devart dbexpress driver for postgreSQL version: 3.3.4

Re: Not Insert empty string

Posted: Mon 11 Nov 2013 11:29
by 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

Re: Not Insert empty string

Posted: Mon 11 Nov 2013 15:52
by Eden0928
Hi
My program and pic in:
https://db.tt/MhdT3dB3

Re: Not Insert empty string

Posted: Mon 11 Nov 2013 16:42
by 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.

Re: Not Insert empty string

Posted: Mon 11 Nov 2013 23:06
by Eden0928
Can not used PostgreSQL 9.3?

Re: Not Insert empty string

Posted: Tue 12 Nov 2013 08:17
by 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.

Re: Not Insert empty string

Posted: Wed 13 Nov 2013 01:22
by 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

Re: Not Insert empty string

Posted: Wed 13 Nov 2013 04:04
by 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

Re: Not Insert empty string

Posted: Wed 13 Nov 2013 08:39
by 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.

Re: Not Insert empty string

Posted: Wed 13 Nov 2013 10:03
by 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))