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.

Here's how you can fix it:

1. Use a calculated field:
   Create a calculated field in your dataset that extracts a short preview of the memo content.

    

    

   procedure TForm1.ClientDataSet1CalcFields(DataSet: TDataSet);
begin
ClientDataSet1.FieldByName('MemoPreview').AsString :=
Copy(ClientDataSet1.FieldByName('YourMemoField').AsString, 1, 100);
end;


2. Assign it to a DBGrid column:
   Add MemoPreview to the grid instead of the original memo field.

3. 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.

Hello!

I´m new to this forum...:)

Can you help me in the first example in SQL. The size of the field is to long. And I want that the field be the size of 10 Characters long. How can I do this in SQL?

Thank you very much.

@pcavenida Welcome!

You can cut down the field length in SQL by using SUBSTR, example:

SELECT SUBSTR ('Some long text that we want to cut down to 10 characters', 1,10);

which results in (without the double quotes of course):

"Some long "

So in the example above, you'd use something like this:

SELECT
  SUBSTR( "Filename",1,10) as "ShortFilename", 
  "Date"
FROM
  "mytable";

Hope this helps 😊 

Hi!

Thank you very much for your help.

Can you say where and how I can put the 2 SQL instrutions?

I put in Lazarus like these. But not working:

SELECT
CAST( "ClienteNome" as CHAR ) as "ClienteNome";
SUBSTR( "ClienteNome",1,10) as "ClienteNome";
FROM
"Clientes";

Can you help? Again?

You added ";" at the end of the 2nd and 3rd line of the SQL statements. That won't work.
The proper SQL statement would be (replace the first ";" with a "," and remove the second ";") something like this.
It is recommended to use two different field names (you used "ClienteNome" for both fields, which leads to confusion).

SELECT
CAST( "ClienteNome" as CHAR ) as "ClienteNome",
SUBSTR( "ClienteNome",1,10) as "ClienteNomeShort"
FROM
"Clientes";

Not sure what you meant with "in Lazarus", so I assume you either pasted it in the SQL property, or you did something like this in code:

...
  mySLQQuery : TSQLQuery;
...
  mSQLQuery.SQL.Text := 
    'SELECT '+
    'CAST( "ClienteNome" as CHAR ) as "ClienteNome", '+
    'SUBSTR( "ClienteNome",1,10) as "ClienteNomeShort" '+
    'FROM "Clientes"';
...

Hope this help 😉 

Hello!! Again.

I put your code in Lazarus like you can see in the atached files. It gives an syntax error. Can you help? Thank you.

@pcavenida I'm afraid you have to learn a few things about how this works - you're probably very new to Lazarus Pascal, so let me see if I can help.

1) The screenshot you're showing is where only SQL code is placed.

So no Pascal here, just SQL. This can only be done in the IDE, do during development (at design time) when you gave access to the Object Inspector (the window on the left where you can change all kinds of properties like the SQL property which brings up the window in our screenshot).

In my previous example that would be (so paste this in the editor in your screenshot):

SELECT
CAST( "ClienteNome" as CHAR ) as "ClienteNome",
SUBSTR( "ClienteNome",1,10) as "ClienteNomeShort"
FROM
"Clientes";

2) The code you pasted is actual Pascal code, which should not be pasted there.

Instead you use that in the IDE code editor, where you'll find all your Pascal code, for example on the OnCreate event of the form, or the OnClick event of a button. This code is executed only at run time, so after you compiled your program.
This would allow you to change the query at run time if needed, but you will not see any result until you run the program.

This would be the second code example, which is where in Pascal you pass SQL code to the TSQLQuery.

...
  mySLQQuery : TSQLQuery;
...
  mSQLQuery.SQL.Text := 
    'SELECT '+
    'CAST( "ClienteNome" as CHAR ) as "ClienteNome", '+
    'SUBSTR( "ClienteNome",1,10) as "ClienteNomeShort" '+
    'FROM "Clientes"';
...

Hope this helps 😊 

p.s. if you want to know more on topics like this, consider starting a new topic for each specific problem. This way topics stay on specific questions which makes it easier for other to find answer when they run into similar problems 😉  

Hello!

Thank you very much for your time and help. I must learn more SQL. Thanks.