Stange behaviour of Lazarus Trunk version

[madref]

I recently updated from Trunk Version 998 to Trunk Version 2071
And now I have some strange behaviour.
In the source code nothing has changed but in version 998 (Blue) I get my referees nicely selected as is stored in the database.
BUT in version 2071 (Red) I get (MEMO) on all my referees.
See attachments.


WTF has happend?

[jamie]

Well, it's up to Version 2074 now 

But really, how are we to know? Do those fields get read in from a Database file or some configuration file?

 And also, where do the colors get decided on, hardcoded or read from a file?


 If they are file related, then I would start looking there.

[Bart]

I recently updated from Trunk Version 998 to Trunk Version 2071

What does that mean?
I don't suppose you "updated" Lazarus to svn revision 2071 

Bart

[dsiders]

I recently updated from Trunk Version 998 to Trunk Version 2071

What does that mean?
I don't suppose you "updated" Lazarus to svn revision 2071 

Bart

It is the information returned by svn2revision, generated using:

git describe --long --first-parent
main_3_99-2075-g346b890f26

[paweld]

(MEMO) appears in controls associated with the ftMemo / ftWideMemo data type. The TDBGrid has a dgDisplayMemoText option whose enabling shows the contents of the cell in the grid. For other controls, you can get around this by setting the OnGetText event for each field:

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

1. procedure TForm1.dbmemoGetText(Sender: TField; var aText: string; DisplayText: Boolean);
2. begin
3.   if (Sender.DataType = ftmemo) or (Sender.DataType = ftWideMemo) then
4.     aText := Sender.AsString;
5. end;
6.  
7. procedure TForm1.FormCreate(Sender: TObject);
8. var
9.   i: Integer;  
10. begin
11.   query.SQL.Text := ' select * from table ';
12.   query.Open;
13.   for i := 0 to query.Fields.Count -  1 do
14.     query.Fields[i].OnGetText := @dbmemoGetText;  
15. end;    
16.  

[madref]

But all for fields are defined as TDBLookupComboBox.
How does that have to do with ftMemo?

[Zvoni]

But all for fields are defined as TDBLookupComboBox.
How does that have to do with ftMemo?

Nothing. It has to do with the FieldType, SQLite returns from your query.
Either do what Pawel wrote (custom Event), or cast all TEXT-Fields to CHAR within your SQl-Query

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

1. SELECT CAST(SomeTextField AS CHAR) AS SomeTextField FROM SomeTable

[madref]

But what I don't understand is that all other selections are ALSO TDBLookupCombobox's and the work as they should.


See the attachments in my first post


And this is how my table is defined.

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

1.     // tbl_Scheidsrechters aanmaken
2.     cSQL := '';
3.     cSQL := 'CREATE TABLE tbl_Scheidsrechters (' +
4.             'Scheids_ID WORD NOT NULL PRIMARY KEY ASC, ' +
5.             'Voornaam VARCHAR(20), ' +
6.             'Tussenvoegsel VARCHAR(10), ' +
7.             'Achternaam VARCHAR(50), ' +
8.             'Adres VARCHAR(60), ' +
9.             'Postcode VARCHAR(6), ' +
10.             'Woonplaats VARCHAR(40), ' +
11.             'Telefoon VARCHAR(11), ' +
12.             'Mobiel VARCHAR(11), ' +
13.             'Geboortedatum DATE, ' +
14.             'Email VARCHAR(250), ' +
15.             'IBAN VARCHAR(18)' +
16.             ')';
17.  

And this is how the fields are being created in the form:

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

