Same Language, Different Dialect
If you’re an indie movie fan, you may be familiar with the movie Trainspotting. The story centers upon a man in Edinburgh, Scotland, who is trying to kick his heroin habit. The lead character has three cohorts, one of whom speaks with a very thick Scottish accent. He speaks English, but one almost requires subtitles when watching scenes with him because it’s certainly not the English we’re used to hearing, though I’m sure Scots would have no trouble understanding him. The different dialects of a language spoken in different regions can be hard to understand, even though the same language is being spoken.
Programming Languages Have Dialects Too
I ran into the same thing at a database level. My experience is largely with Oracle database administration. Oracle uses SQL (Structured Query Language) to manipulate data in the database. When I arrived at DefinedLogic, I was asked to work on an SQL script for a MySQL database; I assumed that syntactically the SQL I used with Oracle would be fine for the MySQL script. Guess again — the developers complained the script wasn’t working. I looked through my script carefully; I couldn’t find anything wrong with it. I was banging my head against the wall trying to figure out why it wouldn’t work. Then I realized — I was looking at it with my Oracle glasses and I had never worked with MySQL before. I discovered that MySQL uses slightly different syntax.
I ran into this problem another time when I was recently coding a stored procedure in SQL Server. I needed to take a character string less than 7 characters and I needed to left-pad it with 0’s to fill it to 7 characters. Oracle has a built-in function to do this; not so with SQL Server. In the interest of time, I had to code a somewhat clunky but workable way to accomplish the same thing.
Although there is an ANSI-standard SQL, it has different “dialects” depending on the database software, whether it’s Oracle, SQL Server or MySQL. If you’re not familiar with the “dialect”, you’ll have a learning curve with something you think you know and with which you think you would have no trouble. Just as I was frustrated trying to understand the character in Trainspotting, I was even more frustrated trying to get code to work that I knew should work, but it wouldn’t because it was in a different software package (“region”). I mean, doesn’t this software speak SQL? It does, but it has its own dialect depending on the package, and its dialect isn’t mine!
What to do?
You would think software developers who do the same kind of processing and follow the same standards would have the same SQL implementation. Well, until the database software package developers all get into the same room and nail down the differences, you have to learn the particular dialect. To paraphrase, when in Scotland, speak as the Scots do.
- SQL at http://en.wikipedia.org/wiki/SQL (note the “Criticism” section)
- Trainspotting at http://www.imdb.com/title/tt0117951