Welcome to the Tweaking4All community forums!
When participating, please keep the Forum Rules in mind!
Topics for particular software or systems: Start your topic link with the name of the application or system.
For example “MacOS X – Your question“, or “MS Word – Your Tip or Trick“.
Please note that switching to another language when reading a post will not bring you to the same post, in Dutch, as there is no translation for that post!
Sometimes, a text field of a database table or query will be interpreted as a ftMemo blob, and the grid will show "(memo)" instead of the actual text.Quite annoying at times and I'm still surprised that this happens to remain unaddressed in both Lazarus and Delphi ...
Anyhoo .. how do we fix this?
Option 1 (easiest) - Modify your SQL Query
Now this option has the intend to make the "Memo Blob" look like a regular string. We can do this with a simply type cast.
Note : this might be slightly different, depending on your SQL Database. I have used this with SQLite, and MySQL, and heard it works in PostgreSQL as well.
Say your query is something like this:
SELECT
"Filename",
"Date"
FROM
"mytable";
And let's assume "Filename" is the offending field.
Now (make sure to remove the fields from the TQuery component!) change your query by casting the TEXT as a VARCHAR like so:
SELECT
CAST( "Filename" as VARCHAR) as "Filename",
"Date"
FROM
"mytable";
You will now see that the actual text is being displayed and not the "(memo)" text.
Option 2 - OnGetText Event
First we need to add fields to our TQuery. Right click the TQuery and select "Edit Fields".
In the upcoming window click the "+" button to at least add the field that shows as "(memo)".
Now click the newly added field. You'll see that a TMemoField has been added in the object inspector.
Click the "Events" tab in the object inspector and double click the "OnGetText" event.
Now add this code to the event handler (assuming: TQuery = qrySomeQuery, fieldname = "SomeField", which creates the TMemoField called "qrySomeQuerySomeField"):
aText := Sender.AsString;
DisplayText:=true;
So we get something like this, which now makes (at runtime) show the actual text instead if (memo):
procedure TForm1.qrySomeQuerySomeFieldGetText(Sender: TField; var aText: string; DisplayText: Boolean);
begin
aText := Sender.AsString;
DisplayText:=true;
end;
This seemed to work fine with older MySQL versions, or I remembered it wrong.
Anyhoo, this works:
SELECT
CAST( "Filename" as CHAR) as "Filename",
"Date"
FROM
"mytable";
However, when using the GetText event, to convert a text to a string, then this is no longer needed in current versions of Lazarus Pascal.
p.s. if you use a DataModule, then you could consider using this.
We will automatically assign the string conversion to any TMemoField in your TDatamodule, so it will save time to assign an OnGetText event for each Field.
First we need a generic function that can be used on all TMemoFields (see explanation above)
procedure TDataModule1.SQLQueryGetMemoAsText(Sender: TField; var aText: string; DisplayText: Boolean); begin aText := Sender.AsString; DisplayText:=true; end;
Next we assign it at runtime to all TMemoField like so (call in the TDatamodule.OnCreate event, before opening the database):
procedure TDataModule1.SetAllMemoFieldToGetText;
var
Counter:integer;
begin
for Counter:=0 to self.ComponentCount-1 do
if self.Components[Counter] is TMemoField then
TMemoField(self.Components[Counter]).OnGetText:=@self.SQLQueryGetMemoAsText;
end;
You'll need to declare both in the TDatamodule; and do not forget to call SetAllMemoFieldToGetText in the TDatamodule.OnCreate event!
TDatamodule1 = class(TDataModule)
...
procedure SQLQueryGetMemoAsText(Sender: TField; var aText: string;
DisplayText: Boolean);
...
public
...
{ Set the OnGetText event to spit out plain text instead of (MEMO) }
procedure SetAllMemoFieldToGetText;
...
end;
If you're seeing (memo) in your DBGrid instead of the actual content, it's likely because the field type in your dataset is a Memo (or TEXT/CLOB depending on your DBMS), which DBGrid doesn't display fully by default due to potential performance and size issues.
Use a calculated field:
Create a calculated field in your dataset that extracts a short preview of the memo content.
Assign it to a DBGrid column:
Add MemoPreview to the grid instead of the original memo field.
Alternative – Use a DBMemo:
If you want to view/edit the full content, consider using a DBMemo component bound to the memo field, triggered by a grid row selection.
Thank you for chiming in ... 😊
In SQLite the TEXT field can be either text or even a binary blob.
I wish Lazarus Pascal would just have a property TFields that allows one to toggle it to actual text (VARCHAR).
All the work-arounds are rather cumbersome when working on projects with a lot of data.