Stange behaviour of Lazarus Trunk version

[rvk]

From the SQLite3-Source-Code

So conclusion is... SQLite is typeless (which we already knew) and you should NEVER use TEXT.
And when SQLite returns TEXT, and it is translated to ftMemo by FPC, then go through hoops to program around this 

It's why that TDBGrid.Option[dgDisplayMemoText] was born.
Now we need TDBLookupComboBox.Option[dgDisplayMemoText] too (and god knows how many more)

[Zvoni]

It's why that TDBGrid.Option[dgDisplayMemoText] was born.
Now we need TDBLookupComboBox.Option[dgDisplayMemoText] too (and god knows how many more)

Would be interesting to see, how DBGrid mapps it to String when that Option is set, then a user could just create a derived class and use that instead

[rvk]

It's why that TDBGrid.Option[dgDisplayMemoText] was born.
Now we need TDBLookupComboBox.Option[dgDisplayMemoText] too (and god knows how many more)

Would be interesting to see, how DBGrid mapps it to String when that Option is set, then a user could just create a derived class and use that instead

Not really a map but just a check on dgDisplayMemoText in options.

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

1.       if CheckDisplayMemo(aField) then
2.         Result := aField.AsString
3.       else
4.         Result := aField.Text;

It was a hack anyway for a problem that was created because TEXT was (wrongly) mapped to ftMemo in SQLite.

The other solution that often was given was to use CAST(field as VARCHAR) but that doesn't work anymore because of the recent change

[madref]

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;

I am running into problems because there are more forms with the same behaviour.
And now two forms are giving me troubles. if I make any changes to them the project won't compile anymore.
Can this be due to the fact that I had to use this solution/work around on 6 different forms also?


or is this my programming.


And yes the logical answer would be my programming skills sucks 

[marcov]

Note that this revision was merged back to the fixes branch

[madref]

But not in Trunk 2079

[madref]

Even in Rev 2099 it is still not resolved.


If I remove RVK 'Patch' I still get the initial error of "(Memo)".

[rvk]

Even in Rev 2099 it is still not resolved.

Marco meant that the change with showing (MEMO) was also made (merged back) in the fixes version. That's a version that's considered more stable than trunk.

I don't think there is a fix planned for this.

[madref]

I have another question about this because I keep getting an error that after a refresh of a query I get the same error all over again.
This is my query and see the attachment for the result.

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

1.   cSQL := 'SELECT ' +
2.           'ID, Scheidsrechter AS Referee, Scheids_ID, Divisie AS Division, ' +
3.           'StrFTime("%d-%m-%Y",Datum) AS Date, ' +
4.           'StrFTime("%H:%M",Tijd) AS Time, ' +
5.           '[Team A], [Team B], Seizoen, Wed_ID, TBL, Positie AS Position, ' +
6.           'Systeem AS System, DD, Sei_Periode, Divisie_Bond, ' +
7.           'Evo_Supervisor, Supervisor, ' +
8.           'CASE WHEN V="x" THEN "•" ELSE NULL END AS S ' +
9.           'FROM qry_Overzicht_Evaluaties ' +
10.           'WHERE ' +
11.           '((Seizoen=:Sei_ID) OR :Sei_ID=0) ' +
12.           'AND ' +
13.           '((Scheids_ID=:Scheids_ID) OR :Scheids_ID=0) ' +
14.           'ORDER BY Datum DESC, Tijd DESC;';

and in a form a can change some values in this query and then I have to refresh it so that in the form in which I show this query the changes are visible.

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

1.   TQ_Evaluaties.Refresh;
2.   TQ_Evaluaties.Locate('ID;TBL', VarArrayOf([Save_ID, Save_RL]), []);

And when I call this function I get an error that says that [Field "TBL" has an invalid field type (Memo) to base index on.]

[rvk]

What kind of field is TBL in the table?
If it's a real field it should be defined and read as string, not memo.

Only calculated fields (functions and concatenations) should be a problem with this change.

[madref]

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

