Retrieving BLOB data from MySQL with Ruby and saving it as file

Question

I just want to know how to retrieve BLOB data saved in a database(MySQL) by using Ruby and save the retrieve data as a file in a specific directory.

Let's say I have 'abc.wav' saved as BLOB in DB, and I want my Ruby code to get it from the database and save it in a folder named "sound_files".

Anyone here who knows how to do it?

Edit: As of now, I am using the following code as shown below. The problem about it is that the outputted file is only "0 kb" in size which I guess indicates that it is not outputted properly.

wav_data = getWavFiles(db_ip, db_id, db_pass, db_schema)
wav_data.each do | wav |
    path = "path/to/file" + wav.filename
    File.open(path, 'w')
    File.write(path, wav.blobfile)
end

(Assuming that wav.filename is the filename, and wav.blobfile is the blob data which I both retrieved from the DB) Any suggestions about this? Thank you!

spickermann · Accepted Answer · 2015-05-25 07:37:15Z

1

I would start with something like this in a Rake task or in the Rails console:

MyModel.find_each do |wav|
  filename = Rails.root.join('path/to/file', wav.filename)
  File.open(filename, 'wb') { |file| file.write(wav.blobfile) }
end

Change MyModel to your models name.

edited May 25, 2015 at 7:37

answered May 22, 2015 at 10:42

spickermann

108k9 gold badges115 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Laurice Llona Over a year ago

Thanks and I've tried your suggested code. The only problem is that the outputted file is "0kb" in size which is very odd and means it is not properly outputted. Any suggestions about this? :)

spickermann Over a year ago

@Laurice : I updated my answer (note the 'wb'). Please try again.

Prabandham Srinidhi · Accepted Answer · 2015-05-25 10:43:08Z

1

Assuming your model is called Song you can do some thing like this.

Song.all.each do |song|
  blob = song.blob
  path = File.join("home","user","sound_files") #assuming you are on a nix* system
  File.write(path+song.name,blob)
end

Essentially we are getting a list of all songs and looping though each of them and getting the blob for each song and saving the desired location.

NOTE: if you have a lot of songs you might want to use find_each.

edited May 25, 2015 at 10:43

answered May 22, 2015 at 10:43

Prabandham Srinidhi

237 bronze badges

2 Comments

Sergio Tulentsev Over a year ago

You should not use string operators to build file paths. That's what File.join is for.

Sergio Tulentsev Over a year ago

yeah, you edited the wrong part. That one was fine :)

Rajdeep Singh · Accepted Answer · 2015-05-22 10:39:39Z

0

You can create an action, e.g. blob_to_file and do something like this, let's assume filename is name of your BLOB file and blob is the column name

def blob_to_file
  record = Model.where(id: params[:id]).first

  File.open(record.filename, 'w') do |f|
    f.write record.blob
  end
end

Hope this help!

answered May 22, 2015 at 10:39

Rajdeep Singh

17.9k6 gold badges58 silver badges80 bronze badges

Collectives™ on Stack Overflow

Retrieving BLOB data from MySQL with Ruby and saving it as file

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related