asked on

MySQL: Convert BLOB to mp3 audio file

This should be easy, but after searching for answers, it looks confusing:

I have an on line MySQL database with many small audio files stored as BLOB fields. What I want to do is display a list of the files and when the user clicks a record in the grid, the file stored in the same record as the viewed record plays. I think I can handle the playing of the audio--I've got that working.

What I DON'T know how to do is convert the BLOB data field to an MP3 or WAV file to play. Right now, the stored file is MP3, but I could change it to WAV or anything else.

I thought that there was an SQL command that could extract the data from the BLOB and in effect place it in a file or variable of the original file type. That is, if a jpg file was stored, it would be converted back to jpg. Or in my case, the stored MP3 file will be converted back so that it can be played.

QUESTION: How do I extract the mp3 file data from the BLOB record? Any additional comments about how to play it would be welcome. Especially regarding how to define a variable to store the converted data. Also, once the file is played, the temp data can be deleted, so if the extracted mp3 data is stored as a physical file, I would like to know how to delete it. Hoever, it does seem that the extracted data would be a memory variable, since that is what will be played.

These are all small sound files (there are a few thousand of them), and the point is that the user can select the file to hear by clicking on the record in a data grid, or repeater, or whatever.

Thanks
JS

Ray Paseur

To a certain extent it will matter how you stored the files in the data base. Have you properly escaped them or base64-encoded the binary data? If not, be aware that some of them will probably not come out the same way they went it :-(

In future designs of this sort, you might want to use the server file system to hold the large "blob" things. and just keep a pointer to the URL in the data base. You will see MUCH better overall performance from that design pattern.

Here is how I would try to start development of your app. SELECT the row with the audio-blob you want and use file_put_contents() to write the audio-blob onto the server with the MP3 file extension, then produce a link to the blob file. Click the link and see if the audio plays. Once you have that part working we can go on to the other parts.

digitron

ASKER

Thank you--I will try this. My weakness is not knowing the commands (SQL and otherwise) that are available for this.

I'm actually trying to duplicate for on line use, an application that I developed with uniPaaS (4GL - type language), which does very much what you suggest. In my program, the BLOB from the selected row is extracted to an MP3 file and then my program points to the extracted file and plays it. This 4GL language has "File to BLOB" and " and "BLOB to file" functions which do pretty much what they sound like, and it works fine using uniPaaS.

The other part of your suggestion is practical--if what you mean is to dump all the MP3 files onto the server and just point to them with a link stored in the data file. I just want to make sure that users can find and download all the audio files using browser tools like Download Them All.

I will try the file_put_contents() suggestion. Here I must show my lack of knowledge. This function (file_put....): is it a Java function? PHP? VB?

Thanks,
JS

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

digitron

ASKER

This helps somewhat. Thanks.

digitron

ASKER

Hello. While I very much appreciate the help that was given in the solution, it did not resulve my issue. Please understand that I'm not sure that there is an answer of the type I'm looking for. However, if there is not, then an answer to my question that would inform me of that fact would be an answer deserving a grade of A, because I would then be able to close my research into the problem and take a different approach.

Also, the code provided--which was very generous--is much more than I require or can use. I tried to make it clear that my data is on line and working properly, and that I can connect to it. So all the code relating to setting up and accessing a database does not apply.

All I have been trying to find is if there is a way to extract a BLOB data field out to its original form (file format) or to extract data as a certain file type.

Thanks again,
JS