Not Insert empty string

Not Insert empty string

Postby Eden0928 » Fri 08 Nov 2013 05:18

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?
Eden0928
 
Posts: 44
Joined: Sun 22 Apr 2012 14:08

Re: Not Insert empty string

Postby AlexP » Fri 08 Nov 2013 12:00

Hello,

We cannot reproduce the problem. Please specify the IDE version, dbExpress driver version, and SQLConnection parameters.
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Re: Not Insert empty string

Postby Eden0928 » Fri 08 Nov 2013 14:54

Delphi 2009 V12.0.3420.21218
Devart dbexpress driver for postgreSQL version: 3.3.4
Eden0928
 
Posts: 44
Joined: Sun 22 Apr 2012 14:08

Re: Not Insert empty string

Postby AlexP » Mon 11 Nov 2013 11:29

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
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Re: Not Insert empty string

Postby Eden0928 » Mon 11 Nov 2013 15:52

Hi
My program and pic in:
https://db.tt/MhdT3dB3
Eden0928
 
Posts: 44
Joined: Sun 22 Apr 2012 14:08

Re: Not Insert empty string

Postby AlexP » Mon 11 Nov 2013 16:42

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.
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Re: Not Insert empty string

Postby Eden0928 » Mon 11 Nov 2013 23:06

Can not used PostgreSQL 9.3?
Eden0928
 
Posts: 44
Joined: Sun 22 Apr 2012 14:08

Re: Not Insert empty string

Postby AlexP » Tue 12 Nov 2013 08:17

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.
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Re: Not Insert empty string

Postby Eden0928 » Wed 13 Nov 2013 01:22

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
 
Posts: 44
Joined: Sun 22 Apr 2012 14:08

Re: Not Insert empty string

Postby Eden0928 » Wed 13 Nov 2013 04:04

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
Eden0928
 
Posts: 44
Joined: Sun 22 Apr 2012 14:08

Re: Not Insert empty string

Postby AlexP » Wed 13 Nov 2013 08:39

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.
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Re: Not Insert empty string

Postby Eden0928 » Wed 13 Nov 2013 10:03

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))
Eden0928
 
Posts: 44
Joined: Sun 22 Apr 2012 14:08


Return to dbExpress driver for PostgreSQL