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 is not computationally complete, nor was it designed to be. By the current SQL-2 standards, SQL does not support recursion. (DB2 actually does! And the coming SQL-3 standards add 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. 


For example, 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. 


Likewise, 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. 

• ~db2educ/sqllib/samples/c/,
• ~db2educ/sqllib/samples/cpp/,
• ~db2educ/sqllib/samples/cobol_mf/,
• ~db2educ/sqllib/samples/fortran/, 

and
• ~db2educ/sqllib/samples/java/,

The 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 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. So first, 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). Once done, one has an executable program that interacts with the database as programmed whenever it is run.
CLI

The 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) CLI functions. 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.
Perl/DBI

Perl and other scripting languages offer database interactivity. For Perl, a module called DBI has been developed. When employed, one can essentially write "embedded SQL" in Perl. Actually, it follows the CLI paradigm, so it is somewhat nicer for the reasons discussed above. As with CLI, the DBI interface calls have been standardized to work with many different RDBMSs. 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.