Char field not Null (BDE vs ODAC)

[FredLemay]

With BDE, this kind of field :

COMPTE_PREFIXE CHAR(20) not null

are filled with space when initialized with empty string like that :

Code: Select all

Query1COMPTE_PREFIXE.AsString := '';

With ODAC, the field stay null, so Odac raise an exception "Field ... must have a value".

Note : I can't use the property TrimFixedChar because varchar2 fields will keep space, and I don't have time to change every places that such things are done. Don't want to scan the table to find char fields and fill these fields with spaces on DoBeforePost procedure.

Solution ?

[Plash]

Please specify your Pascal code where the error occurs. If you manually assign an empty string to the Query1COMPTE_PREFIXE field, try to assign a space instead of the empty string.

You have written that you cannot set the TrimFixedChar option to False because of VARCHAR fields. But this option does not affect such fields. It affects only CHAR fields.

[FredLemay]

The error occurs on Query1.post. The property Required was to True, I changed it to False. Now, I get this error from Oracle

ORA-01400: Cannot insert NULL into "GRMF1"."COMPTE"."COMPTE_PREFIXE")

So, the field is not filled with space when value is empty (null) like BDE do.
I must add a space like you say to have the same result as BDE.

Conclusion, I must find every char field (400 fields), and be sure to add space if value is null ?
Or, run a query to verify if table content char field and initialize with a space if null like I say before ?

Sorry for the mistake with varchar field and property TrimFixedChar, you says truth !

I can send you a demo of the problem if you want.

[Plash]

We think that ODAC behaviour is more correct than BDE, because the empty string is the same as NULL in Oracle. To solve the problem, you can change type of the field in the database so that it allows NULL values.

[FredLemay]

I know that ODAC behaviour is more correct than BDE, that's not the question. I have a problem to resolve without slowing down the application and without checking 400 fields. On ODAC web site it's written :

Existing BDE-based applications can be easily migrated to ODAC

I can't just change the field type from CHAR to VARCHAR2 without impacting the application. Sometimes spaces are required, sometimes not. Sometimes fields cannot be Null, sometimes it's not important. The best way for me for now, is to have the same result as BDE. If there's no property or easy way to make it work like BDE, can you point me the code in ODAC to make my own correction.

[Plash]

We have sent to you by e-mail the description of modification in the ODAC source code, that you can make to solve the problem.

[FredLemay]

Tanks for email. I've tried what you send me, but didn't work, still have Oracle error. I have found a property "Fixed" in FieldDesc list of Data. I have made this correction and it's work. The field content a space after post. It's not exactly like BDE because the field is really empty but not null, but I don't have problem for now with that changes.

Code: Select all

procedure TMyOraTable.InternalPost;
var
   i : integer;
begin
   for i := 0 to FieldCount - 1 do
      if Fields[i].IsNull and 
         (Fields[i].DataType = ftString) and 
         Assigned(Data.FindField(Fields[i].FieldName)) and 
         Data.FieldByName(Fields[i].FieldName).Fixed then
         Fields[i].AsString := ' ';
  inherited;
end;

With your source modification, field is empty but the insert query send null to the field in place of spaces.

By the way, I'm french canadian, my english is poor, so excuse me.

[Plash]

Your fix should work. But be careful when you download a new build of ODAC. It will not contain this fix.

[tuasal]

Is there any solution (without patching source code) to force dataset to "interpret" empty string as "NOT NULL"? Or to replace NULL with empty string. There are third-party components (in JVCL pack, for example) at which use it is sometimes impossible to consider equivalent "NULL" and "empty string".

This replacement (NULL value to "empty string" and back) should work is as much as possible transparent for all components which address to fields ODAC dataset (TOraTable, etc). In particular, if a component carries out search of record using methods Lookup & Locate, then it specifies the "empty string" (a variable of type VARIANT with string '', instead of VARIANT-value "NULL", as should be actually) and therefore the record, having value NULL in the corresponding field, will not be found.

[Plash]

ODAC has no feature to return empty strings for fields containing NULL values.

[tuasal]

hmm... Then the statement "Existing BDE-based applications can be easily migrated to ODAC" mismatches true?! And actually should sound as "ODAC behaviour is more correct than BDE"? With all the ensuing consequences...

In fact in other (popular) databases there is a distinction between "empty string" and NULL. And the logic of some Delhi functions and components is depends on it. It turns out, that it is not always easy to migrate from BDE to ODAC...

[Plash]

We have tested this situation with BDE. BDE also returns Null for CHAR and VARCHAR fields containing NULL values.