mORMot Open Source

BenTen

Member

Registered: 2021-01-19

Posts: 11

TSynDBDataSet: Limiting the length of ftUTF8 fields in TDBEdit

Hello everyone and ask for help.

I am working in Delphi 10.4 Sydney
I am using the TSynDBDataSet to edit table values through the TDBEdit and TDBGrid components, and I found an unpleasant feature when doing this.
When editing the dataset, I cannot enter the longest string value for the ftUTF8 field, which is longer than the longest value from the table.
It turns out that the length of any ftUTF8 field is limited to the longest value in the table.

See example "17 - Using TClientDataset"
If you create a dataset, as in the example, then TSynDBDataSet.IgnoreColumnDataSize does not work.

8: // test TSynDBSQLDataSet / TSynDBDataSet
    if chkViaTClientDataSet.Checked then begin
      ds1.DataSet := TSynDBDataSet.Create(self);
      TSynDBDataSet(ds1.DataSet).Connection := fProps;
      TSynDBDataSet(ds1.DataSet).CommandText := SQL_PEOPLE;
      TSynDBDataSet(ds1.DataSet).IgnoreColumnDataSize := true;

If you create a dataset in such a way as shown below, then the limitation on the field length is removed, but the display of the dataset is distorted with words (WIDEMEMO)

  8: // test TSynDBSQLDataSet / TSynDBDataSet
    if chkViaTClientDataSet.Checked then begin
      ds1.DataSet := TSynDBDataSet.Create(self);
      TSynDBDataSet(ds1.DataSet).IgnoreColumnDataSize := true;
      var Rows := fProps.ExecuteInlined(StringToUTF8('select * from People'), True);
      TSynDBDataSet(ds1.DataSet).From(Rows.Instance);

Please help me solve this problem.
I would be very grateful.
Thanks

Last edited by BenTen (2021-03-09 17:53:24)

BenTen

Member

Registered: 2021-01-19

Posts: 11

Re: TSynDBDataSet: Limiting the length of ftUTF8 fields in TDBEdit

Hello everyone!

I decided this problem myself.
I had to edit the SynDBVCL.pas module code, as shown below.
Now it is possible to explicitly determine the length of dataset's text fields before the open dataset.
In this case, the visual components of TDBEDIT or TDBGRID will not cut more the length of the editable field along the length of the maximum value

procedure TSynBinaryDataSet.InternalInitFieldDefs;
var F: integer;
    DBType: TFieldType;
    FieldList: TStringList;
    i: Integer;
begin
  (*
     If before opening a TSynDBDataset, the field structure (Dataset.FieldDefs)
     was previously defined, we use the length of the fields from this structure
     so as not to cut the length of the text fields TDBedit.Text
  *)

  FieldList := TStringList.Create;
  try
    for i := 0 to FieldDefs.Count - 1 do
      if FieldDefs.Items[i].DataType = ftString then
        FieldList.AddPair(FieldDefs.Items[i].Name, FieldDefs.Items[i].Size.ToString);

    FieldDefs.Clear;
    if fDataAccess=nil then
      exit;
    for F := 0 to fDataAccess.ColumnCount-1 do
      with fDataAccess.Columns[F] do begin
      case ColumnType of
      SynTable.ftInt64:
        DBType := ftLargeint;
      SynTable.ftDate:
        DBType := ftDateTime;
      SynTable.ftUTF8:
        begin
          if ColumnDataSize=0 then
            DBType := ftDefaultMemo
          else
            DBType := ftWideString; // means UnicodeString for Delphi 2009+
        end;
      SynTable.ftBlob:
        DBType := ftBlob;
      SynTable.ftDouble, SynTable.ftCurrency:
        DBType := ftFloat;
      else
        raise EDatabaseError.CreateFmt(
          'GetFieldData ColumnType=%s',[TSQLDBFieldTypeToString(ColumnType)]);
      end;

      if ToInteger(FieldList.Values[ColumnName], i) then
        FieldDefs.Add(UTF8ToString(ColumnName),DBType, i)
      else
        FieldDefs.Add(UTF8ToString(ColumnName),DBType,ColumnDataSize);
    end;
  finally
    FieldList.Free;
  end;
end;

Modification code in mORMotVCLUnit.pas from  "17 - TClientDataset use"

  8: // test TSynDBSQLDataSet / TSynDBDataSet
    if chkViaTClientDataSet.Checked then begin
      ds1.DataSet := TSynDBDataSet.Create(self);

      // ++++  Pre-determine the length of text fields
      TSynDBDataSet(ds1.DataSet).DataSet.FieldDefs.Clear;
      TSynDBDataSet(ds1.DataSet).DataSet.FieldDefs.Add('FirstName', ftString, 30);
      TSynDBDataSet(ds1.DataSet).DataSet.FieldDefs.Add('LastName', ftString, 30);
      // ++++

      TSynDBDataSet(ds1.DataSet).Connection := fProps;
      TSynDBDataSet(ds1.DataSet).CommandText := SQL_PEOPLE;
      //--- Does not affect
      TSynDBDataSet(ds1.DataSet).IgnoreColumnDataSize := true;

      ds1.DataSet.Open;
    end else begin
      ds1.DataSet := TSynDBSQLDataSet.Create(self);
      TSynDBSQLDataSet(ds1.DataSet).Connection := fProps;
      TSynDBSQLDataSet(ds1.DataSet).CommandText := SQL_PEOPLE;
      ds1.DataSet.Open;
    end;
  end;

Many thanks to everyone who responded.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,244

Website

Re: TSynDBDataSet: Limiting the length of ftUTF8 fields in TDBEdit

Nice solution!

Please try https://synopse.info/fossil/info/1b83fb28f4