[SOLVED] How to Render an SQLite3 BLOB Field in a TImage?

[Aruna]

Hello everyone,

The attached screenshot shows the last field, "Picture", as a BLOB in the DBGrid. I'm trying to display this image in a TImage component, but I'm unsure how to properly extract and render it.

What I'm Doing:
    The BLOB field contains image data stored in hex format.
    I believe I need to convert this hex data into a binary format before loading it into TImage.

Example Table Structure:

Code: Text  [Select][+][-]

1. CREATE TABLE [Categories]
2. (      [CategoryID] INTEGER PRIMARY KEY AUTOINCREMENT,
3.        [CategoryName] VARCHAR(100),
4.        [Description] VARCHAR(100),
5.        [Picture] BLOB);
6.  

Example BLOB Data (Hex Format):

Code: Text  [Select][+][-]

1. INSERT INTO Categories VALUES(2,'Condiments','Sweet and savory sauces, relishes, spreads, and seasonings',X'ffd8ffe000104a46494600010200006400640000ffec00114475636b79000100040000004c0[b]<snip>[/b]0c472a3c64768557ccffd900');

The BLOB field contains image data in hex format, shown below:

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

1. X'ffd8ffe000104a46494600010200006400640000ffec00114475636b79000100040000004c0[b]<SNIP>[/b]0c472a3c64768557ccffd900')

My Questions:
    How can I convert the hex BLOB data into a format that TImage can display?
    Does anyone have a working example of loading an image from an SQLite3 BLOB field into a TImage?
    Are there any special considerations for handling different image formats (JPEG, PNG)?

I'm working on porting Northwind.db and later Chinook.db, so I'd love to get this working properly.

Any help or sample code would be greatly appreciated!

[dsiders]

Hello everyone,

The attached screenshot shows the last field, "Picture", as a BLOB in the DBGrid. I'm trying to display this image in a TImage component, but I'm unsure how to properly extract and render it.

What I'm Doing:
    The BLOB field contains image data stored in hex format.
    I believe I need to convert this hex data into a binary format before loading it into TImage.

Example Table Structure:

Code: Text  [Select][+][-]

1. CREATE TABLE [Categories]
2. (      [CategoryID] INTEGER PRIMARY KEY AUTOINCREMENT,
3.        [CategoryName] VARCHAR(100),
4.        [Description] VARCHAR(100),
5.        [Picture] BLOB);
6.  

Example BLOB Data (Hex Format):

Code: Text  [Select][+][-]

1. INSERT INTO Categories VALUES(2,'Condiments','Sweet and savory sauces, relishes, spreads, and seasonings',X'ffd8ffe000104a46494600010200006400640000ffec00114475636b79000100040000004c0[b]<snip>[/b]0c472a3c64768557ccffd900');

The BLOB field contains image data in hex format, shown below:

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

1. X'ffd8ffe000104a46494600010200006400640000ffec00114475636b79000100040000004c0[b]<SNIP>[/b]0c472a3c64768557ccffd900')

My Questions:
    How can I convert the hex BLOB data into a format that TImage can display?
    Does anyone have a working example of loading an image from an SQLite3 BLOB field into a TImage?
    Are there any special considerations for handling different image formats (JPEG, PNG)?

I'm working on porting Northwind.db and later Chinook.db, so I'd love to get this working properly.

Any help or sample code would be greatly appreciated!

Most people would just use the TDBImage control. Otherwise, look at the LoadPicture method implemented in dbctrls.pp / dbimage.inc. That'll show you what you need to know.

[rca]

I don't use hexadecimal formatting for images.

Having a form with:

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

1. uses
2.   DB;
3.  
4.     Button1: TButton;
5.     Image1: TImage;
6.     SQLQuery1: TSQLQuery;
7.  

I load images from the database like this:

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

1. procedure TForm1.Button1Click(Sender: TObject);
2. var
3.   StreamImg: TStream;
4. begin
5.   StreamImg := SQLQuery1.CreateBlobStream(SQLQuery1.FieldByName('FieldNameImg'),bmRead);
6.   Image1.Picture.LoadFromStream(StreamImg);
7.   StreamImg.Free;
8. end;
9.  

I hope you can adapt this code to your project and it can help you.

[paweld]

https://forum.lazarus.freepascal.org/index.php/topic,67168.msg516561.html#msg516561

[Aruna]

Most people would just use the TDBImage control. Otherwise, look at the LoadPicture method implemented in dbctrls.pp / dbimage.inc. That'll show you what you need to know.

@dsiders thank you I never thought of looking for a data-aware image control. TDBImage works. Screenshot attached. Much appreciated. I will go through  dbctrls.pp / dbimage.inc. Many thanks once again.

[Aruna]

I don't use hexadecimal formatting for images.

Having a form with:

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

1. uses
2.   DB;
3.  
4.     Button1: TButton;
5.     Image1: TImage;
6.     SQLQuery1: TSQLQuery;
7.  

I load images from the database like this:

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

1. procedure TForm1.Button1Click(Sender: TObject);
2. var
3.   StreamImg: TStream;
4. begin
5.   StreamImg := SQLQuery1.CreateBlobStream(SQLQuery1.FieldByName('FieldNameImg'),bmRead);
6.   Image1.Picture.LoadFromStream(StreamImg);
7.   StreamImg.Free;
8. end;
9.  

I hope you can adapt this code to your project and it can help you.

Hi @rca, the database I am trying to port into Lazarus was originally released by Microsoft as a sample database for Microsoft Access. So I had nothing to do with the hex blob format. I have no option but to find a way to work with it which I now can because @dsiders told me what to do Thank you for sharing your code it will come in useful. 

[Aruna]

https://forum.lazarus.freepascal.org/index.php/topic,67168.msg516561.html#msg516561

Many thanks @paweld.