'Cannot update to null' errors - updateable TOracleDataset

[Jim Ashton]

I am in the process of converting a fairly complex BDE-based application to DOA, running against an Oracle 8i database.

Almost all of the original updateable TQuery objects have become TOracleDatasets, around 14 of which are opened concurrently using CachedUpdates. When the user hits Save the session's ApplyUpdates method is called in turn for each of these datasets.

The problem arises where the underlying table has one or more varchar columns defined as not null, default = 'empty string'. I am seeing the 'Cannot update [Columnname] to NULL' or 'Field [Columnname] must have a value' error message. Using OracleMonitor I can see that an attempt is being made to update the column explicitly to NULL, whereas the value actually required is 'empty string'.

I have tried setting the following properties of the TDataSet:
- AutoGenerateValue to arDefault
- DefaultExpression to '' or ' '
- OracleDictionary.DefaultValues to true
- OracleDictionary.DynamicDefaults to true

None of these has solved the problem.

Thanks
Jim

 

[Jim Ashton]

My apologies, AutoGenerateValue and DefaultExpression are properties of TStringField.

Jim

 

[Marco Kalter]

Note that NULL and an empty string ('') are exactly the same in Oracle. However, the default behavior of a TOracleDataSet is to remove trailing spaces from string fields. Therefore posting a space would result in posting an empty string, which is NULL. To prevent this, you can set TOracleSession.Preferences.TrimStringFields to False.

I hope this helps.

------------------
Marco Kalter
Allround Automations

 

[Jim Ashton]

Well it's a little early to be certain (loads more testing to be done!) but at this stage it looks like your suggestion has cured most (& possibly all) of the problems.

Many thanks
Jim

 

[Jim Ashton]

Somehow I thought it was too good to be true...

OK, it appears that setting TOracleSession.Preferences.TrimStringFields to False
has dealt with most of the cases where one or more existing database rows are
being edited and then re-saved.

In the case where a brand new set of rows is being inserted I was still getting
alot of errors until I set OracleDictionary.DefaultValues to true on each of the
TOracleDatasets in question.

However there are still some cases where I am seeing this error message (one of
the two I originally reported above). BTW the relevant database columns actually
have a specified default of ' ' (single space) - not empty string as I said at
first.

Once again, setting TStringfield.AutoGenerateValue to arDefault and/or
TStringfield.DefaultExpression to ' ' (single space) has no effect on these
errors at all.

Is there anything else I could try in this situation?

Also, a couple of other questions please:

1. Would you expect that invoking a TOracleDataset.ApplyUpdates method would
cause the corresponding BeforePost event handler to be called first? There is at
least one situation where in the original BDE-based application a
TQuery.ApplyUpdates triggers TQuery.BeforePost yet in the DOA version this does
not happen (and I can assure you that the handler IS linked to the event!).

What could be causing this?

2. One of the original TQuerys has a TDataSource object linked to another TQuery
specified as its DataSource property ie there is a Master-Detail relationship.

In the DOA situation I have set the Master property of the first (detail)
TOracleDataset to the second (master). All parameters have the same name as the
columns to which they relate.

Is it possible to have this sort of relationship between 2 TOracleDatasets where
the SQL in both cases is created dynamically at runtime? (Both queries are
selects from [different] single tables, but depending on the privileges of the
user they may/may not be updateable and hence may/may not need to retrieve the
rowid - hence the SQL has to be dynamic).

When the pointer is moved to a new master row (eg by invoking the First method
on the Master dataset), I assume that the detail query is automatically run to
retrieve the corresponding set of detail rows, and that this would trigger the
Detail's AfterQuery event. Would it always also trigger AfterOpen? (I have a
case where the latter doesn't seem to be happening).

All I can think of for now...(!)

Many thanks in anticipation of your best attention to these issues.
Jim

[This message has been edited by Jim Ashton (edited 03 April 2003).]

 

[Marco Kalter]

Default values will not help for updates, this only applies to inserts. If during an update a value is set to NULL, then I can only assume that is really changed to NULL. Only modified column values will be updated.

BeforePost is only called before a record is posted. It is not called during ApplyUpdates. Note however that you should never call TOracleDataSet.ApplyUpdates, you should always use TOracleSession.ApplyUpdates.

Master/Detail setup can be accomplished at runtime. All you need to do is setup the SQL of the detail dataset, and its Master, MasterFields, and DetailFields properties.

Detail synchronization does not cause an AfterOpen event. Synchronization causes a refresh, and does not close/open the detail dataset.

------------------
Marco Kalter
Allround Automations

 

[Jim Ashton]

Thanks for that Marco.

I just need to clarify the options here - could you please list the TOracleDataset events which are triggered by:

1. TOracleSession.ApplyUpdates([TOracleDataset], false) ie no commit

2. Refresh (eg following detail synchronization)

Thanks again
Jim

 

[Marco Kalter]

1. TOracleSession.ApplyUpdates([TOracleDataset], false) ie no commit

No events will be triggered.

2. Refresh (eg following detail synchronization)

