Generating Reports From MySQL database

[captainhaddock]

Hi, has anyone created any reports from the squeezebox server MySQL database, I would like to get a csv file or similar containing album, artist, track, path on disk, and maybe even size / bitrate of the MP3 file. One use is to be able to identify duplicate tracks / albums etc and thus by seeing the bitrate or size, be able to identify which one to delete.
Any pointers would be most gratefully received. Thanks.

[gharris999]

I did this at one point, but it wasn't straight forward and I'm sure there are easier / better ways. If you have MS Office Pro (specifically, MS Access) on a windows machine, then all the other tools you need to get this done are free and available from the MySQL.org website.

On your server:

Make a backup of your current database. Just stop SBS and copy the whole Cache directory.

Open the SBS MySQL instance to outside snooping: find the my.tt file and comment out the "bind-address" line. Restart SBS.

Make sure your firewalls on both your server and client machines have port 9092 open.

On a windows client:

Install the free MySQL gui tools. Fire up MySQL Administrator. Connect to the SBS MySQL instance on port 9092 on the server and edit the 'slimserver' schema. In key tables (genres, contributors, albums, tracks) change any column datatypes that are BLOB to TEXT.

Install the free MySQL ODBC connector; On windows 7, go to Start->Administrative Tools->Data Sources (ODBC) and create a file dsn targeting your SBS server's MySQL data source on port 9092. The resulting file dsn is actually a text file, whose contents should look something like:

[ODBC]
DRIVER=MySQL ODBC 5.1 Driver
UID=slimserver
PORT=9092
DATABASE=slimserver
SERVER=sbs_server_ip

Fire up MSAccess, create a new empty database. Go to File->Get External Data->Link Tables and select in the "Files of type" combo the "ODBC Databases ()" entry at the bottom of the list. Then navigate and select the dsn file you created earlier. You should now be able to link in the various tables in the SBS's db into your MS Access db.

But be forewarned: if you haven't first changed those datatypes from BLOB to TEXT, it won't work. Period. At least in my experience.

Once you have the tables linked in, to start working with the data in Access, try creating a new query using Access' query designer. It's easy and intuitive to use. Try by making a easy query listing albums by contributor and genre. This will allow you to start getting a feel for the relational links between tables in the SBS' db.

Graduate to using Access' report writer. Again, if you get the relationships right, you'll be able to do exactly what you want.

These instructions are very, very cursory, of course. If you've never worked with setting up ODBC connectors, expect a fairly hefty trial & error phase before you get this working.

[captainhaddock]

Thats great, many thanks for taking the time to give such detailed instructions, I actually like the idea of using access, brings back memories of some 15 years ago, am very rusty these days but will give it a go. In those days it was Informix databases and access 2, things have moved on but I quite enjoy the challenge.

Thanks again.
C.H.