midtoad's blog

Working with mySQL

It must be an axiom in computing that everything you try will take twice as long as expected and be three times as difficult as you thought, while being four times as frustrating as you can tolerate and requiring you to peruse five times as much information as you thought reasonable. This was my recent experience with mySql.

While developing my bikeroots site, I wanted to add a feature to allow visitors to download the GPS tracklog for any route they like. The name of the tracklog file is already in the database, in the 'route' table. If I fire up the mysql command-line interface, I can say 'select * from route' and sure enough, I can see tracklog names in the output listing. But I need to know the colunn name for the tracklog file so that my web application can ask for it. How do I determine that?

I did some searches on the mySql documentation site but couldn't find anything.

Next I got out my Teach Yourself SQL book and didn't find anything there. But there was mention of downloadable code for the examples in the book, so I downloaded the code and tried to read it into mySql. That wouldn't work, though the author said on the support forum that it should. I spent some time struggling with this before determining that the Windows version of mySql can't read files with lines more than 255 characters long. Grrr.

Back to the hunt, I did several more searches on Google, and and finally found the answer on Tech-Recipes. Simple: 'describe routes'. This command tells you the name of every column in the table, and what type of data is in it.

Having paid a high price to acquire that knowledge, I sure won't be likely to forget it! And now neither will you. :-)