SQL in a Nutshell, 2nd Edition [Electronic resources] نسخه متنی

5.1 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).

Following are detailed descriptions of each step shown in Figure 5-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 a simple
logical connection back to the client in cases where the database
application and server are running on the same machine. For more
information on how to establish connectivity, please see the section
Section 5.2 later in this chapter.

Begin transaction
(optional):
A transaction may be begun so that the database changes may be rolled
back on failure or committed on success. For more information on
transaction control from database programming APIs, please see Section 5.4 later in this chapter.

Create statement
object: Most
modern database programming APIs are object-oriented and therefore
use an object to represent a SQL statement. There will typically be
one statement object per SQL statement executed within the
application. The statement object holds the state information
required to execute the SQL statement, such as the SQL statement
itself and input parameters when they exist.

Associate SQL with statement
object: After the statement object has been
created, it needs to have a SQL statement assigned to it. Once this
is done, the statement object may be executed.

Bind input parameters
(optional): While not part of the ANSI SQL
standard, the ability to bind parameters to
"placeholders" within a SQL
statement is supported by all the database platforms covered in this
book. If the SQL statement has placeholders for input parameters, the
statement object will need to have a program variable associated with
each input parameter. If the SQL statement contains no input
parameters, then this step may be skipped. Input parameters are
useful for optimizing performance when the same SQL statement is
re-executed many times, because the server-side parsing of the
statement only needs to be done once prior to the first execution.
Another reason to use input parameters is to embed binary data, such
as BLOB data, into SQL statements such as
INSERT and UPDATE
statements.

Execute statement
object: After
the statement object has been successfully created and initialized
with a SQL statement, the statement object can be executed. This step
executes the SQL statement on the database server.

Process Results (optional): After the database
server returns a result set, the application may process the results.
This step is optional, since it is typically not required for
statements that insert or update data in the database.

Re-execution: If the same
statement
needs to be re-executed to retry on a failure or to execute again
with different values for the input parameters, the application can
return to Step 6. If the application has no need to re-execute the
same statement, it moves on to Step 9.

Execute another SQL statement: If the
application needs to execute a different SQL statement and can reuse
the statement object, then the application can return to Step 4; if
not, it can move to Step 10.

End transaction
(optional):
Assuming a transaction was begun in step 2, the transaction is now
either committed or rolled back. If the transaction is rolled back,
then all changes to the database made by the statement object will be
removed from the database.

Free resources: After successfully
executing the statement and processing
any results, the client- and server-side resources need to be freed
for use by other applications.

The remaining sections in this chapter provide examples of how to use
the ADO.NET and JDBC database APIs to build applications that follow
the steps outlined in Figure 5-1.