Dbexpress convert empty string to null

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for MySQL in Delphi and C++Builder
Post Reply
AngelusB
Posts: 11
Joined: Tue 20 Jan 2009 11:17

Dbexpress convert empty string to null

Post by AngelusB » Wed 04 Mar 2009 15:44

I'm converting an application from D2007 to D2009.
Using DbExpress en dbexpmda40.dll (4.40.0.14).
I use TCrSQLConnection (driver DevartMySQLDirect).
The application works fine in D2007.
In D2009 I did add the parameter UseUnicode=true to the TCrSQLConnection and converted TStringFields in TWideStringFields.

In the MySQL database some fields are NOT NULL. As example I used a DBGrid. When I empty a Widestring, the information send to the database (after applyupdates) is an Null value. So anywhere the empty string is converted to a null- value. This causes a EMyError 25000 : column cannot be null.
What's wrong?

ffcDev
Posts: 8
Joined: Mon 05 May 2008 14:00

Post by ffcDev » Wed 04 Mar 2009 19:40

Hi, I'm having an issue stemming from the same problem as well...

When I pull some data into a TClientDataset using a Provider with UpdateMode := upWhereAll, and then i apply updates to a changed record: Some of the string fields in the record being updated are blank (but not null) so I get "Record not found or changed by another user" because the update statement is sending `where myStringField is null` instead of `where myStringField = ""`

Test table:

create table ATest(
ID integer not null,
Name varchar(50) not null,
Name2 varchar(50) not null
);
insert into ATest values(1,'text','text')
insert into ATest values(2,'text','')

Applying updates to a change in the first record works,
the second fails.

My specs:

dbexpmda40.dll 4.40.0.14
Delphi 2009 SP2
MySQL 5.0.51a on Ubuntu 8.04.1
Win XP SP3

My setup:

TClientDataset->TDataSetProvider->TSQLQuery->TSQLConnection->DevartMySQLDirect

thanks,
Bill.

Dimon
Devart Team
Posts: 2910
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 05 Mar 2009 09:22

This behaviour is connected with the work of TDataSetProvider and does not depend on DbxMda.
The point is that the update queries are generated by TDataSetProvider and dbExpress driver for MySQL only executes them.

ffcDev
Posts: 8
Joined: Mon 05 May 2008 14:00

Re: Dbexpress convert empty string to null

Post by ffcDev » Thu 05 Mar 2009 19:29

AngelusB wrote:When I empty a Widestring, the information send to the database (after applyupdates) is an Null value.
I found a fix on codegear's QC -- use the workaround for QC# 67891. To use the fix, save a copy of DB.pas in your project folder. (keep the name DB.pas) and apply the changes they recommend.

This worked for allowing empty strings in fields to be set properly, but i still can't apply updates. What components are you using to update the data to the database? (Because TClientDataset + TDatasetProvider is choking for me, i may need an alternative...)

thanks,
Bill.

Dimon
Devart Team
Posts: 2910
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Fri 06 Mar 2009 10:24

You can see Data Access Components for MySQL (MyDAC) by the following link: http://devart.com/mydac/
MyDAC is a library of components that provides direct access to MySQL database servers.

AngelusB
Posts: 11
Joined: Tue 20 Jan 2009 11:17

Re: Dbexpress convert empty string to null

Post by AngelusB » Fri 06 Mar 2009 12:16

ffcDev wrote: This worked for allowing empty strings in fields to be set properly, but i still can't apply updates. What components are you using to update the data to the database?
I'm using the same components as you use:TClientDataset->TDataSetProvider->TSQLQuery->TSQLConnection->DevartMySQLDirect

As you write, the workaround in QC #67891 does not solve the problem.

Discovered some strange behavior of MySQL 5.0.51a. I use a windows installation (localhost) as well as a linux one (Suse enterprice 10.1).
Following your example: if you empty field "Name" in record with id 1, this causes an error on windows-MySQL and no error on linux-MySQL. Examining the query that was sent, gives a null-value for field Name. MySQL accepts the null value and write an empty string (!).

Both on Linux and on Windows empty the Name field in record with id 2 causes an error (record not found etc etc)
I'm working for years with the components. This problem is new in D2009.

ffcDev
Posts: 8
Joined: Mon 05 May 2008 14:00

Post by ffcDev » Fri 06 Mar 2009 17:24

AngelusB: I've posted QC# 71984... let's see what we get...

Dimon: Your MyDAC Components look interesting - thanks!

Bill.

AngelusB
Posts: 11
Joined: Tue 20 Jan 2009 11:17

Post by AngelusB » Mon 09 Mar 2009 19:22

ffcDev wrote:AngelusB: I've posted QC# 71984... let's see what we get...
Thanks Bill.
In addition to your example: Using a DBGrid, empty the 'text' data (e.g. field "Name") and ApplyUpdates.
The query will look like:
update
ATest
set
Name is null <--wrong
where
ID = 1 and
Name = 'text' and
Name2= 'text'

ffcDev
Posts: 8
Joined: Mon 05 May 2008 14:00

Post by ffcDev » Tue 10 Mar 2009 15:33

AngelusB wrote:Name is null <--wrong
Did you apply the workaround to DB.pas from QC#67891? It looks like the patch fixed this one part of the issue for me...

AngelusB
Posts: 11
Joined: Tue 20 Jan 2009 11:17

Post by AngelusB » Mon 16 Mar 2009 19:53

ffcDev wrote:
AngelusB wrote:Name is null .
See my posted message on Mar 06 about the behaviour of MySQL on Linux and Windows. All the way: the parameter sent is and not an empty string.

peterback
Posts: 1
Joined: Thu 04 Nov 2004 16:49

Workaround if anyone needs it

Post by peterback » Fri 10 Sep 2010 13:42

Hi, well this solves the null problem. Just patch that in to MySqlNet and recompile. It effectively "search and replaces" the nulls, replacing them with double quotes. It turns out that Delphi uses ", NULL" so if you want to use nulls in your own query, you can simply use lowercase or lose the space between the comma and the N. The qualifier variable ensures that it doesn't effect actual data, and the escape variable takes into account backslashes.

Hope this helps somebody.

Peter

-------

Code: Select all

procedure TMySqlNet.WriteOrSendBytes(const buffer: TBytes; offset, count: integer); // similar to net_write_command, my_net_write
  procedure WriteBuff(offset, count, UncomprPacketLen: integer); // net_write_buff. Copy block from buffer to Self.FBuffer. Flush if need
  var
    left_length: integer;

  qualifier, escape: boolean;
    v: string;
    i,o: integer;
  begin
    // quick and dirty workaround for dbexpress null bug by Pete //
    // if you need to insert/update a null in your project, make sure it's lowercase
    // i.e. insert into atable values ("argh", "a", null) because delphi uses an
    // uppercase NULL in it's updates and inserts

    qualifier:=false; escape:=false;
    for i := 1 to count-5 do
    begin
      if buffer[i]=92 then begin escape:=true; continue; end;
      if escape then begin escape:=false; continue; end;

      if buffer[i]=39 then qualifier:=not qualifier;
      if qualifier then continue;

      v:='';
      for o := 0 to 5 do v:=v + chr(buffer[i+o]);
      if v=', NULL' then
        for o := 2 to 5 do
          buffer[i+o] := ord(' "" '[o-1]);
    end;

    // end of workaround

    Assert(Length >= NET_HEADER_SIZE);

Post Reply