[HOWTO] rendering a MySql TEXT data type in a DBGrid lazarus / 16-Databases ---------------------------------------------------------------------- codifies · 2012-09-13 06:11 turns out that a MySql TEXT data type is rendered as (MEMO) by a DBGrid Here's how I got it to display some actual content instead... set the grids DefaultDrawing property to False use this for the on draw column cell event... Code: [Select]procedure TForm1.DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;   DataCol: Integer; Column: TColumn; State: TGridDrawState); begin    if (datacol=4) then {5th column is (memo) for example you could(should?) also check by name...}      dbgrid1.canvas.textout(Rect.left,rect.Top,column.field.asstring)    else      (Sender As TDBGrid).DefaultDrawColumnCell(Rect,DataCol,Column,State); end; ---------------------------------------------------------------------- teos · 2012-09-13 10:34 The correct field type for "strings" is varchar.. ---------------------------------------------------------------------- codifies · 2012-09-13 10:51 all well and good if you have control over the design of the db schema... in my case its is being accessed by different client technologies, and was designed else where ---------------------------------------------------------------------- BigChimp · 2012-09-13 10:53 Teos, what do you mean by that remark? I don't understand the relation with chris_c's post? Do you mean his code should be something like (air code - not tested, compiled etc) - (couldn't find varchar in sqldb datatype - varchar is SQL AFAIK; sqldb has other names for these types): Code: [Select]procedure TForm1.DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;   DataCol: Integer; Column: TColumn; State: TGridDrawState); begin    if (Column.Field.DataType=ftMemo) or (Column.Field.DataType=ftWideMemo) then {detect all memo fields; note see more candidates in TFieldType}      dbgrid1.canvas.textout(Rect.left,rect.Top,column.field.asstring)    else      (Sender As TDBGrid).DefaultDrawColumnCell(Rect,DataCol,Column,State); end; ---------------------------------------------------------------------- teos · 2012-09-13 14:54 Quote from: BigChimp on September 13, 2012, 12:53:09 pmTeos, what do you mean by that remark? I don't understand the relation with chris_c's post? Do you mean his code should be something like (air code - not tested, compiled etc) - (couldn't find varchar in sqldb datatype - varchar is SQL AFAIK; sqldb has other names for these types): Code: [Select]procedure TForm1.DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;   DataCol: Integer; Column: TColumn; State: TGridDrawState); begin    if (Column.Field.DataType=ftMemo) or (Column.Field.DataType=ftWideMemo) then {detect all memo fields; note see more candidates in TFieldType}      dbgrid1.canvas.textout(Rect.left,rect.Top,column.field.asstring)    else      (Sender As TDBGrid).DefaultDrawColumnCell(Rect,DataCol,Column,State); end;  Varchar is the data type that is interpreted as ftString. Text is ftMemo. My answer should have been that you should use the CAST keyword in your SQL so mysql does a cast from TEXT do VARCHAR. And then DBGrid will display it correctly. Another option (but I can't get that to work) is to add a field to the fielddefs of your dataset, and connect that to the field(s). if your field is ftstring, all is fine. To be honest and very frank: I would not start coding to draw cells in a dbgrid, I would try to get the data type right so my dataset creates TStringfields. ---------------------------------------------------------------------- ludob · 2012-09-13 15:05 QuoteMy answer should have been that you should use the CAST keyword in your SQL so mysql does a cast from TEXT do VARCHAR. And then DBGrid will display it correctly.But that doesn't solve the problem of displaying multi line memo's in a grid. Only custom drawing can do that. Using OnGetText to display a memo as a string is much easier than an SQL cast. ---------------------------------------------------------------------- teos · 2012-09-13 15:11 Quote from: ludob on September 13, 2012, 05:05:03 pmQuoteMy answer should have been that you should use the CAST keyword in your SQL so mysql does a cast from TEXT do VARCHAR. And then DBGrid will display it correctly.But that doesn't solve the problem of displaying multi line memo's in a grid. Only custom drawing can do that. Using OnGetText to display a memo as a string is much easier than an SQL cast. The grid will display the text as long as it fits in the 255 character limit. Multiline is an issue in both the cast and customdraw situation. Custom drawing does not take away the issue where a multi line text does not fit in a single row height, thus introducing a new issue and another workaround. I wonder how hard the requirement is to use DBGrid for this. If I'm correct, Virtualtreeview/Virtualgrid could be more adequate as any grid component that shows inline Memo fields. ---------------------------------------------------------------------- BigChimp · 2012-09-13 15:48 Not having a Delphi background, I'm wondering why DBGrids by default don't just display memo fields with the text cut off and with a vertical scrollbar if needed (e.g. when the cell is selected). Why would one show "Memo" if you know it's text data? All that messing around with custom drawn, other grids etc just seems messy. Would it make sense to extend the dbgrid control for this? ---------------------------------------------------------------------- taazz · 2012-09-13 16:37 Quote from: BigChimp on September 13, 2012, 05:48:27 pmNot having a Delphi background, I'm wondering why DBGrids by default don't just display memo fields with the text cut off and with a vertical scrollbar if needed (e.g. when the cell is selected). Because cells are virtual objects they don't show scrollbars in them you need to auto show the editor with the scrollbars. Quote from: BigChimp on September 13, 2012, 05:48:27 pmWhy would one show "Memo" if you know it's text data? All that messing around with custom drawn, other grids etc just seems messy. Would it make sense to extend the dbgrid control for this? No, what makes sense is to allow the TField to give you the displaytext as that's what is for, dbgrid should not care what type of field is showing if the field it self has a displaytext then show it otherwise don't. It is impossible to provide a common function to represent data from a memo given the fact that the data might be in html, rtf, clear text or any other kind of form (doc, pdf) etc. Since there is no accepted standard for this behavior and since most people just use memo fields for their html needs it is impossible to accept that as a default and let the user decide how he wants to handle the memos on a field by field bases. If the  DBgrid is actively guarding against field types then it should stop in my opinion. ---------------------------------------------------------------------- BigChimp · 2012-09-13 17:44 Sorry, I keep thinking about Firebird's BLOB SUBTYPE TEXT which should map to text content (as opposed to BLOB SUBTYPE BINARY). Presumably MySQL doesn't have something like that. Regardless, I'd still like to make the point regarding VARCHAR(n) where n>255. AFAIR, text isn't shown there either. ---------------------------------------------------------------------- teos · 2012-09-13 17:59 Quote from: BigChimp on September 13, 2012, 05:48:27 pmNot having a Delphi background, I'm wondering why DBGrids by default don't just display memo fields with the text cut off and with a vertical scrollbar if needed (e.g. when the cell is selected). Why would one show "Memo" if you know it's text data? All that messing around with custom drawn, other grids etc just seems messy. Would it make sense to extend the dbgrid control for this? The fun of object oriented programming is that a TDBgrid can be derived. But I'm not aware of other sites than Torry to find them. ---------------------------------------------------------------------- teos · 2012-09-13 18:02 Quote from: BigChimp on September 13, 2012, 07:44:50 pmSorry, I keep thinking about Firebird's BLOB SUBTYPE TEXT which should map to text content (as opposed to BLOB SUBTYPE BINARY). Presumably MySQL doesn't have something like that. Regardless, I'd still like to make the point regarding VARCHAR(n) where n>255. AFAIR, text isn't shown there either. I think you have a point. No doubt. The only thing is we don't know how long the text will be, maybe it is shorter than 255 anyway. But we are all working around the issue that the field type in the database is defined wrong. ---------------------------------------------------------------------- BigChimp · 2012-09-13 18:09 Quote from: teos on September 13, 2012, 07:59:47 pmQuote from: BigChimp on September 13, 2012, 05:48:27 pmWould it make sense to extend the dbgrid control for this? The fun of object oriented programming is that a TDBgrid can be derived. But I'm not aware of other sites than Torry to find them. Yes Yet another item at the very bottom of the todo list I suppose @teos: thanks. ---------------------------------------------------------------------- teos · 2012-09-13 18:26 @BigChimp, it would not surprise me if VirtualDBGrid is capable of displaying memo data. ---------------------------------------------------------------------- codifies · 2012-09-13 18:34 MySql wont allow a cast to varchar only char, and I couldn't get char to display. Where you need multi line data, what's the best way? I personally don't see the problem with abstracting the first dozen or so characters for preview in a grid... ---------------------------------------------------------------------- teos · 2012-09-13 20:04 Quote from: chris_c on September 13, 2012, 08:34:01 pmMySql wont allow a cast to varchar only char, and I couldn't get char to display. Where you need multi line data, what's the best way? I personally don't see the problem with abstracting the first dozen or so characters for preview in a grid... I have used Zeos and SQLite for a quick test, Char displays as TStringField in Lazarus. Which components are you using? For multiline data or abstracted characters, I would use a data entry form and would let the grid only display data. The entry form lets you use a DBMemo for the full contents of the Text field. Or.. maybe better: we talked about a data aware grid that displays memo fields. ---------------------------------------------------------------------- codifies · 2012-09-13 21:44 its mysql itself that won't let you cast to varchar what I've ended up with is both a truncated preview in the grid (which is actually needed) AND a data editing form with data aware memo...