[solved] text in none grid components ( not (memo) )

[codifies]

While in the TDBGrid there is a property to treat memo fields as text, on all the other db aware components, I can't seem to display the field as text it always shows as (memo)

I'm using mariadb-10.5.10 on Void Linux

I tried changing the field (in the db structure) from TEXT to TINYTEXT (that does include its size embedded in the field data) but that doesn't seem to have any effect.

how do I get the none grid components to show actual text, I'm guess I'm okay with having fixed sizes (say 80?) for a forename (if I must), but afaict field types in the actual db don't seem to be effecting the component field type ??

I'm sure other people must have had this issue but a quick search of the forum didn't seem to show anything...

[JanRoza]

Just use a DBMemo element on your form and in the object inspector of that element fill in the correct datasource and datafield property.

[codifies]

that gets me a simple text field, but what about look up components etc ?

I tried changing the fielddefs property to sqlquery but that just shows an empty message dialog and locks up a few of the windows of the IDE with the rest vanishing (so don't know what the error is)

[devEric69]

that gets me a simple text field, but what about look up components etc ?

IIRC, i'm pretty sure, that it should also depend on the database, via such a WHERE sql search-text clause...): you need an index on such a lookup text field;

==> AFAIK, you have to test:
- creating indexes on text fields have limitations with your database (with 512, 1024, 4096 (?) ANSI database-bytes-chars; of course, such a quantity of bytes corresponds to fewer characters if your database uses UTF-8)? It should be remembered that relational databases are often not designed to be "full text\memo search indexed".
- then, if and only if the index on the lookup text field has been created, but the combo (displayed\inserted inside a TDBGrid in order to choose an ID-foreign_key) stay empty, you may have a look at the combo's style (extended, ...).

[egsuh]

In the past, no operations were available for Memo fields even equal (=) evaluation, at least in the case of Firebird. But recently many relational operations are possible. However you have to check carefully what are allowed with your database.

[codifies]

all I want is to treat small text fields like text (just like the DBGrid can) I'm not fully understanding why there isn't this option with other components and for that matter there doesn't even seem to be any easy way to alter the data on some kind of BeforeGetData type of event - I seem to remember a few decades ago that was possible with Delphi

I guess I need to learn how to override the behaviour of some of the controls so they will treat short text fields like text instead of memo's...

Can anyone demonstrate working DB controls with mysql/mariadb as so far the only one that I have *any* utility with is DBGrid, even DBListBox remains blank, let alone all the controls that just display (MEMO)

Its a real shame, without solid DB controls, its a bit of a chocolate fireguard as far as business orientated software is concerned. 

[marsupilami79]

What happens in the normal controls or in lookup controls depends on the kind of TField descandant that gets created. I assume that for you tinytext field still a TMemoField or TWideMemoField gets created.

If you change your fields in the database to be VarChar, then everything should be ok.

[wp]

While in the TDBGrid there is a property to treat memo fields as text, on all the other db aware components, I can't seem to display the field as text it always shows as (memo)

I am attaching a demo project for sqlite3 with a memo field ("Comments") for which the first 3 lines are displayed in a DBGrid cell. The limitation to 3 lines came in because memo field can have "infinite" length in principle. The text is automatically word-wrapped when the width of the column changes.

[codifies]

This is specific to mariaDB (and probably mysql?)

I spent far too long trying to find clues in the Lcl code base and messing with data source field data types and column data types - all of which don't seem to have any bearing and I'm not entirely sure what they actually do...

There are a wide range of textual data types in mysql, and long story short if its not a char or varchar then Lazarus with treat it as a memo - even despite a type like tinytext having a small limit (255 chars) with actual length embedded...

so long story short if you don't want to see the dreaded (MEMO) in most of the DB components you'll need to choose a CHAR data type.

This isn't really limiting as mostly you'll want for example forename or surname etc and VARCHAR for example will let you store just shy of 64kB !