Using Firebird embedded with Text Blobs, Aurelius and FNC Data Grid

BIZ TMS Aurelius
1

I had an issue in with FNC Data Grid hooked to an Aurelius Dataset which contains Blob fields.

The database was imported in to TMS Data Modeler which was used to create Aurelius classes.

The Blob field led to this code in the Aurelius class:

[Column('DESCRIPTION', [TColumnProp.Lazy], 4096)]
[DBTypeMemo]
FDescription: TBlob;

[If I would change the DBTypeMemo attribute to DBTypeWideMemo I get a malformed string message in the application.]

The blob type in the Aurelius Dataset is detected as ftMemo.

If I hook up the field to a FNC Data Grid column I cannot edit the value in the application.

I need to change the BlobType to ftWideMemo, then I can change it, but national character (e.g. German Umlaute äöü) are not persisted.

In order to make it working I must implement the following two handlers for the Aurelius Dataset memo field:

procedure TDM.ADSExamDescriptionGetText(Sender: TField; var Text: string;
DisplayText: Boolean);
var
Bytes: TBytes;
begin
Bytes := Sender.AsBytes;
if Length(Bytes) > 0 then
Text := TEncoding.UTF8.GetString(Bytes)
else
Text := '';
end;

procedure TDM.ADSExamDescriptionSetText(Sender: TField; const Text: string);
var
Bytes: TBytes;
begin
Bytes := TEncoding.UTF8.GetBytes(Text);
Sender.AsBytes := Bytes;
end;

Then, everything seems to be working as it should out of the box.

Can anyone tell me what I do wrong, or is it supposed to be that "complicated" to work with firebird blob text fields and Aurelius Datasets in combination witht the FNC Data Grid?

2

I just figured that this two event handlers are also not the solution to my problem, as on restart of the application I receive an encoding error:

Anwendungsfehler

Exception EEncodingError in Modul App.exe bei 0000000000058792.

Keine Zuordnung für Unicode-Zeichen in der Multibyte-Zielcodeseite vorhanden.

OK

3

I have now a working situation - but it "feels" too complicated:

  1. in the firebird database still Blob Texttype
  2. In the Aurelius model the following attribution:
    [Column('DESCRIPTION', [TColumnProp.Lazy], 4096)]
    [DBTypeMemo]
    FDescription: TBlob;
  3. In the Aurelius Dataset field attributes I have now "ftMemo" - !NOT "ftWideMemo"!
  4. I have now set the OnGettext and OnSetText of the Blob field as follows:
procedure TDM.ADSExamDescriptionGetText(Sender: TField; var Text: string;
  DisplayText: Boolean);
var
  Bytes: TBytes;
begin
  Text := '';
  
  // Prüfe ob Dataset aktiv und Feld nicht NULL ist
  if not Assigned(Sender) or not Assigned(Sender.DataSet) or 
     not Sender.DataSet.Active or Sender.IsNull then
    Exit;
    
  try
    // Lese Blob als Bytes (Firebird speichert UTF-8)
    Bytes := Sender.AsBytes;
    if Length(Bytes) > 0 then
    begin
      // Konvertiere UTF-8 Bytes zu Unicode String
      Text := TEncoding.UTF8.GetString(Bytes);
    end;
  except
    // Bei Fehler leerer String
    Text := '';
  end;
end;

procedure TDM.ADSExamDescriptionSetText(Sender: TField; const Text: string);
var
  Bytes: TBytes;
begin
  // Prüfe ob Dataset aktiv ist
  if not Assigned(Sender) or not Assigned(Sender.DataSet) or 
     not Sender.DataSet.Active then
    Exit;
    
  try
    if Text = '' then
    begin
      Sender.Clear;
    end
    else
    begin
      // Konvertiere Unicode String zu UTF-8 Bytes
      Bytes := TEncoding.UTF8.GetBytes(Text);
      Sender.AsBytes := Bytes;
    end;
  except
    // Bei Fehler nichts tun
  end;
end;
  1. That way it works with Editor := getMemo or Editor:=getHTMLEditor

I think it should work out of the box without the events - or am I too spoiled ? ;-)

4

