Dbexpress convert empty string to null
Dbexpress convert empty string to null
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?
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?
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.
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.
Re: Dbexpress convert empty string to null
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.AngelusB wrote:When I empty a Widestring, the information send to the database (after applyupdates) is an Null value.
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.
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.
MyDAC is a library of components that provides direct access to MySQL database servers.
Re: Dbexpress convert empty string to null
I'm using the same components as you use:TClientDataset->TDataSetProvider->TSQLQuery->TSQLConnection->DevartMySQLDirectffcDev 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?
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.
Thanks Bill.ffcDev wrote:AngelusB: I've posted QC# 71984... let's see what we get...
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'
Workaround if anyone needs it
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
-------
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);