5

For Delphi ClientDataSets where fields have been define at design time, is there a way at runtime to change a specific field's datatype ( change the cds.Fields[n].DataType) ?

I have a legacy Delphi 7 program with both SQLDataSet and ClientDataSet fields set at design time (in order to override various properties).

These are connected to a 3rd-party Sybase SQL Anywhere 11 database.

Recently the vendor changed all 'Description' fields from VarChar(128) to long varchar, but only for certain of his customers. So, my code has to support both types of fields when I query on these 'Description' fields.

I was hoping to set conditional compilation on the class field types (then add the fields before opening the SQL/CLient Dataset), but the compiler ignores {$IFDEF } conditionals in the component definition section of the class (which, the more I think about it, makes good sense)!

There are dozens of modules, with hundreds of fields affected, so any insight is appreciated.

Kromster
  • 7,181
  • 7
  • 63
  • 111
edbored
  • 274
  • 4
  • 11
  • 3
    Either don't add the field at all until runtime, and then add the proper type at that point, or delete it at runtime if needed and add back a new one of the type you need. – Ken White Oct 30 '14 at 21:47
  • What @KenWhite said. It should work fine but if the field(s) in question has a counterpart FieldDef, you'll need to delete and recreate that otherwise you'll likely get an error complaining about the mismatch between the datatype defined in the FieldDef and the replacement TField when you open the CDS. Personally, I'd be inclined to recreate all the fields/fielddefs rather than just the ones involved. – MartynA Oct 30 '14 at 22:03
  • Unfortunately, there are more DB changes than I originally thought - this amounts to maybe a thousand fields (across multiple projects). It also has side-effects - I use DBgrids extensively - I'm getting (MEMO) instead of data. I can set the field's OnGetText - but again, a heck of a lot of fields... The CDS are defined at design time, but I dynamically create the SQL - I'm thinking of just doing a CAST/Convert to VARCHAR in the SQL. Hmm, might have problems with fields[n].DataSize e.g. - casting a long varchar into a field that was originally defined in the CDS as VarChar[128]... – edbored Oct 30 '14 at 22:56
  • @MartynA - I deleted the field from the SDS and CDS. Then used conditional compilation to 1) add the appropriate fields/types to the Public declaration; and 2) add the SDS fielddef; and, 3) add the CDS fielddef and field. Worked fine, but of course, now a "memo" in grids. I have a feeling I'll be 'forking' code... – edbored Oct 30 '14 at 23:04
  • "Forking hell", I suppose you might say (sorry, couldn't resist). Seriously, the more I think about this, the more I'd be inclined to avoid having any persistent fields/fielddefs at all: either create them in code or leave the CDSs to create them from what they get from the server via the SDSs. – MartynA Oct 30 '14 at 23:11
  • If this was new code - sure. This is 10 year old code (written before I knew much about CDS internals), with (in one project) 40 CDS that all contain extra InternalCalc fields, and most of the fields as pulled from the database have provider flag overrides in the SDS (set in SDS field editor at design time). You're probably right. Not gonna get done for tomorrow morning though. "Forking hell" indeed. – edbored Oct 30 '14 at 23:46

1 Answers1

2

I have also faced this problem before, not with CDS, but with TADODataSet using persistent fields at design time. I think that the code below will help you get the idea of how to fix/patch your CDS datasets.

The idea is to query the relevant table schema; get the actual fileds data type; and "change" the persistent field type by un-attaching it from the DataSet and adding a new matching persistent filed instead:

// TData class    
procedure TData.DataModuleCreate(Sender: TObject);
var
  I: Integer;
begin
  for I := 0 to ComponentCount - 1 do
    if (Components[I] is TCustomADODataSet) then
      DataSetPrepareMemoFields(TDataSet(Components[I]));
end;

procedure TData.DataSetPrepareMemoFields(DataSet: TDataSet);
var
  Fld: TField;
  I: Integer;
  FldName, CompName: string;
  AOwner: TComponent;
begin
  // Here you need to query the actual table schema from the database 
  // e.g. ADOConnection.GetFieldNames and act accordingly

  // check which DataSet you need to change
  // if (DataSet = dsOrders) or ... then... 

  if DataSet.FieldList.Count > 0 then
    for I := DataSet.FieldList.Count - 1 downto 0 do 
    begin
      if DataSet.FieldList.Fields[I].ClassNameIs('TMemoField') and (DataSet.FieldList.Fields[I].FieldKind = fkData) then 
      begin
        // save TMemoField properties
        AOwner := DataSet.FieldList[I].Owner;
        CompName := DataSet.FieldList[I].Name;
        FldName := DataSet.FieldList.Fields[I].FieldName;
        // dispose of TMemoField
        DataSet.FieldList[I].DataSet := nil; // Un-Attach it from the DataSet
        // create TWideADOMemoField instead
        Fld := TWideADOMemoField.Create(AOwner); // Create new persistent Filed instead 
        Fld.Name := CompName + '_W';
        Fld.FieldName := FldName;
        Fld.DataSet := DataSet;
      end;
    end;
end;

That said, After I have fixed that issue, I have never ever used persistent fields again. All my fields are generated in run time. including calculated/lookup/internal fields.

kobik
  • 21,001
  • 4
  • 61
  • 121
  • I completed the conversion already, but this looks interesting. I can see problems based on the fact that I reference fields by the automatic declrations - eg in datamodule class declaration I have myStringField : TStringField; and if I replaced the datatype as above I think I might still see incompatible type errors. It will take me a week or so before I can get back to this. For now - thanks! It will help me going forward. – edbored Nov 07 '14 at 08:56
  • I'm trying this technique but it is not allowing me to add a field while my dataset is open neither can I access the fields when it is closed – Frank Pedro Feb 02 '21 at 06:43