1. CREATE VIEW qry_Overzicht_Evaluaties AS SELECT tbl_Evaluatie_Referee.Evo_ID AS ID, qry_Scheidsrechters.Scheidsrechter AS Scheidsrechter, tbl_Evaluatie_Referee.Evo_Scheids_ID AS Scheids_ID, qry_Overzicht_Wedstrijden.Divisie AS Divisie, qry_Overzicht_Wedstrijden.Datum AS Datum, qry_Overzicht_Wedstrijden.Tijd AS Tijd, qry_Overzicht_Wedstrijden.[Team A] AS [Team A], qry_Overzicht_Wedstrijden.[Team B] AS [Team B], qry_Overzicht_Wedstrijden.Seizoen AS Seizoen, qry_Overzicht_Wedstrijden.Wed_ID, CASE WHEN Evo_Systeem>0 THEN "R" ELSE "L" END AS TBL, CASE WHEN Evo_Systeem>0 THEN "Referee" ELSE "Linesman" END AS Positie, COALESCE(Evo_Systeem || " - men", "") AS Systeem, Datum AS DD, tbl_Seizoenen.Sei_Periode, Divisie_Bond, CASE WHEN EVO_Verstuurd THEN "x" ELSE NULL END AS V, tbl_Evaluatie_Referee.Evo_Supervisor, tbl_Supervisors.SPV_Naam AS Supervisor FROM tbl_Seizoenen INNER JOIN qry_Overzicht_Wedstrijden ON tbl_Seizoenen.SEI_ID = qry_Overzicht_Wedstrijden.Seizoen INNER JOIN tbl_Evaluatie_Referee ON qry_Overzicht_Wedstrijden.Wed_ID = tbl_Evaluatie_Referee.Evo_Wed_ID INNER JOIN qry_Scheidsrechters ON tbl_Evaluatie_Referee.Evo_Scheids_ID = qry_Scheidsrechters.Scheids_ID INNER JOIN tbl_Supervisors ON tbl_Supervisors.SPV_ID = tbl_Evaluatie_Referee.Evo_Supervisor UNION SELECT tbl_Evaluatie_Linesman.Evo_ID, qry_Scheidsrechters.Scheidsrechter, tbl_Evaluatie_Linesman.Evo_Scheids_ID, qry_Overzicht_Wedstrijden.Divisie, qry_Overzicht_Wedstrijden.Datum AS Datum, qry_Overzicht_Wedstrijden.Tijd AS Tijd, qry_Overzicht_Wedstrijden.[Team A], qry_Overzicht_Wedstrijden.[Team B], qry_Overzicht_Wedstrijden.Seizoen, qry_Overzicht_Wedstrijden.Wed_ID,
2.  
3.  
4. CASE WHEN Evo_Systeem>0 THEN "L" ELSE "R" END AS TBL,
5. CASE WHEN Evo_Systeem>0 THEN "Linesman" ELSE "Referee" END AS Positie,
6.  
7.  
8. COALESCE(Evo_Systeem || " - men", "") AS Systeem, Datum AS DD, tbl_Seizoenen.Sei_Periode, Divisie_Bond, CASE WHEN EVO_Verstuurd THEN "x" ELSE NULL END AS V, tbl_Evaluatie_Linesman.Evo_Supervisor, tbl_Supervisors.SPV_Naam  AS Supervisor FROM tbl_Seizoenen INNER JOIN qry_Overzicht_Wedstrijden ON tbl_Seizoenen.SEI_ID = qry_Overzicht_Wedstrijden.Seizoen INNER JOIN tbl_Evaluatie_Linesman ON qry_Overzicht_Wedstrijden.Wed_ID = tbl_Evaluatie_Linesman.Evo_Wed_ID INNER JOIN qry_Scheidsrechters ON tbl_Evaluatie_Linesman.Evo_Scheids_ID = qry_Scheidsrechters.Scheids_ID INNER JOIN tbl_Supervisors ON tbl_Supervisors.SPV_ID = tbl_Evaluatie_Linesman.Evo_Supervisor ORDER BY Seizoen DESC, DD DESC

[rvk]

Ha, yes, TBL is a CASE x AS y.

That's the problem.
Another problem why a string type shouldn't be translated to ftMemo.

Maybe it's time someone who was involved in the change to step in.
Because I don't know how you would change the locate when dealing with (MEMO).

It was just a bad fix.

[TRon]

Maybe it's time someone who was involved in the change to step in.
Because I don't know how you would change the locate when dealing with (MEMO).

It was just a bad fix.

Are you perhaps referring to this thread rvk ?

[madref]

Yes

[rvk]

Maybe it's time someone who was involved in the change to step in.
Because I don't know how you would change the locate when dealing with (MEMO).

It was just a bad fix.

Are you perhaps referring to this thread rvk ?

Yes. It will render any calculated field (so also any AS) as ftMemo.
Resulting in the (MEMO) but what's worse, inability to use that fiepd in locate.

And there is no other option to force ftString, other than adding (calculated) fields to your table.

CAST(x as varchar) as y should always be seen as ftString.

The original reason why fields were cut at 255 char/1020 bytes is that somehow an string without size was found (which results in cutting the field). VARCHAR without length is cut in FPC while VARCHAR(25) works just fine. Maybe that should have been addressed.

The fact that TEXT is translated to ftMemo isn't a big deal as long as VARCHAR and CAST is treated as ftString.