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]]

"Guinness Mann" <GMann@dublin.com> wrote in message
news:MPG.1a00749e932cba47989712@news.newsguy.com.. .

> (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.

--
Tom Kaminski IIS MVP
[url]http://www.iistoolshed.com/[/url] - tools, scripts, and utilities for running IIS
[url]http://mvp.support.microsoft.com/[/url]
[url]http://www.microsoft.com/windowsserver2003/community/centers/iis/[/url]

[Mike Florio]

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.

"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:%23MyMFnMmDHA.2080@TK2MSFTNGP10.phx.gbl...

> > 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?
>
>

[Guinness Mann]

In article <#MyMFnMmDHA.2080@TK2MSFTNGP10.phx.gbl>,
[email]aaron@TRASHaspfaq.com[/email] says...

> > 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?

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]

In article <vpdom1oc3f6839@corp.supernews.com>, [email]mike@micro-point.com[/email]
says...

> 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

[Peter Foti]

"Guinness Mann" <GMann@dublin.com> wrote in message
news:MPG.1a0093fb99b3c12a989714@news.newsguy.com.. .

> 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.

Perhaps using the conventions defined by the standards (ISO 639 and possibly
ISO 3166). For example:

ISO 639 defines the abbreviation for English to be "en" (or "eng" if you
want the 3-character version). French is "fr" (or "fre" or "fra"). You
could use this naming convention for your folders:

\mp3\en\ <English mp3s go here>
\mp3\fr\ <French mp3s go here>

You could also apply this convention to the names of the mp3s.

en-someMp3File.mp3
fr-someMp3File.mp3

Whether you use this approach or not, choosing a naming convention and
sticking to it is important.

Hope this helps.
Regards,
Peter Foti