Preserve image names in MySQL Blob

Question

I'm uploading images to a database in Blobs but the file names are being changed to database_name-table_name.bin.png which is not so good.

Is there anyway to preserve the name that the application that uploads them uses?

The java code (prepared statement) I use to upload them is:

FileInputStream inputStream = null;

// Directory with the name i want to use
File image = new File(CreateArticleController.articleDetails.get("introImg")); 

inputStream = new FileInputStream(image);

pstmt.setBinaryStream(7, (InputStream) inputStream,(int) (image.length()));

Database:

CREATE TABLE IF NOT EXISTS `temp_article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `author` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `author_id` int(10) NOT NULL,
  `type` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `wfurl` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `intro` text COLLATE utf8_unicode_ci NOT NULL,
  `intro_image` mediumblob NOT NULL,
  `status` int(10) NOT NULL DEFAULT '0',
  `main_image` mediumblob NOT NULL,
  `content_1` text COLLATE utf8_unicode_ci,
  `image_1` mediumblob,
  `content_2` text COLLATE utf8_unicode_ci,
  `image_2` mediumblob,
  `content_3` text COLLATE utf8_unicode_ci,
  `image_3` mediumblob,
  `content_4` text COLLATE utf8_unicode_ci,
  `image_4` mediumblob,
      // Images and content areas go on for 17 of each
  PRIMARY KEY (`id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ;

and where the file names are being stored? I thought mysql blob fields would store only the bytes — Leo, Jun 26 '14 at 13:20
@Leo - When i download the images from the blob they are renamed to `database_name-table_name.bin.png` - so I was wondering if there is something I can do to change that default name to something I set - I'm just wondering if its at all possible, if not i'll have to work around it — Crizly, Jun 26 '14 at 13:38
how exactly do you download the images from the blobs? my feeling is that some app is generating this name. Is it some mysql frontend? — Leo, Jun 26 '14 at 13:44
I don't know phpmyadmin much, but maybe you'll need to define something like this https://phpmyadmin.readthedocs.org/en/latest/transformations.html - also, you'll probably need to store the original file name somewhere in your database — Leo, Jun 26 '14 at 14:02

score 0 · Accepted Answer · edited May 23 '17 at 12:20

There are a couple of factors going on here. First, you're not actually storing the original filename anywhere. In order to retrieve it later, you have to store it somewhere when you upload. phpMyAdmin is a tool for administrators to manage their databases, and not really designed to view and download your images. Sure, you're able to do both of those through it, but what I mean is that since you want to manipulate the downloaded file name, you'll need your own customized software for that; since phpMyAdmin is an administrative tool it isn't designed to be used like that. So that bring us to the third part, which is the ultimate answer to your problem: it is up to the download tool to provide a name. When you write your download portion of your code, you can have it make up a file name, pull the stored original file name from the database, or just use a generic default. It's all up to the code you implement. You're (hopefully) not planning to have the end users download the images through phpMyAdmin, so show that code as well.

So to recap since I get longwinded: modify your table structure to add a field to store the file name, then store it when creating, and retrieve it when downloading.

Also, (this is secondary but are good design principles) it looks like your database design is suffering from a lack of normalization (for instance see this, though there are many, many articles about it). Any time a table has columns with names like image_1, image_2, and so on it's a good indicator that you could perform some normalization on the structure. Additionally, you'll generally want to store your images in another table away from things like the title and author to maintain access speed on the non BLOB fields (Generally speaking, BLOB columns seem to slow down table access).

Preserve image names in MySQL Blob

1 Answers1