[Solved!] DBLookupListBox shows (MEMO) in place of the data

[heebiejeebies]

Hi all,

I have a DBLookupListBox connected to an SQLite database. I previously had an issue where all entries were appearing in the box as (MEMO) instead of displaying the string.  Someone on here suggested I cast the entries as CHAR. I've been using the following query for 2 years with no issues:

SELECT CAST(Issue AS CHAR) AS Issue, ID FROM MasterProtocol

Without me changing anything other than FPC/Lazarus version (and I presume Sqlite version as well), this has suddenly stopped working and is now displaying (MEMO) in place of the actual data again.  I have tried casting to VARCHAR and TEXT, have tried ensuring the field definition in the query is a String, both in the inspector and in code. Nothing works - I can't get this to display anything but (MEMO) in the DBLookupListBox. Very confusing and frustrating, I have several of these in my program so it's basically broken at the moment until I can figure this out.

Current SQlite version is 3.45.1
Oh and I doubt it's relevant but I did change from using Fedora to using Mint as well.

Any help is appreciated, thanks a lot for your time.

[Zvoni]

There were some changes in the sqlite3connection-class

Use OnGetText-Event

[heebiejeebies]

Thanks. I tried putting this in the OnGetText event of the field:

Code: Pascal  [Select][+][-]

1.   Text := Sender.AsString;

Now everything is showing up as blank instead of (MEMO).

[heebiejeebies]

Hmm, I just noticed when I look at the relevant field in the query using the inspector, the BlobType is stuck on ftMemo? i.e. When I pick another option from the dropdown it stays as ftMemo. Don't know if that's relevant but I just noticed it.

[Zvoni]

Hmm, I just noticed when I look at the relevant field in the query using the inspector, the BlobType is stuck on ftMemo? i.e. When I pick another option from the dropdown it stays as ftMemo. Don't know if that's relevant but I just noticed it.

It is. Try it out if it works.
Since i don't use DB-Bound controls i'm not that much help

[heebiejeebies]

Thanks. I don't know what you mean by try it out. There's nothing to try - it doesn't move from Memo. I've tried changing it in code and it still doesn't work.

[Sieben]

I suppose that field isn't the only Text in your database / application - do other fields you did cast in the way described in your first post work as expected?

There were some changes in the sqlite3connection-class

Do you happen to know what these changes are?

[heebiejeebies]

I suppose that field isn't the only Text in your database / application - do other fields you did cast in the way described in your first post work as expected?

Hi, thanks for the reply! No, they don't - all DBLookupListboxes are returning (MEMO). DBGrids are working fine, so that is an option if I'm desperate - but I'd very much prefer to keep certain items as Listboxes.

[Zvoni]

I suppose that field isn't the only Text in your database / application - do other fields you did cast in the way described in your first post work as expected?

There were some changes in the sqlite3connection-class

Do you happen to know what these changes are?

https://gitlab.com/freepascal.org/fpc/source/-/issues/40686

[rvk]

Which was a poor fix.
Instead of fixing the problem with length for ftString the field was just changed to ftMemo
(which exhibits this problem).

It would have been better to just fix the original problem.

The fix want even for normal tables but for FTS-Tables (virtual tables).
So it introduced more problems than it fixed.

[Zvoni]

Thanks. I tried putting this in the OnGetText event of the field:

Code: Pascal  [Select][+][-]

1.   Text := Sender.AsString;

Now everything is showing up as blank instead of (MEMO).

you should use "AText"
https://www.freepascal.org/docs-html/fcl/db/tfieldgettextevent.html

The event handler should return the contents of the field in formatted form in the AText parameter.

Code: Pascal  [Select][+][-]

1.   aText := Sender.AsString;

[Zvoni]

Which was a poor fix.
Instead of fixing the problem with length for ftString the field was just changed to ftMemo
(which exhibits this problem).

It would have been better to just fix the original problem.

The fix want even for normal tables but for FTS-Tables (virtual tables).
So it introduced more problems than it fixed.

In hindsight i agree with you.
Yes, it's a poor fix, since it only fixed FTS-tables, but i also wrote in the ticket:

Though: No idea if it would break anything else.

Yes, i agree the correct approach would be to target the "original" issue with the length

In my defence: I never use DB-Bound controls, so that's the only explanation i can offer

[heebiejeebies]

Thanks. I tried putting this in the OnGetText event of the field:

Code: Pascal  [Select][+][-]

1.   Text := Sender.AsString;

Now everything is showing up as blank instead of (MEMO).

you should use "AText"
https://www.freepascal.org/docs-html/fcl/db/tfieldgettextevent.html

The event handler should return the contents of the field in formatted form in the AText parameter.

Code: Pascal  [Select][+][-]

1.   aText := Sender.AsString;

THANK YOU!

I looked at the past thread and it was also you who solved the original problem by suggesting the SELECT CAST query, too.  For someone who doesn't use DB bound controls, you seem to know a lot about them!

**Nominates Zvoni for canonisation**