document offers some pointers on how to write application programs
and for relational database management systems (RDBMSs) generally.
An application program
(or often called an "APP", for short)
for a database
is any computer program that interacts with the database.
Given that we have SQL for interacting with the database system,
why would we need to use a programming language?
There are many reasons we might.
Basically, SQL was never meant to be a programming language,
so there are many things that it cannot do.
For anything we need that it cannot do,
we must use a programming language.
Some such cases are as follows.
- One may need to evaluate a "query" (often)
which is beyond SQL's ability.
SQL proper is not computationally complete,
nor was it designed to be.
It is meant to be a query language,
not a programming language.
By the SQL-2 standards,
SQL does not support recursion.
(DB2 actually does!
The current SQL-3 standards have added recursion.)
SQL has no conditional statements, per se;
for instance, an if ... then ... else.
So it is virtually impossible to write a "program" in "SQL".
one may want to present certain information about alternative
products to the user if the product requested is out of stock
(as determined by a query the APP asked behind the scenes),
but information about the product itself
in the case that it is in stock.
Recall that an SQL query always returns a relational table.
Sometimes, one wants to present information in some other format.
This requires an APP.
One may want to offer a GUI (graphical user interface)
form to users to interact with the database in a more reasonable way
(than raw SQL),
or even just a nicer shell program interface,
for some specific database task.
SQL is not a programming language, so it offers no hooks
to the window system or OS as do many programming languages.
an APP, since it is a program,
can be designed to interact with other programs.
(GUI is just a special case of this, really.)
Again, SQL is not equipped for this.
One may need to enforce a "business rule" over the database
which cannot be easily enforced some other way,
such as via integrity constraints.
- For this, the APP will usually check for violations
of the business rule.
- Then, if there are violations,
the APP repairs the database
in a sensible way.
Facilities for writing APPs
The basic APP approach is embedded SQL.
We recommend this for the project.
at the least
for embedded SQL programs and for CLI programs.
on db.cs.wm.edu, respectively,
for example programs and how to do the pre-compilation,
bind, and compile steps.
Even scripting languages of various sorts such as
Perl are generally supported.
Perl's connection package is called DBI.
We support locally embedded SQL in
and "CLI" in
for your APP project.
You may write a CLI APP in C, C++,
or Java (JDBC), instead.
There are examples in the DB2 directories.
However, there are no local web-pages up to walk you through it.
So only go the CLI route (instead of the embedded SQL route)
if you have some experience with it,
and/or you are willing to put in the work yorself
to look up the library calls and to look over the examples.
Some good resources for learning JDBC and SQLJ are:
(C & C++,
standard approach for building APPs that the database environments offer
is called Embedded SQL.
This allows one to write SQL statements in one's programming code.
Different vendors support different programming languages
for embedded SQL.
DB2 here supports C and Java, at least.
The program one writes then is a hybrid of regular (say, C) code and
"embedded" SQL statements.
DB2 calls such code for C a "SQC" program.
Clearly the language compiler (say, cc)
will not compile an embedded SQL program,
since it will not recognize the "embedded SQL" statements.
one must pre-compile the program.
This replaces the embedded SQL statements with legitimate
program code (albeit quite ugly code)
that consists of calls to special libraries for the database system.
Thus, the result of the pre-compilation is a pure program
(say, a C program).
Every database system vendor which supports embedded SQL
offers such a pre-compiler.
One next compiles the resulting pure program
as usual with the language compiler.
The last step is then to use the compiler (say, cc)
to link the compilation result with the appropriate libraries
(for instance, the database system specific libraries mentioned above).
one has an executable program that interacts with the database as programmed
whenever it is run.
CLI (Call Level Interface) approach is newer.
In this approach,
one writes pure programs.
One makes calls to the database via pre-defined (and documented)
No pre-compilation stage is needed.
One just compiles the program and links the appropriate CLI libraries.
This is more elegant than the embedded SQL approach
because no pre-compilation is needed,
and because the code is clearer.
It is not a hybrid of SQL and program.
CLI is also meant to offer another advantage:
The CLI calls are defined for most all the RDBMSs out there.
So a given CLI program which was written and used with a DB2 database
could be ported to work with an ORACLE database
without being rewritten!
It would only need recompiling,
linked with the appropriate libraries.
The current disadvantage of CLI is that
it supports a subset of the functionality that any given RDBMS supports.
This is because it only supports the intersection of functionality
of all the RDBMSs.
So sometimes, one simply cannot do what one wants in CLI.
Also, embedded SQL is still more common.
The ODBC and JDBC standards are based on CLI.
and some other "scripting" languages (like Python)
offer database interactivity.
a module called DBI has been developed.
one can essentially write database connectivity and processing code
It follows the CLI paradigm.
As with CLI,
the DBI interface calls have been standardized to work with many
So the same Perl program could be, in principle,
used with a DB2 database and with an ORACLE database
with no rewriting necessary!
Also, Perl/DBI does not need the pre-compile stage,
as an embedded SQL program does.
Of course, this is somewhat necessary since Perl
is an "interpreted" language.