Lazarus: DBGrid showing "(MEMO)" as the value of string fields in SQLite 3

Question

I'm trying to write a simple SQLite 3 application using Lazarus and the SQLdb components.

I have managed to connect to the database and populate a TDBGrid. Problem is that all the columns that are text fields display the value "(MEMO)" rather then the string in the DB.

Answer 1

I forgot the source of this but this is what I am doing with memo fields in tdbgrid. bluish is right about the gettext event, this is how to implement it in the code:

Create a class called MemoDifier:

MemoDifier = class
  public
    procedure DBGridOnGetText(Sender: TField; var aText: string;
      DisplayText: boolean);
  end;                  

At the implementation section of your code, put this:

procedure MemoDifier.DBGridOnGetText(Sender: TField; var aText: string;
  DisplayText: boolean);
begin
  if (DisplayText) then
    aText := Sender.AsString;
end; 

Then click the tdbgrid control in your form and at the Object Inspector(Lazarus IDE), click the Events tab, scroll below to find the OnPrepareCanvas event. Double click it to generate the code. Then modify the code to suit to your needs such as the name of your tdbgrid control:

procedure Tmainui.TDBGrid1PrepareCanvas(sender: TObject;
  DataCol: Integer; Column: TColumn; AState: TGridDrawState);
var
  MemoFieldReveal: MemoDifier;
begin
   if (DataCol = 1) then
   begin
     try
       TDBGrid1.Columns.Items[0].Field.OnGetText := @MemoFieldReveal.DBGridOnGetText;
       TDBGrid1.Columns.Items[1].Field.OnGetText := @MemoFieldReveal.DBGridOnGetText;
       TDBGrid1.Columns.Items[2].Field.OnGetText := @MemoFieldReveal.DBGridOnGetText;
     except
       On E: Exception do
       begin
         ShowMessage('Exception caught : ' + E.Message);
       end;
     end;
   end;
end; 

The variable MemoFieldReveal points to the class MemoDifier. Don't forget to modify the index (Items[x]) to point to your index number of the tdbgrid items/fields which shows the (MEMO) text.

Answer 2

I have found a simple solution:

The property dgDisplayMemoText from the DBGrid must be enabled.

Answer 3

Another option

If you are using TZConection add this line to you code when you Connect you database

TZConnection).Properties.Add('Undefined_Varchar_AsString_Length=100'); 

Answer 4

I have the same in MySQL and Tgrid so I'm hoping the answer is the same as it is quite simple :-)

Say if s is the field causing the problem then Instead of writing

SELECT s

write

SELECT LEFT(s,200) AS s 

Answer 5

As said on IRC, you probably need to add the fields of your query to the form, (so that "field" components are generated for them) and then implement the TMemoField.GetText event.

See if entering the "fields" field in the object inspector brings up an editor to generate the components (it does so in Zeos iirc).

Answer 6

Memo fields cannot be shown in the TDBGrid. Add TDBMemo to the form and connect it to the same TDataSource. You will see the text in your memo in this case.

Answer 7

An apparently simple solution is to limit the length of the TEXT in the field using something like VARCHAR(n) in the column type where n is the maximum number of allowed characters.

Answer 8

This article gives a solution: Displaying and editing MEMO fiels in Delphi's TDBGrid.

Here I summarize what you have to do:

• in the .dfm add OnGetText = MyDataSetMyFieldGetText to the TMemoField (here named MyField) belonging to your data set (for example a TTable, here named MyDataSet)

• in the .pas > interface > type > inside your form definition, add

  procedure MyDataSetMyFieldGetText(Sender: TField; var Text: string; DisplayText: Boolean);
  

• in the .pas > implementation > add this method

  procedure TDM.WorkVisiteNoteGetText(Sender: TField; var Text: string; DisplayText: Boolean);
  begin
    Text := Copy(WorkVisiteNote.AsString, 1, 100);
  end;