SQL in a Nutshell, 2nd Edition

Database Programming Overview

Developing successful database applications, large and small, involves many steps. Careful thought must be given to application architecture, and especially to the following issues:

• How to map application data, which is typically object-oriented, to a relational database

• How to handle errors gracefully

• How to maximize performance and scalability

A typical database application will require many different SQL statements. The management of so many statements is simplified by the fact that all SQL statements will follow roughly the same pattern of execution within an application. Figure 5-1 is a state diagram showing how SQL statements are prepared, executed, and then processed by a database application when interacting with a relational database system. The state diagram has been broken down into eleven steps, four of which are optional (and are indented in the diagram).

Figure 5-1. Statement execution state diagram

Following are detailed descriptions of each step shown in Figure 5-1:

1. Establish connectivity : Establishing connectivity is the first step in every successful database application. It is in this step that the client, or database application, makes a physical connection to the database that will be used to transmit the SQL statements to the database and the results back to the client. The actual physical connection could be over a LAN, WAN, or even ...