The second part of SQL is the Data Manipulation Language ( DML ). It includes
statements such as SELECT , INSERT , UPDATE , and DELETE . DML is used to manipulate
the data directly. Originally SQL was designed to be a language simple enough for
end users to use. It was designed so that there should be no need for a rich user
interface or even an application at all. Of course, this was back in the day of
green-screen terminals, a time when we had more hope for our end users!
These days, databases are much too complex to allow SQL to be run directly
against the database by end users. Can you imagine handing a bunch of SQL state-
ments to your accounting department as if to say, “Here you go, you'll find the
information you're looking for in the BSHEET table.” Indeed.
SQL alone is no longer an effective interface for end users, but it is an
extremely powerful tool for developers. SQL is the only complete means of access-
ing the database; everything else is a subset of the complete set of capabilities of
SQL . For this reason, i BATIS fully embraces SQL as the primary means of accessing
the relational database. At the same time, i BATIS provides many of the benefits of
the other approaches discussed in this chapter, including stored procedures and
object/relational mapping tools.
Old-school stored procedures
Stored procedures may be the oldest means of application programming with a
relational database. Many legacy applications used what is now known as a two-tier
design . A two-tier design involved a rich client interface that directly called stored
procedures in the database. The stored procedures would contain the SQL that was
to be run against the database. In addition to the SQL , the stored procedures could
(and often would) contain business logic. Unlike SQL , these stored procedure lan-
guages were procedural and had flow control such as conditionals and iteration.
Indeed, one could write an entire application using nothing but stored proce-
dures. Many software vendors developed rich client tools, such as Oracle Forms,
PowerBuilder, and Visual Basic, for developing two-tier database applications.
The problems with two-tier applications were primarily performance and scal-
ability. Although databases are extremely powerful machines, they aren't neces-
sarily the best choice for dealing with hundreds, thousands, or possibly millions of
users. With modern web applications, these scalability requirements are not
uncommon. Limitations, including concurrent licenses, hardware resources, and
even network sockets, would prevent such architecture from succeeding on a mas-
sive scale. Furthermore, deployment of two-tier applications was a nightmare. In
addition to the usual rich client deployment issues, complex runtime database
engines often had to be deployed to the client machine as well.