1.   cSQL := 'SELECT ' +
2.     'CAST (REPLACE(RTRIM(COALESCE(Voornaam || " ", "") || ' +
3.     '  COALESCE(Tussenvoegsel || " ", "") || ' +
4.     '  COALESCE(Achternaam, "")), "  ", " ") AS CHAR) AS Scheidsrechter, ' +
5.     'tbl_Scheidsrechters.Voornaam, ' +
6.     'tbl_Scheidsrechters.Tussenvoegsel, ' +
7.     'tbl_Scheidsrechters.Achternaam, ' +
8.     'tbl_Scheidsrechters.Scheids_ID ' +
9.     'FROM tbl_Scheidsrechters ' +
10.     'LEFT JOIN tbl_Licenties ON tbl_Licenties.Fluit_Scheids_ID = tbl_Scheidsrechters.Scheids_ID ' +
11.     'WHERE ' +
12.     '((Fluit_Seizoen_ID=:Wed_Seizoen_ID) or :Wed_Seizoen_ID=0) ' +
13.     'OR ' +
14.     '(Scheids_ID IN (1,2,3,4)) ' +
15.     'GROUP BY 1,2,3,4,5 ' +
16.     'ORDER BY ' +
17.     'tbl_Scheidsrechters.Achternaam, ' +
18.     'tbl_Scheidsrechters.Voornaam;';

[paweld]

In FPC-trunk (SqlDB) has changed the behavior for create automatic field defs. And for this field:

Code: [Select]

