Page 1 of 1
[Oracle] Empty string and NULL Value
Posted: Thu 25 Jun 2015 13:53
by humantool
Oracle treats empty string and null value the same way => Ok
I have VARCHAR2 Field that contains a Localisation (ex.: 'SomeWhere'), and I reinit it to empty string in the applcation and Oracle update it with NULL -> OK (...kind of
)
When I want to set a new value to my localisation, TuniQuery generate a update query that contains empty String in the where clause....so the update not working (0 lines updated) :
Code: Select all
UPDATE MyTable SET Localisation = 'SomePlace' WHERE [..] AND Localisation = '';
As the TSQLConnection know the provider would it be possible to generate an update with NULL value instead ?
Code: Select all
UPDATE MyTable SET Localisation = 'SomePlace' WHERE [..] AND Localisation = NULL;
Re: [Oracle] Empty string and NULL Value
Posted: Fri 26 Jun 2015 10:40
by bork
Here is a sample updating the table by key field, that contains NULL:
Code: Select all
var
Connection: TUniConnection;
Query: TUniQuery;
begin
Connection := TUniConnection.Create(nil);
try
Connection.Server := '...';
Connection.Username := '...';
Connection.Password := '...';
Connection.ProviderName := 'Oracle';
Connection.Open;
Connection.ExecSQL('CREATE TABLE TEST_NULL (NAME VARCHAR2(50), VALUE NUMBER)');
Connection.ExecSQL('INSERT INTO TEST_NULL VALUES (NULL, 10)');
Query := TUniQuery.Create(nil);
try
Query.Connection := Connection;
Query.SQL.Text := 'select * from test_null';
Query.KeyFields := 'name';
Query.Open;
Query.Edit;
Query.FieldByName('value').AsFloat := Query.FieldByName('value').AsFloat + 1;
Query.Post;
finally
Query.Free;
end;
finally
Connection.Free;
end;
end;
In this sample, TUniQuery generates the following query:
Code: Select all
UPDATE TEST_NULL
SET
VALUE = :VALUE
WHERE
NAME IS NULL
Please modify this sample to reproduce your issue.
Re: [Oracle] Empty string and NULL Value
Posted: Mon 29 Jun 2015 08:37
by humantool
Thanks bork !
I've tried it and it worked correctly, this make think I use TDataSetProvider between my Server and my client and they prepare a query too but there maybe doing it wrong.......
I'm going to do some other tests in this way, I post the results as ASAP.
Re: [Oracle] Empty string and NULL Value
Posted: Tue 30 Jun 2015 08:32
by bork
Try to set TDataSetProvider.ResolveToDataSet = True. In this case, SQL for update will be generated by TUniQuery. If TDataSetProvider.ResolveToDataSet = False, then SQL is generated by TDataSetProvider or TClientDataSet and can be invalid.
Re: [Oracle] Empty string and NULL Value
Posted: Mon 13 Jul 2015 15:41
by humantool
I've tested with a TDataSetProvider and a TClientDataSet with AND without the TDataSetProvider.ResolveToDataSet = True, and I've got reconcile error too.
Here is my test project :
https://drive.google.com/file/d/0BwYtGW ... sp=sharing
The second edition not working : i've a reconcile error, here is the what the memo show :
Code: Select all
Connected.
DROP TABLE
CREATE TABLE
INSERT
SELECT 1
value=9
value=9
Here is what the logs from SQL monitor shows, reconcile error is on line 14 :
Code: Select all
1 17:25:51 Log start?
2 17:25:53 Disconnect: test@localhost:1521:sn=XE
3 17:25:53 Connect: test@localhost:1521:sn=XE
4 17:25:53 SQL Execute: DROP TABLE TEST_NULL
5 17:25:53 SQL Execute: CREATE TABLE TEST_NULL (NAME VARCHAR2(50), VALUE NUMBER)
6 17:25:53 SQL Execute: INSERT INTO TEST_NULL VALUES ('TOTO', 10)
7 17:25:57 SQL Execute: SELECT * FROM TEST_NULL
8 17:25:57 Start:
9 17:25:57 SQL Execute: SELECT * FROM TEST_NULL
10 17:25:57 SQL Execute [Update]: UPDATE TEST_NULL
SET
NAME = :NAME, VALUE = :VALUE
WHERE
NAME = :Old_NAME AND VALUE = :Old_VALUE
11 17:25:57 :NAME(String[0],IN)=<NULL>
:VALUE(Float,IN)=9
:Old_NAME(String[4],IN)='TOTO'
:Old_VALUE(Float,IN)=10
12 17:25:57 Commit:
13 17:25:57 Start:
14 17:25:57 SQL Execute: SELECT * FROM TEST_NULL <----------------Reconcile error
15 17:25:57 Commit:
I'm gonna create a TCustomStringField which will know it i sin Oracle mode and has to manipulate empty string as null value, except if someone have a better idea.....
Re: [Oracle] Empty string and NULL Value
Posted: Wed 15 Jul 2015 07:51
by AlexP
Hello,
To solve the problem, you should also set the aSetProvider1.UpdateMode property to upWhereKeyOnly.
Re: [Oracle] Empty string and NULL Value
Posted: Wed 15 Jul 2015 16:07
by humantool
@AlexP : It change nothing in my test project because my table have no key (it was just for the example).
And for my main project, i need to have my TDataSetProvider.UpdateMode on "upWhereChanged".
Re: [Oracle] Empty string and NULL Value
Posted: Thu 16 Jul 2015 11:45
by AlexP
Yes, this error will occur when the upWhereChanged value is set. We will consider the possibility to modify the Locate method behavior on Null values in string fields.