SQLite 3 error

[sabouras]

Dear All,
I am very new in Lazarus and FPC. I try to learn how to work with the database SQLITE3. I have make step by step several tutorials founded in the web pages. The problem i cope with is that the DBGrid shows only the table fields and no the contain of the fields. In order to understand the problem i am attaching the code and the results i have in my onedrive (the files are too big to upload in the site).
Hope help me found a solution.
Thanks in advance for your time.
Best regards to all.

https://1drv.ms/f/s!AsGJl3xpsi23gvErmrwdNKB55-iv-w

[sky_khan]

DBGrid can not display or edit blob fields. Your table definition has TEXT columns which is recognized as BLOB type.
You may use TDBMemo component to edit them or you should change their type to VARCHAR() if you need them be editable in Grid.

[JanRoza]

SkyKhan, your information is incorrect as you can show memo field in a dbgrid.
Just look at this topic: http://forum.lazarus.freepascal.org/index.php?topic=14082.0

[sky_khan]

@JanRoza

"you can show memo field in a dbgrid" <> "DBGrid can display or edit BLOB fields"
I said latter.

You can draw anything you want to anywhere you want with some hacks but unless you add this code to component source,
it does not mean that component have this capability.

For example,
Will you tell me how can I edit a 3D model file stored in BLOB field within DBGrid cell ? No ?
I thought so.

[marcov]

IIRC there is some "ongettext" event for that (memo) fieldtype. Assign it to a function like this:

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

1. procedure TSqlCreateFrm.defaultgettext(Sender: TField; var Textit: string;
2.   DisplayText: Boolean);
3. begin
4.     if DisplayText then
5.      Textit := Copy(sender.AsString, 1, 50)
6.   else
7.      Textit:=sender.AsString;
8. end;
9.  

[rvk]

TEXT maps to a TMemoField (which shows as (memo) in the grid).
BLOB maps to a TBlobField (which shows as (blob) in the grid).
(note: they are different !!!)

A TMemoField could still be shown in a DBGrid through the OnGetText.
Or you could do a CAST to VARCHAR in the SQL-query (in which case you loose the ability to edit).

So the easiest way is to use VARCHAR during creation of the DB for limited string length.

[Jurassic Pork]

hello,
for that :

Or you could do a CAST to VARCHAR in the SQL-query (in which case you loose the ability to edit).

have a look to this topic

Friendly, J.P

[sabouras]

First of all thank you all for your effort.
As you could see either the name and the path of the sample data are not display at all in the DBGrid.
Is the blob field which mesh up with the entire code or i have made some else some error?
Thank you very much all.

[Thaddy]

First of all thank you all for your effort.
As you could see either the name and the path of the sample data are not display at all in the DBGrid.
Is the blob field which mesh up with the entire code or i have made some else some error?
Thank you very much all.

Yes, it is the blob field that gets things messed up: Blob fields are binary fields and the programmer is responsible for making it visible in the correct manner.
e.g. you can store an image in a blob field and it is your responsability to display it properly, because only you know if it is a bmp, jpg, raw or png, or...
The problem is Blob fields can store zero's which are not part of the specification for strings in most databases because they specify a zero as a terminator.

Blob means Binary Large OBject

[rvk]

As you could see either the name and the path of the sample data are not display at all in the DBGrid.
Is the blob field which mesh up with the entire code or i have made some else some error?

Yes, it is the blob field that gets things messed up: Blob fields are binary fields and the programmer is responsible for making it visible in the correct manner.

Correct for real BLOB-fields. But if you looked at the screenshots in the OP you could have seen that the "name" and "path" fields were (MEMO)-fields (TEXT). Only the "image" field is a real (BLOB)-field (BLOB).
They are very different. Like I already said... TEXT maps to TMemoField which causes the confusion because a TMemoField shows the (MEMO). But it is still a string and not just a binary field. So the problem for "name" and "path" is caused by the TEXT-fields which are normally used for strings !!. (and yes, I know a TEXT-field in SQLite can also store binary data but that's just the relaxed typing-system of SQLite. Normally you wouldn't define a TEXT for binary, you would use BLOB.)

See: https://www.sqlite.org/datatype3.html#section_3

@sabouras, the easiest solution would be that you change the field-type of "name" and "path" to VARCHAR. In that case SQLite will still store the string but FPC/Lazarus can recognize it is a string and use TStringField (which will show correctly in a DBGrid). Only when you are going to use a larger TMemo (for complete multiline description) using TEXT is useful.

Although VARCHAR is not an internal SQLite Affinity, it will map to the TEXT Affinity, but still use the VARCHAR to communicate with the program (which is why the program can show the TStringField). See https://www.sqlite.org/datatype3.html#section_3_2

[sabouras]

Thank you all for detail explanations.