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

My apologies, AutoGenerateValue and DefaultExpression are properties of TStringField.

Jim

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

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

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).]

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

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

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

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

I will try to reproduce this.

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