Trouble saving a space in a Char(1)

[ctiedeman]

I am trying to save a ' ' (space character) in a Char(1) field, but it always ends up in the database as a null. (needed for legacy system compatibility) I have tried setting FixedChar=True on the TField and TrimStringFields=False on the TOracleSession, but they don't seem to have any effect. I am using D5, Oracle8.1.6, and DOA 3.4.5.

TIA

 

[Marco Kalter]

If TOracleSession.TrimStringFields is False, then this should work just fine. Can you use the Oracle Monitor to find out what update/insert is fired?

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

 

[ctiedeman]

Ok, the problem occurs on the TField assignment. When I do TField.Value:=' ', it trims it to ''. (which Oracle then interprets as null during the update) Is there a way to get the TField not to trim the value? (I had assumed that FixedChar would have stopped it)

 

[Marco Kalter]

If I try something similar with our DeptEmp demo (e.g. set the DNAME field to 1 space), it works just fine...

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

 

[ctiedeman]

It works if you make your change directly to a TQuery or TOracleDataSet. (like in the DeptEmp example - fat client style) However, if you are using a TClientDataSet, it will trim the value down to ''. (I should have mentioned this fact in the beginning, sorry)

The only way that I have found to get a space in the database through a TClientDataSet is by adding an AfterUpdateRecord event to the DataSetProvider. In there I can check for '' and execute a TOracleQuery update to explicitly set the field to ' ' on that record. (not very efficient since there are two database hits)

 

[F.Hinlopen]

Maybe your DBA will allow a pre-insert/update trigger?

Greetings,
Frans

 

[ctiedeman]

Unfortunately, Oracle translates the '' to null before any trigger gets fired.

 

[F.Hinlopen]

I mean turn the null back into a space serverside like this:
create or replace trigger mytable_biu
before insert or update on mytable
for each row
when (new.mycolumn is null)
begin
:new.mycolumn := ' ';
end mytable_biu;

Greetings,
Frans

 

[ctiedeman]

Unfortunately, null is also a valid value, so that is not an option.

 

[F.Hinlopen]

I give up

Greetings.

------------------
Frans