SQL programs are the cockroaches of the IT world.

by Paul Browne

Back in the dark days when World War III threatened us with imminent nuclear oblivion, we were told that cockroaches and other insects were the highest form of life that would survive. I've recently come to the conclusion that SQL programs, like cockroaches, will surive anything that the IT world can throw at them.


Oracle Ireland Logo


SQL is a way of getting useful information from databases ,like 'get me all Bank Customers with an account balances greater than 10,000 Euro'. It's been around since the stone age and it is strongly suspected that the ancient Egyptians were familar with it's earlier forms. Given that at least part of most systems talk to a database, it is probably the most widely used programming language in the world. It's the most widely used because it's the most useful : do one thing and do one thing well.


However, you get into trouble when you use SQL in ways nature never intended. Once you step beyond the 'get me this' or 'update that' you're in trouble. Like a 12m high cockroach from a 1950's Horror Movie, your code is out of control. Even Oracle are migrating to a Java stack to implement business logic outside of the core database engine.



More in Technology in Plain English

18 Comments

wertt
2006-11-06 15:10:44
Relational theory on which sql was based is actually very modern compared to the other database models so called modern database like xml databases and object databases are just hierarchical and network databases. Sql is very powerful if you understand it is is about logic
topher
2006-11-06 17:05:21
Oracle is where it is because of SQL. They may make it easier to do logic in Java, but they'll never drop it for one big reason. Relational theory, and therefore SQL, scales really well for huge datasets. Oracle's scalability is one of their big trump cards over the competition (along with platform support).


Anyway, relational theory is based on algebraic set theory, so you're not far off when you say it's an ancient practice.


I've been coding Java since 1995 and think it solves a lot of problems, but I think SQL is extraordinarily powerful and will be around solving problems long after Ruby takes over Java's mindshare.

wertt
2006-11-07 02:12:10
"Anyway, relational theory is based on algebraic set theory, so you're not far off when you say it's an ancient practice."


Set theory is also very modern but more on the math scale of modern not on the computer science scale of modern ;-)

Daniel Serodio
2006-11-07 05:17:19
SQL != Relational Model


The relational theory doesn't account for NULL values.

Reedo
2006-11-07 05:21:31
Not only does writing programs using SQL (or stored procedures) often spiral into unmaintainability, but it'll be a pain to rewrite it all when you discover that you don't, in fact, need Oracle and you want to switch to a cheaper competitor.
John Flack
2006-11-07 09:41:57
So what are you saying? Give me an example of "use SQL in ways nature never intended". I've seen lots of examples of developers using application programming languages to loop through sets of data to duplicate the effects of a WHERE, a JOIN or an ORDER BY. They loop and UPDATE every row, instead of a single UPDATE to a set of rows. They think in terms of row by row, not sets - and relational databases were DESIGNED to work with sets.
bartekkl
2006-11-07 12:15:46
I guess I know what Paul meant by unnatural usage of SQL: just imagine a complex financial application whose entire business logic is coded with T-SQL stored procedures, triggers etc. I once used to maintain such a monster. I saw procedures that did not touch a single table or view - instead, they did statistical computation! I admired the developer that had been patient enough to translate those formulas and algorithms into primitive T-SQL constructs...


Anyway, SQL is the proper tool as long as you work with sets. I would say that when you begin to use loops and cursors, it is a good moment to stop for a while and think about something else.

Trevor
2006-11-07 21:53:39
with it's earlier forms --> with its earlier forms
Behi
2006-11-08 15:49:25
Looks like XML DBs together with XQuery and the fellows are trying to outplace RDBMs DBs (and SQL) progressively. It's rumored that ancient Cretans were the first people who ever invented XML but they suddenly built everything on top of XML and abused it so much that their civilization just blown up and faded away... There are also a few around murmuring that Egyptians have decided to support the brainchild of Cretans natively inside their giant RDBMSes... Of those I have IBM and Oracle right now on my mind...
Paul Browne
2006-11-09 14:08:12
Two comments to explain myself a little bit more:
- SQL may be like cockroaches, but both will be around long after Java (and Humans) are gone (and this is speaking as a Java guy).
- Select data in SQL in data is great. Implementing business logic in SQL is a headache for the guy who has to maintain it.
Basil
2006-11-10 13:26:15
Mr Browne,


Can you provide an example of what you mean when you say, "Implementing business logic in SQL is a headache for the guy who has to maintain it" ?


In the case of Oracle, SQL is SQL. PL/SQL is a programming language optimized for databases. Java is a programming that can use databases, but is hardly optimized for it. In any case, I'm not getting your point.

Huck
2006-11-11 10:46:43
talking about a babbling drunk Irishman.....
njgreen
2006-11-11 19:46:16
I tried to click on the "More in Technology in Plain English" and guess what - I got a database error! OK, granted the message seemed to say it was a connection error, but still - what a great illustration of this article's point!

2006-11-12 16:05:02
Oracle's Blunder: C# Stored Procedures.
Going from Bad to Good:
- Business Logic in Asp.Net
- Business Logic in C# Stored Procedures, that run ONLY in Oracle ONLY on a Windows OS.
- Business Logic in PL/Sql - Big Advantage, Gets you off the Microsoft Rewrite schedule, which restricts the ultimate size and cost of your business software.
- Business Login in Oracle Java Stored Procedures: Easy to write and maintain, has the Lifespan of Java applications, and can run on Linux, Windows, Mac or Solaris.
anonymous coward
2006-11-16 23:12:10
Ungodly code is exactly what springframework and other Dependency Injection was made to combat. Just because JDBC is a royal PITA to use out of the box doesn't mean you should take it out on an industry standard.
aaron
2007-07-10 03:11:49
Please help.. i'm a student takin up information management. i'm having a hard time with this problem.. can any1 help me?.. PLEASE..
CASE IN POINT
Company A wants to use access database to handle returns for all equipment sold in the Country. The system handles returns, repairs, replacements,


warranties, testing, logistics shipping & handling, stock control and the procurement of new equipment. This was originally developed in-house but was now


starting to outgrow the original developer's capabilities to support it and, as the system had also by now become mission critical, they decided to outsource


it's support and future development.



ACTIVITY


A1. Create a quick database design solution to handle the needs of Company A. Create Tables. (30pts)


A2. Create Queries that represents output/result reports that shows what the system is handling (returns, repairs, warranties, etc...). Create Select


Queries. Prefix all queries with A2 (40pts).

mohamad
2007-10-16 00:53:01
I interested about SQL databese , I want to know how to produce and
make own database using internet. Here T trying for student registration. Pls help me.
jk
2007-10-20 18:11:06
John Flack makes a good point. SQL is a higher level language than procedural languages. I was one of those programmers using loops to update rows. Oh, how wrong I was. I've since replaced my dozen-plus lines of procedural code with two or three SQL statements. You can do this not only with simple UPDATES, but you can update JOINs to update specific subsets of your data. Add a few lines of comments, and the program is not only clearer -- the business logic is easier to modify because it maps more closely to the code.