Another hint for other people who might be struggling with firebird, Aurelius, FNC Data Grid Memo issues:
I also had to set the UseColumnEditor attribute of the DB adapter columns who refer to Memos to true. Otherwise the memo contents would be shown, but not editable if the Grid had the edit option set to true.

5

I think it boils down to how FireDAC (I assume you are using it) translates the blob content.

Looks like it's bring the value as raw data in UTF8, which is strange. Can you maybe try TFDQuery without Aurelius with different settings and see what content FireDAC brings as binary?

Another option to try is to map the field as string, not as TBlob:

[Column('DESCRIPTION', [], 65536)]
FDescription: string;
6

Hi Wagner, thanks for your answer.
Yes I use Firedac.
I will try if it works by mapping as string.
And I also can try to look into TFDQuery - but did not 100% understand what I should look for there.

7

Setting to string leads to encoding issues and malformed string exceptions.

8

I must correct myself - I figured that the UTF8 connection parameter in the FDConeection got lost while trial and error ... and if I add that parameter it works by mapping the table field as string as above. The Blobtype must be set also to ftWideString then and then it seems to be working without the GetText and SetText events.

I will fully test and then mark as solution.

9
  • can confirm that changing the mapping as above and the blobfieldtype of the field in the Aurelius Dataset to ftWideString solves the issue and no events GetText and SetText are needed when the FD Connection is correctly setup as UTF8 character connection*
10

Maybe it would be a good idea that datamodeler creates the mapping as such right away?

11

If you have "solved" the FD Connection configuration, it will likely also work with TBlob and either one of [DBTypeMemo] or [DBTypeWideMemo].

12

I'll try again and will post the result here, thanks a lot Wagner.

13

Hi Wagner, it is not working with [DBTypeMemo] or [DBTypeWideMemo]

If I use DBTypeMemo in the mapping and ftMemo as BlobType the FNCDataGrid column referring to the field is not editable. (if I click twice in the cell it is not entering edit mode)

If I use DBTypeMemo in the mapping and ftWideString as BlobType the FNCDataGrid column is editable, everything looks like it would be working, but the data shown in the grid does not get saved in the database.

If I use DBTypeMemo in the mapping and ftWideMemo as BlobType the FNCDataGrid column is editable, but if I enter "äöü" in the FNC Data Grid I get only "äö" shown after pressing enter and the data is also not saved in the database.

If I use DBTypeWideMemo in the mapping and ftMemo as BlobType the FNCDataGrid column referring to the field is not editable. (if I click twice in the cell it is not entering edit mode)

If I use DBTypeWideMemo in the mapping and ftWideMemo as BlobType the FNCDataGrid column is editable, but if I enter "äöü" in the FNC Data Grid I get only "äö" shown after pressing enter and the data is also not saved in the database.

If I use DBTypeWideMemo in the mapping and ftWideString as BlobType the FNCDataGrid column is editable, and if I enter "äöü" in the FNC Data Grid it shows "äöü" after pressing enter (OK) but once I use linebreaks bogus data starts to appear in the FNC Grid.

