How to Use SQL on a Client/Server System - dummies

How to Use SQL on a Client/Server System

By Allen G. Taylor

SQL is a data sublanguage that works on a standalone system or on a multiuser system. SQL works particularly well on 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 channel — 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 commands 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, the server does nothing. If multiple clients require service at the same time, however, servers must respond quickly. Servers generally differ from client machines in terms of how much data they handle. They have large amounts of very fast disk storage, optimized for fast data access and retrieval. And because they handle traffic coming in simultaneously from multiple clients, servers need fast multi-core processors.

What the server is

The server is the part of a client/server system that holds the database. The server also holds the server software — the part of a database management system that 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 sublanguages.

A sublanguage doesn’t qualify as a complete language — it implements only part of a language. A data sublanguage may, for example, deal 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 input/output to printers. SQL is the most common data sublanguage and has become an industry standard.

In fact, SQL has supplanted proprietary data sublanguages on machines in all performance classes. With SQL:1999, SQL acquired many of the features missing from traditional sublanguages. However, SQL is still not a complete general-purpose programming language; it must be combined 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 distinguishing 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 (say, in accounts receivable or order entry). The client part of the DBMS executes the application program’s 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-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.