While embedded SQL was the most popular choice a few years ago, it is not the best answer to the problem of querying databases in client-server environments. It is static in all senses of the term, and this limitation makes it unsuitable for newer software architectures.

SQL-92, the newer ANSI specification, addresses modern environment needs. It contains new features such as support for dynamic SQL and for an advanced technique to access result sets called scrollable cursors. While dynamic SQL is not as efficient as static SQL, it allows SQL statements to be prepared, to include parameters, and to be generated at run time. In the case of prepared statements, performance may be increased. In fact, dynamic SQL allows the database to prepare an access plan before the execution. This access plan is reused each time the statement is called.

SQL language is usable for a variety of purposes, including:

•  Querying a database by entering SQL text directly

•  Querying a database within a program

•  Defining data organization

•  Administering data

•  Accessing multiple data servers

•  Managing transactions

The SQL language supports a set of verbs used to define, store, manipulate, and retrieve data. The following are the basic SQL verbs used to build SQL clauses for such data manipulation:

To create a table:

CREATE TABLE table
(column type   [ NOT NULL | PRIMARY KEY |
UNIQUE | ... ]
[, column type    [ NOT NULL | PRIMARY KEY |
    UNIQUE | ... ]]*)

For example, to create a table of employees:

CREATE TABLE employees
(id      int PRIMARY KEY,
name      char(25) NOT NULL,
salary   int)

To drop a table:

DROP TABLE table

For example:

DROP TABLE employees

To supply new record values:

INSERT INTO table   [ (column [, column ]*)]
VALUES (expr [, expr ]*)

For example, to add Jones as employee number one, with a salary of $60,000/year:

INSERT INTO employees
VALUES (“1”, “JONES”, 60000)

To delete rows:

DELETE FROM table
WHERE column [ < | > | = | <= | >= | <> | LIKE ] expr
[ AND | OR ... ]*]

For example, to delete all employees earning more than $150,000 a year:

DELETE FROM employees
WHERE salary > 150000

To retrieve data:

SELECT [ DISTINCT ] [table.]column [, [table.]column ]*
FROM table [= name] [, table [=name] ]*
[ WHERE [table.]column [ < | > | = | <= | >= | <> |
LIKE ] expr
[ AND | OR ... ]*]
[ ORDER BY [table.]column [ ASC | DESC ]
[, [table.]column [ ASC | DESC ]]]
[ HAVING ... ]

For example, to retrieve all employees earning more than $50,000, sorted by salary (higher first) and name:

SELECT * FROM employees
WHERE salary > 50000
ORDER BY salary DESC, name

To modify data:

UPDATE table SET column = expr [, column = expr ]*
WHERE [table.]column [ < | > | = | <= | >= | <> |
LIKE ] expr
[ AND | OR ... ]*

For example, to raise Jones’ salary to $70,000 (Jones is employee number one):

UPDATE employees SET salary = 70000
WHERE id = 1

To create an index:

CREATE [ UNIQUE ] INDEX index
ON table (column [, column ]*)

For example, to create an index on the name field:

CREATE INDEX idx_employees
ON employees (name)

To create a stored procedure:

CREATE PROCEDURE procedure
[[(]@parameter type [= default ] [ IN | OUT | INOUT ]
[, @parameter type [= default ]
[ IN | OUT | INOUT ]]* [)]] [ WITH RECOMPILE ]
AS sqlstatement

For example, to create a stored procedure returning the highest salary via a parameter:

CREATE PROCEDURE maxsalary (@themax int OUT)
AS SELECT @themax = MAX(salary)
FROM employees

Book references are listed in the appendix for those who have not mastered SQL. DBMS reference books and online manuals may help while providing more details about specific implementations of SQL.

SAG-X/Open CLI

The X/Open and SQL Access Group defined the Call Level Interface (CLI). CLI is a library of function calls that support SQL statements. For example, Microsoft’s ODBC (Open Database Connectivity) is a Call Level Interface. JDBC is also a Call Level Interface. Most database vendors have optimized CLI implementations for their database management system products. ODBC and JDBC are less proprietary interfaces, though they intensively use these specific CLIs to access databases. The most important benefit for programmer’s using the ODBC CLI or the JDBC CLI is interoperability — all clients adhere to a standard programming interface. CLI requires neither host variables nor other embedded SQL concepts that would make it less flexible from a programmer’s perspective. It is still possible, however, to maintain and use specific functions of a database management system when accessing the database through a CLI.

An Industry Standard: ODBC

ODBC is Microsoft’s implementation of a CLI. It allows the programmer to develop, compile, and deploy an application without targeting a specific DBMS. Modules called drivers link the application to the database of their choice. For this reason and because it is independent of the network layer protocols, ODBC permits maximum interoperability. The availability of specific drivers for almost all relational database management systems has determined its success. The JDBC mechanisms are very close to the ODBC, but are adapted for Java. In designing an appropriate interface for direct use from Java, issues such as security, implementation, robustness, and portability were addressed. The following section provides more details about ODBC.

The ODBC Interface

The ODBC interface defines a library of function calls that allow an application to connect to a DBMS, execute SQL statements, and retrieve results. Its syntax is based on the X/Open and SQL Access Group SQL CAE specification (1992), which defines a standard set of error codes, a standard way to initiate a connection, and a standard representation for data types. In addition to the core functions based on the X/Open and SQL Access Group Call Level Specification, it provides extended functions for handling scrollable cursors and asynchronous processing.

ODBC Components

The ODBC interface defines the possible interactions between the user application and the driver manager. Figure 2-4 shows the relationship between the four ODBC components.

Figure 2-4:  The four ODBC components.