Recent

Author Topic: SQLite 3 error  (Read 4611 times)

sabouras

  • New Member
  • *
  • Posts: 12
SQLite 3 error
« on: July 05, 2016, 07:40:04 pm »
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

  • Guest
Re: SQLite 3 error
« Reply #1 on: July 05, 2016, 08:14:28 pm »
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

  • Hero Member
  • *****
  • Posts: 618
    • http://www.silentwings.nl
Re: SQLite 3 error
« Reply #2 on: July 05, 2016, 10:25:31 pm »
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
OS: Windows 10 (64 bit) / Linux Mint (64 bit)
Laz: Lazarus 2.0.12 FPC 3.2.0
       CodeTyphon 7.4 FPC 3.3.1

sky_khan

  • Guest
Re: SQLite 3 error
« Reply #3 on: July 05, 2016, 10:47:21 pm »
@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.
« Last Edit: July 05, 2016, 10:48:58 pm by SkyKhan »

marcov

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 9224
  • FPC developer.
Re: SQLite 3 error
« Reply #4 on: July 05, 2016, 11:04:32 pm »
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

  • Hero Member
  • *****
  • Posts: 4472
Re: SQLite 3 error
« Reply #5 on: July 05, 2016, 11:07:42 pm »
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.
« Last Edit: July 05, 2016, 11:10:11 pm by rvk »

Jurassic Pork

  • Hero Member
  • *****
  • Posts: 963
Re: SQLite 3 error
« Reply #6 on: July 06, 2016, 12:22:53 am »
hello,
for that :
Quote
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
Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

sabouras

  • New Member
  • *
  • Posts: 12
Re: SQLite 3 error
« Reply #7 on: July 06, 2016, 04:30:51 am »
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

  • Hero Member
  • *****
  • Posts: 10725
Re: SQLite 3 error
« Reply #8 on: July 06, 2016, 08:54:32 am »
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
« Last Edit: July 06, 2016, 08:58:33 am by Thaddy »

rvk

  • Hero Member
  • *****
  • Posts: 4472
Re: SQLite 3 error
« Reply #9 on: July 06, 2016, 09:26:21 am »
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
« Last Edit: July 06, 2016, 09:43:36 am by rvk »

sabouras

  • New Member
  • *
  • Posts: 12
{SOLVED]: SQLite 3 error
« Reply #10 on: July 06, 2016, 06:33:49 pm »
Thank you all for detail explanations.

 

TinyPortal © 2005-2018