retrieve image from mysql blob field

Other
#1

I have a mySql database with images in a blob field.  What is the best way to download these? Is json the way to go?  I have successfully retrieved normal data this way with network listener and json, but I wasn’t sure if I could do it with binary data.  Are there any special considerations?

Thanks,

Greg

#2

What do you want to do with these images?

I think this is going to be a challenge for you. If you want to display these in your Corona app, you will need to be able to pass a file name to display.newImageRect() or if you use display.loadRemoteImage(), you need a file in the file system.

To make this work, on the Corona side you will need to make a network.request() call to a PHP script (or some other web scripting language) that will extract the blob from the database. Then you will need to base64 encode it because HTTP is not a binary safe transport protocol. When network.request() finishes, you will have to base64 decode the event.response value then write the binary data out to a file in system.TemporaryDirectory (or .CachesDirectory). Then you can use display.newImageRect() to load the image.

Personally I wouldn’t store binary data in a database if I could avoid it. I personally prefer to save the image files on the file system with other files. Then store the path to the file in the database. If you did that, you could get the path from the database and use network.download() or display.loadRemoteImage() without having to do all the work.

Rob

#3

Rob,

Thanks for the suggestions.  We took your advice and decided to only store the image filename within the database and then download the image (not stored in the DB) using http.request.

Thanks,

Greg 

#4

What do you want to do with these images?

I think this is going to be a challenge for you. If you want to display these in your Corona app, you will need to be able to pass a file name to display.newImageRect() or if you use display.loadRemoteImage(), you need a file in the file system.

To make this work, on the Corona side you will need to make a network.request() call to a PHP script (or some other web scripting language) that will extract the blob from the database. Then you will need to base64 encode it because HTTP is not a binary safe transport protocol. When network.request() finishes, you will have to base64 decode the event.response value then write the binary data out to a file in system.TemporaryDirectory (or .CachesDirectory). Then you can use display.newImageRect() to load the image.

Personally I wouldn’t store binary data in a database if I could avoid it. I personally prefer to save the image files on the file system with other files. Then store the path to the file in the database. If you did that, you could get the path from the database and use network.download() or display.loadRemoteImage() without having to do all the work.

Rob

#5

Rob,

Thanks for the suggestions.  We took your advice and decided to only store the image filename within the database and then download the image (not stored in the DB) using http.request.

Thanks,

Greg