inserting into BLOB column type

[Allblack]

Maybe I got this totally wrong but I believe you can put MP3, video etc in a blob column.

How do I insert it? I believe mp3.com uses mysql to store there mp3 files.

Can anyone help me?

[genlee]

Same way you would text data:

INSERT into <table> (blob_col) values ('data here');

With something like perl you could just do this:

open(FILE, "/path/to/file");
$blob=<FILE>;
$query='insert into blob_table (blob) values (?);';
$sth=$db -> prepare($query);
$sth -> execute($blob);

[Allblack]

Thanks Genlee

Is there a way I can specify a path into the Insert command?
Something like Insert into mytable mp3column values(\path\to\mp3\)?

Let's say that I have all my files on the same box but in a different directory. What is the best way of doing this without using script

[genlee]

INSERT INTO blob_tabl (some_col) values(load_file('/path/to/file')); ;

[Dolda2000]

In that case, the easiest way to automate it is probably to run this command:

(for file in /path/to/files/*; do echo 'INSERT INTO blob_tabl (some_col) values (load_file('\'"$file"\''));'; done) | mysql -D some_db -u some_user -p

[Allblack]

Thanks guys, I will give that a go. Is there a special way to extract it as well? Lets say that I want to extract it to an other folder. Can I do this with a select statement?

Cheers

[andutt]

Use my program DBloader (engine) on my page (the link below) to load info of music or ftproot structures (or any other structure) in to mysql,mssql,postregsql..

you can easily modify it to load the mp3-file like a blob instead, if you need any help with that just let me know. Or if you have questions about the program.