Stange behaviour of Lazarus Trunk version

[madref]

Sorry for all the commotion I am making you guys do

[rvk]

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

Yes, because that isn't stored, the sqlite3_column_decltype will return empty.
But because sqlite3_column_type returns stInteger for that, it will still be an integer.

The only 'problem' is with string types.
And that lies inherently at the translation of TEXT/stText to ftMemo.

But because stText (TEXT) was always translated to ftMemo, this is according to the other part (being the stored string fields).
It's just that you can't overrule CAST or || or other function to VARCHAR, it will always revert back to stText (because of sqlite3_column_decltype being empty).

I would rather have all TEXT and VARCHAR translated to ftString but I'm sure that's not going to happen.

Until then... the only problem is that TDBLookupComboBox loaded the values too soon (before the OnGetText).

Sorry for all the commotion I am making you guys do

No problem at all. This question will get asked a lot more in the future, I'm sure

At least you can use the fix I gave here to make sure you don't see those (MEMO).
https://forum.lazarus.freepascal.org/index.php/topic,67472.msg519407.html#msg519407

[Zvoni]

It's just that you can't overrule CAST or || or other function to VARCHAR, it will always revert back to stText (because of sqlite3_column_decltype being empty).

I doubt that, since i did use CAST As Char but with DBGrid/DBComboBox without that MemoThingy-Option

[rvk]

It's just that you can't overrule CAST or || or other function to VARCHAR, it will always revert back to stText (because of sqlite3_column_decltype being empty).

I doubt that, since i did use CAST As Char but with DBGrid/DBComboBox without that MemoThingy-Option

Weird. For me this also gives (MEMO) for lol2.

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

1.   SQLQuery1.SQL.Text := 'select *, CAST(lol as VARCHAR) as lol2 from master';

[Zvoni]

It's just that you can't overrule CAST or || or other function to VARCHAR, it will always revert back to stText (because of sqlite3_column_decltype being empty).

I doubt that, since i did use CAST As Char but with DBGrid/DBComboBox without that MemoThingy-Option

Weird. For me this also gives (MEMO) for lol2.

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

1.   SQLQuery1.SQL.Text := 'select *, CAST(lol as VARCHAR) as lol2 from master';

And it really returns stText for FT?
That is making no sense whatsoever

[rvk]

It's just that you can't overrule CAST or || or other function to VARCHAR, it will always revert back to stText (because of sqlite3_column_decltype being empty).

I doubt that, since i did use CAST As Char but with DBGrid/DBComboBox without that MemoThingy-Option

Weird. For me this also gives (MEMO) for lol2.

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

1.   SQLQuery1.SQL.Text := 'select *, CAST(lol as VARCHAR) as lol2 from master';

And it really returns stText for FT?
That is making no sense whatsoever

sqlite3_column_decltype returns "" for the lol2 field when doing CAST(x as VARCHAR).

This is seen here (it returns NULL for non-declared types).
https://www.sqlite.org/c3ref/column_decltype.html

SELECT c1 + 1, c1 FROM t1;
this routine would return the string "VARIANT" for the second result column (i==1), and a NULL pointer for the first result column (i==0).

So all non-stored fields have "" as FD and the code needs to guess from the sqlite3_column_type what type it is.
And SQLite will return stText for string types (ultimately resulting in (MEMO) ).

That's why I find stText -> ftMemo not a good choice for SQLite. It was better to do stText -> ftString and fix the non-specified size problem.
But that might introduce a whole lot of other unseen issues.

[Zvoni]

Found this:
https://www.sqlite.org/datatype3.html#determination_of_column_affinity

3.2. Affinity Of Expressions

Every table column has a type affinity (one of BLOB, TEXT, INTEGER, REAL, or NUMERIC) but expressions do not necessarily have an affinity.