REPLACE(RTRIM(COALESCE(Voornaam || " ", "") || ' +    '  COALESCE(Tussenvoegsel || " ", "") || ' +
    '  COALESCE(Achternaam, "")), "  ", " ")is treated as ftMemo, even after attempting to cast to char/varchar.
So you can either use the code I provided earlier (with a slight modification), or define the fields for the data set yourself (setting their type as ftString instead of ftMemo)

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

1. procedure TForm1.dbmemoGetText(Sender: TField; var aText: string; DisplayText: Boolean);
2. begin
3.   aText := Sender.AsString;
4. end;
5.  
6. procedure TForm1.FormCreate(Sender: TObject);
7. var
8.   i: Integer;  
9. begin
10.   query.SQL.Text := ' select * from table ';
11.   query.Open;
12.   for i := 0 to query.Fields.Count -  1 do
13.   begin
14.     if (query.Fields[i].DataType = ftmemo) or (query.Fields[i].DataType = ftWideMemo) then
15.       query.Fields[i].OnGetText := @dbmemoGetText;
16.   end;      
17. end;  
18.  
19.  

[Zvoni]

Errrrr....

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

1. 'WHERE ' +
2.     '((Fluit_Seizoen_ID=:Wed_Seizoen_ID) or :Wed_Seizoen_ID=0) ' +
3.     'OR ' +

How in blazes is this supposed to work?
A Parameter left-hand-side of equal-sign?!?!?!

Next: What are you trying to get with that Replace/Coalesce-Thingy?
The Coalesce is wrong there.
You need coalesce for Fields returning from a Left Join. You are using it on Fields from the "Master"-Table

If it is SQLite, change your Create table-Statement to this

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

1. 'CREATE TABLE tbl_Scheidsrechters (' +
2.             'Scheids_ID WORD NOT NULL PRIMARY KEY ASC, ' +
3.             'Scheidsrechter TEXT GENERATED ALWAYS AS ("Voornaam"||' '||"Tussenvoegsel"||' '||"Achternaam") VIRTUAL, ' +
4.             'Voornaam VARCHAR(20), ' +
5.             'Tussenvoegsel VARCHAR(10), ' +
6.             'Achternaam VARCHAR(50), ' +
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.             ')';

Then it should work with

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

1. cSQL := 'SELECT ' +
2.     'CAST(REPLACE(tbl_Scheidsrechters.Scheidsrechter, "  ", " ") AS CHAR) AS Scheidsrechter, ' +
3.     'tbl_Scheidsrechters.Voornaam, ' +
4.     'tbl_Scheidsrechters.Tussenvoegsel, ' +
5.     'tbl_Scheidsrechters.Achternaam, ' +
6.     'tbl_Scheidsrechters.Scheids_ID ' +
7.     'FROM tbl_Scheidsrechters ' +
8.     'LEFT JOIN tbl_Licenties ON tbl_Licenties.Fluit_Scheids_ID = tbl_Scheidsrechters.Scheids_ID ' +
9.     'WHERE ' +
10.     '((Fluit_Seizoen_ID=:Wed_Seizoen_ID) or :Wed_Seizoen_ID=0) ' +  /*?!?!?!?!?!?!*/
11.     'OR ' +
12.     '(Scheids_ID IN (1,2,3,4)) ' +
13.     'GROUP BY 1,2,3,4,5 ' +
14.     'ORDER BY ' +
15.     'tbl_Scheidsrechters.Achternaam, ' +
16.     'tbl_Scheidsrechters.Voornaam;';

btw: You are using unqualified Fieldnames there (in the WHERE-Clause).
Don't!
Always qualify Fieldnames with its table, and use Table-Aliases!
because if i read this correctly your WHERE-Clause actually turns your LEFT JOIN into an INNER JOIN

Untested, though.
And i repeat: If it is SQLite.....

[rvk]

Errrrr....

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

1. 'WHERE ' +
2.     '((Fluit_Seizoen_ID=:Wed_Seizoen_ID) or :Wed_Seizoen_ID=0) ' +
3.     'OR ' +

How in blazes is this supposed to work?
A Parameter left-hand-side of equal-sign?!?!?!

That's not a problem in SQL.
Parameter is substituted and the comparison is done.

Next: What are you trying to get with that Replace/Coalesce-Thingy?
The Coalesce is wrong there.
You need coalesce for Fields returning from a Left Join. You are using it on Fields from the "Master"-Table

No, it's used in a concatenation.
You need to convert the NULL to "" otherwise the concatenated result is always NULL even if the other fields is not. (This is a normal way of getting a full name)

If it is SQLite, change your Create table-Statement to this

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

1.             'Scheidsrechter TEXT GENERATED ALWAYS AS ("Voornaam"||' '||"Tussenvoegsel"||' '||"Achternaam") VIRTUAL, ' +
2.             '

This will fail if one of the fields is NULL.
Then the complete result is NULL, even if the other fields have values.

So that's why the coalesce is used inside the concatenate

[Zvoni]

Well then....

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

1. 'Scheidsrechter TEXT GENERATED ALWAYS AS (COALESCE(Voornaam, "")||' '||COALESCE(Tussenvoegsel, "")||' '||COALESCE(Achternaam, "") VIRTUAL, ' +

.... or use DEFAULT "" for all 3 Fields. That way you would avoid the NULL-Crap in a Master-Table

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

1.     'WHERE ' +
2.         '((Fluit_Seizoen_ID=:Wed_Seizoen_ID) or :Wed_Seizoen_ID=0) ' +
3.         'OR ' +

That's not a problem in SQL.
Parameter is substituted and the comparison is done.

You do realize, that this would result in something like: ..... OR 3=0
And it would always return False except if :Wed_Seizoen_ID is 0, making the OR superfluos in 99% of cases

[rvk]

.... or use DEFAULT "" for all 3 Fields. That way you would avoid the NULL-Crap in a Master-Table

Yep. That'll work (and that's what was also done in the SELECT itself).

You do realize, that this would result in something like: ..... OR 3=0
And it would always return False except if :Wed_Seizoen_ID is 0, making the OR superfluos in 99% of cases

Yes. and 3=0 will evaluate to a boolean. What's wrong with this.
:Wed_Seizoen_ID=0 is the same as 0=:Wed_Seizoen_ID
It just checks of the parameter :Wed_Seizoen_ID is 0, and if it is it will not check Fluit_Seizoen_ID=:Wed_Seizoen_ID (because of the or).

This is perfectly fine for when you don't want to change the SQL and do want to check if the input parameter is 0.
I also have similar constructions but I even have a COALESCE around :Wed_Seizoen_ID so if the parameter is NULL it also skips the check for Fluit_Seizoen_ID.

How would you create a SQL where for a parameter <> 0 (or NULL) it will only give you the Fluit_Seizoen_ID = parameter, AND if parameter is 0 (or NULL) it will return everything? (without changing the SQL)

[madref]

Where do I put the declaration of this procedure?

procedure TForm1.dbmemoGetText(Sender: TField; var aText: string; DisplayText: Boolean);

[rvk]

Where do I put the declaration of this procedure?

You can put the declaration in the public part of your form-declaration.

(just copy that line to the declaration part under public and strip the "TForm1.")