[Oracle] Empty string and NULL Value

[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;

[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.

[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.

[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.

[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.....

[AlexP]

Hello,

To solve the problem, you should also set the aSetProvider1.UpdateMode property to upWhereKeyOnly.

[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".

[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.