Expression affinity is determined by the following rules:

    The right-hand operand of an IN or NOT IN operator has no affinity if the operand is a list, or has the same affinity as the affinity of the result set expression if the operand is a SELECT.

    When an expression is a simple reference to a column of a real table (not a VIEW or subquery) then the expression has the same affinity as the table column.

        Parentheses around the column name are ignored. Hence if X and Y.Z are column names, then (X) and (Y.Z) are also considered column names and have the affinity of the corresponding columns.

        Any operators applied to column names, including the no-op unary "+" operator, convert the column name into an expression which always has no affinity. Hence even if X and Y.Z are column names, the expressions +X and +Y.Z are not column names and have no affinity.

    An expression of the form "CAST(expr AS type)" has an affinity that is the same as a column with a declared type of "type".

    A COLLATE operator has the same affinity as its left-hand side operand.

    Otherwise, an expression has no affinity.

There seems to be a conspiracy......

[rvk]

An expression of the form "CAST(expr AS type)" has an affinity that is the same as a column with a declared type of "type".

Yes. It has the affinity of declared type of "type".
It doesn't become a declared type by itself

(as is seen here https://www.sqlite.org/c3ref/column_decltype.html)

Not sure if you can get to the part of "AS TYPE" in SQLite via the API.

[Zvoni]

An expression of the form "CAST(expr AS type)" has an affinity that is the same as a column with a declared type of "type".

Yes. It has the affinity of declared type of "type".
It doesn't become a declared type by itself

(as is seen here https://www.sqlite.org/c3ref/column_decltype.html)

Not sure if you can get to the part of "AS TYPE" in SQLite via the API.

Actually, i think the result of the sqlite3_column_type call is correct!
Numeric Value is 3, which is acc. to SQLite3-Source SQLITE3_TEXT, which translates to stText from TStorageType.
This actually looks correct.

[rvk]

Actually, i think the result of the sqlite3_column_type call is correct!
Numeric Value is 3, which is acc. to SQLite3-Source SQLITE3_TEXT, which translates to stText from TStorageType.
This actually looks correct.

True. sqlite3_column_type only has a few types and all string types are TEXT according to SQLite.
A VARCHAR(25) will probably also have sqlite3_column_type as TEXT.
But because if VARCHAR(25) is declared/stored, the sqlite3_column_type is never queried and just the sqlite3_column_decltype is used which was never a problem.

For CAST/FUNCTION etc (non declared types) the sqlite3_column_decltype is "" and the fallback is to sqlite3_column_type which gives TEXT.

The issue is that TEXT is seen as ftMemo in FPC. Not only with this change but also for TEXT and VARCHAR without length.
So the change is accordance with the complete handling of TEXT.
(And because you can't get to the wanted declaration for CAST it results in the (MEMO) )

But ok. I think we need to live with the fact TEXT fields and non-declared (calculated and cast) are translated to ftMemo.

Only problem was that for dynamic created fields where you set the OnGetText after opening, this could be too late for some components (like the TDBLookupComboBox).
For TDBGrid it is not too late because it just retrieves the text after viewing and not during creation (as TDBLookupComboBox does).

For users who use the Object inspector to create fields and set the OnGetText there, this isn't a problem.

(Although I still don't like the stText -> ftMemo bit )

[Zvoni]

(Although I still don't like the stText -> ftMemo bit )

I think that stems from SQLite 2, since e.g. a BLOB is mapped to ftMemo, too, and in SQLite 2 it probably was stored as TEXT or something

[madref]

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)

I can't seem to get it to work.
But I accidentally did this:

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

1. TQ_Referee.DataSource := nil;

And then I saw everything as it should be....

and when I assigned the datasource back to what it was...POEFF there are the (memos) again

[rvk]

(Although I still don't like the stText -> ftMemo bit )

I think that stems from SQLite 2, since e.g. a BLOB is mapped to ftMemo, too, and in SQLite 2 it probably was stored as TEXT or something

It's probably because in the beginning, the developers came from MSSQL or some other database.
Normally TEXT is a non-unicode of variable length.
See https://learn.microsoft.com/en-us/sql/t-sql/data-types/ntext-text-and-image-transact-sql?view=sql-server-ver16
and https://www.firebirdsql.org/manual/migration-mssql-data-types.html

Variable-length non-Unicode data with a maximum length of 231 - 1 (2,147,483,647) characters.

BUT... For SQLite it is:
https://sqlite.org/datatype3.html

TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).

AND... there is NO other string type in SQLite.
So it was a really bad idea to translate TEXT to ftMemo (also because there is a BLOB type in SQLite which seems better for the TEXT from other databases).
It's just wrong. TEXT in SQLite should have been ftString.
(or maybe I'm missing the motivation of the original developers)

I can't seem to get it to work.
But I accidentally did this:

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

1. TQ_Referee.DataSource := nil;

And then I saw everything as it should be....

and when I assigned the datasource back to what it was...POEFF there are the (memos) again

Yes. I take it that this is after you added that ListSource and ListSource := DS_Referee ??

The problem there is that if TQ_Referee has a Datasource property, it will close and reopen the dataset itself when the connected Datasource is re-opened. When that is done, the event dbOnGetText is lost for all fields.

So you need to reset those whenever any connected dataset is opened.

But... there might be a better option.
Double click the AfterOpen event for the TQ_Referee en put those lines there. Then they get assigned directly after opening.
(Can't test now because I messed up the project trying out things)

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

1. procedure TForm_Wedstrijd_Informatie.TQ_RefereeAfterOpen(DataSet: TDataSet);
2. var
3.   i: Integer;
4. begin
5.   for i := 0 to TQ_Referee.Fields.Count -  1 do
6.   begin
7.     if TQ_Referee.Fields[i].DataType in [ftmemo, ftWideMemo, ftString] then
8.       TQ_Referee.Fields[i].OnGetText := @dbmemoGetText;
9.   end;
10. end;
11.  
12. procedure TForm_Wedstrijd_Informatie.dbmemoGetText(Sender: TField; var aText: string; DisplayText: Boolean);
13. begin
14.   aText := Sender.AsString;
15. end;

You can remove the assignment-loop under the TQ_Referee.Active := True; because it's now done in AfterOpen.

Edit: Still doesn't work for me but AfterOpen is still the best place to set this event.

[rvk]

O wow... when you do TQ_Wedstrijd.Edit the TQ_Referee does a SCROLL (which is no problem).
BUT... it looses all the fields-events too. (is that a bug?)

So you need to set the OnGetText in the AfterScroll event of TQ_Referee.
Then it works. Pffffffff 

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

1. procedure TForm_Wedstrijd_Informatie.TQ_RefereeAfterScroll(DataSet: TDataSet);
2. var
3.   i: Integer;
4. begin
5.   for i := 0 to TQ_Referee.Fields.Count -  1 do
6.     begin
7.       if TQ_Referee.Fields[i].DataType in [ftmemo, ftWideMemo, ftString] then
8.         TQ_Referee.Fields[i].OnGetText := @dbmemoGetText;
9.     end;
10. end;
11.  
12. procedure TForm_Wedstrijd_Informatie.dbmemoGetText(Sender: TField; var aText: string; DisplayText: Boolean);
13. begin
14.   aText := Sender.AsString;
15. end;

[Zvoni]

It's probably because in the beginning, the developers came from MSSQL or some other database.
Normally TEXT is a non-unicode of variable length.
See https://learn.microsoft.com/en-us/sql/t-sql/data-types/ntext-text-and-image-transact-sql?view=sql-server-ver16
and https://www.firebirdsql.org/manual/migration-mssql-data-types.html

Variable-length non-Unicode data with a maximum length of 231 - 1 (2,147,483,647) characters.

BUT... For SQLite it is:
https://sqlite.org/datatype3.html

TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).

AND... there is NO other string type in SQLite.
So it was a really bad idea to translate TEXT to ftMemo (also because there is a BLOB type in SQLite which seems better for the TEXT from other databases).
It's just wrong. TEXT in SQLite should have been ftString.
(or maybe I'm missing the motivation of the original developers)

From the SQLite3-Source-Code

** Note that the SQLITE_TEXT constant was also used in SQLite version 2
** for a completely different meaning. Software that links against both
** SQLite version 2 and SQLite version 3 should use SQLITE3_TEXT, not
** SQLITE_TEXT.
*/
#define SQLITE_INTEGER  1
#define SQLITE_FLOAT    2
#define SQLITE_BLOB     4
#define SQLITE_NULL     5
#ifdef SQLITE_TEXT
# undef SQLITE_TEXT
#else
# define SQLITE_TEXT     3
#endif
#define SQLITE3_TEXT     3

Erratum: I meant CLOB, not BLOB above