Dumping blob rows to files from MySQL

Question

I have a table with a few thousand rows in it that has a few integer columns and a blob column. I want to dump each row out as its own file with the blob being the content and the integers being used to form the file name. This is a one time op so quick and dirty is OK. One constraint is that I have almost no tools installed in this enviornment so that will be part of the dev cost no matter what I use.

Edit: I ended up using C# from another box. It only took downloading a single assembly and about the same amount of code as given in the answers below.

What kind of environment is that, especially the OS/distribution? — Svante, Jun 27 '09 at 10:37

MiffTheFox · Answer 1 · 2009-06-27T06:37:25.687

2

Something quick in PHP:

<?php
$connection = mysql_connect("mysqlserver.example.com", "username", "password");
mysql_select_db("dbname");
$sql = "SELECT `blob_column`, `id` FROM `mytable`";
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)){
    file_put_contents("file" + $row["id"] + ".dat", $row["blob_column"]);
}
mysql_close($connection);

You could probably do something simular with whatever method you have to access MySQL, but AFAIK, there's no way to do it with pure SQL.

edited Jun 27 '09 at 06:37

answered Jun 26 '09 at 21:52

MiffTheFox

21,302
14
69
94

nice and simple +1, how hard is it to install PHP and set up MySQL support for it? – BCS Jun 27 '09 at 05:51
@BCS: Most servers (especially UNIX) ones come with PHP already installed. If you have SSH/telnet access, you can type php -v at the command prompt to find out if it's available. PHP should have been compiled with MySQL support as well. If you don't have it though, you can install a package like Apache Friends XAMPP, but that may be too bloated for your usage (it comes with Apache, MySQL, PHPMyAdmin, and a ton of other stuff too). – MiffTheFox Jun 27 '09 at 06:34
Oh, and one more thing, you'll need to connect to your DB as well, I'm going to add that code in here too. – MiffTheFox Jun 27 '09 at 06:35
"-bash: php: command not found" I own the box and haven't installed much that I haven't needed already – BCS Jun 27 '09 at 13:54

score 1 · Answer 2 · answered Jun 26 '09 at 23:00

In Common Lisp, using CLSQL, something like the following should work (untested, don't have MySQL installed at the moment):

(require 'clsql)
(require 'clsql-mysql)

(clsql:connect (host db user password port) :database-type :mysql)

(clsql:do-query ((col1 col2 blob) "select col1,col2,blob from blobtable")
  (with-open-file (outfile (format nil "~a-~a" col1 col2)
                           :direction :output
                           :element-type 'byte)
    (write-sequence blob outfile)))

You would have to fill in host, db etc. (port is optional), and adjust the query, of course.

Dumping blob rows to files from MySQL

2 Answers2

Linked