0

I have a Query which involves JSON

say I have created a table like below

create table test_image (
  id int(10) not null AUTO_INCREMENT PRIMARY KEY,
  name varchar(25) not null default '',
  image blob not null
);

I insert the values for the tables and for image i give the location assuming my image is in image folder on the server at home named image.jpg

when i generate JSON ( I am using ExpressJS to generate JSON) I am getting correct JSON response to id and name data but for the Image i am getting something like [12 32 33 44] kinda thing.

why is this happening ?

  • I need the url for the Image location for JSON response
  • How can i correct my steps
hjpotter92
  • 78,589
  • 36
  • 144
  • 183

1 Answers1

0

It's because you store the image as a blob (aka binary large object) in mysql. Recall that JSON is mostly used to represent textual data only, you can't (it's difficult / inappropriate ) put binary data such as image in there

Hence one solution is to change the schema to store a filesystem path instead, or manually download the image into a disk and return the path on JSON

gerrytan
  • 40,313
  • 9
  • 84
  • 99
  • Thanks for your input :: using file system path is good solution .... but is it good idea when my database is going to extend to thousands of images in future... is it feasible .... Is using file system path .... people deal with JSON for image data... sorry for my bad technical question .... i am a newbie –  Sep 17 '13 at 04:42
  • 1
    In fact storing image in mysql is [often **not** recommended](http://stackoverflow.com/a/6472268/179630). When storing image path, it's also a good practice to separate the path prefix, so if you need to relocate the server with different path, you can just simply update the prefix – gerrytan Sep 17 '13 at 04:46
  • Ok .... but how to give a filesystem path ..... can i just put the file path in an URL and store it in table using VARCHAR ... –  Sep 17 '13 at 05:23
  • 1
    Yes, don't get confused between filesystem path and URL path. Basically you store the filesystem path in mysql, and you need to somehow map it and serve it as a web URL (eg: using apache). Eg: store your image on /var/www/html/images/picnic/pic01.jpg on filesystem, store only `/picnic/pic01.jpg` on mysql, make the prefix configurable, then map /var/www/html/images using apache to `http://mywebsite.com/images`, you can then return `http://mywebsite.com/images/picnig/pic01.jpg` on your JSON – gerrytan Sep 17 '13 at 05:43
  • Ok :) ... thanks again . I also did some googling on my question .... Base 64 encoding was what i was looking for which converts binary image data to text data .... but how to apply that concept in the query i have mentioned in the question any ideas ? .... can you edit my query to base64 encoding in your answer ... thanks –  Sep 17 '13 at 05:55