BeforeRefresh and AfterRefresh will be triggered.

------------------
Marco Kalter
Allround Automations

 

[Jim Ashton]

Marco: In regard to your last reply, my TOracleDatasets don't seem to have BeforeRefresh or AfterRefresh events.

The only *Refresh* event is AfterRefreshRecord, which does not appear to be triggered by detail synchronisation - at least according to my testing. Is there any other event I could use?

Going back to the previous answer, I definitely have at least one situation in which a row is being inserted (dataset in dsInsert state), DefaultValues is True, TrimStringFields is False and when ApplyUpdates is called I get the 'Field.... must have a value' error. Even more bizarrely, checking with the Delphi Evaluate/Modify function just before the call shows the field value to be ' ' (single space).

As hitherto, I'd be grateful for any suggestions you might have.

Thanks
Jim

 

[Marco Kalter]

I will try to reproduce this.

------------------
Marco Kalter
Allround Automations

 

[Jim Ashton]

Hi Marco - just wondering if there's been any progress on this - my customer is getting a little edgy!

Thanks
Jim

 

[Marco Kalter]

I have not yet been able to reproduce this.

------------------
Marco Kalter
Allround Automations

 

[Jim Ashton]

Marco: we have now managed to make the 'Field must have a value' error go away by setting the Required property of all relevant TStringfield objects to false.

Could I now refer you back to my earlier question re. detail synchronisation please.

To repeat, my TOracleDatasets don't seem to have BeforeRefresh or AfterRefresh events.

The only *Refresh* event is AfterRefreshRecord, which does not appear to be triggered by detail synchronisation - at least according to my testing. Is there any other event I could use?

 

[Marco Kalter]

Sorry, I noticed that the AfterRefresh and BeforeRefresh events are not published in 3.4, so you can only set them at run-time. For example:

procedure TMainForm.EmpDataSetAfterRefresh(DataSet: TDataSet);
begin
  ShowMessage('AfterRefresh');
end;

procedure TMainForm.FormCreate(Sender: TObject);
begin
  EmpDataSet.AfterRefresh := EmpDataSetAfterRefresh;
  ...
end;

------------------
Marco Kalter
Allround Automations

[This message has been edited by mkalter (edited 17 April 2003).]

 

[Jim Ashton]

Marco - thank you for that last reply, which was most helpful.

I have now almost finished the conversion!

However I am still having problems with Master/Detail relationships.

My question now is this: for master/detail relationships to work between two TOracleDatasets, must a Foreign Key (referential integrity) constraint exist IN THE DATABASE between the relevant tables?

Thanks again
Jim

 

[Marco Kalter]

No, these foreign keys do not need to exist. A foreign key merely allows the detail dataset to automatically create the appropriate MasterFields, DetailFields, Variables, and where clause at design time.

If this foreign key does not exist, you have to set the MasterFields, DetailFields, Variables, and where clause yourself.

------------------
Marco Kalter
Allround Automations

 

[Jim Ashton]

OK Marco - thanks for that. Unfortunately it doesn't help solve my problem, which I will now briefly describe.

In the Application there is a form featuring two TDBGrid components plus some TDBEdit and TEdit boxes.

Grid 1 is read-only and displays master table data - its DataSource property is set to a TDataSource component which links to a TOracleDataset containing SQL which queries the master table.

Grid 2 is updateable and relates to the Detail table (similar connection via a DataSource and a TOracleDataset). One of its columns is populated from a TStringfield component defined as a look-up field in a third (look-up) TOracleDataset.

The SQL for each TOracleDataset is set up at runtime, as is the master-detail relationship. Both master & detail datasets have CachedUpdates = true.

At runtime the edit boxes are used to input data to the master table, which is then displayed in Grid 1. When the user clicks on a row in Grid 1, Grid 2 should display the corrsponding detail row(s) and/or allow the input of detail data relating to that master row.

In the BDE-based version all of this was working correctly. In the DOA version at present, the master-detail relationship breaks down. If the user inputs detail data for a master row, then moves to another master row, then returns to the first, those detail rows appear sometimes to have associated themselves with the wrong master row, and other times to have disappeared altogether. The look-up function however works properly.

Do you have any suggestions as to what might be happening here?

Thanks again
Jim

 

[Marco Kalter]

My guess is that the MasterFields or DetailFields property is not defined correctly.

Let's assume that you have a table "master" and a table "detail". A record in the master table is identified by an "id" column. The detail table has a "master_id" column that identifies the master.

In this case, the following properties need to be set for the detail dataset:

Master =
MasterFields = id
DetailFields = master_id
SQL = select ... where master_id = :master_id

This should do the trick.

------------------
Marco Kalter
Allround Automations

 

[Jim Ashton]

YES - that's fixed it! I hadn't quite read the documentation closely enough and had included an extra column in the DetailFields property.

So a testing version of the module has just gone to the customer for appraisal - doubtless there will be a few issues still to be sorted, but I'm confident now that the bulk of the work has been done and that it's basically working.

Many thanks for all your help Marco.
Cheers
Jim