Fun and games with cross-platform database interoperability
Okay, so I'm no longer buzzword-compliant with the current fashion on Microsoft data access. That's because I haven't followed it for around 5 years.
- The problem:
-
We've got a Windows application (it's a honking great big CD burning "appliance" (I use the term loosely, it's really a full-blown Windows box with a burn of CD burners in it and a bit of robotic stuff going on)). We want to push files to it from a Solaris box, and have it produce CDs when it's received enough to fill a CD.
The software on the Windows box driving this whole process can apparently log to a database, using OLE DB. Good for it. We don't want to delete the files from the Solaris box sending them across until we know we've successfully got them on CD.
The current school of thought on how to deal with this was to use a MySQL database on the Solaris box, and query it to find out what files have been burned to CD, and then remove them from the Solaris box.
I got involved at the point where a co-worker had been bashing on some (what appeared to be) significantly dated piece of software to do OLE DB access to MySQL. I actually don't have the foggiest on what the difference between OLE DB and ODBC is. I'd previously had success getting Microsoft Access (as a front end) to talk to MySQL (as a back end) via ODBC, so I thought I'd have a fiddle.
I threw away the MySQL OLE DB stuff and got the latest greatest MyODBC driver for Windows (which appears to be significantly better maintained). I also grabbed the latest greatest Microsoft Data Access Components (MDAC) just to get the OLE DB provider for ODBC if I didn't already have it.
So the problem appears to be that the application, when it tries to create tables in the data source provided, tries to do it the "Microsoft SQL" way, and throws square brackets around the table name, and possibly the attribute names as well. Someone muttered something about part-time DBAs creating tables with spaces in the names.
I'm of the theory that this is happening in the application, and no amount of data access crap between the application and the database is going to clean this up. It's just not talking in an ODBC compliant manner. Damn it.
So the next approach is to try using MSDE as the database, and have it on the Windows box, and use FreeTDS on Solaris to query the database. Bags not having to build that.
I'd really like a sacrificial Linux box for prototyping. It would mean I could very quickly test the viability of things like this without having to inflict building the software on Solaris on someone (and waiting for it to happen).