Our application at work can store its data in a local MS Access database, or on a MS SQL Server or Oracle 8 database.  When I first started working with the database portion of the application, I was under the impression that the SQL for accessing these databases would be standard.  Boy, was I wrong.

There’s apparently a standard for SQL, but you couldn’t tell it by the SQL I’ve seen.  The differences among these three platforms are huge.  Supposedly standard syntax fails on some platforms or works differently.  MS Access, for example, won’t let you use COUNT (DISTINCT field).  Oracle doesn’t support the SELECT INTO syntax, but instead requires CREATE TABLE name AS (SELECT…).  SQL Server has the concept of temporary tables.  Oracle and Access?  Nope.  SQL Server and MS Access support the “new” INNER JOIN syntax, but Oracle doesn’t.  The languages look similar on the surface, but when I  actually try to write portable code, it’s like trying to write portable C++ 10 years ago. 

This is nuts.  I’ve been fighting with Oracle recently because I’m porting code that was originally written for MS Access or SQL Server.  There are probably Oracle equivalents to most of the Access or SQL Server idioms that we’ve used, but I have a devil of a time finding them.  I suspect I’d be having the same problem if I was trying to port Oracle code to SQL Server or Access.  What a nightmare.

At this point, I don’t know where to go.  I can write the queries and program logic differently for each database platform, or I can write code that grabs raw data from the database and massages it either internally in memory or in a local MS Access database.  Neither of these ideas is very appealing.