If I use [Column('DESCRIPTION', , 65536)] in the mapping and ftWideString as BlobType the FNCDataGrid column is editable, and if I enter "äöü" in the FNC Data Grid it shows "äöü" after pressing enter (OK) but once I use linebreaks bogus data starts to appear in the FNC Grid. (so the solution previously marked as solution above is not the solution - I just did not figure out previously that I still have problems, as soon as I enter a longer text with linebreaks without the GetText and SetText events.

If I use [Column('DESCRIPTION', , 65536)] in the mapping and ftMemo as BlobType the FNCDataGrid column referring to the field is not editable. (if I click twice in the cell it is not entering edit mode)

If I use [Column('DESCRIPTION', , 65536)] in the mapping and ftWideMemo as BlobType t
he FNCDataGrid column is editable, but if I enter "äöü" in the FNC Data Grid I get only "äö" shown after pressing enter and the data is also not saved in the database.

As there is not any combination, in which it works, I suppose my database is the problem, probably.

14

As a workaround I currently use this functions to GetText and SetText for all Memofields in the Aurelius dataset. With these it is working, but it did not work like a charm for me and I was not able to identify the root cause:

class function TDM.BlobToUTF8String(const Field: TField): string;
var
  Bytes: TBytes;
begin
  Result := '';

  // Prüfe ob Dataset aktiv und Feld nicht NULL ist
  if not Assigned(Field) or not Assigned(Field.DataSet) or
    not Field.DataSet.Active or Field.IsNull then
    Exit;

  try
    // Lese Blob als Bytes (Firebird speichert UTF-8)
    Bytes := Field.AsBytes;
    if Length(Bytes) > 0 then
    begin
      // Konvertiere UTF-8 Bytes zu Unicode String
      Result := TEncoding.UTF8.GetString(Bytes);
    end;
  except
    // Bei Fehler leerer String
    Result := '';
  end;
end;

class procedure TDM.UTF8StringToBlob(const Field: TField; const Text: string);
var
  Bytes: TBytes;
begin
  // Prüfe ob Dataset aktiv ist
  if not Assigned(Field) or not Assigned(Field.DataSet) or not Field.DataSet.Active
  then
    Exit;

  try
    if Text = '' then
    begin
      Field.Clear;
    end
    else
    begin
      // Konvertiere Unicode String zu UTF-8 Bytes
      Bytes := TEncoding.UTF8.GetBytes(Text);
      Field.AsBytes := Bytes;
    end;
  except
    // Bei Fehler nichts tun
  end;
end;

procedure TDM.MemoFieldGetText(Sender: TField; var Text: string;
  DisplayText: Boolean);
begin
  Text := BlobToUTF8String(Sender);
end;

procedure TDM.MemoFieldSetText(Sender: TField; const Text: string);
begin
  UTF8StringToBlob(Sender, Text);
end;
16

There is too much mixing of several different components and layers. I'd kindly ask you that you do tests and provide information in order to isolate things.

First thing first, do tests only with FireDAC. Do a norma SQL SELECT and UPDATE statement, make it work for both reading and writing, and confirm exactly what field type it returns for the blob field in the database, and also which parameter type it's being used that works for blob update.

Once that is done, use Aurelius without FNC Grid and see if everything is working. Is it working with mapping the field as string, and TBlob, and if works with TBlob, does it work with DBTypeMemo or DBTypeWideMemo, with AsString and/or AsUnicodeString? Can you read and write blob values property with Aurelius classes?

Then, only then, we can see the FNC Grid issues where the field is editable or not - that is a completely different thing, I guess.

1 Like
17

Hi Wagner,
I think I found the root cause and will after testing - if it really works now, write what was the issue.
Thanks a lot for your support. I will write the followup today and wish you a great weekend.

18

As simple FireDAC also did not work, I rechecked the database and found the issue that lead to the WideMemos not working.
To write it clearly: This would have all not been any problem, if the database would not have had this "error". So, for others who might have similar issues - here is how you solve the issue on the databse side:

  1. Check if your text blob fields are really UTF-8 ... in my case I had a domain set for the fields that implied it was OK by the name, but it was not. A domain that would be OK should look like this ...

CREATE DOMAIN D_TEXT_UTF8
AS BLOB SUB_TYPE TEXT SEGMENT SIZE 80 CHARACTER SET UTF8;

Not OK would be this:

CREATE DOMAIN D_TEXT
AS blob sub_type 1
COLLATE UTF8;

This is what I fell for, because I just glimpsed UTF8 and thought it was allright.

In my case I had to change all domain assignments from the wrong D_TEXT to ta corrected D_TEXT_UTF8 .

Then, if you do not already have it, you must add the UTF8 parameter to the FireDac CharacterSet connection parameter - I had that already.

I further had to set the UseColumnEditor property to false again (I set it to true while trying around to solve the issue within my project).

And then everything is working like a charm with an Aurelius mapping like this for the text blobs:
[Column('ANSWER_TEXT', [TColumnProp.Lazy], 4096)]
[DBTypeMemo]

And the according Aurelius fields blobtypes are set to ftWideMemo .

Everything is working, thanks once again for your awesome support Wagner.

1 Like
19

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.