I have been using MySQL for years now and when I started my new job I felt like I was fairly comfortable with databases. I always hear people say “if you know one language, you pretty much know them all” when talking about computer languages. This is pretty close to being true, if you stay within your class.
By class I mean, don’t go from Perl to Java, or Javascript to C and think you won’t need to buy a book! There are many types of languages all with specific purposes. I cover a bunch of these in Taming the Electron. Some are object oriented, portable, compiled, non compiled, used to display static things in web browsers, do things asynchronously, used for system administration, used in appliances, etc. The list goes on forever it seems. Those who say that phrase probably simply refer to logical constructs. And thank God. Logical constructs are the most standardized things in IT. In fact they probably are the only thing that is truly standard! If, else, elsif, elseif, unless, while, for, foreach, etc. They all do the same things no matter what language you use. And Boolean is the same as well; True is ALWAYS true – False is ALWAYS false!
Now, languages have different syntax. Syntax is where you would get tripped up while trying to translate your code into another language. Here’s a cool example. I code a LOT of my web applications using PHP, or AJAX Javascript with PHP. Now, PHP has functions for working nicely with MySQL. (I never used it with Oracle, or even know if you can, since it is far too much money). The PHP functions look like this:
function(arg1, arg2);
This is a pretty standard way to show and use functions in laguages (with small differences). Now, if I were to use, like I have to now everyday, Coldfusion and Oracle to make a simple SQL Query from a database. I would be, and was the first time I tried, lost. Like a fish in deep snow.
Coldfusion is a “tag” language. This language looks like HTML, but does more than markup. That’s right, it’s almost like PHP+HTML! You can set variables using tags, like so:
< cfset var="Hello World" >
Then, if you want to display anything, you need tags like so:
< cfoutput >#var#< /cfoutput >
yeah, you see that right, variables in Coldfusion are between hash symbols! You are forced to put the logical constructs I spoke of earlier into these tags like so:
< cfif var is "GoodBye World" >
< cfoutput >It says GoodBye?< /cfoutput >
< cfifelsif var is "Hello World" >
< cfoutput >It says Hello!< /cfoutput >
< /cfif >
Yeah, to me – that’s gross. That is like looking at ancient Hungarian script. I have no idea what to make of it. You can see now, that the differences in languages can be pretty big. Next time you hear someone say “if you know one language, you pretty much know them all” you can reply with a hearty “Nay!”
TO get Coldfusion apps to talk to a database, you need to set a “data-source” in a web based administration panel. Each data source is simply a different database. Let’s put the, completely obvious, security risks behind for a second and I’ll talk a little more about Databases.
I have taken this phrase and sometimes applied it to other technologies as well, and it’s pretty much the same. Now applying this to databases; MySQL is not like Oracle at all. MySQL is so awesome in comparison. I am not saying this because I come from the MySQL background, I can assure you and will right now…
The Client Differences
MySQL’s Monitor is a prompt that looks like a shell. You have probably seen it a bunch of times, something like this:
This is where you can do all of your DBA (Database Administration) stuff. It’s so simple. You create databases, tables, alter tables, update fields, insert values, grant permissions, etc. Everything can be done from right there. I was told that the university I work for only uses Oracle. I never used Oracle before and had to get help from my colleagues. It turns out that the client for Oracle is called “SQL Developer” and is a Java based application. I downloaded it and found out that I also needed the Java SDK! This is looking bleak now.
I installed everything and tried connecting to the database; no dice. I found out that Oracle uses SID’s that are simply the table name and “schema” that I was told – is a user. I asked for help once more and finally got it working. I was trying to adjust to the new database terms and such when I realized that the bloated SQL Developer wasn’t saving my history. I had to keep re-typing everything I was doing!! In MySQL you simply hit “up” to go through your history, that’s even saved from all of your sessions as a .mysql_history file in your home directory!! Awesome! At this point, if I were new to databases, I would usually step back and try to get a grip on all of the cool new stuff that I was doing. Because I came from a MySQL background, all I could see was inefficiency. This was getting frustrating, but I moved on.
Another thing that ticked me off about the SQL Developer tool was that you had to hit “commit” for the changes to take effect, in an almost transaction-like manner. This tripped me a few times when I was pulling output from the DB using CF and it wasn’t changing after I altered the tables in SQL Developer!!
It was brought to my attention by androsyn that SQL+ was a command line utility for Oracle. At this point I felt like the learning curve was showing my age. I downloaded the client which was about 25 MB’s and got it to work almost similar to the MySQL Monitor, but the output was very strange and things were listed in a sequence rather than a table. I found a cool way to change the output by typing this:
set lines 256
set trimout on
set space 1
set tab off
from a tutorial somewhere on the web. This made things feel a bit less alien like to me. (thanks Daniel for the reminder to add this!)
SQL Syntax
For the longest time, I thought that a few operators in MySQL were actually part of SQL! Like auto_increment for instance. This is only used in MySQL. Next, I tried to “auto_increment” a column in a table when we create it. After using SQL Developer and banging my head off of the desk for about 5 minutes trying to figure out why SQL Developer was saying that I was missing a parenthesis in the middle of the word “null” (don’t believe me? here’s a screen shot):
I found out that auto_increment was a bare word! That means that it is an operator native to the MySQL engine. The Oracle engine has no such thing. You actually need to type this:
create sequence test_seq
start with 1
increment by 1
nomaxvalue;
To create a “sequence” then type:
create trigger test_trigger
before insert on my_test
for each row
begin
select test_seq.nextval into :new.id from dual;
end;
To create a “trigger.” Now, this creates the urge in my body to make a face like this:
-_-
Because in MySQL, you simply type this to get the same result:
auto_increment
End
I put these behind me as I go along, i guess. Here’s another cool trip: Regular Expressions. Oracle has a REGEXP_LIKE() function that you can put regular expressions into, but everything you query in Oracle NEEDS to be in single quotes. This will trip you if you migrate, i assure you. Not only that, if you use Coldfusion + Oracle, well, white space will break your applications. That’s right, the output from the databases will contain white-space and break your potentially dynamically created AJAX elements if you have white-space in your < cfoutput > tags.
These are only a few examples of tons of which I am trying not to remember.
What’s going to come of MySQL now that Microsof..I mean, Oracle bought it? Who knows. If you know MySQL and you are looking at a new position in an institution that uses Oracle, I would seriously suggest buying a few books on the subject. Databases will continue to drive a lot of applications, even newer no-sql databases are becoming more popular as well.
This was just a recollection of my experience.
