Page 1 of 1
Dbexpress convert empty string to null
Posted: Wed 04 Mar 2009 15:44
by AngelusB
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?
Posted: Wed 04 Mar 2009 19:40
by ffcDev
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.
Posted: Thu 05 Mar 2009 09:22
by Dimon
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.
Re: Dbexpress convert empty string to null
Posted: Thu 05 Mar 2009 19:29
by ffcDev
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.
Posted: Fri 06 Mar 2009 10:24
by Dimon
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.
Re: Dbexpress convert empty string to null
Posted: Fri 06 Mar 2009 12:16
by AngelusB
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.
Posted: Fri 06 Mar 2009 17:24
by ffcDev
AngelusB: I've posted
QC# 71984... let's see what we get...
Dimon: Your MyDAC Components look interesting - thanks!
Bill.
Posted: Mon 09 Mar 2009 19:22
by AngelusB
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'
Posted: Tue 10 Mar 2009 15:33
by ffcDev
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...
Posted: Mon 16 Mar 2009 19:53
by AngelusB
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.
Workaround if anyone needs it
Posted: Fri 10 Sep 2010 13:42
by peterback
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);