Embedded SQL

Abstract: (Most chapters begin with "Introduction".) I. INTRODUCTION. 1. Why Sql is Important. Background. Is a Standard Desirable? 2. An Overview of Sql. Introduction. Data Definition. Data Manipulation. Cursor Operations. Views. Security and Integrity. Recovery and Concurrency. Schemas and Catalogs. Sessions and Connections. II. Some Preliminaries. 3. Basic Language Elements. SQL Language Characters. Tokens and Separators. Key Words. Identifiers and Names. Scope of Names. Notation. 4. Catalogs and Schemas. The SQL-Environment. Catalogs. Schemas. 5. Connections, Sessions, and Transactions. SQL-Agents SQL-Connections SQL-Sessions. SQL-Transactions. 6. Binding Styles. Introduction. The Module Language. Embedded Sql. Direct Sql. III. Data Definition and Manipulation. 7. Scalar Objects, Operators, and Expressions. Introduction. Data Types. Literals. Scalar Operators and Functions. Aggregate Functions. Assignments and Comparisons. 8. Data Definition: Domains and Base Tables. Introduction. Domains. Base Tables. 9. Data Manipulation: Noncursor Operations. Introduction. Single-Row SELECT. Insert. Searched Update. Searched Delete. 10. Data Manipulation: Cursor Operations. Introduction. Cursors. Cursor-Based Manipulation Statements. A Comprehensive Example (Module Version). A Comprehensive Example (Embedded SQL Version). 11. Table Expressions. Introduction. Join Expressions. Table References. Unions, Differences, and Intersections. Table Primaries. Select Expressions. Derived Table Column Names. 12. Conditional Expressions. Introduction. General Remarks. Comparison Conditions. BETWEEN and LIKE Conditions. IN and MATCH Conditions. ALL-Or-ANY Conditions. EXISTS Conditions. UNIQUE Conditions. A Final Remark. 13. Views. Introduction. Data Definition Operations. Data Manipulation Operations. The Check Option. IV. DATA CONTROL. 14. Integrity. Introduction. Domain Constraints. General Constraints. Base Table and Column Constraints. Foreign Keys. Deferred Constraint Checking. 15. Security. Introduction. Authorization Identifiers. Privileges and Access Rules. GRANT and REVOKE. The Grant Option. RESTRICT Vs CASCADE. V. ADVANCED TOPICS. 16 Missing Information and Nulls. Introduction. Effect of Nulls on Scalar Expressions. Effect of Nulls on Conditional Expressions. Effect of Nulls on Table Expressions. Effect of Nulls on Integrity Constraints. A Recommendation. 17. Dates and Times. Introduction. Data Types. Literals. Time Zones. Data Conversion. Scalar Operators and Functions. Assignments and Comparisons. 18. Temporary Tables. Introduction. Type 1: "Declared" Temporary Tables. Types 2 and 3: "Created" Temporary Tables. 19. Character Data. Introduction. Properties of Character Strings. Character Sets and Collations. Data Types. Literals. Scalar Operators and Functions. Assignments and Comparisons. Character Sets For Identifiers. 20. Dynamic Sql. Introduction. What is The Problem? Statement Preparation and Execution. SQL Descriptor Areas. Cursor Operations. Session Defaults. 21. Information Schemas. Introduction. Information Schema Tables. 22. Exception Handling. Status Codes. The Diagnostics Area. 23. Call-Level Interface. Introduction. What is The Problem? CLI Routines and Parameters. CLI Resources and Related Matters. Statement Preparation and Execution. CLI Descriptor Areas. Placeholders. Retrieval Operations. Exception Handling. A Sample "SQLCLIH" File. A Sample CLI Application. APPENDIXES. A. An Sql Grammar. Introduction. Sessions, Connections, and Transactions. Data Definition. Modules. Data Manipulation. Table Expressions. Conditional Expressions. Constraints. Dynamic Sql. Scalar Expressions. Get Diagnostics. Miscellaneous. B. Language Levels and Conformance. Introduction. Intermediate Sql. Entry Sql. SQL Flagger. C. Sql/92 Vs Sql/89. Introduction. Extensions. Incompatibilities. Deprecated Features. D. Some Outstanding Issues. Introduction. The Issues. E. Persistent Stored Modules. Introduction. Overview. Routines, Modules, and Schemas. Type Precedence. Subject Routine Determination. SQL Control Statements. Exception Handling. External Routines. Miscellaneous Topics. F. An Overview of Sql3. Introduction. Data Definition and Manipulation. Functions and Operators. Integrity. Security. Transactions. G. References and Bibliography. 0201964260T04062001

Abstract: System R supports a high-level relational user language called SQL which may be used by ad hoc users at terminals or as an embedded data sublanguage in PL/I or COBOL. Host-language programs with embedded SQL statements are processed by the System R precompiler which replaces the SQL statements by calls to a machine-language access module. The precompilation approach removes much of the work of parsing, name binding, and access path selection from the path of a running program, enabling highly efficient support for repetitive transactions. Ad hoc queries are processed by a similar approach of name binding and access path selection which takes place on-line when the query is specified. By providing a flexible spectrum of binding times, System R permits transaction-oriented programs and ad hoc query users to share a database without loss of efficiency.System R is an experimental database management system designed and built by members of the IBM San Jose Research Laboratory as part of a research program on the relational model of data. This paper describes the architecture of System R, and gives some preliminary measurements of system performance in both the ad hoc query and the “canned program” environments.

Abstract: The access of information systems to underlying relational databases is commonly programmed using embedded SQL queries. Such embedded queries may take the form of string literals that are programmatically concatenated into queries to be submitted to the DBMS, or they may be written in a mixture of the syntax of SQL and a host programming language. The particular ways in which embedded queries are constructed and intertwined with the surrounding code can have significant impact on the understandability, testability, adaptability, and other quality aspects of the overall system. We present an approach to tool-based analysis of the quality of systems that employ embedded SQL queries. The basis of the approach is the identification and reconstruction of embedded queries. These queries are then submitted to a variety of analyses. For example, we chart the relationships of queries to the surrounding code and, via the control and data flow of that code, to each other. Also, we define a suite of metric extractors for embedded queries. Through a number of case studies, involving PL/SQL, Cobol, and Visual Basic, we show how the results of these analyses can be employed to make an assessment of various quality aspects related to the use of embedded queries.

Abstract: Testing of database applications is crucial for ensuring high software quality as undetected faults can result in unrecoverable data corruption. The problem of database application testing can be broadly partitioned into the problems of test cases generation, test data preparation and test outcomes verification. Among the three problems, the problem of test cases generation directly affects the effectiveness of testing. Conventionally, database application testing is based upon whether or not the application can perform a set of predefined functions. While it is useful to achieve a basic degree of quality by considering the application to be a black box in the testing process, white box testing is required for more thorough testing. However, the semantics of the Structural Query Language (SQL) statements embedded in database applications are rarely considered in conventional white box testing techniques. In this paper, we propose to complement white box techniques with the inclusion of the SQL semantics. Our approach is to transform the embedded SQL statements to procedures in some general-purpose programming language and thereby generate test cases using conventional white box testing techniques. Additional test cases that are not covered in traditional white box testing are generated to improve the effectiveness of database application testing. The steps of both SQL statements transformation and test cases generation will be explained and illustrated using an example adapted from a course registration system. We successfully identify additional faults involving the internal states of databases.