Stange behaviour of Lazarus Trunk version

[madref]

Thanks RVK. I thought it was me doing something wrong.


And yes I know it's still large. But I just couldn't remove all graphics

[rvk]

This is a test project... (see attached) 

It'll create it's own database.
I changed the line SELECT * into a SELECT CAST(lol as varchar).

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

1.   SQLQuery2.SQL.Text := 'select * from maindetail where master_id=:id';
2.  
3.   // now with cast
4.   SQLQuery2.SQL.Text := 'select id, master_id, cast(lol as VARCHAR) as lol from maindetail where master_id=:id';
5.  
6.   SQLQuery2.FieldByName('lol').OnGetText := @dbmemoGetText;
7.  

The TDBGrid picks up the OnGetText just fine.
But the TDBLookupCombobox doesn't.

It's only a problem when using CAST.

Edit: I added a comment to https://gitlab.com/freepascal.org/fpc/source/-/issues/40686
(not sure if it gets picked up there)

[madref]

Yes that is strange....

Code: [Select]

REPLACE(RTRIM(COALESCE(Voornaam || " ", "") || ' +
    '  COALESCE(Tussenvoegsel || " ", "") || ' +
    '  COALESCE(Achternaam, "")), "  ", " ") AS Scheidsrechter
Is this also a CAST?

[rvk]

Yes that is strange....

Code: [Select]

REPLACE(RTRIM(COALESCE(Voornaam || " ", "") || ' +
    '  COALESCE(Tussenvoegsel || " ", "") || ' +
    '  COALESCE(Achternaam, "")), "  ", " ") AS Scheidsrechter
Is this also a CAST?

Maybe it's not just CAST but FUNCTION AS ALIAS. This also fails.

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

1.   SQLQuery2.SQL.Text := 'select id, master_id, REPLACE(lol, " ", "-") as lol from maindetail where master_id=:id';

and this

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

1.   SQLQuery2.SQL.Text := 'select id, master_id, COALESCE(lol, "-") as lol from maindetail where master_id=:id';

Anyway, this was implemented as fix for FTS tables.
I think they should have only implemented when dealing with FTS tables and not for everything else.

(This is going to get a LOT of questions in the future, here on the forum)

[madref]

Is there a workaround for this problem?

[dsiders]

Is there a workaround for this problem?

Which problem? Pick one...

[Zvoni]

Anyway, this was implemented as fix for FTS tables.
I think they should have only implemented when dealing with FTS tables and not for everything else.

(This is going to get a LOT of questions in the future, here on the forum)

Rik, since that fix was from me, (and i don't have trunk), could you check:
Go into sqlite3conn.pas, search for
procedure TSQLite3Connection.AddFieldDefs(cursor: TSQLCursor; FieldDefs: TFieldDefs);

And set a breakpoint at

if FT=ftUnknown then
      case TStorageType(sqlite3_column_type(st,i)) of
(some 70 lines lower)

and check what's returned for FD, FT and TStorageType

EDIT: My Fix was actually only if FT returns ftUnknown through the first run (could not find in FieldMap), so i think the crucial part is:
What's the Value of FD?
FD := uppercase(sqlite3_column_decltype(st,i));
This one returns the "real" Type-Name how it's saved in SQLite
Then it checks if that Type-Name exists in the FieldMap-Array.
If it doesn't (FT=ftUnknown), only then it jumps into the Case Of, where my Fix was placed

[rvk]

and check what's returned for FD, FT and TStorageType

Well, that's not so difficult.
In my testprogram FT is only ftUnknown for the CAST/FUNCTION/calculated field.
FN does have a name (lol).
FD is empty.
And sqlite3_column_type(st,i) of course results in stText (otherwise you wouldn't get the problematic ftMemo now).

So, because FD is empty, sqlite3_column_decltype somehow doesn't get the correct datatype of that field.
Then the type is guessed via sqlite3_column_type.
Normally, when that's stText, you just use ftString (TStringField).

    // In case of an empty fieldtype (FD='', which is allowed and used in calculated
    // columns (aggregates) and by pragma-statements) or an unknown fieldtype,
    // use the field's affinity:

Why was the decision made to use ftMemo for calculated fieds (like CONCAT/COALESCE/CAST etc) ???

stText fields have no problems being larger than 1020.
But I don't work with FTS tables.

Can you provide a simular smal example project where you encountered that problem?

Above is point 1. The wrongly translating calculated fields from stText to ftMemo.

Point 2 is... when you do use the override for OnGetText... why does TDBLookupComboBox not use that text.
(I'll need to dive into that one)


Edit: O, WOW. Point 1 even gets more problematic.
If I use this for table creation

Code: SQL  [Select][+][-]

1. CREATE TABLE master(id INT, lol text);

So I use the native TEXT from SQLite... then all those fields also show (MEMO).
For VARCHAR(25) this was not the case.
But it seems that native TEXT is always routed to ftMemo (see FieldMap).

So maybe the calculated fields should be too.
But how to force them to ftString when you use CAST as VARCHAR?

BTW. standard using VARCHAR without specifying length in SQLite, FPC cuts the field to 1020.
Although the fields itself in SQLite does contain the original length.
So somewhere, there is a limit in FPC when no length is specified.
This might also be the case for the FTS tables.

[Zvoni]

Can you provide a simular smal example project where you encountered that problem?

https://forum.lazarus.freepascal.org/index.php/topic,66335.0.html (Complete thread!)

EDIT:

So I use the native TEXT from SQLite... then all those fields also show (MEMO).

Yes, but that's "expected" behavior, (and documented).

[rvk]

Is there a workaround for this problem?

The problem is that TDBLookupComboBox loads the records directly after the dataset is opened.
After that, you set the OnGetText in place (which is too late for TDBLookupComboBox).

The fix is to reload the lines for TDBLookupComboBox.
I tried TDBLookupComboBox.Refresh but that doesn't work.
This however does work.

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

1.   SQLQuery2.FieldByName('lol').OnGetText := @dbmemoGetText;
2.   DBLookupComboBox2.ListSource := nil;
3.   DBLookupComboBox2.ListSource := DataSource2;

So setting the ListSource to nil and resetting it to the TDataSource will reload all the lines.

(Unfortunately the TDBLookupComboBox.UpdateLookup is private so we need to do it this way)

[rvk]

Can you provide a simular smal example project where you encountered that problem?

https://forum.lazarus.freepascal.org/index.php/topic,66335.0.html (Complete thread!)

When defining a virtual FTS table, you do not specify the data type. Zeos immediately recognizes it as ftMemo, while SQLdb recognizes it as ftString - so I chose ZEOS :-)

*sigh*
and because it's ftString with no size, it get's cut off at 255 characters....

Yes... I see the ftString being cut too with VARCHAR without length in SQLite.
(4 bytes per character in UTF8 is 1020 bytes total)

I'm not sure why the decision was made to cut the string without length and not just use TStringField without length (i.e. maximum length).

But seeing that the native TEXT is also (and was always) translated to ftMemo, this was the most logical choice here, for unspecified stText.
I'm also still not sure why TEXT is ftMemo and not ftString...

It's a shame that SELECT CAST(xx as VARCHAR(25) doesn't translate back to sqlite3_column_decltype() because then it could be seen as a ftString instead of stText/ftMemo.

(In my post above I gave a fix for the tdblookupcombobox problem)

[Zvoni]

Rik,
could you try a las test?


Try with this CREATE TABLE-Statement

Code: SQL  [Select][+][-]

1.     'CREATE TABLE tbl_Scheidsrechters (' +
2.                 'Scheids_ID WORD NOT NULL PRIMARY KEY ASC, ' +
3.                 'Scheidsrechter VARCHAR(82) GENERATED ALWAYS AS ("Voornaam"||' '||"Tussenvoegsel"||' '||"Achternaam") STORED, ' +
4.                 'Voornaam VARCHAR(20) DEFAULT "", ' +
5.                 'Tussenvoegsel VARCHAR(10) DEFAULT "", ' +
6.                 'Achternaam VARCHAR(50) DEFAULT "", ' +
7.                 'Adres VARCHAR(60), ' +
8.                 'Postcode VARCHAR(6), ' +
9.                 'Woonplaats VARCHAR(40), ' +
10.                 'Telefoon VARCHAR(11), ' +
11.                 'Mobiel VARCHAR(11), ' +
12.                 'Geboortedatum DATE, ' +
13.                 'Email VARCHAR(250), ' +
14.                 'IBAN VARCHAR(18)' +
15.                 ')';

and check the Return-Type of Field "Scheidsrechter"
Not sure about double-Quotes vs. Single-Quotes for DEFAULT in Fields "Scheidsrechter" to "Achternaam"
It's just to avoid NULL's

EDIT:

But seeing that the native TEXT is also (and was always) translated to ftMemo, this was the most logical choice here, for unspecified stText.
I'm also still not sure why TEXT is ftMemo and not ftString...

At a guess: Probably, because FieldMap is a "generic" Array, handling all Databases....

EDIT2: And considering that this Thread is already on its Page 3, it's no wonder that i despise DB-Bound controls, and have (and will) never use(d) them

[rvk]

Code: SQL  [Select][+][-]

1.                 'Scheidsrechter VARCHAR(82) GENERATED ALWAYS AS ("Voornaam"||' '||"Tussenvoegsel"||' '||"Achternaam") STORED, ' +

and check the Return-Type of Field "Scheidsrechter"

I did this in my test project. (I did need to upgrade the sqlite because I was at 3.13 which doesn't have GENERATED )

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

1.     SQL := 'CREATE TABLE master(id int, lol varchar(25), Scheidsrechter VARCHAR(25) GENERATED ALWAYS AS (lol||" "||lol) STORED );';

The VARCHAR(25) gets stored as VARCHAR so is translated to ftString with length.
Fun part here is that using VARCHAR without length also gets ftString.
Using TEXT gets the (MEMO) again (so stText).

(using VIRTUAL or STORED doesn't make a difference)

Not sure about double-Quotes vs. Single-Quotes for DEFAULT in Fields "Scheidsrechter" to "Achternaam"
It's just to avoid NULL's

This won't avoid the NULL's because you do "Voornaam"|| etc.

Even when using DEFAULT, your code can still push NULL as value.
If you don't want NULL you need to specify NOT NULL for those fields. Then you can ommit the COALESCE in the SELECT.

[rvk]

But seeing that the native TEXT is also (and was always) translated to ftMemo, this was the most logical choice here, for unspecified stText.
I'm also still not sure why TEXT is ftMemo and not ftString...

At a guess: Probably, because FieldMap is a "generic" Array, handling all Databases....

No, the fieldmap is in SQLite3Conn, so specific for SQLite.
Maybe it's because there is no Memo field in SQLite. But I would rather use ftString for TEXT instead of ftMemo.
There is BLOB for ftMemo.

[Zvoni]

OK,
correct on the NOT NULL (i'm getting distracted with Bulls**t by my coworkers....).

So basically, we have the "Problem" boxed into a corner....

hmmm...... what's the Value for FD for something simple as
SELECT IntField1+IntField2 As SomeField from SomeTable

Does this count as "calculated" Field? I think you'll get my drift....

It's a shame that SELECT CAST(xx as VARCHAR(25) doesn't translate back to sqlite3_column_decltype() because then it could be seen as a ftString instead of stText/ftMemo.

But it does.
Try a SELECT CAST(Blabla As CHAR) FROM Table (with Blabla being of native Type TEXT)
and use a DBGrid withouth setting the dbShowMemoastext-Option (or whatever its name)