SQL Series...6

Using SQL in a Client/Server System

SQL is a data sublanguage that works on a stand-alone system or on a multi-user system. SQL works particularly well in a client/server system. On such a system, users on multiple client machines that connect to a server machine can access — via a local area network (LAN) or other communications chan-nel — a database that resides on the server to which they’re connected. The application program on a client machine contains SQL data-manipulation commands. The portion of the DBMS residing on the client sends these com-mands to the server across the communications channel that connects the server to the client. At the server, the server portion of the DBMS interprets and executes the SQL command and then sends the results back to the client across the communication channel. You can encode very complex operations into SQL at the client and then decode and perform those operations at the server. This type of setup results in the most effective use of the bandwidth of that communication channel.

If you retrieve data by using SQL on a client/server system, only the data you want travels across the communication channel from the server to the client. In contrast, a simple resource-sharing system, with minimal intelligence at the server, must send huge blocks of data across the channel to give you the small piece of data that you want. This sort of massive transmission can slow operations considerably. The client/server architecture complements the characteristics of SQL to provide good performance at a moderate cost on small, medium, and large networks.

The server

Unless it receives a request from a client, the server does nothing. It just stands around and waits. If multiple clients require service at the same time, however, servers need to respond quickly. Servers generally differ from client

machines in that they have large amounts of very fast disk storage. Servers are optimized for fast data access and retrieval. And because they must handle traffic coming in simultaneously from multiple client machines, servers need a fast processor, or even multiple processors.

What the server is

The server (short for database server) is the part of a client/server system that holds the database. The server also holds the server portion of a data-base management system. This part of the DBMS interprets commands coming in from the clients and translates these commands into operations in the database. The server software also formats the results of retrieval requests and sends the results back to the requesting client.

What the server does

The server’s job is relatively simple and straightforward. All a server needs to do is read, interpret, and execute commands that come to it across the network from clients. Those commands are in one of several data sublan-guages. A sublanguage doesn’t qualify as a complete language — it imple-ments only part of a language. A data sublanguage deals only with data handling. The sublanguage has operations for inserting, updating, deleting, and selecting data but may not have flow control structures such as DO loops, local variables, functions, procedures, or I/O to printers. SQL is the most common data sublanguage in use today and has become an industry stan-dard. Proprietary data sublanguages have been supplanted by SQL on machines in all performance classes. With SQL:1999, SQL acquired many of the features missing from traditional sublanguages. However, SQL:2003 is still not a complete general-purpose programming language, so it must be com-bined with a host language to create a database application.

The client

The client part of a client/server system consists of a hardware component and a software component. The hardware component is the client computer and its interface to the local area network. This client hardware may be very similar or even identical to the server hardware. The software is the distin-guishing component of the client.

What the client is

The client’s primary job is to provide a user interface. As far as the user is concerned, the client machine is the computer, and the user interface is the application. The user may not even realize that the process involves a server. The server is usually out of sight — often in another room. Aside from the user interface, the client also contains the application program and the client

part of the DBMS. The application program performs the specific task you require, such as accounts receivable or order entry. The client part of the DBMS executes the application program commands and exchanges data and SQL data-manipulation commands with the server part of the DBMS.

What the client does

The client part of a DBMS displays information on the screen and responds to user input transmitted via the keyboard, mouse, or other input device. The client may also process data coming in from a telecommunications link or from other stations on the network. The client part of the DBMS does all the application-specific “thinking.” To a developer, the client part of a DBMS is the interesting part. The server part just handles the requests of the client part in a repetitive, mechanical fashion.

Using SQL on the Internet/Intranet

Database operation on the Internet and on intranets differs fundamentally from operation in a traditional client/server system. The difference is primar-ily on the client end. In a traditional client/server system, much of the func-tionality of the DBMS resides on the client machine. On an Internet-based database system, most or all of the DBMS resides on the server. The client may host nothing more than a Web browser. At most, the client holds a browser and a browser extension, such as a Netscape plug-in or an ActiveX control. Thus the conceptual “center of mass” of the system shifts toward the server. This shift has several advantages, as noted in the following list:

The client portion of the system (browser) is low cost. You have a standardized user interface.

The client is easy to maintain.

You have a standardized client/server relationship.

You have a common means of displaying multimedia data.

The main disadvantages of performing database manipulations over the

Internet involve security and data integrity, as the following list describes:

To protect information from unwanted access or tampering, both the Web server and the client browser must support strong encryption.

Browsers don’t perform adequate data-entry validation checks.

Database tables residing on different servers may become desynchronized.

Client and server extensions designed to address these concerns make the Internet a feasible location for production database applications. The archi-tecture of intranets is similar to that of the Internet, but security is less of a concern. Because the organization maintaining the intranet has physical con-trol over all the client machines as well as the servers and the network that connects these components together, an intranet suffers much less exposure to the efforts of malicious hackers. Data-entry errors and database desyn-chronization, however, do remain concerns.