Text - String field length limits

[Niven]

I have a SQLite Db with a text field of several hundred characters. It displays the data fine from the sqlite command line but in Lazarus I can only get it to display 255 characters. Is there a way around this? I'd prefer to be able to keep it in a dbGrid, but am open to other options.
Sorry if this a bit of a dumb question: I've searched long and hard to find some documentation or previous answers on forums but to no avail ... so, would much appreciate being pointed in the right direction.

[taazz]

As far as I have worked with SQLite (from the SQLDB objects tab) it reports the string fields as memo and does not allow it to show any characters on the grid, you have to implement the onGetText/OnSetText events to show the data.

If my assumption is correct you have such an event declared on the fields and the code in this event is truncating the text for you, try to remove the truncation code, it should look something like
  Result := copy(x,1,255)

that's my assumption anyway take a look on your code or any auto events that might get attached to all the text fields.

Regards.

[Niven]

I've looked through all the code with no luck at finding any set limit. I checked using a dbmemo field as well and the same thing, truncates at 255 characters. All I'm doing to get the data is:

Code: [Select]

procedure TForm1.Button3Click(Sender: TObject);
begin
DataSource1.DataSet := sqlquery1;
DbMemo1.DataSource := DataSource1;
DbMemo1.DataField := 'COMMENT';
with sqlquery1 do
   begin
      if Active then Close;
      SQL.Clear;
      SQL.Add('SELECT COMMENT FROM vs');
      Open;
   end;
end;I've tried changing the field type in SQLite3 (eg, text, varchar) but it has made no difference. Maybe I could change the type as I execute the query in Lazarus but I'm not sure how, or if it would help. There's got to be a trick somewhere ...

[Niven]

A little update.
Changing the fieldtype to TEXT in SQLite allows the longer text to show in the Lazarus dbMemo, but only shows MEMO in the dbGrid. So, getting somewhere.
Changing the fieldtype in SQLite to VARCHAR(800) shows the extended text in both dbGrid and dbMemo. Maybe Lazarus SQLDB reads VARCHAR as 255 characters unless told otherwise??
Seems I could go with the large VARCHAR field but is that likely to cause issues along the way? Presumably VARCHAR takes only the resources it uses, rather than taking what it's allocated. Out of 50,000 records, only a small number go anywhere near the 800 characters.

[TheBlackSheep]

If a field has more than 255 characters you really want to keep it as Text but as you say you then can't see the text of the field in a dbgrid. 

A simple way around this is to create a calculated field of 255 characters (it doesn't exist in the database table it's just a runtime field created in Lazarus) - you then assign it in the OnCalculate event to copy the left 255 characters of the original field into this runtime field - this will then display ok in the grid (you hide your original field as it's only going to show [MEMO] anyway).

You could even split the field into two to show the first block of characters and the last few characters with a "..." in between.

TheBlackSheep

[taazz]

Varchar = character varying, which means that the field will keep as many characters as the data required to be saved, char on the other hand will hold the maximum number of character declared at all times regardless of the data length.

This is an extremely simplistic view of the data types, you need to read more about them for sqlite and see when it makes sense to stop using varchar and start using text since there is a limit on the bytes per record for each data base which when exited the use of varchar will use more disk space than the text type and will have a negative impact on speed too.

I'm sorry I can't be more informative I do not use SQLite at all.

Regards

[BigChimp]

@taazz: I'm not sure your advice would apply to sqlite... I suspect sqlite stores its fields all in the same way (because IIRC it doesn't enforce data type: i.e. you can insert an integer in a varchar field)...

@Niven: I also wanted to display text longer than 255 characters in memo fields... and finally succeeded. Can't seem to find it in my notes, but have a look here:
http://lazarus.freepascal.org/index.php/topic,16266.0.html
and
http://lazarus.freepascal.org/index.php/topic,16300.0.html
and an example of the application (and source code you can use) can be found:
https://bitbucket.org/reiniero/db2securityscript/src
in the OutputParser directory.

I don't think I've succeeded in getting scrollbars in the focused cell etc, but it is certainly a start.

[goodname]

Think the 255 character limit is imposed by the data aware controls. Since DBGrid is a data aware control the limit applies. Suggest that you use the SQLite field type that is the best fit for the data and adapt the front end to use it.

You might try using stringGrid and manually fill in the data. It is possible to embed controls in a DBGrid. A search for embed comboBox in DBGrid should give enough to find how to do it.

[BigChimp]

You might try using stringGrid and manually fill in the data. It is possible to embed controls in a DBGrid. A search for embed comboBox in DBGrid should give enough to find how to do it.

Just to clarify: in my post above I'm talking about showing memo fields on a dbgrid control...

The grids wiki page has some details on embedding controls on grids, IIRC...

[TheBlackSheep]

I use my solution all the time - is there a reason why I shouldn't be using it?

[goodname]

I use my solution all the time - is there a reason why I shouldn't be using it?

If your solution works for you then I don't see a problem using it. The embedding of a component in a DBGrid is my first thought on how to handle this.

[Niven]

Many thanks for the tips and insights. It'll take a while to churn it all over.
In the meantime I'll go with the long varchar, especially if I can word wrap in a grid (which is altogether another question). Varchar has some distinct advantages over Text, and apparently only uses memory for the length of the contents, not the size of the field, except for in-memory tables. So I can probably work around that. My database is single-user, standalone, and unlikely ever to take more than about 100K records, so maybe I can afford to be a bit more resource intensive anyway.