Dec 222009

I have done many things in my misguided past as a programmer, but strangely, I never did much work with XML. Which is why a recent annoyance turned into an interesting learning opportunity.

I usually watch TV on my computer. (This is why I see more TV than many people I know… not because I am a TV junkie who really “watches” it, I am actually working, but I have, e.g., CNN running in the background, in a small window, and I do occasionally pay attention when I see something unusual. Or change to a channel with The Simpsons.) For years, I’ve been using various ATI All-In-Wonder cards. (No, I don’t recommend them anymore; whereas in the past, they used to attach a tuner to some of their really high-end cards, this is no longer the case, the base graphics hardware of their current crop of AIW cards is quite lame. Their current software sucks, too.) The old ATI multimedia program I am using, while far from perfect, is fairly robust and reliable, and among other things, it comes with a built-in program guide feature. A feature that downloads programming information from an online server.

Except that, as of last week, it was no longer able to do so; the server refused the request. Several customers complained, but to no avail; they were not even able to get through to the right people.

So what is a poor programmer to do? I have known about Schedules Direct, the fee-based but non-profit, low-cost replacement of what used to be a free service from Zap2It, providing the ability to download TV guide data for personal use. The information from Schedules Direct comes in the form of XML. The ATI multimedia program stores its data in a Paradox database. In theory, the rest is just a straightforward exercise of downloading the data and loading it into the Paradox tables, and presto: one should have updated programming information.

Indeed things would be this simple if there were no several hurdles along the way.

First, the Paradox database is password-protected. Now Paradox passwords are a joke, especially since well-known backdoor passwords exist. Yet it turns out that those backdoor passwords work only with the original Borland/Corel/whatever drivers… third party drivers, e.g., the Paradox drivers in Microsoft Access 2007, do not recognize the backdoor passwords. Fortunately, cracking the password is not hard; I used Thegrideon Software’s Paradox Password program for this purpose, and (after payment of the registration fee, of course) it did the trick.

Second, the Microsoft drivers are finicky, and may not allow write access to the Paradox tables. This was most annoying, since I didn’t know the cause. Eventually, I loaded the tables on another machine that never saw the original Borland Database Engine, but did have Access 2007 installed (hence my need for a “real” password, not a backdoor one), and with this machine, I was able to write into the files… not sure if it was due to the absence of the BDE, the fact that I was using Office 2007 as opposed to Office 2003, or some other reason.

So far so good… Access can now write into the Paradox tables, and Access can read XML, after all, Microsoft is all about XML these days, right? No so fast… That’s when I ran into my third problem, namely the fact that Access cannot read XML attributes, whereas a lot of the programming information (including such minor details like the channel number or start time) are provided in attribute form by Schedules Direct (or to be more precise, by the XMLTV utility that I use to access Schedules Direct.) The solution: use XSLT to transform the source XML into a form that Access can digest properly.

With this and a few lines of SQL, I reached the finish line, more-or-less: I was able to update the Paradox tables, and the result appears digestible to the ATI media center application… though not to the accompanying Gemstar program grid application, which still crashes, but that’s okay, I never really used it anyway.

And I managed to accomplish all this just in time to find out that suddenly, the ATI/Gemstar update server is working again… once again, I can get programming information from them. More-or-less… a number of channels have been missing from the lineup for a long time now, so I may prefer to use my solution from now on anyway. Perhaps when I have a little time, I’ll find out what causes the crash (I have some ideas) and the program grid application will work, too.

Needless to say, I know a lot more about XML and XSLT than I did 24 hours ago.

 Posted by at 7:41 pm