Opening JPEG files from MySQL Blob?

General
1

So I’m experimenting with storing JPEG files in a MySQL database which is pretty easy to by just inserting the JPEG directly into the database like so:

INSERT INTO photos(Photo_ID, Photo_Thumbnail, Photo_MediumRes) VALUES('012345-21.01', LOAD_FILE('C:\photos\photo1.thumb.jpg'), LOAD_FILE('C:\photos\photo1.jpg'))

That is straight up SQL but I also want to retrieve the file and open it without saving the file and I’m unsure about the best way to go about doing that. I’m not finding anything searching the archives that deals with directly storing a file in MySQL. I’m finding other methods for storing and retrieving pictures but not as an existing file.

This will be for an internal web application where a user can upload a jpg file and it gets stored directly in the database quickly. However I need to retrieve it and display it later. Has anyone done anything like this that can advise me on how to approach this.

2

if you use BLOB fields (in MySQL better LONGBLOB), you can just get String from RecordSet/RowSet after selecting the right records.

Just read data from field, which is the JPEG field content. Then you can pass that to Picture.FromData() to get a picture and show it in a window.

1 Like
3

Hi Christian,

I am using BLOB for the thumbnail and chose MEDIUMBLOB for the image because I was planning to not allow any file to be over 4MB, but I can change that to LONGBLOB if you think that is the way to go.

I’m not sure it make sense to actually store a thumbnail, but that is what would be displayed on the web page. My thinking was the user may not choose to view the picture so why load the large one into memory except on demand

I’ve been continuing to review other posts and I’m still trying to decide if storing a file is the way to go or if one of the other methods like picture to string would be better or possibly binary picture encoding. This particular app is for customer service to store product complaints and I plan to allow up to 4 photos per record.

If you have a recommendation and can point me to an example I have your main plugins if you have a better way to approach this.

4

I ended up using the PictureToJPEGStringMBS to store the pictures and JPEGStringToPictureMBS when I retrieved them. It was really fast and simple after playing with the MBS example:
Picture Database.xojo_binary_project

5

You can do the same thing in Xojo code with Picture.ToData and Picture.FromData

4 Likes
6

here is a example to read the field as xojo picture
https://documentation.xojo.com/api/databases/databasecolumn.html#databasecolumn-picturevalue

for web output you would return the binary data 1:1 with correct mimetype.
storing thumbnail make sense.

just let binary binary data.
in xojo can a memoryblock also be a string for some reason … bytes = bytes
but it make no sense to transform data into a hex string/base64 or whatever. it waste time.