Playing MP3 from Database

[Guinness Mann]

(Crossposted to c.d.ms-sqlserver and m.p.asp.general. I think it's more
of an ASP question, but I'm hoping that maybe somebody on c.d.m has
encounterd the problem before.)


The Application

I'm working on a 3-tier client server application where MS SQL Server
2000 is the database tier, ASP (classic) on MS IIS is the business logic
tier, and Windows 2000 pro with IE6 is the presentation layer.

My application is a language learning environment and I have the need to
play sound files (encoded as MP3s) to the student. Traditionally we've
played some tricks with the html "bgsound" attribute to play the files.

As our application has grown, we've begun to run into problems keeping
the sound files organized. As they are in various languages, and we
don't keep native speakers of all those languages on staff, you can
imagine the problems if the sound file becomes "detached" from its
descriptive media. Especially when we have tens of thousands of these
things, so the names can't be all that descriptive.


The Problem

The obvious solution, to me, is to store the files as blobs in SQL
Server in a record that includes descriptive text, as well as where
(logically) the sound file belongs.

I'm familiar with storing and retrieving blobs, but the part I don't
know how to approach, is how to deliver the MP3 to the student via
classic ASP. In other words, I want to have a button on an HTML page
that when pressed by the student plays the associated MP3 file.

I'm presuming that as a first step I'll need to cache the MP3 file on
the server as some kind of tmp file. But then, what mechanism do I use
to get the client's browser to play the MP3 file?

Thank you,

-- Rick

 

[Aaron Bertrand - MVP]

I'm presuming that as a first step I'll need to cache the MP3 file on

the server as some kind of tmp file.

Ugh. Doesn't that make it sound like, it makes more sense to keep the MP3
on the file system? How hard could it be to manage/mimic the folder
structure (hence keeping the files organized) through the database?

 

[Tom Kaminski [MVP]]

(Crossposted to c.d.ms-sqlserver and m.p.asp.general. I think it's more
of an ASP question, but I'm hoping that maybe somebody on c.d.m has
encounterd the problem before.)


The Application

I'm working on a 3-tier client server application where MS SQL Server
2000 is the database tier, ASP (classic) on MS IIS is the business logic
tier, and Windows 2000 pro with IE6 is the presentation layer.

My application is a language learning environment and I have the need to
play sound files (encoded as MP3s) to the student. Traditionally we've
played some tricks with the html "bgsound" attribute to play the files.

As our application has grown, we've begun to run into problems keeping
the sound files organized. As they are in various languages, and we
don't keep native speakers of all those languages on staff, you can
imagine the problems if the sound file becomes "detached" from its
descriptive media. Especially when we have tens of thousands of these
things, so the names can't be all that descriptive.


The Problem

The obvious solution, to me, is to store the files as blobs in SQL
Server in a record that includes descriptive text, as well as where
(logically) the sound file belongs.

I'm familiar with storing and retrieving blobs, but the part I don't
know how to approach, is how to deliver the MP3 to the student via
classic ASP. In other words, I want to have a button on an HTML page
that when pressed by the student plays the associated MP3 file.

I'm presuming that as a first step I'll need to cache the MP3 file on
the server as some kind of tmp file. But then, what mechanism do I use
to get the client's browser to play the MP3 file?

As Aaron said, keep the files in the file system and just use the database
to manage a reference to them. Then use ASP/ADO and whatever logic you need
to lookup the correct file paths to Response.Write the bgsound HTML code
with paths to the appropriate files.

 

[Guinness Mann]

Ugh. Doesn't that make it sound like, it makes more sense
to keep the MP3 on the file system? How hard could it be
to manage/mimic the folder structure (hence keeping the
files organized) through the database?

And Tom Kaminski IIS MVP added...

As Aaron said, keep the files in the file system and just
use the database to manage a reference to them. Then use
ASP/ADO and whatever logic you need to lookup the correct
file paths to Response.Write the bgsound HTML code with
paths to the appropriate files.

I'm not one to ask for advice and then ignore it, so that's probably
what I'll do. It's a really big problem, though. I have maybe 4,000 of
these small files per language, and 9 languages.

So with 36,000 files to keep track of, it's hard to find a naming
convention that makes it quick and easy to figure out where something
came from if it gets misplaced. I should mention that the production of
the MP3 files is done at a different time and place than the rest of the
work, so they end up getting moved a couple of times before they reach
their final resting place.

I was just thinking that if I included the script and the module, lesson
and language in a database table, that once I got them all matched up it
would be difficult to disconnect them again. Or at least easy to search
for, if misplaced.

Here's a typical question:

M01Q1L22A

(A telephone rings)

A: Hello, Mr. Smith speaking.
B: Good morning, may I speak to Mrs. Johnson?
A: I am sorry. Mary is not at home. Her sister, Jane is here.

Who does the caller want to speak to?

A. Mrs. Johnson’s sister
B. Mr. Smith
C. Mrs. Mary Johnson
D. Miss Jane Baker

Answer: c

Thanks for your advice.

-- Rick

 

[Aaron Bertrand - MVP]

So with 36,000 files to keep track of, it's hard to find a naming

convention that makes it quick and easy to figure out where something
came from if it gets misplaced.

How does a file get misplaced? Take away manual human modifications, and
this should disappear as well. The files should be moved around by the
software, and this should be automated.

A

 

[Guinness Mann]

Also, remember that the mp3 spec allows information to be
written to the header. You could always read this (of
course, assuming the mp3's were encoded with it) and
incorporate it into some sort of housekeeping method to
refresh your database.

Hot damn! That's the brightest idea I've heard all month.


And Aaron said:

How does a file get misplaced? Take away manual human
modifications, and this should disappear as well.
The files should be moved around by the software, and
this should be automated.

Good point. If I make an application for the guy doing the sound
production to use, he can select the file for each question and the
program can move it to the right place. (And write the ID3 tags at the
same time -- just in case.) (And give it a normalized name...)

Thanks for all your help!


-- Rick