to SQL Programming
In Chapters 4 and 5, we described several aspects of the SQL language,
which is the standard for relational databases. We described the SQL statements
for data definition, schema modification, queries, views, and updates. We also
described how various constraints on the database contents, such as key and
referential integrity constraints, are specified.
In this chapter and the next, we discuss some of the methods that have
been developed for accessing databases from programs. Most database access in
practical applications is accomplished through software programs that implement
database applications. This software is usually developed in a
general-purpose programming language
such as Java, C/C++/C#, COBOL, or some other programming language. In addition,
programming of database access within Web applications. In this chapter, we
focus on how databases can be accessed from the traditional programming
languages C/C++ and Java, whereas in the next chapter we introduce how
Recall from Section 2.3.1 that when database statements are included in a
program, the general-purpose programming language is called the host language, whereas the database
language— SQL, in our case—is called the data
sublanguage. In some cases, special database
programming languages are developed
specifically for writing database applications. Although many of these were developed as research prototypes, some
notable data-base programming languages have widespread use, such as Oracle’s
PL/SQL (Programming Language/SQL).
It is important to note that database programming is a very broad topic.
There are whole textbooks devoted to each database programming technique and
how that technique is realized in a specific system. New techniques are
developed all the time, and changes to
existing techniques are incorporated into newer system versions and languages.
An additional difficulty in presenting this topic is that although there are
SQL standards, these standards themselves are continually evolving, and each
DBMS vendor may have some variations from the standard. Because of this, we
have chosen to give an introduction to some of the main types of database
programming techniques and to compare these techniques, rather than study one
particular method or system in detail. The examples we give serve to
illustrate the main differences that a programmer would face when using each of
these database programming techniques. We will try to use the SQL standards in
our examples rather than describe a specific system. When using a specific
system, the materials in this chapter can serve as an introduction, but should
be augmented with the system manuals or with books describing the specific
We start our presentation of database programming in Section 13.1 with
an overview of the different techniques developed for accessing a database from
pro-grams. Then, in Section 13.2, we discuss the rules for embedding SQL
statements into a general-purpose programming language, generally known as embedded SQL. This section also briefly
discusses dynamic SQL, in which
queries can be dynamically constructed at runtime, and presents the basics of
the SQLJ variation of embedded SQL that was developed specifically for the
programming language Java. In Section 13.3, we discuss the technique known as SQL/CLI (Call Level Interface), in which
a library of procedures and functions is provided for accessing the data-base.
Various sets of library functions have been proposed. The SQL/CLI set of
functions is the one given in the SQL standard. Another library of functions is
ODBC (Open Data Base Connectivity).
We do not describe ODBC because it is con-sidered to be the predecessor to
SQL/CLI. A third library of functions—which we do describe—is JDBC; this was developed specifically
for accessing databases from Java. In Section 13.4 we discuss SQL/PSM (Persistent Stored Modules),
which is a part of the SQL standard that allows program modules—procedures and
functions—to be stored by the DBMS and accessed through SQL. We briefly
compare the three approaches to database programming in Section 13.5, and
provide a chapter summary in Section 13.6.