| Computing technology has designed a perpetual | | | | recommends a core group of data manipulation |
| modification in the style of professional exertion | | | | functions. As we will find out, many database |
| circuitously of the world. Information that was at | | | | systems also have tools for ensuring data |
| one time emporium in storehouse safeguarding | | | | integrity and enforcing security (see Day 11, |
| management can now be entranced without delay | | | | "Controlling Transactions") that enable |
| at the small noise of mouse identification. | | | | programmers to stop the execution of a group |
| Instruction placed by customers in foreign | | | | of commands if a certain condition occurs. |
| countries can now be forthwith prepared on the | | | | Popular STRUCTURED QUERY LANGUAGE |
| bottom surface of any room of a fabrication | | | | Implementations |
| facility. Although 15 years ago much of this | | | | This section introduces some of the more popular |
| information had been enchanted onto corporate | | | | implementations of STRUCTURED QUERY |
| mainframe databases, offices still worked in a | | | | LANGUAGE, each of which has its own strengths |
| batch-processing environment. If a query is | | | | and weaknesses. Where some implementations of |
| needed to be accomplished, someone may | | | | STRUCTURED QUERY LANGUAGE have been |
| apprise the management information systems | | | | developed for PC use and easy user interactivity, |
| department; the requested data are delivered as | | | | others have been developed to accommodate |
| soon as possible nevertheless often not speedy | | | | very large databases (VLDB). This section |
| and sufficient in normal manner. | | | | introduces selected key features of some |
| In summing up to the outgrowth of the relational | | | | implementations. |
| database archetype, two technologies led to the | | | | Microsoft Access |
| rapid growth of what are now called client/server | | | | We use Microsoft Access, a PC-based DBMS, to |
| database assemblage. The important momentous | | | | illustrate some of the examples in this text. |
| technology was the personal computer. | | | | Access is very easy to use. We can use GUI |
| Reasonable, easy-to-use applications such as | | | | tools or manually enter our STRUCTURED QUERY |
| Spreadsheet and Word Perfect enabled | | | | LANGUAGE statements. |
| employees to create documents and accomplish | | | | Personal Oracle7 |
| data dashing and decently. The people, who are | | | | We use Personal Oracle7, which represents the |
| chain used to consecutively modernising systems, | | | | larger corporate database world, to demonstrate |
| as the rate of modification was so expeditious, | | | | command-line STRUCTURED QUERY LANGUAGE |
| even as the price of the more highly evolved | | | | and database management techniques. (These |
| systems protracted to decline. The next | | | | techniques are important because the days of the |
| substantial technology was the local area network | | | | standalone machine are drawing to an end, as are |
| (LAN) and its solidarity into offices across the | | | | the days when knowing one database or one |
| world. Although users our established to end | | | | operating system was enough. This tool then |
| connections to a corporate mainframe, now word | | | | returns data to the screen for the user to see, |
| processing files could be stored locally within an | | | | or it performs the appropriate action on the |
| office and accessed from any computer attached | | | | database. |
| to the network. After the Apple Macintosh | | | | Most examples are directed toward the beginning |
| introduced a friendly graphical user interface, | | | | programmer or first-time user of STRUCTURED |
| computers our not only economical and powerful | | | | QUERY LANGUAGE. We begin with the simplest |
| but also easy to use. In addition, they could be | | | | of STRUCTURED QUERY LANGUAGE statements |
| accessed from remote sites, and large amounts | | | | and advance to the topics of transaction |
| of data could be off-loaded to departmental data | | | | management and stored procedure programming. |
| servers. | | | | The Oracle RDBMS is distributed with a full |
| In the course of rapid change and betterment, a | | | | complement of development tools. It includes a |
| new sort of system has appeared known as | | | | C++ and Visual Basic language library (Oracle |
| client/server development because processing is | | | | Objects for OLE) that can link an application to a |
| split between client computers and a database | | | | Personal Oracle database. It also comes with |
| server; this new breed of application was a radical | | | | graphical tools for database, user, and object |
| change from mainframe-based application | | | | administration, as well as the STRUCTURED |
| programming. Among the many advantages of | | | | QUERY LANGUAGE Loader utility, which is used |
| this type of the system are: | | | | to import and export data to and from Oracle. |
| •Simplified supporting costs | | | | We select the Personal Oracle7 RDBMS for |
| •Uncomplicated network load (processing | | | | several reasons that are vitally important for |
| occurs on database server or client computer) | | | | creating object oriented database customised |
| •Manifold functioning systems that can | | | | software |
| inordinate as long as they share a common | | | | •It includes nearly all the tools needed to |
| network etiquette | | | | demonstrate the topics discussed in this book. |
| •Improved data integrity owing to | | | | •It is available on virtually every platform |
| centralized data location | | | | in use today and is one of the most popular |
| In developing Client/Server Computing, Bernard H. | | | | RDBMS products worldwide. |
| Boar defines client/server computing as follows: | | | | •A 90-day trial copy can be downloaded |
| "Client/server computing is a processing model in | | | | from Oracle Corporation's World Wide Web |
| which a single application is partitioned between | | | | server. |
| multiple processors (front-end and back-end) and | | | | Microsoft Query is a useful query tool that comes |
| the processors cooperate (transparent to the end | | | | packaged with Microsoft's Windows development |
| user) to complete the processing as a single | | | | tools, Visual C++, and Visual Basic. It uses the |
| unified task. Implementing Client/Server | | | | ODBC standard to communicate with underlying |
| Computing a client/server bond product ties the | | | | databases. Microsoft Query passes STRUCTURED |
| processors together to provide a single system | | | | QUERY LANGUAGE statements to a driver, which |
| image (illusion). Shareable resources are positioned | | | | processes the statements before passing them |
| as requestor clients that access authorized | | | | to a database system. ODBC is a functional library |
| services. The architecture is endlessly recursive; in | | | | designed to provide a common Application |
| turn, servers can become clients and request | | | | Programming Interface (API) to underlying |
| services of other servers on the network, and so | | | | database systems. It communicates with the |
| on and so on. "This type of application | | | | database through a library driver, just as |
| development requires an entirely new set of | | | | Windows communicates with a printer via a |
| programming skills. User interface programming is | | | | printer driver. Depending on the database being |
| now written for graphical user interfaces, whether | | | | used, a networking driver may be required to |
| it is MS Windows, IBM OS/2, Apple Macintosh, or | | | | connect to a remote database. |
| the UNIX X-Window system. Using STRUCTURED | | | | The unique feature of ODBC as compared to the |
| QUERY LANGUAGE and a network connection, | | | | Oracle or Sybase libraries is that none of its |
| the application can interface to a database residing | | | | functions are database-vendor specific. For |
| on a remote server. The increased power of | | | | instance, we can use the same code to perform |
| personal computer hardware enables critical | | | | queries against a Microsoft Access table or an |
| database information to be stored on a relatively | | | | Informix database with little or no modification. |
| inexpensive standalone server. In addition, this | | | | Once again, it should be noted that most vendors |
| server can be replaced later with little or no | | | | add some proprietary extensions to the |
| change to the client applications. We can apply the | | | | STRUCTURED QUERY LANGUAGE standard, such |
| basic concepts introduced in this book in many | | | | as Microsoft's and Sybase's |
| environments--for example, Microsoft Access | | | | Transact-STRUCTURED QUERY LANGUAGE and |
| running on a single-user Windows application or | | | | Oracle's PL/STRUCTURED QUERY LANGUAGE. |
| STRUCTURED QUERY LANGUAGE Server running | | | | We should always consult the documentation |
| with 100 user connections. One of STRUCTURED | | | | before beginning to work with a new data source. |
| QUERY Language's greatest benefits is that it is | | | | ODBC has developed into a standard adopted into |
| truly a cross-platform language and a | | | | many products, including Visual Basic, Visual C++, |
| cross-product language. Because it is also what | | | | FoxPro, Borland Delphi, and Power Builder. As |
| programmers refer to as a high-level or | | | | always, application developers need to meditate |
| fourth-generation language (4GL), a large amount | | | | on the advantage of using the emerging ODBC |
| of work can be done higher-level language 4GL | | | | standard, which enables we to design code |
| (fourth-generation) language fourth-generation | | | | without regard for a specific database, versus the |
| (4GL) language in fewer lines of code. | | | | speed gained by using a database specific function |
| Oracle Corporation unconstrained the first | | | | library. In other words, using ODBC will be more |
| mercantile RDBMS that promulgated | | | | portable but slower than using the Oracle7 or |
| STRUCTURED QUERY LANGUAGE. Although the | | | | Sybase libraries. |
| original versions our developed for VAX/VMS | | | | STRUCTURED QUERY LANGUAGE in Application |
| systems, Oracle was one of the first vendors to | | | | Programming including Object linking and |
| release a DOS version of its RDBMS. (Oracle is | | | | embedding |
| now available on more than 70 platforms.) In the | | | | STRUCTURED QUERY LANGUAGE was in the |
| mid-1980s Sybase released its RDBMS, | | | | beginning created an ANSI criterion in 1986. The |
| STRUCTURED QUERY LANGUAGE Server. With | | | | ANSI 1989 standard (often called STRUCTURED |
| client libraries for database access, support for | | | | QUERY LANGUAGE-89) defines three types of |
| stored procedures (discussed on Day 14, | | | | interfacing to STRUCTURED QUERY LANGUAGE |
| "Self-motivated Uses of STRUCTURED QUERY | | | | within an application program: |
| LANGUAGE"), and interoperability with various | | | | •Module Language-- Uses procedures |
| networks, STRUCTURED QUERY LANGUAGE | | | | within programs. These procedures can be called |
| Server became a successful product, particularly | | | | by the application program and can return values |
| in client/server environments. One of the | | | | to the program via parameter passing. |
| strongest points for both of these STRUCTURED | | | | •Embedded STRUCTURED QUERY |
| QUERY LANGUAGE Server powerful database | | | | LANGUAGE--Uses STRUCTURED QUERY |
| systems is their scalability across platforms. C | | | | LANGUAGE statements embedded with actual |
| language code (combined with STRUCTURED | | | | program code. This method often requires the |
| QUERY LANGUAGE) written for Oracle on a PC is | | | | use of a precompiled to process the |
| virtually identical to its counterpart written for an | | | | STRUCTURED QUERY LANGUAGE statements. |
| Oracle database running on a VAX system. | | | | The standard defines statements for Pascal, |
| STRUCTURED QUERY LANGUAGE and Client | | | | FORTRAN, COBOL, and PL/1. |
| Server Application Development | | | | •Direct Invocation--Left up to the |
| The common thread that runs throughout client | | | | implementers. |
| server application development is the use client | | | | Before the concept of dynamic STRUCTURED |
| server computing of STRUCTURED QUERY | | | | QUERY LANGUAGE evolved, embedded |
| LANGUAGE and relational databases. Also, using | | | | STRUCTURED QUERY LANGUAGE was the most |
| this database technology in a single-user business | | | | popular way to use STRUCTURED QUERY |
| application positions the application for future | | | | LANGUAGE within a program. Embedded |
| growth. | | | | STRUCTURED QUERY LANGUAGE, which is still |
| An Overview of STRUCTURED QUERY | | | | used, uses static STRUCTURED QUERY |
| LANGUAGE | | | | LANGUAGE--meaning that the STRUCTURED |
| STRUCTURED QUERY LANGUAGE is the de | | | | QUERY LANGUAGE statement is compiled into |
| facto standard language used to manipulate and | | | | the application and cannot be changed at runtime. |
| retrieve data from these relational databases. | | | | The principle is much the same as a compiler |
| STRUCTURED QUERY LANGUAGE enables a | | | | versus an interpreter. The performance for this |
| programmer or database administrator to do the | | | | type of STRUCTURED QUERY LANGUAGE is |
| following: | | | | good; however, it is not flexible--and cannot |
| •Modify a database's structure | | | | always meet the needs of today's changing |
| •Change system security settings | | | | business environments. Dynamic STRUCTURED |
| •Add user permissions on databases or | | | | QUERY LANGUAGE is discussed shortly. |
| tables | | | | The ANSI 1992 standard (STRUCTURED QUERY |
| •Query a database for information | | | | LANGUAGE-92) extended the language and |
| •Update the contents of a database | | | | became an international standard. It defines three |
| NOTE: The term STRUCTURED QUERY | | | | levels of STRUCTURED QUERY LANGUAGE |
| LANGUAGE can be confusing. The S, for | | | | compliance: entry, intermediate, and full. The new |
| Structured, and the L, for Language, is | | | | features introduced include the following: |
| straightforward enough, but the Q is a little | | | | •Connections to databases |
| misleading. Q, of course, stands for "Query," | | | | •Scrollable cursors |
| which--if taken literally--would restrict we to asking | | | | •Dynamic STRUCTURED QUERY |
| the database questions. But STRUCTURED | | | | LANGUAGE |
| QUERY LANGUAGE does much more than asks | | | | This obsession covers not only all these |
| questions. With STRUCTURED QUERY | | | | extensions but also some proprietary extensions |
| LANGUAGE we can also create tables, add data, | | | | used by RDBMS vendors. Dynamic STRUCTURED |
| delete data, splice data together, trigger actions | | | | QUERY LANGUAGE allows we to prepare the |
| based on changes to the database, and store our | | | | STRUCTURED QUERY LANGUAGE statement at |
| queries within our program or database. | | | | runtime. Although the performance for this type |
| Unfortunately, there is no good substitute for | | | | of STRUCTURED QUERY LANGUAGE is not as |
| Query. Obviously, Structured Add Modify Delete | | | | good as that of embedded STRUCTURED QUERY |
| Join Store Trigger and Query Language | | | | LANGUAGE, it provides the application developer |
| (SAMDJSTQL) are a bit burdensome. In the | | | | (and user) with a great degree of flexibility. A |
| interest of agreement, we will stay with | | | | call-level interface, such as ODBC or Sybase's |
| STRUCTURED QUERY LANGUAGE. However, we | | | | DB-Library, is an example of dynamic |
| now know that its function is larger than its name. | | | | STRUCTURED QUERY LANGUAGE. Call-level |
| The most commonly used statement in | | | | interfaces should not be a new concept to |
| STRUCTURED QUERY LANGUAGE is the SELECT | | | | application programmers. When using ODBC, for |
| statement that retrieves data from the database | | | | instance, we simply fill a variable with our |
| and returns the data to the user. The EMPLOYEE | | | | STRUCTURED QUERY LANGUAGE statement |
| table example illustrates a typical illustration of a | | | | and call the function to send the STRUCTURED |
| SELECT statement situation. In addition to the | | | | QUERY LANGUAGE statement to the database. |
| SELECT statement, STRUCTURED QUERY | | | | Errors or results can be returned to the program |
| LANGUAGE provides statements for creating new | | | | through the use of other function calls designed |
| databases, tables, fields, and indexes, as well as | | | | for those purposes. Results are returned through |
| statements for inserting and deleting records. | | | | a process known as the binding of variables. |
| ANSI STRUCTURED QUERY LANGUAGE also | | | | |