[Solved]Retrieving wrong fields value by SqlDB components
lazarus / 16-Databases

----------------------------------------------------------------------
PeterHu · 2025-07-11 07:41

Basic information:
windows 10,64 bit,lazarus/fpc:the most recent release(4.0,May 5,2025)

sqlite3 database file name:world.db3
table name:city

A short story:several years ago I connected and retreved the table
field values by design time visual tools/component and everything is
great,that ,is ,an older Lazarus.Now I use the most recent one,but
there is an issue.

Following this tutorial to connect all necessary data aware component:
https://wiki.freepascal.org/SQLdb_Tutorial1

And yes,everything goes fine,except some of the field values retrieved
back are marked as (MEMO)-not the real value,but some of the fields do
get the real values.Please see the attachment.

I have no clue what is going on here,and am really confused how come
some of the fields value are correct but some are not.

Help would be appreciated.

----------------------------------------------------------------------
CharlyTango · 2025-07-11 09:39

SQLite uses 5 standard datatypes (Position 2)
https://www.sqlite.org/datatype3.html

TEXT is then used usually for fields in which character strings are
stored.  Lazarus interprets this data type as memo.
Use Varchar() and the data should be displayed correctly.

----------------------------------------------------------------------
marcov · 2025-07-11 11:05

This used to be in the wiki, but it seems to have disappeared.

Have a function like this:

Code: [Select]procedure TSqlCreateFrm.defaultgettext(Sender: TField;
var Textit: string;
  DisplayText: Boolean);
begin
    if DisplayText then
     Textit := Copy(sender.AsString, 1, 50)
  else
     Textit:=sender.AsString;
end;

And assign it to the OnGetText of every TMemoField.

----------------------------------------------------------------------
PeterHu · 2025-07-11 11:54

Quote from: marcov on July 11, 2025, 01:05:21 pmThis used to be in the
wiki, but it seems to have disappeared.

Have a function like this:

Code: [Select]procedure TSqlCreateFrm.defaultgettext(Sender: TField;
var Textit: string;
  DisplayText: Boolean);
begin
    if DisplayText then
     Textit := Copy(sender.AsString, 1, 50)
  else
     Textit:=sender.AsString;
end;

And assign it to the OnGetText of every TMemoField.

Thanks,this works!
Code: [Select]procedure Tform1.Formcreate(Sender: Tobject);
begin
  sqlquery1.FieldByName('Name').OnGetText:=@defaultGetText;
  sqlquery1.FieldByName('CountryCode').OnGetText:=@defaultGetText;
  sqlquery1.FieldByName('District').OnGetText:=@defaultGetText;      
end;

But I was stuck when  I tried to do something further,in a for loop to
check each field of a SqlQuery,if the DataType is ftString,will assign
defaultGetText function pointer to its getText.But it won't compile.
Code: [Select]procedure Tform1.Formcreate(Sender: Tobject);
var
i:integer;
begin
  for i:=0 to sqlquery1.fieldCount-1 do
  begin
       if (sqlquery1.Fields[i].DataType=ftString) then
          sqlquery1.fields[i].OnGetText=@defaultGetText;// this won't
compile
  end;

End;

How can I get there?

Furthermore,may I ask what the codes do actually?
Code: [Select]procedure TSqlCreateFrm.defaultgettext(Sender: TField;
var Textit: string;
  DisplayText: Boolean);
begin
    if DisplayText then
     Textit := Copy(sender.AsString, 1, 50)
  else
     Textit:=sender.AsString;
end;

----------------------------------------------------------------------
af0815 · 2025-07-11 12:55

Look in Objectinspector for Options of the TDBGrid, expand this and
check dgDisplayMemoText (to true) . This should also show the contend
of the 'memo' fields.

----------------------------------------------------------------------
PeterHu · 2025-07-12 01:19

Quote from: af0815 on July 11, 2025, 02:55:45 pmLook in
Objectinspector for Options of the TDBGrid, expand this and check
dgDisplayMemoText (to true) . This should also show the contend of the
'memo' fields.

Great! Thanks a lot!