Myth of the database independent applications (Was: Are you using PL/SQL)

On Wed, 23 May 2007 15:11:07 -0700, zigzagdna wrote:

> In practice, you cannot make your application as database independent by
> writing lots of stored procedures in PL/SQL.

There is no such thing as a database independent application. There is no Nessie, there is no elixir of youth and there is no Humpty Dumpty. Never was, never will be.
Databases have vastly different optimizers, locking strategies, connection management policies, vastly different ways of performing I/O and caching and vastly different implementations of transactional primitives. Some databases allow dirty reads, some do not. Some implement consistency through locking, some through multiversioning.

The only way to create a figment of imagination called "database independent application is not to use databases at all. The truly database independent applications are vi editor, flex, bison and Perl.

If you commit yourself to writing an application using databases, then you should know what underlying database will you use. The most important application design principle which is broken most of the time is that general business rules should be implemented in the database and not in the user interface. Also, rules which are local to an application module should be implemented in the UI. The best sign of breaking the rules are the expressions like ":VAR = 0" in the SQL. Conditions on variables should be checked in the UI.

Unfortunately, this is precisely the thing frequently generated by one of the most popular tools "for writing database independent applications", namely Hibernate. Hibernate helps you create applications which will suck regardless of the underlying platform.

-- 
http://www.mladen-gogala.com