Watch, Follow, &
Connect with Us

QualityCentral

Public Report
Report From: Delphi-BCB/Midas/TDataSetProvider    [ Add a report in this area ]  
Report #:  8121   Status: Closed
Strings fields cannot be cleared the filled again using DataSnap and ORACLE
Project:  Delphi Build #:  4.453
Version:    7.0 Submitted By:   David Claux
Report Type:  Minor failure / Design problem Date Reported:  5/11/2004 7:28:38 AM
Severity:    Serious / Highly visible problem Last Updated: 5/11/2004 9:32:52 AM
Platform:    All versions Internal Tracking #:  
Resolution: Third Party (Resolution Comments) Resolved in Build: : None
Duplicate of:  None
Voting and Rating
Overall Rating: No Ratings Yet
0.00 out of 5
Total Votes: None
Description
Using ORACLE, clearing a string field and applying updates to the DataBase will work, but typing a new value for the same field and applying updates will fail due to how TDataSetProvider formats the UPDATE command.
Steps to Reproduce:
To reproduce, create the following sample project:

- Create a simple windows app (no need for a client/appserver/database architecture)
- Drop any database connection component on the form (TADOConnection for instance) and set its properties to connect to the database
- Drop a table or query component, and adjust its Connection and TableName/SQL properties. Insure there is at least one string field in the table/query.
- Drop a TDataSetProvider and set its DataSet property to the dataset described above. Insure its UpdateMode property is set to upWhereAll (the problems don't happen in upWhereKeyOnly mode).
- Drop a TClientDataSet and set its ProviderName to then name of the above TDataSetProvider
- Drop a TDataSource and set its DataSet to the ClientDataSet
- Drop a TDBGrid and set its DataSource property to the TDataSource mentioned above
- Drop a button, double-click it and type this code:

procedure TForm1.Button1Click(Sender: TObject);
begin
  if ClientDataSet1.ApplyUpdates(0) = 0 then
    ShowMessage('Database was successfuly updated.')
  else
    ShowMessage('Error updating database!');
end;

- Set the ClientDataSet's Active property to True

Run the application, and do the following:
- Choose a string field that contains some text in the grid, and clear it. Click the button to update the database. You should get a "Database was successfuly updated" message.
- Now type some text in the field you just cleared, and click the button. This time, you should get a "Error updating database!" message.

The error occurs because the provider sends an UPDATE command to the ORACLE server with a where clause looking like:
WHERE ... AND <YOUR_STRING_FIELD> = '' AND ...

When the field was cleared, DataSnap set the field value to '' in the database, but ORACLE treated it as NULL. Now, when trying to update the field again, the above syntax is  used when it should read:
WHERE ... AND <YOUYR_STRING_FIELD> IS NULL AND ...
Workarounds
[Dave Rowntree: one solution could be to clear (TField.Clear) any string fields that trying to be set to '']
Attachment
None
Comments

None

Server Response from: ETNACODE01

Copyright© 1994 - 2013 Embarcadero Technologies, Inc. All rights reserved.