User's Guide for the
SQL Test Suite,
Version 6.0

December 1996

David Flater
Leonard Gallagher
Shirley Hurwitz
Joan Sullivan

User's Guide for the SQL Test Suite, Version 6.0

ABSTRACT: This manual describes the SQL Test Suite (Version 6.0) and the procedures needed to test and evaluate an SQL implementation through a standard programming language interface or through Interactive Direct SQL. This release of the SQL Test Suite was completed December 31, 1996, and includes new tests to validate the Intermediate level of conformance to 1992 SQL standards. The SQL Test Suite, Version 6.0, was developed jointly by the U.S. National Institute of Standards and Technology (NIST), National Computing Centre Limited (NCC) in the U.K, and Computer Logic R&D in Greece. The first five versions of the NIST SQL Test Suite were produced by NIST over the years 1987 through 1995. The donation of tests by NCC and Computer Logic, under the CTS5 SQL2 Project sponsored by the European Community, has been a major contribution to the current version.

The SQL Test Suite may be used to evaluate conformance to the following SQL standards specifications: ISO/IEC 9075:1992, ANSI X3.135-1992, FIPS 127-2, and X/Open XPG4 SQL. The test suite contains tests and procedures to evaluate conformance to various levels of the standards or profiles: Intermediate SQL, Transitional SQL, Entry SQL, sizing profiles, flagging of extensions, X/Open profiles. The test suite consists of schemas and test programs for Interactive SQL as well as ten different programming language test suite types: Embedded C, Embedded COBOL, Embedded Fortran, Embedded Ada, Embedded Pascal, Module Language C, Module Language COBOL, Module Language Fortran, Module Language Ada, and Module Language Pascal.

The SQL Test Suite is used to validate commercial SQL products for conformance to ISO, ANSI, and FIPS SQL standards. The results of the validation service are listed in an online Validated Products List. The software for the SQL Test Suite can be downloaded from the Web pages of the NIST Software and Systems Division. To download this conformance testing software, go to:

and select SQL

KEY WORDS: conformance testing; database standards; interoperability; SQL; testing of software; user guide; Validated Products List; validation of software.

DISCLAIMER: Because of the nature of this report, it is necessary to mention vendors and commercial products. The presence or absence of a particular trade name product does not imply criticism or endorsement by the National Institute of Standards and Technology, nor does it imply that the products identified are necessarily the best available.




















This manual describes the SQL Test Suite (Version 6.0) and the procedures needed to test and evaluate an SQL implementation through a standard programming language interface or through Interactive Direct SQL.

The SQL Test Suite may be used to evaluate conformance to the following SQL standards specifications:

Goals of SQL Test Suite

This test suite was originally developed by the Information Technology Laboratory of the National Institute of Standards and Technology in support of its federally mandated program of Federal Information Processing Standards (FIPS). The purpose of this test suite is to help evaluate conformance of SQL implementations to mandatory requirements of FIPS PUB 127-2. This is the FIPS Publication that adopts ANSI X3.135-1992, a voluntary industry standard for database language SQL, for use by the federal government. FIPS PUB 127-2 became effective on December 3, 1993, six months after its publication in the Federal Register.

FIPS PUB 127-2 supersedes previous FIPS PUBs 127 and 127-1. The original FIPS PUB 127 required SQL in relational DBMS applications acquired or developed after August 3, 1988. FIPS PUB 127-1 offered new conformance alternatives, new programming language interfaces, a new integrity enhancement option, clarification and correction of existing specifications, and additional considerations for use in procurements. FIPS 127-2 provides a substantial, upward-compatible enhancement of Database Language SQL. It includes four levels of conformance: Entry SQL, Transitional SQL, Intermediate SQL, and Full SQL. Entry SQL is a minor enhancement over the requirements of FIPS PUB 127-1. Version 6.0 of the SQL Test Suite contains tests for Entry SQL, Transitional SQL, and Intermediate SQL.

An important objective of FIPS PUB 127-2 is:

To reduce overall software costs by making it easier and less expensive to maintain database definitions and database application programs and to transfer these definitions and programs among different computers and database management systems, including replacement database management systems.

The programs in this test suite can be viewed as straightforward standard application programs that a user wishes to transfer from one standard environment to another standard environment. Is this

goal achievable on the implementation being tested? Or, is substantial analysis and modification required before these programs will execute correctly?

The process of installing and running the programs in this test suite is to be documented by the testers according to instructions provided in this manual. That documentation, along with the automated summary report of pass/fail results for individual SQL features, can be used to help evaluate conformance to FIPS PUB 127-2.

Since April 1990, NIST has offered a formal testing service. This service issues Certificates of Validation for tested products passing all required tests. A Validation Summary Report is issued for all implementations tested. This validation report documents, to the extent tested, the implementation's conformance to FIPS PUB 127-2. Beginning July 1, 1997, NIST will turn over SQL testing services to the private sector. Federal agencies should continue to require validated products in their SQL procurements, since validation services will continue to be available and since standard-conforming SQL products are critically important in heterogeneous distributed database environments.

The test suite can also be used to evaluate the adherence to X/Open XPG4 SQL specifications (with optional Integrity Enhancement Feature) for X/Open Branding Requirements; it covers the language bindings Embedded SQL C and COBOL only.

NIST publishes an on-line register, Validated Products List, showing SQL implementations that hold current Certificates of Validation and registered Validation Summary Reports. This publication also lists results of testing for the programming languages (Ada, C, COBOL, and FORTRAN) as well as Graphics, POSIX, and Security standards. As NIST transitions its testing services to the private sector, NIST World Wide Web pages will point to a directory of conformance testing programs, products, and services to provide additional information.

History of SQL Test Suite

The SQL Test Suite was first made available to the public in August 1988 as the NIST SQL Test Suite, Version 1.1. This version included tests for three programming languages: COBOL, FORTRAN, and C. Version 1.1 included tests for Embedded SQL as well as Module Language SQL. In May 1989 the test suite was enlarged and released as Version 1.2. This version included tests for additional SQL features, as well as tests for Embedded SQL Pascal and a Pascal interface to Module Language SQL. The NIST SQL Test Suite was distributed, for a fee, under the terms of a software agreement. Version 2.0 of the SQL Test Suite contained additional tests as well as the support system (software utilities) to administer the validation process.

Continuing standardization work for SQL resulted in a revised SQL standard, ANSI X3.135-1989, published December 1989. This revised standard contained integrity enhancements for SQL, including referential integrity, default values for columns, and check clauses. FIPS PUB 127-1 was revised to specify these new integrity features as an optional module which federal agencies could

either require or (by default) not require in a procurement. Version 2.0 of the test suite also contained a set of tests to validate conformance to this optional module.

In the same time frame, ANSI X3.168-1989 standardized the embedding of SQL in programming languages (Ada, C, COBOL, FORTRAN, Pascal and PL/I). The first release of the SQL Test Suite contained tests for Embedded SQL, in anticipation of this standard. Since numerous implementations of Embedded SQL already existed, prior to standardization, NIST hoped that the early availability of tests for that interface would hasten the conformance of implementations to the revised FIPS PUB 127-1. Version 3.0 provided test suites for Ada bindings to SQL and also tests for the errata in the SQL Information Bulletin SQLIB-1.

ANSI X3.135-1992, the 1992 revision of the SQL standard, represents a major enhancement in SQL functionality. Conformance to FIPS PUB 127-2, Entry SQL, requires additional capabilities from an SQL implementation beyond those required for minimal conformance to FIPS PUB 127-1. The Integrity Enhancement Feature is now mandatory. Support for the following additional features is now required: SQLSTATE status codes, delimited identifiers, renaming columns, commas in parameter lists, and SQL Errata against ANSI X3.135-1989 (approved after publication of SQLIB-1). Version 4.0 of the SQL Test Suite provides tests for all the features in Entry SQL. Although MUMPS is one of the standard programming language interfaces specified in FIPS 127-2, the SQL Test Suite does not yet have programs to validate the MUMPS interfaces to SQL.

Version 2.0 was used in the formal testing service offered by NIST which opened in April 1990. Version 3.0 became the official version of the test suite in July 1992, and Version 4.0 became the official version in January 1994.

In Version 5.0, tests were included to address features of Transitional SQL features defined in FIPS 127-2, as well as features of the X/Open CAE Specification Structured Query Language (SQL). Version 6.0 grew substantially through the donation of new tests for Intermediate SQL written by European collaborators, National Computing Centre in the U.K. and Computer Logic R&D in Greece. These tests were developed under the Conformance Testing Service Project for SQL-92 called CTS5 SQL2, sponsored by the European Community (EC). Version 6.0 became freely accessible over the World Wide Web on December 31, 1996.

Description of SQL Test Suite

The SQL Test Suite provides ten programming language test suite types: Embedded (preprocessor) SQL Ada, Embedded SQL C, Embedded SQL COBOL, Embedded SQL FORTRAN, Embedded SQL Pascal, Module Language Ada, Module Language C, Module Language COBOL, Module Language FORTRAN, and Module Language Pascal. The test suite also provides an Interactive Direct SQL test suite type to test interactive invocation of SQL statements as defined in FIPS 127-2. In Versions 5.0 through 6.0, new tests for Transitional and Intermediate SQL were not translated into three interfaces: Module Language FORTRAN, Embedded, and Module Language Pascal. These three interfaces have been the least popular, and generating additional tests for these interfaces is not cost effective.

The original test programs were developed in Embedded (preprocessor) SQL for the C language. The design objective for the test programs was to provide a simple test for every general rule in the standard and to cover fully all SQL syntax.

Ada, COBOL, FORTRAN, and Pascal test routines, as well as module language test routines, were generated by software (written by NIST) from the original Embedded SQL C language. For this reason, the style of the translated code may seem unnatural for a given language. The original Embedded SQL C Language tests are very simple, using only a carefully restricted subset of the C Language. Otherwise, it would be technically infeasible to translate these tests into the other programming languages.

The Interactive Direct SQL test files were created by extracting SQL statements from the Embedded SQL C programs. Test cases were reworked to avoid reference to cursors and host variables. The resulting text files were annotated with comments describing the test and the expected results required for a "pass."

Each test is designed to be short and simple, exercising as little of the host language as possible. The host language compiler should be validated separately to ensure that it conforms to the applicable standards. The use of complex host language code in SQL conformance programs would make tests difficult to understand and would make it more difficult to resolve questions of interpretation of the SQL standard.

Many of the tests involve 3 small tables containing a total of 23 rows. The data types of columns in these tables are either character string or integer, so the tests will work across all these programming languages. Other tables are used to test approximate numeric and scaled exact numeric data types. Additional tests have been written to cover the data type variables specific to each language.

Each program contains one or more tests. Although allowing only one test per program would simplify the evaluation of implementations with a high degree of nonconformity, it would impose additional overhead on implementations with a high degree of conformity. The tests within a program are intended to be independent so any one test may be removed without affecting the remaining tests.

Each test is self-evaluating; i.e., each test is written with knowledge of the data in the database and the correct response for a specific SQL statement. Each test checks for correct execution of the SQL statement and then inserts into the reporting table, TESTREPORT, a "pass" or "fail" value for that test. After all the test programs have executed, a summary of test results is produced automatically by another program which reads TESTREPORT.

As each test is executed, a description of the test is printed on standard output (the screen) along with appropriate data values and the test result. This output should be considered as a "log" of the test programs. It is intended to assist in debugging and in analyzing nonconformities. This output is not needed to produce the automated conformance analysis of the SQL test suite.

These tests are not designed to debug DBMS software; however, they may help identify problem areas. The use of small tables does not challenge the buffer-management strategy of an implementation. In addition, the frequent use of ROLLBACK (after tests which modify tables), to restore the base data to its original state (and thus simplify testing), limits testing of the COMMIT path. Since the SQL standard does not address physical database design, it is likely that schema definition and DML tests will be run in the simplest manner possible, without optimization.

The test suite includes a few tests for the "SQL Flagger" option specified in Section 10.d of FIPS PUB 127-2. These tests contain extensions to the SQL standard. In general, if an SQL implementation supports these extensions, it must be able to flag the extensions with warning messages. These tests are to be run with the flagging turned off and then, if successful, rerun with the flagging turned on. Test evaluation for the SQL Flagger is subjective, based upon examination of any warnings which are printed (or displayed on the screen) when extensions to SQL are used. The "SQL Flagger" tests are very limited. They are intended to demonstrate the existence and style of monitoring provided by a vendor. They do not systematically attempt to detect SQL extensions which are not flagged. For Entry SQL, standard features which are required only by higher levels (beyond Entry) should all be flagged along with nonstandard features. It is desirable, but not required (until Intermediate SQL), that the flagging message indicate the exact status (Transitional SQL, Intermediate SQL, Full SQL, nonstandard extension) of the flagged feature.

The test suite has a set of programs to test the specifications in FIPS PUB 127-2, Section 16.6, "Sizing for database constructs." These minimum specifications for the precision, size, or number of occurrences of database constructs are contained (by default) in procurements which do not provide alternate specifications. Reporting of the FIPS sizing tests is separate from reporting on other tests. FIPS sizing tests are not technically considered conformance tests, and passing these tests is not required for a Certificate of Validation for FIPS 127-2.

The test suite includes a set of programs to test features from the X/Open CAE Specification Structured Query Language (SQL), Document Number C201, which contain some extensions to the ISO/IEC 9075:1992 standard.

Utility programs are included to make global and program-specific changes in a controlled and systematic manner and to document those changes in the automated report.

Unless stated otherwise, all references to sections, syntax rules or general rules in this documentation are to ANSI X3.135-1992 (or equivalently ISO/IEC 9075:1992).


The technical goal of the test suite is to help evaluate an SQL implementation's conformance to various levels of the SQL standard, as specified in ANSI X3.135-1992 (or equivalently ISO/IEC 9075:1992), through one or more standard programming language interfaces.

The test suite contains additional tests to help evaluate conformance to: (1) the minimum sizing parameters for database constructs specified in FIPS PUB 127-2, Section 16.6, (2) the flagging of extensions, specified in FIPS PUB 127-2, Section 10.d, SQL Flagger, (3) Interactive Direct SQL, as specified in FIPS PUB 127-2 Section 16.5, and (4) X/Open Extensions for features specified in the X/Open CAE Specification.

The test suite contains ten different programming language test suite types. An SQL implementation claiming conformance to FIPS PUB 127-2 for a particular SQL interface; for example, Embedded SQL COBOL, should be tested with the appropriate test suite type. The programming language compiler used for testing should conform to the FIPS standard for that language and should be listed in the Validated Products List, which is published on the NIST Web Server URL address

The intention of NIST is that this test suite should be used to help evaluate compliance of implementations of SQL to FIPS PUB 127-2. A correct implementation of FIPS 127-2 requires the incorporation of the SQL standard document, ANSI X3.135-1992 (or ISO/IEC 9075:1992), into the design specifications for the SQL implementation. The SQL test suite then confirms that the standard has been interpreted and implemented correctly by the SQL supplier. The test suite is intended to be used in conjunction with the SQL supplier's own independently-developed regression tests to ensure a robust and internally consistent product. A quality SQL implementation is not achievable by simply "fixing the product" until it passes the SQL Test Suite.

It is important to recognize the limitations of this test suite and of any test suite. In particular, it would be incorrect for implementations to claim conformance to FIPS PUB 127-2 simply by virtue of correct performance of these tests. It is reasonable, however, for purposes of procurement, to substantiate claims of conformance to FIPS PUB 127-2 by demonstrating correct execution of these tests.

Performance is recognized as a critical selection factor in many DBMS procurements. However, performance is not an issue for standards validation testing and is not measured by this test suite.

Currently, it is the responsibility of the implementor to prepare the driver scripts (i.e. operating system command files, shells, makefiles, runstreams, JCL) to execute the test suite.

NIST will maintain Version 6.0 of the SQL test suite as resources allow. NIST will evaluate error reports and distribute documentation of approved corrections via a World Wide Web page on SQL programs.


The SQL Test Suite contains schemas and programs to test an SQL implementation for various levels of the SQL standard. The test suite contains 18 users, 19 schemas, 208-463 programs and up to 849 test cases (depending upon test suite type). The Interactive Direct SQL version is smaller, with 379 programs and 660 test cases. In all there are 5887 files in the Version 6.0 distribution.

Before testing begins, the Test Editor, TEd, is installed. This editor is used (1) to install maintenance updates from NIST and (2) to facilitate installation and documentation of any changes made by the tester to the original test suite files.

Figure 1 shows a system flow diagram for basic SQL testing. Running an SQL test suite consists of 5 steps.

In step 1, the schema files are processed in some implementation-defined manner, typically using Interactive SQL.

In step 2, a few programs are run to insert values into the base tables. The contents of these base tables will remain unchanged throughout testing; i.e., these values will be restored by each program that changes them.

In step 3, the test programs are run to interact with the database tables. Each program contains logic to evaluate the database responses and determine whether a test passes or fails. This pass/fail decision is recorded by inserting a row into the table TESTREPORT. In general, programs may be run and rerun in any order.

In step 4, static values are inserted into the reference tables. These tables are required to produce the automated summary report. These tables are also a valuable resource to testers, since they can be queried interactively to create a variety of useful cross-references. In addition to the static values, rows are inserted (by the tester, via Interactive SQL) into tables FEATURE_CLAIMED and BINDING_CLAIMED to specify which profiles and interfaces are to be tested.

In step 5, the report programs are run to produce three listings for each profile tested:

PROBLEMS - a listing of failed or missing results

TEST RESULTS - a listing of the result (pass / fail / missing / not applicable) for each individual test in each interface tested

TOTALS - counts for test results for each interface tested

To test Interactive Direct SQL, the tester visually evaluates the execution log (screen display) from running the SQL command files, assigns a pass/fail grade, and completes a check list.


1. Download files from the World Wide Web. The SQL Test Suite is available as a set of compressed TAR files. You will download the default SQL TAR file and then will select from among the TAR files for different programming language interfaces.

The full SQL Test Suite consists of 14 directories, organized as 11 TAR files. When you download the default TAR file, you will receive the basic set of directories needed by all the other directories. The default SQL TAR file contains a schema directory (SCHEMA), a reporting directory (REPORT), the Interactive SQL directory (SQL), and the utility/documentation directory (OTHER). You then download directories for the programming language interfaces you want to test. The 10 remaining directory names, consistent with our file-naming conventions (see item 5, below), are: PC, PCO, PFO, PAD, PPA, MC, MCO, MFO, MAD, and MPA. The most popular of these 10 directories is the PC directory, for testing the Precompiler C (Embedded C) interface.

2. Uncompress the TAR files in a suitable directory. Each TAR file will create its own directories. Lock the files so they cannot be changed. DO NOT CHANGE the test suite filenames in these directories (unless mandated by your operating system). The maintenance scheme depends on stable naming conventions.

3. Create additional directories. In preparation for a validation, we suggest that you create at least three additional working directories to store (1) permanent objects which you create such as driver scripts, TEd input files, makefiles, subroutine AUTHID; (2) generated intermediate objects that can be recreated at will and deleted en masse, such as temporary versions of the programs (output of TEd or precompiler), object modules, executables, Ted executable, etc.; and (3) audit trail/documentation objects such as logs showing schema creation and test program execution. These logs will be stored on tape or diskette after a validation, along with the permanent objects which you used to run the tests.

The procedures for creating schemas, preprocessing, compiling, linking, and running will vary with the operating system, DBMS, programming language compilers, etc. The user of this test suite is responsible for creating the driver scripts (i.e. operating system command files, shells, makefiles, runstreams, JCL) to execute the test suite. Sample driver scripts for a few environments are included as APPENDIX A.

4. Verify that all files have been received for the test suite type being tested. Print the file RUN*.ALL (where * denotes a wild-card matching symbol in a file name) for the test suite type being tested. For example, the Embedded SQL C ("PC") test suite will contain a file RUNPC.ALL. This file lists the programs to be executed. Additional files are CHG*.TED and RPT*.SQL (i.e. CHGPC.TED and RPTPC.SQL for the "PC" test suite). Print these files also. All other files in the directory are programs of the type to be tested (e.g., Embedded SQL C programs - ending in "PC").

Note that each test suite type will contain a slightly different list of programs. Certain programs are tests of the Embedded SQL and are not meaningful tests for module language. For example, for the following programs you will find only precompiler versions: DML017 (tests WHENEVER) and DML063 (tests use of reserved words as host variables). Other programs are meaningful for only one host language. For each of the five embedded test suite types, there is a sample optional login program, AUTHID, which may be useful.

Note that directories PPA, MFO, and MPA contain programs which are not listed in the files RUNPPA.ALL, RUNMFO.ALL, and RUNMPA.ALL. These extraneous programs are tests for Transitional SQL features. The translation of programs into test suite types PPA, MFO, and MPA was begun, but not completed because of time constraints and limited demand. Interfaces PPA, MFO, and MPA will not be validated for conformance to Transitional SQL. Programs for validation of Entry SQL are complete in all test suite types.

5. Identify test programs. Each of our test programs is of the form,,,,,,,,,,,,,,, or

The program prefix has the following meaning:

CDRtests "integrity enhancement" to SQL
-- Check clause, Default column value, Referential integrity
DMLtests data manipulation language
SDLtest schema definition language via DML
ISIinformation schema for Intermediate SQL
ISTinformation schema for Transitional SQL
FLGSQL Flagger test
MPAconcurrency test, program A
MPBconcurrency test, program B, to be run at the same time as program A
XOPX/Open Extension tests, for Embedded SQL C and COBOL only
XTS,YTSIntermediate SQL programs donated by the CTS2 SQL2 project
ADAAda only
CCCC Language only
PASPascal only

The xxx designates the type of program as follows:

.PADprecompiler (embedded syntax) Ada program
.SADstandard (calling module language) Ada program
.MADModule Language SQL called by a Ada program
.PCprecompiler (embedded syntax) C program
.SCstandard (calling module language) C program
.MCModule Language SQL called by a C program
.PCOprecompiler (embedded syntax) COBOL program
.SCOstandard (calling module language) COBOL program
.MCOModule Language SQL called by a COBOL program
.PFOprecompiler (embedded syntax) FORTRAN programa
.SFOstandard (calling module language) FORTRAN program
.MFOModule Language SQL called by a FORTRAN program
.PPAprecompiler (embedded syntax) Pascal program
.SPAstandard (calling module language) Pascal program
.MPAModule Language SQL called by a Pascal program
.SQLInteractive Direct SQL statements

The iii stands for an integer, assigned serially.

6. Install the NIST Test Editor, TEd. It is not necessary to use the NIST Test Editor for in-house evaluations. However, if you plan to have your product validated in the future, you will need to learn how to use the editor. Keep in mind that the explicit purpose of the editor is to facilitate running the test suite. Despite the natural resistance of users to learning yet another editor, and a batch editor at that, we expect that your investment of time will more than pay for itself.

Our test editor is written in highly-portable C. The name of the source code program in the OTHER directory is TED.C. Please notify us immediately of any portability issues or errors which we have overlooked. We will assist you in debugging, if problems arise.

Read the user documentation for TEd to understand why and how we plan to use this editor. If you change any of our test programs or schemas in the process of testing, we suggest that you use our editor to accomplish this. At the end of testing, you will have a single text file of batch editor commands (input to TEd) which documents all the changes you made. This single file will greatly simplify retesting (and validation) later. A separate text file may be used for each test suite type and for schema processing. Or, all change specifications may be stored in a single file.

Each directory contains a sample or "starter" change file, CHG*.TED, which the tester should modify (with any editor) throughout the testing process. For example, the sample change file for Embedded SQL C is CHGPC.TED. This file also contains instructions for installing implementation-dependent options.

Prior to scheduling for an SQL validation, the SQL testing laboratory will review the proposed file and will determine whether proposed changes are nonconformities or allowable modifications.

7. Download the Version 6 maintenance file UPD600.TED. The approved maintenance file, UPD600.TED, is an important input to TEd. This file should be copied into the same directory containing your driver scripts and CHG*.TED files. This file should be referenced with the "-t" option on the TEd command line to effect automatic maintenance of the SQL Test Suite. From time to time, this file should be replaced with the most recent version of the maintenance file. See the section titled "Software Maintenance" for more details.

Since the TEd editor is used to install maintenance, it is important to process every file from the SQL Test Suite using the TEd editor with the official maintenance file UPD600.TED. This means that schema files, data files, test programs, reporting programs, etc. must have maintenance applied before they are used.

Prior to scheduling for an SQL validation, the SQL testing laboratory will review the maintenance file to verify that it contains the most recent changes.

Note that TEd has an option (-o) to write the edited program (or schema file) with a different name. If you want to change the downloaded filenames to satisfy naming conventions for your SQL processor, do so on output from TEd. For example, if your SQL preprocessor expects Embedded C programs (such as dml001.pc) to have a file extension of ec, then use the following command:

ted -t upd600.ted -t chgpc.ted -o dml001.pc

This command will read the downloaded Embedded C test program dml001.pc, apply the changes coded in file upd600.ted, apply the changes coded in file chgpc.ted, and then write the modified Embedded C test program If the file chgpc.ted contained a command such as:

sub dml001.pc //

This command would change the text E1 to the text E2 globally in only program dml001.pc. It would not change anything if program were input to TEd. So, it is important not to change filenames prior to applying the NIST file upd600.ted.


1. Set up accounts and passwords, with the assistance of the DBA, for the following list of authorization id's:

HUfor file SCHEMA1
for file SCHEMA6
for file SCHEM11**
for file SCHEM12***
SUN*for file SCHEMA8
CTS1***for file CTS5SCH2***
for file CTS5SCH3***
CTS2***for file CTS5SCH1***
CTS3***for file CTS5SCH5***
CTS4***for file CTS5SCH4***
T7013bPC***has no schema file
T7013PC***has no schema file
XOPEN1++for file XSCHEMA1
XOPEN2++for file XSCHEMA2
XOPEN3++has no schema file

* this authorization and schema are used to test Integrity Enhancement Feature (not used for one of the X/Open profiles, but required for FIPS 127-2).

++ this authorization and schema are used to test X/Open profiles only.

** this authorization and/or schema are used to test Transitional SQL profiles.

*** this authorization and/or schema are used to test Intermediate SQL profiles.

If the operating system does not allow a user id of length 18, then replace CANWEPARSELENGTH18 with another user id which is the maximum length allowed. If your SQL implementation uses the system user id as the SQL USER value or as the authorization id for privilege enforcement, then your computer system administrator should also create system accounts for these users. You may need to process schema and programs for each authorization id while logged in as that user.

The tester will need to choose passwords for the authorization id's. The SQL standard has no requirements concerning the passwords chosen. The tester should probably choose passwords consistent with in-house regression testing procedures, if possible.

2. Define the schema for each of the appropriate authorization id's, processing the schema definition text files in an implementation-defined manner; e.g., interactively. See

Step 1 of Figure 1. The file RUNSCH.ALL contains pseudo-scripts for creating the schemas. Use the SCHEMA text files as follows:

a. If your implementation is fully conforming to the standard, use SCHEMA1.STD through SCHEM10.STD. (See item (e) below for a discussion of privilege violation processing for schemas).

b. If your implementation needs semicolons to terminate SQL schema definition statements, use SCHEMA1.SMI through SCHEM10.SMI. Be sure to report this nonconformity in your final conformance analysis. Note that this will be counted as a nonconformity for a validation.

c. If your implementation needs semicolons and also requires CREATE UNIQUE INDEX instead of the UNIQUE declaration, then use SCHEMA1.NC instead of SCHEMA1.SMI. Be sure to report this nonconformity in your final conformance analysis. Note that this will be counted as a second nonconformity for a validation.

d. Otherwise, make whatever changes are needed to obtain a logically equivalent syntax for the schema definition. Keep in mind that changes are generally counted as nonconformities. It is essential that the tables HU.TESTREPORT, HU.STAFF, HU.PROJ, and HU.WORKS be installed; otherwise further evaluation of the test suite is impossible. It may be necessary to change the exact numeric data types to INTEGER instead of DECIMAL(I). (We chose to use exact numeric data types with a declared scale so that numeric precision would not be an issue.)

Code your change specifications as inputs to TEd by editing the sample file CHGSCH.TED or CHGALL.TED, which is provided. If the schema contains a privilege violation, capture the error message as proof that a privilege violation has been detected and then edit CHGSCH.TED to specify removal of the offending text. The appropriate changes may already be coded in the CHGSCH.TED file as comments. If you are not using TEd, keep a log of your changes as documentation of either implementation-defined or as nonconformities to be reported in your final conformance analysis.

e. In an effort to simplify the process of editing schema files to remove schema privilege violation syntax (demonstrated by the SQL implementation to be a "fatal" error), alternate files are provided in the test suite. If your schema processor will entirely reject a whole schema because of a single error, then you may use the schema files ending in PV* and OK. For example, instead of using SCHEMA2.STD without and with TEd changes, you may use SCHEMA2.PV1 (with privilege violation #1) to demonstrate the fatal error, followed by SCHEMA2.OK (no privilege violation).

f. Note that there is a file of DROP TABLE and DROP VIEW statements, DROPHU.NC (clearly containing extensions to Entry SQL), to drop all views and tables created by SCHEMA1. There is a similar file, DROPSUN.NC, for SCHEMA8. These files may prove useful while you are analyzing your implementation's SDL conformance. Note that SCHEMA1 and SCHEMA8 are the only large schemas.

It is permissible to insert one or more statements at the beginning of the schema file to login or to establish the ANSI/ISO environment. You may add an implementation-defined terminator, such as a semicolon, at the end of the schema file. Other variations may be allowed after consultation with NIST or the SQL testing laboratory.

Verify that the tables have been created. Read the schema files and compare to tables and views existing in your data dictionary.


1. Code a procedure to create an executable. Decide how to invoke all processors, such as precompilers, compilers, and TEd, to create an executable. Be sure to invoke any option on a processor which is needed to obtain the ANSI/ISO/FIPS behavior. Be sure to keep notes on which options or parameters are used (whether explicitly or implicitly), because they should be documented later in your evaluation. Read "Special Notes on Individual Programs" at the end of this section for variations in creating or running executables. Your procedure should invoke TEd as the first step, to install changes without touching the NIST original programs. On output from TEd, you should rename files to suit your SQL implementation's naming conventions. For example, you may want to rename DML001.PC to DML001.EC. We do not recommend renaming files before executing TEd, because the NIST maintenance TEd file will then need to be modified to specify the new names of the test programs.

To save mass storage space, you will probably want to delete all intermediate files (such as outputs of TEd, precompilers, and compilers) after successful completion of the program.

2. Code a script to invoke the procedure for each program. For each test suite type, there is a text file, RUN*.ALL, listing the programs to be executed to support various claims of conformance. For example, file RUNPC.ALL lists the Embedded SQL C programs, organized into sections for the various possible claims. You may ignore programs not applicable for your claim. The text file may be edited to create a command file that will create executables or run the test suite. Read the notes in RUN*.ALL for additional instructions. Also, if you are going to claim support for individual features in addition to the Entry SQL Profile, you will need to use the automated reporting system to generate the correct list of programs. The files RUN*.ALL were generated by program REPORTA, and you may choose to run program REPORTA rather than using files RUN*.ALL.

The authorization id for each program is listed in these files to assist you in constructing driver scripts with the appropriate authorization id values.

If your SQL password is tied to your system login password (and this is not disallowed by the SQL standard) you will probably need to compile and/or execute each of the test programs while logged into the system as that user. Since the order of execution of the test programs is not important, you should group them by authorization id; i.e., in the interest of efficiency, you should write driver scripts which minimize login and logout.

3. Solve the login problem. The method of establishing a <module authorization identifier> is implementation-defined in embedded languages. We have chosen to code a CALL to a subroutine, AUTHID, with a variable, uid, containing the authorization identifier. This subroutine logs into the database with the given authorization. This solution may not work for you! You may need to move the authorization identifier into the driver scripts (external to the program) and logically delete the subroutine call. You may replace the subroutine call with an include statement or a CONNECT statement - see APPENDIX D. In Embedded SQL C, you may want to insert after the #include <stdio.h> statement some other statement which will effect initialization of your database. Or, you may solve the authorization identifier problem in some other way. Your report of test results should include a description of how your implementation handles login.

Additional statements are allowed to connect to database components and to establish the ANSI/ISO environment. Basically, one or more statements to login and to establish the ANSI/ISO environment will be allowed near the beginning of each test file. For example, you may make a call to the server to invoke ANSI/ISO mode or to invoke ANSI/ISO-required features separately (e.g., SERIALIZATION mode, TRANSACTION mode, etc.). The call to AUTHID is a convenient hook for TEd to make these insertions. It is very desirable that all ANSI/ISO-required features be default features; however, it is allowable to invoke them as a group or individually. This invocation may take the form of precompiler/compiler parameters, configuration files, software installation parameters, calls from inside a program (prior to execution of test cases), etc.

Since it is implementation-defined (in Entry SQL-92) whether the authorization is checked at run time or at compile time, the most straightforward approach is to precompile (prepare executables) and execute each program while logged in as the USER specified (by the variable uid) inside the test program. For client/server architectures where the catalog is not referenced until run time, it may be more convenient to compile all programs while logged on as a single user.

Note that each test program verifies that the correct USER is logged in before executing the test cases. Incorrect USER value will STOP the program. You may easily use TEd to remove or replace this verification during debugging if your SQL implementation does not support the key word USER.

4. Make global changes for implementation-defined parameters. We have made an attempt to code certain constructs consistently so that global changes would be possible as a means of installing implementation-defined parameters. Edit the sample TEd input file, CHG*.TED, for the test suite type being run. Specify the appropriate implementation-defined values according to examples contained in the CHG*.TED file.

Change authorization identifiers which are too long to shorter ones; e.g., CANWEPARSELENGTH18 may be changed to CANWEPAR. Exercise care, when making global changes to character string literals in the Pascal and Ada interfaces, not to change the length of the literal, since unequal-length comparisons and assignments may not compile or execute as expected.

The precision of indicator variables is implementation-defined. We have chosen to use "short" in Embedded SQL C, "integer*2" in FORTRAN, "PIC S9(4) DISPLAY SIGN LEADING SEPARATE" in COBOL, and "integer" in Pascal. If this is not the correct precision or exact numeric type for your implementation, you must change the precision in the declarations of variables beginning with the name "indic"; e.g., indic1, indic2, etc. These variables occur in programs CDR003, CDR027, DML004, DML008, DML010, DML013, DML023, DML025, DML036, DML061, DML071, DML076, DML077, DML082, SDL026, etc. For Ada programs, the required package SQL_STANDARD (for SQL-92) or INTERFACES.SQL (for SQL-92 as corrected by Technical Corrigendum #2, TC2) specifies whether the indicator type is INT or SMALLINT.

The precision of SQLCODE is implementation-defined for COBOL. We have chosen "PIC S9(9) COMP" in COBOL. If this is not the correct precision for your implementation, you must change globally the precision of the COBOL SQLCODE declaration.

COBOL programs may need global changes in the Source-Computer and Object-Computer paragraphs.

The precision of a CHAR column with DEFAULT USER in SCHEMA8 should be changed to reflect the implementation-defined length for USER.

Implementation-defined keywords (possibly HOURS and PROGRAM), which are not allowed by your implementation as column and tables names, should be changed globally to some other word. This does not count as a nonconformity.

Character set is implementation-defined. Contact NIST or the SQL testing laboratory if your character set (whether SQL processor or host language compiler) is incompatible with test suite materials. NIST or the SQL testing laboratory will discuss the implications with you and authorize appropriate workarounds.

5. Using TEd, make global changes for documentation purposes. Code a TEd substitution for "59-byte ID" to identify the SQL product and version being tested and the test platform, including hardware and operating system. If the length exceeds 59, then the replacement string may overflow COBOL and FORTRAN source code margins. (Compulsively wordy testers should investigate the -c option in TEd to specify multiple-line substitutions.)

For FORTRAN, Ada, and Pascal interfaces, determine how to print date and time for your system. Code TEd substitutions for "date_time declaration" and "date_time print". These substitutions will differ among host languages and operating systems. C and COBOL programs have already been modified to print date and time using standard features.

6. Using TEd, make global changes for nonconformities which must be resolved before further testing is possible. These global changes should be included in your conformance analysis as nonconformities. All test suite materials must be supported correctly by the SQL implementation being tested. That is, in addition to the obvious test programs, all test suite materials such as schema files, dataload programs, reporting system files (schema files, report dataload program, programs reporta and reportb), are considered part of the demonstration of conformance by the SQL implementation under test. Any changes to any of these materials necessitated by nonconformities of the SQL implementation are to be documented as nonconformities.

If you are testing Embedded SQL COBOL and your SQL implementation does not support the COBOL numeric data type [USAGE] DISPLAY SIGN LEADING SEPARATE, you will need to make global changes to the COBOL programs (.PCO and .SCO) and the COBOL modules (.MCO). TEd can be directed to change only those declarations within the scope of the BEGIN and END DECLARE SECTION.

If your implementation of SQL does not support a direct declaration of SQLCODE, but instead requires an INCLUDE SQLCA statement, you should globally delete the declaration for SQLCODE and insert one for the required structure. For language C, you may want to direct TEd to delete all lines containing "long SQLCODE" and insert the required INCLUDE SQLCA statement before the text "main()". This will be counted as a nonconformity.

If SQLCODE has some other name in your product, such as SQLCDE or sqlca.sqlcode, you will want to make a global change to the programs. If the substitute value is longer, you may also need to split lines which become too long. In C, if the substitute value is longer, you may prefer to "#define SQLCODE sqlca.sqlcode". This will be counted as a nonconformity.

We have attempted to code certain SQL constructs in a consistent manner, so global changes would be possible. These constructs include:

We have taken care to code Embedded SQL statements so the global insertion of a line of code (such as a "print" of SQLCODE) either before or after executable SQL statements will not disrupt the logic of any "for" loops or "if" blocks containing them.

7. Begin running the script to invoke the programs listed in RUN*.ALL. Run program BASETAB to load the primary tables. The correct authorization identifier for BASETAB is HU. Then run programs CUGTAB, FLATTAB, SUNTAB0, SUNTAB1, SUNTAB2, SUNTAB3, and SULTAB1 with the authorization shown in RUN*.ALL. For testing Intermediate SQL, run program CTS5TAB. For testing of X/Open profiles, run XBASETAB. For testing X/Open profiles without Integrity Enhancement Feature, there is no need to execute programs for authorizations SUN and SULLIVAN. See Step 2 of Figure 1. Check the printout to verify that the data has been inserted. These programs may be rerun at any time to re-initialize the data in the primary tables.

8. Run each of the programs which are appropriate to the test suite type. See Step 3 of Figure 1. Typically, if a problem occurs in one of the test programs, the tester performs the following steps: (1) determines the cause of the problem, (2) changes the program by modifying input to TEd, (3) recreates the executable program, and (4) runs the modified executable program. Often, for the purpose of debugging, it is helpful to run the Interactive Direct SQL version of a program. The on-line user interface is often very informative and the SQL statement in question can be rapidly modified and retried until the problem is isolated. The test programs may be run and rerun in any order (except for programs beginning with MP, which must be run in pairs). Other exceptions are the sequence of programs YTS790 through YTS792 and the sequence of programs YTS793 through YTS795 for Intermediate SQL, as well as the sequence of programs XOP719 through XOP723 for X/Open profiles. Each of these sequences must be run in ascending order. See Special Notes on Individual Programs below.

There are thirteen pairs of programs for concurrent testing:

Each pair is to be run concurrently, either from separate terminals or windows or started as separate batch processes. All MPA and MPB programs for a given test suite type may be run at the same time. Any pair of concurrent programs may be run and rerun in any order.

Start the MPA program first, and then a few seconds later or when prompted by the MPA program, start the MPB program. It is common to see both programs issue messages to start the companion program, even after the tester has started both.

The concurrency programs contain tuning variables which may be used to lengthen the workload or planned waiting periods. This will allow programs to "interleave" better, as required by program logic to get a "pass."

9. Use TEd to install changes. If the SQL language for a given test (except for a FLG test) prevents a program containing several tests from running, use TEd to delete the entire problem test. Then rerun the program to exercise the remaining tests. Note that the missing test will be reported as a "fail" by the automated reporting system. Make only the global changes to the FLG programs. Do not make any other special changes (except FLG005) to these programs. (If an SQL Flagger test does not compile and execute, then the reasons given by the implementor for not compiling or executing may constitute the "flagging" required by FIPS PUB 127-2.)

Another approach is to change the syntax of the problem test to syntax acceptable to your DBMS. This would allow you to further evaluate the SQL implementation; however, it would probably give a false test result of "pass." Code these changes in the SYNTAX DEFICIENCIES SECTION of the CHG*.TED file. This file is a log of changes applied to tests which fail syntactically, although they may pass functionally.

10. Run PREDML. When you are ready for a final analysis of a test suite type, remove all rows for that test suite type from the table TESTREPORT. This will eliminate conflicting results caused by earlier rerunning of changed tests. This is done by executing the program PREDML for authorization identifier HU. Then rerun all the test programs. Capture the screen printout of this final run as part of the documentation of your testing.


1. Install the Reporting Structure.

Run the following in Interactive SQL as user HU:

a. REPORT.SQL, in directory REPORT (creates the tables) See Appendix H.1.

b. DATALOAD.SQL, in directory REPORT (loads the static data)

** If DATALOAD.SQL causes problems, refer to the long instructions on the media, file REPORT_L.DOC in directory REPORT.

Compile the following programs:

a. REPORTA (any embedded or module language)

b. REPORTB (any embedded or module language)

c. REPORTZX.C, in directory REPORT (ANSI C with no embedded SQL)

Put the executables for REPORTA, REPORTB, and REPORTZX together in a directory where user permissions will allow the creation of temporary files.

2. Execute the Reporting System.

a. Insert one or more of the following values into BINDING_CLAIMED according to which bindings you wish to test: 'PCO', 'PFO', 'PC ', 'PPA', 'PAD', 'MCO', 'MFO', 'MC ', 'MPA', 'MAD', 'SQL'. For example, to test Embedded C: INSERT INTO BINDING_CLAIMED VALUES ('PC ');.

b. Insert the profile identifiers for the profiles that you want to test into FEATURE_CLAIMED. For example, to test Transitional SQL:


The profile identifiers can be found in the diagram PROFILES.PS (in directory OTHER) or by typing the Interactive SQL command:


The typical FIPS 127-2 validation (for Entry SQL) will use only the following inserts into FEATURE_CLAIMED:


NOTE: Subprofiles are automatically selected when you select a parent profile. Refer to the diagram PROFILES.PS in directory REPORT or to Appendix H.3. For example, if you select 'P135' (FIPS 127-2 Transitional SQL), the following subprofiles are automatically selected: 'P110' (FIPS 127-2 Entry Syntax Flags), 'P120' (ISO/IEC 9075:1992 Entry SQL), and 'P125' (FIPS 127-2 Entry SQL).

c. Run REPORTA. If you are testing a combination of individual features, rather than one of the established profiles, then you will need to capture the output to get a list of programs that you must run to test the feature(s) that you selected. If only some of the tests in a particular program are required, REPORTA will also provide a TEd change to delete the extra tests. Include the TEd change specification in your TEd file. Run any additional test suite programs required for your claim.

d. Run RPT*.SQL for each binding claimed (test suite type). Before producing the final reports, it is often useful to know if there are any unexpected failures, any missing tests, or any conflicting results (both "pass" and "fail" for a single test). An efficient way to ensure that the table TESTREPORT contains no surprises is to run the Interactive Direct SQL queries in the appropriate RPT*.SQL file. For example, for Embedded SQL COBOL, run RPTPCO.SQL. Run it under authorization HU. RPT*SQL must be run after REPORTA in order to detect missing tests. If the query results of RPTPCO.SQL accurately reflect the testing, then you are ready to run the final report.

e. Run REPORTB to generate the temporary files used by REPORTZX.

f. Run REPORTZX and look at COMBINED.LST to see the results of testing. As a general rule, you should always run REPORTB immediately before running REPORTZX.

g. If REPORTZX shows a test number is missing or a test has failed incorrectly, then you will need to follow restart procedures below.

NOTE: The diagram REPORTIN.PS in directory OTHER shows the data flow of the above procedure.

NOTE: Programs REPORTA, REPORTB, REPORTZX are run only once to report on all bindings claimed. This is a different architecture from the one in Version 4.0, where the reporting programs were run for each binding claimed.

3. Restart Procedures for the Reporting System.

If you do find a test case with conflicting results (e.g., both a "pass" and a "fail") for the test suite type being tested, then the test will be assigned a "fail" by the automated reporting system. If a test fails for an operational reason (such as starting two copies of MPA001 at the same time) and does not represent a real failure (a nonconformity) and you wish to rerun to demonstrate conformance, then delete all results for that test number and that test suite type. You may then rerun the program containing the test. A similar procedure is allowed for a test case which fails because it was executed incorrectly; e.g., before loading the initial data into the tables. For example, DELETE FROM HU.TESTREPORT WHERE TESTTYPE = 'PCO' AND TESTNO = '0099'; will remove all results for the Embedded SQL COBOL test number 99. Note that duplicate test results in TESTREPORT do not cause a problem.

If test cases are missing because you simply failed to run the containing program, then you need to run the missing program(s) and rerun REPORTB and REPORTZX. It is not necessary to rerun REPORTA. Refer to Appendix H.2 to see the structure of the reporting system.

If you want to change the bindings or features claimed, you will need to adjust tables BINDING_CLAIMED and FEATURE_CLAIMED using Interactive SQL. You will then need to rerun programs REPORTA, REPORTB, and REPORTZX.


1. Prepare the final CHG*.TED file. Edit the cumulative change file which has been used to specify changes to TEd. Remove any of the original NIST comments or examples which are not applicable. Analyze each change and assign it to one of the sections in the CHG*.TED file. Create a new section if necessary and document each change with a comment explaining the purpose of the change.

2. Select a testing strategy. Now that all test programs have been debugged on your SQL implementation, it is time to plan for regression testing and/or validation. You will want to develop procedures to run the tests from beginning to end with minimal effort. We have found, through experience conducting validations, there are two approaches generally used to execute the test suite.

a. Process/Link/Execute: For each test program, one at a time: (1) prepare the executable program (execute TEd, precompiler, compiler, linker), (2) execute it, and (3) delete it. This saves on mass storage and generates a complete log. This approach is recommended for debugging and for validations running only one or two test suite types.

b. Prepare executables in advance: An efficient way to run multiple test suite types is the following: (1) prepare executable versions of each program, (2) prepare a driver script to execute PREDML and all of the test programs (except the MPB programs) in the correct order, (3) turn on the "screen capture" or log and start the driver script, (4) when the MPA programs call for the MPB programs, start the appropriate MPB program from another terminal, or specify in the driver script that MPB starts a few seconds after MPA, (5) turn off the "screen capture" or log and print or review the output, and (6) rerun the reporting programs in batch if the page ejects got lost in the "screen capture."

Steps 3 through 5 of this strategy typically take us 30 minutes, while step 1 takes 3 hours; although we have seen platforms which complete all steps in 20 minutes. Step 1 can be broken up into several driver scripts, submitted as separate processes or run from different terminals. There is no need to worry about concurrency during step 1. While we are running steps 3 through 5, we do worry about concurrency, and we do not run any test programs from other test suite types.

Each of the concurrency program pairs uses tables used by no other programs in that test suite type. Consequently, all thirteen program pairs may be run simultaneously, and they may overlap the stream of other test programs in execution without concern for accidentally altering data used by another test program. However, no two test suite types should be executed simultaneously.


AUTHID This is a sample subroutine which can be modified with implementation-defined statements to accomplish login and/or to establish the ANSI/ISO test environment. It is entirely optional. Instead of using a subroutine to login, the tester may globally replace the call to AUTHID in the main routine with some implementation-defined statement.

CCC004 Each of these C language programs contains an additional subroutine, CCC004S CCC009 and CCC009S respectively, which needs compilation and additional link statements. Embedded SQL C test suites contain programs CCC004 and CCC009; Module SQL C contains only program CCC004.

DML015 Tests in this program are not independent of each other. If one test is deleted, other tests in the program may be affected.

DML035 In COBOL, this program is optional (and may not compile) because it contains a nonstandard variable with USAGE COMP-1. Change the data type to suit the compiler.

DML038 Test number 0205 prints a Cartesian product of three tables. Do not panic and cancel the program when you see 360 lines scrolling across the screen. Do cancel the job, however, if you see more than 400 lines!

DML044 In Pascal, there is no standard way to continue a character string literal onto the DML047 the next line. In order to test database columns for long character strings, we coded a procedure, "concat," to create a long character string value. "concat" is used in test 0216 to assign a 118-byte value to variable vtr119 and in test 0222 to assign a 240-byte value to variable STR240. If procedure "concat" does not work for your compiler, propose another method to assign the long character string values.

DML063 This is an Embedded (only) SQL program to test the use of SQL key words as host identifiers. A vendor may declare up to one third of these key words to be "reserved" and not allowable as host identifiers. The CHG*.TED file must contain specifications to disallow the "reserved" key words.

DML102 Two copies of this program may be run in place of MPA013.PC and MPB013.PC. This program is a more elegant approach to concurrency testing, but unfortunately, it

contains C language constructs which do not translate easily into the other test suite types. This option is available only in Embedded SQL C.

DML103 Two copies of this program may be run in place of MPA012.PC and MPB012.PC.

DML116 This program contains two subroutines for each Embedded SQL main routine. For module language interfaces, there are two host language subroutines and three modules to be linked together.

DML169 Check that some form of flagging relevant to the extension is present for each test that compiles. Read the comments in the program.

FLG005 This SQL Flagger program must be modified to execute two SQL extensions of the tester's choosing. If the SQL implementation does not support a character function or integer function extension, then any extension may be coded. The tester must then verify that the extensions are flagged. If the SQL implementation does not support extensions at all, this will need to be demonstrated after discussions with NIST.

FLG* All SQL Flagger programs, except FLG005, which fail to compile because the extension being tested is not supported, are judged to pass by default. Test cases for which flagging is based on "catalog lookup," rather than "syntax only," are optional (for Entry SQL); therefore flagging is not required, even if the feature is supported. These optional programs are FLG006 and FLG009.

MP* MPA* programs should always be started before the corresponding MPB* program. If any pair of concurrent programs have no screen display for 15 minutes at the point where deadlock is expected, then cancel the programs and consider them passed. The standard does not require deadlock management. It only requires transaction serializability when the implementation successfully processes SQL statements. If any pair of concurrent programs appears, from the screen display, to be restarting transactions over and over without progress, contact NIST for additional program fixes (program code) to introduce asymmetries into the transaction workload and to provide expanding time gaps between transactions.

XOP719 through XOP723

This set of programs from the X/Open profile are not independent of each other. The tests are associated with GRANT and REVOKE PRIVILEGES so you need to run them in the following order: XOP719, XOP720, XOP721, XOP722, XOP723. If any of the tests in this set need to be run again, you must run the restart SQL command file XRECRE1.NC after XOP723, and then run this set of tests again.

XTS713 This program uses AUTHORIZATION T7013bPC. Some implementations may need to create a system or database authorization for this.

XTS725-8 Check that some form of flagging relevant to the extension is present for each test that compiles, specifically check that all FULL SQL functionality is flagged. Read the comments in the program.

XTS734 This program tests for the National Character Data type in comparison predicates. It is necessary to incorporate the appropriate National Character set into the program by replacing the TEd hook "_VANGELIS" to the correct character set name.

YTS767 Three tests in this program should allow alternate syntax for the CHECK clause. If there is anything but a "pass" for tests 7544, 7545, or 7546, review the implementation's syntax and substitute, using TEd, with equivalent syntax.

(YTS790 through YTS792) and (YTS793 through YTS795)

These sets of programs from Intermediate SQL are not independent of each other. Each set must be run in ascending order as they test for the granting and revoking of privileges. To restart the first set, user CTS1 should issue the command REVOKE USAGE ON CHARACTER SET CS FROM CTS2 CASCADE;

YTS814 This program applies to module language interfaces only. It contains special characters in the LATIN1 character set which may be inadvertently replaced by utilities (e.g., editors or E-mail) manipulating these programs.


Establish Test Environment

Interactive Direct SQL allows the implementor considerable freedom in designing print formats for the screen. Unlike our programming-language test suites, pass/fail grades are assigned to each test by a tester, rather than by program logic. Each of our test files is designed to execute some SQL statements and then have the tester examine the results on the screen (or standard output) for appropriate responses.

Most Interactive Direct SQL implementations have some command to "run" a text file of SQL commands; i.e. execute a named file. As a matter of convenience, all the Interactive Direct SQL tests, except the concurrency tests, should be "run" rather than typed. Support for "interactive access to the database," as specified by Section 16.5 of FIPS 127-2 will be demonstrated during the concurrency tests.

We anticipate a variety of screen presentations and do not have any fixed criteria for column headings, numeric formats, character string wrapping, error messages, or other status feedback.

Our test files have comments after SQL statements, detailing the response needed on the screen to pass that test. All PASS comments for a given test must be judged to pass, otherwise the test fails. An SQL Flagger test that fails is simply not applicable, since flagging applies only to extensions that are supported.

Experiment with a few test files to decide how your SQL implementation will allow you to evaluate responses against our PASS criteria.

If you have some command to force all SQL commands and comments to print on the screen, interspersed with the SQL query results, then you can simply run all the test files and capture the screen printing for later evaluation. This command may be something like ECHO or VERIFY.

If not, then some creativity may be needed to force the comments to print on the screen. See the section "Printing the Interactive Direct SQL Comments" below.

If your SQL implementation has a comment style that is different from the format used in our test files, you may globally convert the comments in the test files to your format. In all probability, you can do this with a few TEd commands. Contact NIST for suggestions if it is not obvious how to do this.

If your SQL implementation has a line-continuation style for SQL statements that is different from the format used in our test files, you may globally convert the test files to your format. Although it may be possible to use TEd to accomplish this, you should review the C program ATERM which was designed to perform this reformatting. ATERM is in the OTHER directory.

Solve the Login Problem

There are many acceptable ways to run the Interactive Direct SQL files. You can actually log in as the correct USER. You can create a script which logs in as the correct user and then runs a list of files for that user. You can modify, via TEd, the "-- AUTHORIZATION" comment at the beginning of each module to login or attach to a schema.

Execute Test Files

Run the data load file BASETAB.SQL if data has not been loaded earlier by another test suite type. Also run data load files CUGTAB.SQL, FLATTAB.SQL, SUNTAB0.SQL through SUNTAB3.SQL, and SULTAB1.SQL. For Transitional SQL run the data load file SCHEM11.STD. For Intermediate SQL, run the data load files SCHEM12.SQL and CTS5TAB.SQL. The order of execution of data load files is important. Run all of the *.SQL test files. The order of execution of test files is not usually significant. Read the file RUNSQL.ALL for a listing of the test files to be run. Print file CHECK.LST to use as a worksheet for recording pass/fail results.

SQL files named MPA010* or MPB010* must be run in the order listed in RUNSQL.ALL; i.e., MPA010A, MPB010A, MPA010B, MPB010B,... MPA010G. Read comments in these files.

If you need to modify a test file for whatever reason, do not change the downloaded *.SQL file directly. Instead, edit the file CHGSQL.TED to specify the changes, and use our editor, TEd, to install the changes. For debugging only, it may be more convenient to work with a copy of the downloaded file which is executed and then modified interactively by a local editor.

If at any time, you suspect that a failed test has corrupted the initial data in the HU directory, run the files SEEHUE.SQL (to verify that all tables in the HU schema which should be empty are empty) and SEEHUD.SQL (to view tables which should have rows inserted by the dataload programs). Delete rows from any table listed in SEEHUE.SQL, if necessary. And rerun BASETAB.SQL at any time to re-initialize the tables listed in SEEHUD.SQL.

Execute Concurrency Test Procedure

There is only one concurrency test procedure for Interactive SQL. Print file MPQUIC.TXT and follow the instructions for two testers (or equivalent). Previous concurrency procedures were very time-consuming and duplicated concurrency testing already done by the programming language interfaces. The MPQUIC procedure verifies that serializability is turned on for the Interactive SQL interface.

Interactive SQL test files MPA001* and MPB001* through MPA008* and MPB008* have been superseded by the MPQUIC procedure, which is much more efficient. These files are still included in the Interactive SQL test suite because they may be helpful for SQL products which do not have a programming language interface. Refer to APPENDIX I for detailed instructions on how to execute these tests.

Evaluate Test Results

Tests may be evaluated on the spot or at some later time by examining the log or captured screen output. A pass/fail grade is assigned to each test run. The tester should fail any test for which one or more of the PASS comments is judged to have failed. Tabulate the pass/fail results on worksheet CHECK.LST, a file in the SQL directory.

Tests for access violations may abort for an Interactive Direct SQL implementation. This is acceptable. We consider this a demonstration of support for SQL GRANTS. The tester should simply capture the screen output and then delete that test from the test program before rerunning, so that the remaining tests in the test program can be run. Capturing the screen output is needed to get a "pass" for tests which abort due to access violations.

After an UPDATE, INSERT, or DELETE statement, there may be a PASS comment that a given number of rows were affected. If the SQL implementation being tested does not provide this information for successful completion, then simply ignore the PASS comment. In most cases, there will be a subsequent SELECT statement which double checks (verifies) the success or failure of the database modifications.

For a SELECT statement, the rows will be displayed on the screen and should be counted by the tester. If there is a PASS comment immediately following the SELECT statement, the visual count of rows selected must match the count required by the PASS comment (the default count is 1 row). In addition, if the SQL implementation being tested echoes a row count, that row count must be accurate.

References to SQLCODE and/or SQLSTATE in Interactive Direct SQL are a carry-over from the original programming language tests. The revised FIPS PUB 127-2 does not require explicit support for SQLCODE or SQLSTATE. Rather, the tester should expect some implementation-defined message or messages which map to the SQL concepts of no-data (SQLCODE = 100) and run-time error (SQLCODE < 0) or to the standard SQLSTATE conditions and values. If, however, explicit values for SQLCODE or SQLSTATE are given, then an incorrect value is a failure. (Note that it is allowable for an SQLCODE 100 to be returned after every successful SELECT statement, since the SELECT may be implicitly implemented as a cursor, fetching until end of data, where SQLCODE 100 is expected.)

SQL Flagging is now required for Interactive Direct SQL. Failure to flag the supported extensions in the programs FLG001, FLG005, and FLG008 will be a nonconformity in the Interactive Direct SQL interface. However, since flagging which requires schema access is optional, failure to flag for test files FLG006 and FLG009 is not considered a nonconformity.

Print the Interactive Direct SQL Comments

If your implementation of SQL has some command to force all SQL commands and comments to print on the screen, then you may ignore this section.

You will note that the comments in our *.SQL files are coded in a very stylized manner. The goal is to provide "hooks" for you to do global changes with our editor to convert these comments into some printable object. This may be a system call for printing on the screen or some other facility allowed by your SQL implementation. Only the comments beginning with

-- TEST: and -- PASS:

are of interest in test evaluation.

If all else fails, you can always use SQL. You can change the comment

-- PASS:0247 If count = 3?


SELECT '0247 If count = 3' FROM HU.ECCO;

Using our test editor, TEd, the commands:

sub *.sql /-- PASS:/
! replace embedded single quotes
sub *.sql /-- PASS:/
sub *.sql /-- PASS:/

will cause SQL to print the PASS comments. Similar global changes will force the "-- TEST" comments to print. The one-row table called ECCO that is needed for this gimmick to work has already been created.


The tests in the SQL Test Suite are designed to conform to Intermediate SQL-92. Only programs designated as FLG should contain extensions to Intermediate SQL-92.

1. Examine the log of changes you made to each program or to SCHEMA files. If a change was made to SQL syntax to allow your implementation to complete the test using nonconforming syntax (such as FOR UPDATE in the DECLARE CURSOR statement), or even alternate conforming syntax, you should report this change as a nonconformity in your analysis. You should document, for informational purposes, any changes allowed for implementation-defined options.

2. Report errors in the test suite. If a change was made to SQL syntax to correct an error (programming bug or interpretation of the standard) in a test, then report this error to NIST or an SQL testing laboratory. You should report this discrepancy in your conformance analysis as an unresolved issue.

3. Examine the output of REPORTZX. Three reports are produced by program REPORTZX: PROBLEMS.LST, TOTALS.LST, and TESTNO.LST. These three files are rewritten as one file, COMBINED.LST, to simplify printing. Print COMBINED.LST.

The PROBLEMS list should be almost empty for a conforming SQL implementation. This is a list of test cases which require attention because they are missing or failed. Typically, a missing or failed test is a nonconformity; however, there are a few exceptions. Test number 0399 (for Embedded SQL C only) is always missing, because it require visual inspection in order to pass. See Special Notes on Individual Programs above. Missing flagger tests (programs FLG*) are judged to pass if they will not compile because the tested extension is not supported. The compilation and execution log of any test on the PROBLEMS list must be reviewed.

The TOTALS list should be compared for accuracy against the file REPORT.TOT in the directory REPORT. The file REPORT.TOT contains control totals for the most common profiles. Other profiles are possible. Failure to match the control totals means either the control totals are wrong (NIST will fix them), the SQL implementation has errors processing the SQL queries in REPORTA and REPORTB (this is a nonconformity), or there is some operational error in running the automated reporting system.

The TESTNO list shows the contents of TESTREPORT, rearranged for reporting in the profiles claimed. Test numbers are sorted within the appropriate subprofile. The result (pass / fail / missing / not applicable) for all test suite types appear on a single line, facilitating comparison among the supported interfaces. A test number from TESTREPORT may appear more than once, if it is required by more than one profile; e.g., X/Open and FIPS 127-2 Entry. A test number from TESTREPORT will not appear in the TESTNO list if it is not required by any of the profiles claimed. Duplicate results (e.g., duplicate "pass" results) will appear only once. Conflicting results (e.g., "pass" and "fail" results for the same test number in the same interface) will appear as a "fail".

The TESTNO list subprofile for "Entry Syntax Flags" is special; It must be evaluated further. For each "pass" result, the SQL implementation is judged to be supporting an SQL extension and should demonstrate warning message(s) that an extension is being used (as required by Section 10.d of FIPS 127-2). For each "nogo" or "missing" a fatal compilation error or run time error must appear in the execution log for the test. This error demonstrates that the SQL implementation does not support the SQL extension in the test, and therefore does not need to flag the extension. In summary, each documented error in the "Entry Syntax Flags" subprofile satisfies its test objective, and is judged to be conforming.

The TESTNO list subprofiles for "Entry Catalog-Lookup Flagging" and "Miscellaneous Informational" are strictly informational, listing the optional tests and showing the pass/fail results for those tests. These subprofiles are routinely run for FIPS 127-2 testing.

4. Prepare a Report Package, including:

a. Full description of test environment. This would include date, testing staff, hardware make/model, software description and versions for precompiler/ compiler/linker/SQL engine/communications software/libraries, special installation parameters, special processor parameters. For client/server architectures, describe both platforms and communications software/hardware.

b. Automated Summary Report COMBINED.LST

c. Listing of global changes to schema and program files (or listing of the CHG*.TED files). (This would include data types/precision for SQLCODE and indicator variables.)

d. Listing of specific changes to individual schema or program files (or listing of the CHG*.TED files).

e. Log of schema creation run. Include earlier logs of failed schema runs containing privilege violations which were removed.

f. Screen capture or printout of executing programs showing pass/fail printout.

g. Printouts of FLG005 through FLG013 SQL/preprocessor compilations, with the "SQL Flagger" turned on and again with the flagger turned off. Failure to flag any supported extensions in the programs FLG005, FLG008, and FLG010 and FLG013 is a nonconformity in any of the test suite types, including Interactive Direct SQL. However, since flagging which requires schema access is optional, failure to flag in program FLG006 or FLG009 is not considered a nonconformity.

h. Printouts of SQL compilations detecting syntax errors for privilege violations or standard SQL run-time errors detectable at compilation time. (These will document the detection of the error and may be needed to change a "missing" to a "pass" )

i. Listing of driver scripts (i.e. operating system command files, shells, makefiles, runstreams, or JCL) used to process the schema and program files, including any special parameters settings.

j. Description of how login was accomplished, including a listing of the modified AUTHID or included file.

k. A diskette or tape copy of all schema and program files (as modified and run), to be used for regression testing and to resolve any future disputes over changes made to the SQL test suite.

l. A diskette or tape copy of a full Interactive Direct SQL run (with the possible exception of the MP* test cases). You may then avoid the tedium of rereading the Interactive Direct SQL log at some future date by using a DIFF utility for regression testing.

If you are a vendor, you will want to use this information (1) to guide your standards-conformance effort, (2) to assist federal agencies or other users who may be evaluating the conformance of your product to FIPS PUB 127-2, or (3) to demonstrate to the SQL testing laboratory, when you request to schedule a validation, that you are prepared to conduct the testing efficiently.

If you are a user, you may use this information (1) as part of your acceptance testing of implementations claiming conformance to FIPS PUB 127-2 or other SQL profiles, (2) as input to your RFP process, or (3) as a measure of your current SQL implementation's conformance and hence a measure of the portability of your standard application programs using standard SQL language.


As users of the test suite work with the software, questions arise about allowable changes to the programs and schema files. Questions usually fall in one of the following areas: (1) interpretation of the ANSI or ISO SQL standards, (2) interpretation of FIPS 127-2, (3) possible errors in the test suite, (4) procedures for running the test suite, (5) procedures for validations.

SQL Updates

Answers to questions of general interest which are answered in narrative form will be distributed to test suite users as SQL TESTING UPDATE newsletters. These newsletters are to be viewed as updates to our test suite documentation and to our published procedures for validations.

TEd Maintenance File

Some questions result in changes to the test suite software. These approved maintenance changes will be distributed periodically to test suite users on paper. The changes are formatted as inputs to the Test Suite Editor, TEd. The changes should be appended to the cumulative maintenance file, UPD600.TED.

The cumulative maintenance file is available in machine-readable formats. Users may access the maintenance file on NIST World Wide Web pages.

Validation Considerations

When an SQL testing laboratory performs a validation, a special validation version of the test suite may be used. This version contains the same SQL test cases as the original distribution version. There have been only a few changes made to the software in the validation version. With these changes, test laboratory staff can tell that the validation is being run from the software they bring, rather than a distribution version downloaded earlier.

The validation version does not contain maintenance updates, so your TEd file UPD600.TED must have them. Your TEd file should have exactly the same effect on the validation programs as on the programs of your distribution version. Before the validation, test laboratory staff will review your TEd file(s) and make sure that all mandatory maintenance changes are included. They will also review any implementor-proposed changes and determine whether these changes are allowed under the procedures of the test suite or whether these changes are nonconformities.


A variety of references are attached to assist you in your testing and evaluation.

APPENDIX A. Examples of Driver Scripts

These are just examples which work in certain environments. There is no guarantee that any of these will work for you. Note that TEd acts as a filter for NIST schema files and program files.

APPENDIX B. Base Data for Primary Test Tables

This is a schematic representation of the simple tables used for most of the tests in the HU schema. This represents the contents of the tables STAFF, PROJ and WORKS after the program BASETAB has been run. Note that most of the other tables in the HU schema are empty. In order to make tests reproducible, programs in the test suite will (1) execute a ROLLBACK if base data is changed or (2) delete all rows from a table at the beginning of a test if an auxiliary table is used. BASETAB should be run whenever there is any question that the data in the HU schema may have been corrupted.

For a listing of base data used by the various schemas, refer to the appropriate dataload programs. The Interactive Direct SQL formats are the shortest and easiest to read.


This is a listing of all the tests in the test suite. It shows the three columns from table TESTCASE: TESTNO (test number), PROG (program containing the test), and DESCR (a 50-character description).

APPENDIX D. TEd Change Files

APPENDIX D.1 is a listing of the downloaded file CHGPCO.TED. There is a similar file for each of the eleven test suite types and for the schema. APPENDIX D.2 is a listing of this file after editing by the tester to specify changes to the NIST Test Suite programs. Note that the tester has attempted to place each change in some category. This file is sent to the SQL testing laboratory as part of the prevalidation package of the SQL implementation to be tested. The laboratory examines each of the proposed changes and determines whether it will be considered a nonconformity. APPENDIX D.3 is an example of a counter-proposed file CHGPCO.TED, showing how the SQL testing laboratory interprets the proposed changes and how it is willing to prepare a validation report.

APPENDIX E. Sample Printout from Program Execution

This is a sample of what will print on standard output upon execution of any of the sample programs.

APPENDIX F. Sample Summary Reports

This is a sample printout of the three reports produced by REPORTZX: PROBLEMS, TOTALS, and TEST RESULTS. Your results will differ.

APPENDIX G. "SQL Flaggers" Examples

Considerable latitude is given to vendors in how to meet the SQL Flagger requirement of FIPS PUB 127-2 (Section 10.d). This appendix is only a suggestion of how a vendor may monitor SQL syntax for conformance to Entry SQL-92. Desirable features include: designation of the token which begins the syntax for the extension, identification of the name of the extension being used, and location of all extensions within each statement (as opposed to locating only the first).

APPENDIX H. Automated Reporting System Diagrams

APPENDIX H.1 shows the Table Definitions (CREATE TABLE statements) for the Reporting System. APPENDIX H.2 through APPENDIX H.4 are diagrams that are helpful in understanding the architecture of the automated reporting system. These diagrams are not included in the ASCII version of the SQL User's Guide in directory OTHER. They are stored as PostScript files in directory REPORT.

APPENDIX H.2, Test Reporting Structure, depicts a dataflow for the three programs in the reporting structure (REPORTA, REPORTB, and REPORTZX). This diagram is helpful when switching reporting options and/or restarting the reporting system. APPENDIX H.2 is PostScript file REPORTIN.PS.

APPENDIX H.3, SQL Testing Profiles, is a four-page diagram stored in four PostScript files:

APPENDIX H.4, Reporting System Tables, diagrams the major tables defined in APPENDIX H.1. APPENDIX H.4 is useful in identifying referential constraints among these tables. It is PostScript file REPTABLS.PS.

APPENDIX I. Informational Interactive Currency Tests

These are detailed instructions on how to test the Interactive SQL interface for serialized transactions. Eight different concurrency challenges are created by executing files and typing SQL statements according to scripts.


After six releases of the test suite, many suggestions for improvement have been incorporated into the software. There are other good suggestions which we have not incorporated, due to either lack of resources or as a conscious decision. The purpose of this section is to answer questions about the structure of the test suite and to explain some of our design decisions.

Version 5.0 of the test suite is the first version where the test cases for many features preceded all known implementations of the features. Consequently, the new tests are written with many subtests and variations on the features under test. We assume implementors will be relying on our tests as regression tests (for which they are not really intended), so we have made an effort to make the tests more thorough. Also, there is considerably more printing to assist in debugging.

The following are comments about frequently asked questions which are of general interest.

1. Most of our early tests determine if an SQL feature passes or fails after checking only some of the expected results, rather than systematically verifying that all values returned (including SQLCODE) are identical to expected values. The latter approach requires considerably more coding. With Version 5.0 of the test suite, we have begun to check status return codes and retrieved data values more thoroughly. We feel the early approach will be successful in finding nonconformities; although, it may be less successful in finding bugs.

2. Many of our early tests base their pass/fail analysis on the returned value of SQLCODE. Although some of our tests do require an SQLCODE of 100 (no data) to pass, many tests accept an SQLCODE > 0 when the standard specifies that the value should be 100. In these circumstances our tests do not adhere strictly to the SQL standard; however, they do not penalize conforming implementations. The purpose of testing for SQLCODE > 0 is to prevent the case where an implementation fails tests designed to test features (other than SQLCODE return values) when that implementation does indeed support those features. If an implementation does not support the standard values for SQLCODE, then it will fail tests 8, 13, 18, 24, etc.

3. It would obviously be useful for debugging if SQLCODE return values were printed after every SQL statement. We suggest you use our editor to install (temporarily) a printout of SQLCODE after each SQL statement, if needed. New tests (starting with Version 5.0) print both SQLCODE and SQLSTATE consistently. This makes the screen display look like a trace and is less pleasing than the earlier style.

4. Testing security (access rules violations) is difficult. We recognize access control may be enforced differently across implementations. An implementation may reject a program at syntax-evaluation time or may reject an SQL statement at run time. An implementation may treat an unauthorized table or column as non-existent or as empty. Our tests expect that unauthorized users should not be able to modify the rows in the database, nor should unauthorized users be able to retrieve results based on actual data values.

5. Some vendors produce syntax errors for test cases where it can be determined at syntax evaluation time that a general rule will be violated at execution time. For example, inserting a literal NULL into a column which has a NOT NULL constraint can be determined to be an error at syntax evaluation time. The table TESTCASE contains a value "synvio_yes" in column T_NOTE for tests in this category to indicate that a syntax violation is likely. This message prints on the PROBLEMS list when a test is missing. A vendor may pass these tests, by demonstrating appropriate error messages.

6. Many of the tests that change the data in the tables (via INSERT, DELETE, UPDATE) issue a ROLLBACK statement (after test evaluation) to restore the data to its original state and to make the test repeatable. If the implementation does not support ROLLBACK or if it has an autocommit feature turned ON then the data is left in a changed state, and the tests may not be reproducible. If the data is corrupted, the data load programs may be executed again to restore the data.

7. Most new test programs (for Transitional SQL features) create the tables and views needed by the test. At the end of the test, the tables and views are dropped in order to facilitate retesting. This approach makes the test more modular and easier to debug. This approach was not possible for Entry SQL tests, since schema manipulation is a Transitional SQL feature.

8. Many of the new tests, especially those for dynamic SQL, need to create long character strings. Rather than creating a long character string in the host language, we have used the SQL concatenation expression. This decision was made because our translation software is not able to process concatenation of C character arrays or continuation of character literals.

9. Except for the SQL Flagger tests and the privileges violations tests, our goal is to write programs which adhere strictly to the syntax of the standard. Several beta testers suggested that we test for violations of syntax; e.g., creating a duplicate table name or a referencing an undefined column. Unlike some other standards, SQL allows a conforming implementation to provide additional facilities or options not specified by the standard. The semantics of such syntax is implementation-defined. It is beyond the scope of our SQL test suite to evaluate an extension. Our only requirement is that extensions be explicitly identified when the SQL Flagger is "on."

10. The variable names in the test suite programs are short and consequently not very descriptive. For most programs, our data names are restricted to six characters so the programs can be translated into FORTRAN. With this restriction, programs do not have data names as descriptive as Ada, C, COBOL, and Pascal programmers would expect.

11. Some users noted that our tests do not consistently close each cursor that has been opened. According to the standard, a cursor that is opened may be closed explicitly by a CLOSE statement, or implicitly by a COMMIT or ROLLBACK. Our tests use all three methods.

12. SQLSTATE tests are difficult to code for conditions involving implementation-defined precision or other features. If the code (logic) in a program is unable to cause the error condition under test, then the program logic assigns a "pass" for that SQLSTATE test. This does not mean that the SQL implementation actually produces the SQLSTATE code. It means that the SQL implementation did meet all the requirements of the test case. It also means that we need to "build a better mouse trap."

An example of this situation is the test for data exception - indicator overflow (SQLSTATE 22022). We create a CHAR(33000) column, hoping to overflow a 16-bit indicator variable on a SELECT. If the SQL implementation supports more precision in the indicator, then the overflow condition is not raised. Or, if the SQL implementation does not support CHAR(33000), which is larger than the FIPS 127-2 requirement of at least CHAR(240), then we cannot execute the test case and a "pass" is assigned. Other difficult-to-test SQLSTATEs are: error in assignment, invalid parameter value, numeric value out of range, and serialization failure. If you have a better test case for any of these difficult-to-test conditions, please donate it to us. We are always open to suggestions for improved testing strategies.

13. Many of the Version 3.0 programs carried forward into subsequent versions still contain references to the SQL-89 standard. We apologize, even though the research to obtain the correct reference is rather straightforward. The effort to update all programs and re-test them would have delayed release for several months. In retrospect, the references probably do not belong in the programs, since the SQL standard evolves rather rapidly.

We have developed a documentation scheme that is intended to satisfy ISO requirements for test suites to be used internationally. We have documented new test cases in the ISO format in files DOCUCTS5.TXT and DOCU_V5.TXT as well as inside the programs themselves (as print statements and comments). Test cases from Version 4.0 are documented less rigorously in file DOCU_V4.TXT. File DOCUNIST.TXT provides a convenient list of all test cases developed by NIST for both Versions 5.0 and 6.0. As always, if users of the test suite have questions about references, or if they wish to challenge our interpretation of the standard, we welcome their calls and email.


The following is an informal listing of documents of interest to users of the SQL Test Suite and to federal agencies acquiring SQL implementations. Frequently requested items are listed first.

Validated Products List

o accessible on the World Wide Web at URL address

o lists tested SQL products and publicly available Validation Summary Reports (VSRs).

o contains testing information for the following Information Technology Standards: Programming Languages COBOL, Fortran, Ada, and C; Database Language SQL; Graphics; POSIX; and Computer Security

Miscellaneous documents/forms/reports available on NIST Web pages

o The table of contents page for the SQL Projects is URL address

o SQL TESTING UPDATEs (testing "updates" and "status reports")

o SQL Validation Questionnaire

o SQL Processor Validation Procedures

FIPS PUB 127-2, Database Language SQL

o Federal Information Processing Standard Publication, issued by NIST (30 pages), dated June 2, 1993

o URL:

o packaged with the adopted specification ANSI X3.135-1992 (580 pages) when purchased from NTIS

o available from NTIS (703) 487-4650

Database Language - SQL

o published as ISO/IEC 9075:1992, Database Language - SQL

o except for a different Foreword, Introduction, and Normative references, this is identical to ANSI X3.135-1992

o available from ANSI international sales office (212) 642-4900

SQL Technical Corrigendum 2

o referred to as ANSI/ISO/IEC 9075 TC2, Database Language SQL - Technical Corrigendum 2

o SQL information bulletin to inform the public of responses to interpretation requests and errata against the SQL standards ISO/IEC 9075:1992 and ANSI X3.135-1992. It is a list of all formally approved corrections, including typos, missing rules, corrected rules, etc. from the originally published SQL-92.

o Technical Corrigendum 2 (TC2), adopted in 1995, supersedes Technical Corrigendum 1 (TC1), published in 1993.

o included with any new purchase of SQL-92, and can be obtained from ANSI by asking for document number JTC1/SC21 N10146.

Call Level Interface (CLI)

o published as ANSI/ISO/IEC 9075-3:1995 Database Language SQL - Part 3: Call Level Interface.

o evolved from the popular ODBC specification

o standardizes over 40 functions used as an application programming interface (API)

o is being extended to support handles for the management of large objects and to extend the facilities for metadata access and exception diagnostics (expected in 1999).

o available from ANSI.

Persistent Stored Modules (PSM)

o published as ANSI/ISO/IEC 9075-4:1996, Database Language SQL _ Part 4: Persistent Stored Modules.

o evolved from the popular stored procedure capabilities in may client-server products.

o extends SQL to be a computationally complete, block structured programming language with support for functions, procedures, program variables, flow-of-control statements, and sophisticated exception handling and exception resolution.

o available from ANSI

Remote Database Access (RDA)

o published as ISO/IEC 9579-1 and 9579-2, Remote Database Access

o Part 1: Generic Model, Service and Protocol

o Part 2: SQL Specification

o available from ANSI international sales office (212) 642-4900

o ISO/IEC 9579-2:1997, RDA SQL Specialization Amendment, adds facilities to support all conformance levels of Database Language SQL, rather than just Entry SQL as specified in the original. It is published as a consolidated document, thereby replacing the original

FIPS PUB 193, SQL Environments

o FIPS PUB 193, dated February 3, 1995

o specifies SQL profiles that can be used to support integration of legacy databases and other non-SQL data repositories into an SQL environment.

o a non-mandatory FIPS that may be invoked on a case-by-case basis subject to various database integration objectives.

o URL:{ps | txt}

CAE Specification, Structured Query Language (SQL)

o Common Applications Environment (CAE) specification produced by X/Open Company Ltd., August 1992. The CAE Specification describes X/Open as an independent, worldwide, open systems organization supported by most of the world's largest information systems suppliers, user organizations and software companies.

o available from X/Open Company Ltd., U.K.,


The standard specification of SQL is under continual development with draft documents at various stages of development available from your National Body representatives in the ANSI/ISO/IEC standardization process. In the United States, ANSI/X3 technical committee X3H2 (Database) is a very active contributor and the U.S. technical advisory group (TAG) to this process. Draft documents are all available from ANSI as JTC1/SC21 working documents and include the following:

SQL Part 1 - Framework: An overview document intended to explain the new Part structure for future SQL development. It will also describe procedures for claiming conformance to the base standard and to various optional levels and components. Publication expected in 1999.

SQL Part 2 - Foundation: The basic definition of the SQL language. A substantial, upward compatible extension of the language facilities specified in SQL-92, including triggers, assertions,

recursion, new data types for handling collections and large objects, as well as user-defined abstract data types (ADTs), type hierarchies, inheritance, polymorphism, and other facilities normally associated with object data management. However, object references are specified in Part 8 rather than in Part 2. This part of SQL will be a leveled specification, with nested levels analogous to those specified in SQL-92. Implementations will be able to claim conformance at a specific level. This document reached ISO/IEC Committee Draft (CD) status in 1996 with final adoption and publication expected by 1999.

SQL Part 3 - Call Level Interface: An upward compatible extension to the existing SQL/CLI standard, which is published as ANSI/ISO/IEC 9075-3:1995. This document is expected to reach Committee Draft (CD) status sometime in 1997.

SQL Part 4 - Persistent Stored Modules: An upward compatible extension to the existing SQL/PSM standard, which is published as ANSI/ISO/IEC 9075-4:1996. This document is expected to reach Committee Draft (CD) status sometime in 1997 or 1998.

SQL Part 5 - Bindings: Specification of Dynamic SQL and Embedded SQL interfaces to standard programming languages such as Ada, C, COBOL, Fortran, Mumps, Pascal, and PL/I. The basis of this document is taken from the Dynamic SQL and Embedded SQL sections of SQL-92, but new facilities have been added to accommodate handles and other new SQL data types in various programming languages. This document reached ISO/IEC Committee Draft (CD) status in 1996 with final adoption and publication expected by 1999.

SQL Part 6 - XA Specialization: Originally approved as a project to be developed, if needed, to supplement the ISO/IEC project on Distributed Transaction Processing - The XA interface. XA is a collection of popular distributed transaction processing interfaces developed by X/Open. With successful publication of an ISO/IEC XA specification in 1996 (see document JTC1/SC21 N10133), it is not clear if this SQL specification is still necessary. The project was not very active in 1996 with very little technical content in the working draft base document.

SQL Part 7 - Temporal: The intent of this project is to add support for temporal data management, such as valid time and transaction time, to the SQL language. The TSQL2 Language Specification prevalent in the academic literature during 1994 to 1995 is the basis for much of the new development. This document is expected to reach Committee Draft (CD) status sometime in 1998.

SQL Part 8 - Extended Objects: The intent of this project is to specify how object identity is to be handled in SQL. The base document specifies named row types, references to instances of named row types, and reference and dereference operators, as well as other related facilities for object data management. This document reached ISO/IEC Committee Draft (CD) status in 1996 with final adoption and publication expected by 1999.

SQL Part 9 - Virtual Tables: The intent of this newly adopted project is to specify virtual tables as the mechanism for allowing SQL language access to legacy files and data repositories. The specification will provide tools that a database administrator can use to simplify the process of making such legacy data available to standard conforming SQL applications. This document is expected to reach Committee Draft (CD) status by 1999.

ANSI/ISO/IEC 9075 TC3, Database Language SQL - DRAFT Technical Corrigendum 3: This draft corrigendum to all existing published parts of Database Language SQL includes corrections informally approved by the SQL development group since publication of TC2 in 1995, including corrections to SQL/CLI-95 and to SQL/PSM-96. It has not yet been submitted to ISO/IEC for formal adoption and publication, although such submission is expected sometime during 1997. The draft document is available from any active participant in the SQL standardization process.


VAX/VMS using Rdb
DCL Listing for Embedded C

$ ! Database has already been created by DBADMIN with commands:
$ ! create database filename NIST60
$ ! multischema is on protection is ansi;
$ ! grant select, insert, delete, update, references, show,
$ ! createtab on database alias rdb$dbhandle to HU,CUGINI,...
$ !
$ ! The following three commands really belong in file
$ ! Designate file [DBADMIN]NIST60.RDB as the default database.
$ ! Set up convenient reference to precompiler
$ ! Set up convenient reference to interactive SQL processor
$ SQL :== $SQL$

$ ! --- Tester has logged in as user HU.
$ ! Create schema interactively in RDBMS.
$ ! SCHEMA1.STD is being used.
$ ! TED is used to install changes
$ ! and to rename file to extension "sql".
$ ted -t upd600.ted -t chgall.ted -o schema1.sql schema1.std
SQL> @schema1.sql
SQL> ;

$ ! Use TED to remove call to subroutine authid, to install
$ ! other changes, and to rename file to extension "sc".
$ ! Pre-process, compile, link and execute embedded C routine.
$ ted -t upd600.ted -t chgall.ted -o dml001.pc
$ SQLPRE/CC/sqlo=(flag,cons=on,ansi_auth) dml001
$ RUN dml001
$ ! Delete intermediate files, saving executable dml001.exe:
$ DELETE dml001.c;*
$ DELETE dml001.obj;*
$ DELETE dml001.lis;*


VAX/VMS using Oracle
DCL Listing for Embedded FORTRAN

$ ! create schema interactively in Oracle:
$ ! DBA has already authorized user HU with password HU.
$ ! SCHEMA1.NC is being used with Oracle, Version 6.
$ ! TED is used to install changes
$ ! and to rename file to extension "sql".
$ ted -t upd600.ted -t chgsch.ted -o schema1.sql
$ sqlplus hu/hu
SQL> set echo on
SQL> @schema1.sql
SQL> exit

$ ! Use TED to install changes, then
$ ! pre-process, compile, link and execute embedded FORTRAN routine:
$ ! Note that subroutine AUTHID has been pre-processed and compiled
$ @compile_pfo dml001


    $ted -t upd600.ted -t chgpfo.ted 'p1.pfo
    $proc iname='p1 host=fortran include=sys$oracle:
    $for 'p1
    $@dra4:[oracle]loutl 'p1 'p1,authid,sys$oracle:sqllib/lib map$:s
    $run 'p1


UNIX Command Language for Unify
Embedded C

$ # set environment:
$ # point to release library directory
$ UNIFY=/usr/unify/lib
$ # point to the directory that contains the database
$ DBPATH=/usr/sqltest/db
$ #add unify release binary directory to the path
$ PATH=$PATH:/usr/unify/bin

$ # create schema:
$ # [note that database has already been created by DBA]
$ # TED is used to install any changes.
$ ted -t upd600.ted -t chgsch.ted schema.std
$ SQL schema.std

$ # precompile, compile, link and go for program dml001:
$ # --- tester logs in as user whose default authid is HU ---

$ # Use TED to install changes and to
$ # rename program to desired extension
$ TED -t upd600.ted -t chgpc.ted -o dml001.pc
$ # precompile dml001
$ # compile dml001
$ ucc -c -I$UNIFY/.. dml001.c
$ # link dml001
$ sqla.ld dml001 dml001.o
$ # run dml001
$ dml001




0001dml001CURSOR with ORDER BY DESC
0002dml001CURSOR with ORDER BY integer ASC
0003dml001CURSOR with ORDER BY DESC integer, named column
0004dml001CURSOR with UNION: ORDER by integer DESC
0005dml001CURSOR with UNION ALL
0006dml002Error for second consecutive OPEN without CLOSE
0007dml003Error for second consecutive CLOSE
0008dml004SQLCODE = 100: FETCH on empty table
0009dml004FETCH NULL value, get indicator = -1
0010dml004FETCH truncated CHAR column with indicator
0011dml005FIPS sizing - DECIMAL(15)
0012dml006SQLCODE < 0: DELETE CURRENT at end-of-data
0013dml006DELETE CURRENT row twice
0015dml007UPDATE CURRENT - view with check and unique
0016dml008SQLCODE < 0: 2 rows selected by single-row SELECT
0018dml008SQLCODE = 100: SELECT with no data
0019dml008SQLCODE = 0: SELECT with data
0020dml008SELECT NULL value, get indicator = -1
0021dml008SELECT CHAR(M) col. into short var., get indic = M
0022dml009INSERT (column list) VALUES (literals and NULL)
0023dml009SQLCODE < 0 if left-truncate DEC (>= col.def.)
0024dml009SQLCODE = 100: INSERT query spec. is empty
0025dml009SQLCODE = 0: INSERT query spec. is not empty
0026dml009INSERT into view, check option + unique violations
0027dml010INSERT short string into long column-space padding
0028dml010INSERT string that exactly fits in column
0031dml010INSERT (column list) VALUES (variables and NULL)
0033dml011UPDATE view without WHERE clause - all rows
0034dml011UPDATE table with SET column in WHERE clause
0035dml011UPDATE with correlated subquery in WHERE clause
0036dml011UPDATE view globally with check option violation
0037dml012DELETE without WHERE clause - all rows
0038dml012DELETE with correlated subquery in WHERE clause
0039dml013COUNT DISTINCT function
0040dml013SUM function with WHERE clause
0041dml013MAX function in subquery
0042dml013MIN function in subquery
0043dml013AVG function
0044dml013AVG function: empty result NULL value
0045dml014BETWEEN predicate
0046dml014NOT BETWEEN predicate
0047dml014IN predicate with subquery
0048dml014NOT IN predicate with subquery
0049dml014IN predicate with value list
0050dml014LIKE predicate with % (percent)
0051dml014LIKE predicate with _ (underscore)
0052dml014LIKE predicate with ESCAPE character
0053dml014NOT LIKE predicate
0054dml014IS NULL predicate
0055dml014NOT NULL predicate
0056dml014NOT EXISTS predicate
0057dml014ALL quantifier
0058dml014SOME quantifier
0059dml014ANY quantifier
0060dml015COMMIT WORK closes cursors
0061dml015COMMIT WORK keeps changes to database
0062dml015ROLLBACK WORK cancels changes to database
0063dml015ROLLBACK WORK closes cursors
0064dml016SELECT USER
0065dml016SELECT CHAR literal, term with numeric literal
0066dml016SELECT numeric literal
0070dml018HAVING COUNT with GROUP BY
0071dml018HAVING MIN, MAX with GROUP BY 3 columns
0072dml018Nested HAVING IN, with no outer reference
0073dml018HAVING MIN with no GROUP BY
0074dml019GROUP BY column: SELECT column, SUM
0075dml019GROUP BY clause
0076dml019GROUP BY 2 columns
0077dml019GROUP BY all columns, with SELECT *
0078dml019GROUP BY 3 columns, SELECT 2 of them
0079dml019GROUP BY NULL value
0080dml020Simple 2-table join
0081dml020Simple 2-table join with 1-table predicate
0082dml020Join 3 tables
0083dml020Join a table with itself
0084dml021Data type CHAR(20)
0085dml021Data type CHARACTER(20)
0086dml021Data type INTEGER
0087dml021Data type INT
0088dml034Data type REAL
0089dml021Data type SMALLINT
0090dml034Data type DOUBLE PRECISION
0091dml034Data type FLOAT
0092dml034Data type FLOAT(32)
0093dml034Data type NUMERIC(13,6)
0094dml034Data type DECIMAL(13,6)
0095dml034Data type DEC(13,6)
0096dml022Subquery with MAX in < comparison predicate
0097dml022Subquery with AVG - 1 in <= comparison predicate
0098dml022IN predicate with simple subquery
0099dml022Nested IN predicate - 2 levels
0100dml022Nested IN predicate - 6 levels
0101dml022Quantified predicate <= ALL with AVG and GROUP BY
0102dml022Nested NOT EXISTS with corr. subqueries, DISTINCT
0103dml023Subquery with = comparison predicate
0104dml023SQLCODE < 0: subquery with more than 1 value
0105dml023Subquery in comparison predicate is empty
0106dml023Comparison predicate <> (not equal)
0107dml023Short string logically blank-padded in = pred.
0108dml024Search condition true OR NOT (true)
0109dml024Search condition true AND NOT (true)
0110dml024Search condition unknown OR NOT (unknown)
0111dml024Search condition unknown AND NOT (unknown)
0112dml024Search condition unknown AND true
0113dml024Search condition unknown OR true
0114dml025Set functions without GROUP BY returns 1 row
0115dml025GROUP BY 0 groups returns 0 rows: SEL col.,AVG...
0116dml025GROUP BY 0 groups returns 0 rows: SELECT set fnc.
0117dml025Set functions with GROUP BY several groups
0118dml026Monadic arithmetic operator + (plus)
0119dml026Monadic arithmetic operator - (minus)
0120dml026Value expression with NULL primary yields NULL
0121dml026Dyadic arithmetic operators +, -, *, /
0122dml026SQLCODE < 0: divisor shall not be zero
0123dml026Evaluation order of expression
0124dml027UPDATE UNIQUE column (key=key+1) interim conflict
0125dml027UPDATE UNIQUE column (key+1): no interim conflict
0126dml028CLOSE, OPEN, FETCH returns first row
0127dml028OPEN 2 cursors at same time
0128dml028OPEN 3 cursors at same time
0129dml029Double quote mark (') in character string literal
0130dml029Approximate numeric literal <mantissa>E<exponent>
0131dml029Approximate numeric literal with neg. exponent
0135dml033Upper and lower case letters are distinct
0140sdl004Priv.violation: GRANT SELECT TO PUBLIC, no INSERT
0141sdl005GRANT SELECT and UPDATE to individual
0144sdl008Priv.violation: colunm not in UPDATE column list
0145sdl009Fully qualified column specification
0148sdl012CREATE TABLE with NOT NULL
0151sdl015CREATE VIEW
0153sdl017CREATE VIEW joining 3 tables
0154sdl018Schema def.- same table name for different schemas
0155sdl019CREATE TABLE with UNIQUE(...): INSERT via SELECT
0156sdl020Tables are multi-sets, dup. INSERTed via subquery
0157dml035CURSOR with ORDER BY approximate numeric
0158dml001CURSOR with UNION and NOT EXISTS subquery
0159dml001CURSOR with 2 UNIONs, ORDER BY 2 integers
0161dml004SQLCODE < 0: FETCH NULL value without indicator
0162dml004FETCH NULL value with INDICATOR syntax
0163dml006SQLCODE < 0: DELETE CURRENT without FETCH
0164dml008Default of SELECT is ALL, not DISTINCT
0165dml008Truncate CHAR column SELECTed into shorter var.
0166dml036INSERT NULL value with indicator = -1
0167dml013SUM ALL function
0168dml013SUM function
0169dml013COUNT (*) function
0170dml013SUM DISTINCT function with WHERE clause
0171dml013SUM (column) + literal
0172dml016SELECT USER into short variable
0173dml021Data type CHAR
0174dml021Data type CHARACTER
0175dml021Data type NUMERIC
0176dml021Data type NUMERIC(9): SELECT *
0177dml021Data type NUMERIC(9): SELECT column
0178dml021Data type DECIMAL
0179dml021Data type DECIMAL(8)
0180dml023NULLs sort together in ORDER BY
0181dml023NULLs are equal for DISTINCT
0182dml029Approx. num. literal with neg. mantissa and exp.
0183ccc001C language embedded host identifiers
0184ccc002C language NULL terminator
0185cob001COBOL - embedded host identifiers
0186cob002COBOL - CHAR(80)
0187cob002COBOL - CHAR(132)
0188cob002COBOL - CHAR(240)
0189dml078OPTIONAL - CHAR(256)
0190dml078OPTIONAL - CHAR(512)
0191dml078OPTIONAL - CHAR(1024)
0192ccc003C language continuation of SQL char. literal
0193ccc003C language comments within embedded SQL statement
0194ccc004C language EXTERN storage class
0195ccc005C language STATIC storage class
0196cob004COBOL PIC S9(12) precision test
0197cob004COBOL PIC S9(18) precision test
0198ccc006C language AUTO storage class
0199sdl021Priv.violation: GRANT SELECT to PUBLIC, no DELETE
0200sdl022Priv.violation: GRANT INSERT to indiv., no SELECT
0201sdl023Priv.violation: GRANT without GRANT OPTION
0202dml037Host variable names same as column names
0203sdl024CREATE VIEW on VIEW
0204sdl025Updatable VIEW with AND, OR in CHECK clause
0205dml038Cartesian product is produced without WHERE
0206cob005COBOL - continuation of SQL char. literal
0207cob006COBOL - comments within embedded SQL statement
0208dml039LIKE predicate with underscore is case sensitive
0209dml040Join 2 tables from different schemas
0210cob007COBOL - PIC S9(1) syntax
0211cob007COBOL - PIC S9(7) syntax
0212dml041Enforcement of CHECK clauses in nested views
0213dml042FIPS sizing - 100 columns in a row
0214dml043FIPS sizing - 2000 byte-row
0215dml044FIPS sizing - 6 columns in a UNIQUE specification
0216dml044FIPS sizing - 120 bytes in a UNIQUE specification
0217for001FORTRAN - continuation of SQL character literal
0218dml045FIPS sizing - 6 columns in GROUP BY
0219dml045FIPS sizing - 120 bytes in GROUP BY
0220dml046FIPS sizing - 6 columns in ORDER BY
0221dml046FIPS sizing - 120 bytes in ORDER BY
0222dml047FIPS sizing - CHARACTER(240)
0223for001FORTRAN - comments within embedded SQL statements
0224dml048FIPS sizing - 10 cursors open at once
0225dml049FIPS sizing - 10 tables in FROM clause
0226dml050FIPS sizing - 10 tables in nested SQL statements
0227dml051BETWEEN predicate with character string values
0228dml051NOT BETWEEN predicate with character string value
0229dml052LIKE predicate is case sensitive
0230mpa001Transactions serializable: assign sequential key
0231mpa002Transactions serializable: SELECT/UPDATE(replace)
0232mpa003Transactions serializable: UPDATE with arithmetic
0233dml053Tables are multi-sets: duplicate via INSERT VALUE
0234dml037SQL comments (double hyphen) in SQL statements
0235cob008COBOL - exact numeric types S9(i)V9(k)
0236cob008COBOL: SQLCODE < 0: exception losing signif. digit
0237sdl026Identifier length 18
0238pas002Pascal - comments within embedded SQL statements
0239pas001Pascal - embedded host identifiers
0240dml054Updatable CURSOR with ALL, IN, BETWEEN
0241dml054Updatable CURSOR with LIKE, NULL, >, =, <
0242dml054Updatable CURSOR with view, correlation name, NOT
0243dml055FIPS sizing - precision of SMALLINT >= 4
0244dml055FIPS sizing - precision of INTEGER >= 9
0245dml055FIPS sizing - precision of DECIMAL >= 15
0246dml056FIPS sizing - 100 values in INSERT
0247dml056FIPS sizing - 20 values in update SET clause
0248dml057FIPS sizing - binary precision of FLOAT >= 20
0249dml057FIPS sizing - binary precision of REAL >= 20
0250dml057FIPS sizing - binary precision of DOUBLE >= 30
0251dml058COMMIT keeps changes of current transaction
0252dml058ROLLBACK cancels changes of current transaction
0253dml058TEST0124 workaround (key = key+1)
0254dml058Column name in SET clause
0255dml058Key word USER for INSERT, UPDATE
0256dml058Key word USER in WHERE clause
0257dml059SELECT MAX, MIN (COL1 + or - COL2)
0258dml059SELECT SUM (:var * COL1 * COL2)
0259dml059SOME, ANY in GROUP BY, HAVING clause
0261dml060WHERE (:var * (COL1 - COL2)) BETWEEN
0262dml060WHERE clause with computation, ANY/ALL subqueries
0263dml060Computed column in ORDER BY
0264dml059WHERE, HAVING without GROUP BY
0265dml060UPDATE : positioned - view with check option
0266dml060UPDATE : positioned - UNIQUE violation under view
0267dml060UPDATE compound key, interim uniqueness conflicts
0268mpa004Transactions serializable: deadlock management
0269dml061BETWEEN value expressions in wrong order
0270dml061BETWEEN approximate and exact numeric values
0271dml061COUNT(*) with Cartesian product subset
0272dml061Statement rollback for integrity violation
0273dml061SUM, MAX, MIN = NULL (not 0) for empty arguments
0274dml062COMMIT and ROLLBACK across schemas
0275dml062COMMIT and ROLLBACK of multiple cursors
0276dml062View across schemas
0277dml061Computation with NULL value specification
0278dml061IN value list with USER, literal,variable spec.
0279dml062IN is a 3-valued predicate, EXISTS is 2-valued
0280dml063SQL key words used as embedded host identifiers
0281dml064Updatable VIEW with ALL, IN, BETWEEN
0282dml064Updatable VIEW with LIKE, NULL, >, =, <
0283dml064Updatable VIEW with view, correlation name, NOT
0284dml065INSERT,SELECT character strings with blanks
0285dml065INSERT,SELECT integers with various formats
0286dml066Compatibility of structures and host variables
0287dml066Compatibility of arrays and host structures
0288dml067Embedded - multiple identifiers in 1 declaration
0289dml067Embedded - multiple declare sections
0290ccc007C language common placement of SQL statements
0291ccc008C language SQL statements in functions
0292cob009COBOL - VALUE IS initialization
0293cob009COBOL - placement of SQL statements
0294pas003Pascal - placement of SQL statements
0295for002FORTRAN - placement of SQL statements
0296flg005FIPS Flagger - vendor provided character function
0297flg005FIPS Flagger - vendor provided integer function
0298pas004Pascal - SQL statements in functions
0299flg006FIPS Flagger - identifier length > 18
0300cdr001DEFAULT value literal + number in a table
0301cdr001DEFAULT value USER in a table
0302cdr002CHECK <comp. predicate> in <tab. cons.>, insert
0303cdr002CHECK <comp. predicate> in <col. cons.>, insert
0304cdr002CHECK <between predicate> in <tab. cons.>, insert
0305cdr002CHECK <null predicate> in <tab. cons.>, insert
0306cdr003CHECK X IS NOT NULL, NOT X IS NULL are equivalent
0307cdr003CHECK <like predicate> in <tab. cons>, insert
0308cdr003CHECK <in predicate> in <tab. cons.>, insert
0309cdr004CHECK combination predicates in <tab. cons.>
0310cdr004CHECK X NOT IN, NOT X IN equivalent, insert
0311cdr004CHECK NOT NULL in col.cons., insert, null explicit
0312cdr004CHECK NOT NULL in col.cons., insert, null implicit
0313cdr005CHECK <comp. predicate> in <tab. cons.>, update
0314cdr005CHECK <comp. predicate> in <col. cons.>, update
0315cdr005CHECK <between predicate> in <tab. cons.>, update
0316cdr006CHECK <null predicate> in <tab. cons.>, update
0317cdr006CHECK X IS NOT NULL, NOT X IS NULL same, by update
0318cdr006CHECK <like predicate> in <tab. cons.>, update
0319cdr007CHECK <in predicate> in <tab. cons.>, update
0320cdr007CHECK combination pred. in <tab. cons.>, update
0321cdr007CHECK if X NOT LIKE/IN, NOT X LIKE/IN same, update
0322cdr007CHECK <null predicate> in <col. cons>, update
0323cdr008(2 pr.,1 son),both P.K e, F.K e,insert another F.K
0324cdr008(2 pr.,1 son),1 P.K exist,another not. insert F.K
0325cdr008(2 pr.,1 son),both P.K e, F.K e, delete 1 P.K
0326cdr008(2 pr.,1 son),P.K e, no F.K, modify P.K
0327cdr009(2 pr.,1 son),check PRIMARY KEY unique via insert
0328cdr009(2 pr.,1 son),F.K exist,modify P.K
0329cdr009(2 pr.,1 son),check PRIMARY KEY unique via modify
0330cdr009(2 pr.,1 son),modify F.K to no P.K corr.
0331cdr009(2 pr.,1 son),modify F.K to P.K corr. value
0332cdr010(self ref.), P.K exist, insert a F.K
0333cdr010(self ref.), delete P.K but F.K exist.
0334cdr010(self ref.), update P.K, no corr. F.K
0335cdr011(self ref.), insert a F.K but no corr. P.K
0336cdr011(self ref.), update P.K, but corr. F.K e.
0337cdr011(self ref.), update P.K, check P.K unique via var.
0338cdr011(self ref.), update F.K and no corr. P.K
0339cdr011(self ref.), update F.K and corr. P.K exist
0340cdr012(ref. each other), insert F.K and corr. P.K e
0341cdr012(ref. each other), delete P.K but corr. F.K e
0342cdr012(ref. each other), update P.K and no corr. F.K
0343cdr013(ref. each other), update P.K and corr. F.K e
0344cdr013(ref. each other), update F.K to no corr. P.K
0345cdr013(ref. each other), update F.K to corr. P.K e
0346cdr013(ref. each other), insert F.K and no corr. P.K
0347cdr014FIPS sz. (comb.keys=6), P.K unique,insert
0348cdr014FIPS sz. (comb.keys=6), insert F.K + no corr. P.K
0349cdr015FIPS sz. (comb.keys=6), delete P.K + corr. F.K e
0350cdr015FIPS sz. (comb.keys=6), update P.K + no corr. F.K
0351cdr015FIPS sz. (comb.keys=6), update P.K + corr. P.K e
0352cdr016FIPS sz. (comb.keys=6), P.K unique, update
0353cdr016FIPS sz. (comb.keys=6), update F.K to no corr. P.K
0354cdr016FIPS sz. (comb.keys=6), update F.K to corr. P.K e
0355cdr017FIPS sz. (1 pr.,6 son), insert F.K + no corr. P.K
0356cdr017FIPS sz. (1 pr.,6 son), delete P.K + corr. F.K e
0357cdr017FIPS sz. (1 pr.,6 son), update P.K but corr. F.K e
0358cdr017FIPS sz. (1 pr.,6 son), check key unique, update
0359cdr017FIPS sz. (1 pr.,6 son), update F.K to no corr. P.K
0360cdr018FIPS sz. (6 pr.,1 son), insert F.K, without P.K
0361cdr018FIPS sz. (6 pr.,1 son), delete P.K, but corr.F.K e
0362cdr018FIPS sz. (6 pr.,1 son), update P.K, but corr.F.K e
0363cdr018FIPS sz. (6 pr.,1 son), check key unique ,update
0364cdr018FIPS sz. (6 pr.,1 son), update F.K to no corr. P.K
0365cdr019(3-level schema), check insert F.K
0366cdr019(3-level schema), check delete P.K
0367cdr019(3-level schema), update mid. tab. check P.K + F.K
0368cdr019(3-level schema), check update P.K
0369cdr020update P. K, set F1=F1+1, interim violation
0370cdr020update F. K, set F1=F1+1, interim violation
0371cdr020update self-ref table, interim violation
0372cdr020delete self-ref table, interim violation
0373cdr003insert with embeded var.+ indic. var. CHECK clause
0374cdr003computation in update, CHECK clause
0375cdr017ref. integrity with computation
0376cdr017ref. integrity with join
0377cdr001DEFAULT value with explicit NULL
0378cdr021(ref. acr. sch.) delete P.K and corr. F.K e.
0379cdr021(ref. acr. sch.) update P.K and corr. F.K e.
0380cdr022(ref. acr. sch.) insert F.K and no corr. P.K
0381cdr022(ref. acr. sch.) update F.K to no P.K corr.
0382cdr023(ref. acr. sch.) with GRANT OPTION, insert
0383cdr023Priv.violation: GRANT without GRANT OPTION
0384cdr023Priv.violation: SELECT, but not REFERENCES
0385cdr024character default column values
0386cdr024exact numeric default column values
0387cdr024approximate numeric default column values
0388cdr024FIPS sz. default column values
0389dml06895-char ASCII collating sequence (PL vs. SQL)
0390dml072Short char column value blank-padded in larger var
0391sdl027Correlation names used in self-join of view
0392for003FORTRAN - placement of additional SQL statements
0393dml073SUM, MAX on Cartesian product
0394dml073AVG, MIN on joined table with WHERE
0395dml073SUM, MIN on joined table with GROUP BY
0396dml073SUM, MIN on joined table with WHERE,GROUP,HAVING
0397sdl028Grouped view
0398ccc011Embedded C initial value
0399ccc009C language storage class and class modifier comb.
0401sdl027View with computed columns (degrees F to C)
0402cdr025Computed GROUP BY view over referencing tables
0403cdr025View on computed GROUP BY view with joins
0404dml0692 FETCHes (different target types) on same cursor
0405dml0692 cursors open from different schemas (coded join)
0406dml069Subquery from different schema
0407dml069SELECT INTO :XX ... WHERE :XX =
0408dml069UPDATE references column value BEFORE update
0409dml070Effective outer join--join with two cursors
0410dml076NULL value in OPEN CURSOR
0411dml070Effective set difference
0412dml070Effective set intersection
0413cdr025Computed SELECT on computed VIEW
0414dml071WHENEVER NOT FOUND, multiple settings
0415dml071WHENEVER SQLERROR, multiple settings
0417dml073Cartesian product GROUP BY 2 columns with NULLs
0418dml073AVG,SUM,COUNT on Cartesian product with NULLs
0419dml073SUM, MAX, MIN on joined table view
0420sdl028View with multiple SELECT of same column
0421dml074Module language constants and expressions
0422dml074Module language order of SQLCODE (not first)
0423dml074Module language multiple SQLCODE parameters
0424ada001Ada embedded host identifiers
0425ada002Ada comments
0426ada002Ada initial value
0427ada003Ada common placement of SQL statements
0428ada004Ada placement of SQL statements
0430ada005Ada unqualified type spec - without SQL_STANDARD
0431dml075Redundant rows in IN subquery
0432dml075Unknowns in subquery of ALL, SOME, ANY
0433dml075Empty subquery of ALL, SOME, ANY
0434dml075GROUP BY with HAVING EXISTS-correlated set fnc
0435dml076Host variables in UPDATE WHERE CURRENT
0436dml076NULL values for various SQL data types
0437dml076NULL values for various host variable types
0438cdr026(partial-NULL F.K) F.K INSERT supported
0439cdr026(partial-NULL F.K) F.K UPDATE supported
0440cdr026(partial-NULL F.K) no restrict P.K update/delete
0441dml076NULL value for various predicates
0443dml077VIEW with check option rejects unknown (NULL)
0444dml077Updatable cursor, modify value selected on
0445dml077Values not assigned to targets for SQLCODE=100
0446cdr027Table CHECK constraint allows unknown (NULL)
0447cdr027NULLs with check constraint and check option
0448cdr027PRIMARY KEY implies UNIQUE
0449cdr027Constraint definition is case sensitive
0450cdr027Referential integrity is case sensitive
0451dml079UNIQUEness is case sensitive
0452dml079Order of precedence, left-to-right in UNION [ALL]
0453dml079NULL with empty subquery of ALL, SOME, ANY
0454flg008SELECT nonGROUP column in GROUP BY
0455flg009Relaxed union compatability rules for columns
0456ada006Module language Ada subtype enforcement,name assoc
0457mpa005Transactions serializable: phantom read
0458sdl029Priv.violation: GRANT only SELECT to individual
0459sdl029Priv.violation: GRANT only INSERT to individual
0460sdl029Priv.violation: GRANT only UPDATE to individual
0461sdl029Priv.violation: GRANT only DELETE to individual
0462dml080SQLCODE 100: DELETE with no data
0463dml080SQLCODE 100: UPDATE with no data
0464sdl030Priv.violation: GRANT only SELECT to PUBLIC
0465sdl030Priv.violation: GRANT only INSERT to PUBLIC
0466sdl030Priv.violation: GRANT only UPDATE to PUBLIC
0467sdl030Priv.violation: GRANT only DELETE to PUBLIC
0468sdl031Priv.violation: individual without any privileges
0469sdl031GRANT ALL PRIVILEGES to individual
0470sdl031GRANT ALL PRIVILEGES to public
0471sdl031Priv.violation: GRANT privilege not grantable
0472sdl032Priv.violation: individual SELECT, column UPDATE
0473sdl033Priv.violation: GRANT all on view but not table
0474sdl034Priv.violation: need SELECT for searched UPDATE
0475sdl034Priv.violation: GRANT ALL w/o GRANT OPTION
0476sdl034Priv.violation: GRANT OPTION view but not table
0477sdl035Priv.violation: GRANT only SELECT on view
0478sdl035Priv.violation: GRANT only INSERT on view
0479sdl035Priv.violation: GRANT only UPDATE on view
0480sdl035Priv.violation: GRANT only DELETE on view
0481sdl036Priv.violation: no privileges on view
0482sdl036GRANT ALL PRIVILEGES on view
0483sdl036Priv.violation: GRANT UPDATE not grantable on view
0484sdl032Priv.violation: SELECT and column UPDATE on view
0485sdl032Priv.violation: SELECT and column UPDATE cursor
0486cdr028Priv.violation: illegal REFERENCES
0487dml081SQLSTATE 00000: successful completion
0488dml081SQLSTATE 21000: cardinality violation
0489dml081SQLSTATE 02000: no data
0490dml081SQLSTATE 22012: data exception/division by zero
0491dml082SQLSTATE 22022: data exception/indicator overflow
0492dml082SQLSTATE 22019: data exception/invalid escape char
0493dml082SQLSTATE 22025: data exception/invalid escape seq.
0494dml082SQLSTATE 22003: data exception/numeric val.range 1
0495sdl037Priv.violation: illegal GRANT to self
0496dml083SQLSTATE 22002: data exception/null but no indic
0497dml091SQLSTATE 22003: data exception/numeric val.range 2
0498dml083SQLSTATE 22001: data exception/string right trunc.
0499ccc010SQLSTATE 22024: data exception/unterminat.C string
0500dml083SQLSTATE 01003: warning/null elim. in set function
0501dml083SQLSTATE 01004: warning/string right truncation
0502dml081SQLSTATE 24000: invalid cursor state
0503dml084SQLSTATE 42000: syntax error or access rule vio.1
0504dml084SQLSTATE 42000: syntax error or access rule vio.2
0505dml082SQLSTATE 44000: with check option violation
0506mpa006SQLSTATE 40001: trans.rollback/serialization fail.
0507mpa007Transactions serializable: dueling cursors
0508dml085Delimited identifiers
0509dml085Renaming columns with AS for ORDER BY
0510dml085<parameter name> = <column name> (OK with SQL-92)
0511dml086CHECK clauses in nested views(clarified in SQL-92)
0512dml090<value expression> for IN predicate
0513dml090NUMERIC(4) implies CHECK BETWEEN -9999 AND 9999
0514dml141SQLSTATE 23000: integrity constraint violation
0515dml132FIPS sizing: NUMERIC (15) decimal precision
0516cdr030SQLSTATE 23000: integrity constraint violation
0517mpa008Transactions serializable: Twins Problem
0519dml087CREATE VIEW with subqueries
0520dml087Underscores are legal and significant
0521dml088New format in MODULE-<parameter declaration list>
0522cdr029No implied natural join on FROM T1, T2
0523dml090<value expression> for BETWEEN predicate
0524dml132FIPS sizing: 100 Items in a SELECT list
0525dml132FIPS sizing: 15 Table references in SQL statement
0526cdr031FIPS sizing: Length FOREIGN KEY column list = 120
0527dml142Priv. violation: HU
0528dml142Tables are multi-sets: cursor operations
0529dml143Priv. violation: SELECT in <insert statement>
0530mpquicInteractive SQL serializability: dirty read
0531mpquicInteractive serializability: non-repeatable read
0532ada007package SQLSTATE_CODES
0533ada008Misc. in package SQL_STANDARD
0534ada009ADA Tasks
0536cob010FIPS sizing - COBOL BINARY decimal precision >= 9
0537cdr029Table check constraint: column vs. column
0538cdr029With check option: column vs. column
0539mpquicInteractive SQL serializability: phantom read
0554dml085More column renaming - single row select with join
0556dml123Static insert, dynamic fetch, static commit
0557dml123Static insert, dynamic commit, static rollback
0558dml123Dynamic insert, static delete, dynamic count
0559dml123Static insert, dynamic rollback, static fetch
0560mpa010Table privileges vs. column privileges
0561dml149Double SET TRANSACTION
0562mpquicInteractive serializability: ISOLATION MODE
0564dml090Outer ref. directly contained in HAVING clause
0565dml092VARCHAR for Transitional SQL
0566dml093VARCHAR for TSQL: dynamic version
0567dml094CHAR type in Dynamic SQL
0568isi001INFORMATION_SCHEMA.TABLES definition
0569isi002INFORMATION_SCHEMA.VIEWS definition
0574isi007Orphaned IS data structures, Intermediate SQL
0576dml095NUMERIC type in Dynamic SQL
0577dml096DECIMAL type in Dynamic SQL
0578dml097INTEGER and SMALLINT types in Dynamic SQL
0579dml098FIPS sizing, Dynamic SQL exact numerics
0580dml098FIPS sizing, Dynamic SQL approximate numerics
0581dml099Implicit numeric casting (feature 9) dynamic
0582dml099Implicit numeric casting (feature 9) static
0583dml099FIPS sizing, Dynamic SQL character strings
0584dml092FIPS sizing, VARCHAR (254) strings (static)
0585dml093FIPS sizing, VARCHAR (254) strings (dynamic)
0586dml098Sizing of FLOAT in a descriptor (dynamic)
0587dml100SET TR READ ONLY / READ WRITE (static)
0588dml101SET TR READ ONLY / READ WRITE (dynamic)
0589mpa013SET TR ISOLATION LEVEL (static)
0590mpa012SET TR ISOLATION LEVEL (dynamic)
0591dml104NATURAL JOIN (feature 4) (static)
0592dml104INNER JOIN (feature 4) (static)
0593dml104LEFT OUTER JOIN (feature 4) (static)
0594dml104RIGHT OUTER JOIN (feature 4) (static)
0595dml105NATURAL JOIN (feature 4) (dynamic)
0596dml105INNER JOIN (feature 4) (dynamic)
0597dml105LEFT OUTER JOIN (feature 4) (dynamic)
0598dml105RIGHT OUTER JOIN (feature 4) (dynamic)
0599dml106UNION in views (feature 8) (static)
0600dml107UNION in views (feature 8) (dynamic)
0601dml106DATETIME data types (feature 5) (static)
0602dml107DATETIME data types (feature 5) (dynamic)
0603ist001INFO_SCHEM.TABLES definition
0604ist002INFO_SCHEM.VIEWS definition
0605ist003INFO_SCHEM.COLUMNS definition
0606ist004INFO_SCHEM.SCHEMATA definition
0607ist005INFO_SCHEM.TABLE_PRIVILEGES definition
0609ist007Orphaned IS data structures, Transitional SQL
0611dml106FIPS sizing, DATETIME data types (static)
0612dml107FIPS sizing, DATETIME data types (dynamic)
0613dml106<datetime value function> (static)
0614dml107<datetime value function> (dynamic)
0615dml106DATETIME-related SQLSTATE codes (static)
0616dml107DATETIME-related SQLSTATE codes (dynamic)
0617dml108DATETIME with predicates, set fns (static)
0618dml109DATETIME with predicates, set fns (dynamic)
0619dml110DATETIME cursor operations (static)
0620dml111DATETIME cursor operations (dynamic)
0621dml112DATETIME NULLs (static)
0622dml113DATETIME NULLs (dynamic)
0623dml112OUTER JOINs with NULLs and empty tables (static)
0624dml113OUTER JOINs with NULLs and empty tables (dynamic)
0625dml112ADD COLUMN and DROP COLUMN (static)
0626dml113ADD COLUMN and DROP COLUMN (dynamic)
0627mpa010<grant statement> (static)
0628mpa010<revoke statement> (static)
0629mpa011<grant statement> (dynamic)
0630mpa011<revoke statement> (dynamic)
0631dml112Datetimes in a <default clause> (static)
0632dml113Datetimes in a <default clause> (dynamic)
0633dml112TRIM function (static)
0634dml113TRIM function (dynamic)
0635dml114Feature 13, grouped operations (static)
0636dml115Feature 13, grouped operations (dynamic)
0637dml114Feature 14, Qualified * in select list (static)
0638dml115Feature 14, Qualified * in select list (dynamic)
0639dml114Feature 15, Lowercase Identifiers (static)
0640dml115Feature 15, Lowercase Identifiers (dynamic)
0641dml114Feature 16, PRIMARY KEY enhancement (static)
0642dml115Feature 16, PRIMARY KEY enhancement (dynamic)
0643dml133Feature 17, Multiple schemas per user
0644dml116Feature 18, Multiple module support
0645dml117Feature 19, Referential delete actions (static)
0646dml118Feature 19, Referential delete actions (dynamic)
0647dml119Feature 20, CAST functions (static)
0648dml120Feature 20, CAST functions (dynamic)
0649dml121Feature 22, Explicit defaults (static)
0650dml122Feature 22, Explicit defaults (dynamic)
0651dml121Feature 24, Keyword relaxations (static)
0652dml122Feature 24, Keyword relaxations (dynamic)
0653dml124Descriptors: DESCRIBE OUTPUT
0654dml124Descriptors: INTO SQL DESCRIPTOR
0655dml124Descriptors: USING SQL DESCRIPTOR
0656dml124Descriptors: datetimes
0657dml125Descriptors: VARCHAR
0658dml125Descriptors: SQLSTATE codes
0659dml125Descriptors: TSQL orphaned features
0660dml126Dynamic SQL SQLSTATEs
0661dml121Errata: datetime casting (static)
0662dml122Errata: datetime casting (dynamic)
0663dml121Errata: datetime SQLSTATEs (static)
0664dml122Errata: datetime SQLSTATEs (dynamic)
0665dml127Diagnostics: statement information
0666dml127Diagnostics: condition information
0667dml152Diagnostics: access violations
0668dml152Diagnostics: COMMAND_FUNCTION (static)
0669dml152Diagnostics: COMMAND_FUNCTION F# 3, 11
0670dml126Diagnostics: COMMAND_FUNCTION (dynamic)
0671dml126Diagnostics: DYNAMIC_FUNCTION
0672dml152Diagnostics: Multiple conditions
0673dml152Diagnostics SQLSTATE: inv. cond. number
0674dml128Diagnostics: TSQL orphaned features
0675dml128Diagnostics: MORE
0676dml129Diagnostics: VARCHAR
0677dml129VARCHAR with <like predicate>
0678dml130Data type semantics with NULL / NOT NULL
0679dml130INFO_SCHEM: Table data types
0680dml130INFO_SCHEM: View data types
0681dml129INFO_SCHEM: Varchar data types
0682dml130INFO_SCHEM: Datetime data types
0683dml131INFO_SCHEM: Changes are visible
0684dml131INFO_SCHEM: Visibility to other users
0685dml131INFO_SCHEM: Privileges and privilege views
0686dml131INFO_SCHEM: Primary key enh. is not null
0687dml131INFO_SCHEM: Multiple schemas per user
0688dml134INFO_SCHEM: Dynamic changes are visible
0689dml134Many Trans SQL features #1: inventory system
0690dml134Many Trans SQL features #2: talk show schedule
0691dml134INFO_SCHEM: SQLSTATEs for length overruns
0692dml135Many TSQL features #3: enhanced proj/works
0693dml135Many TSQL features #4: enhanced INFO_SCHEM
0694dml135Interval Arithmetic and Casts
0695dml135<updatability clause> in <declare cursor>
0696dml136Many TSQL features #5: Video Game Scores
0697dml137Erratum: drop behavior, constraints (static)
0698dml138Erratum: drop behavior, constraints (dynamic)
0699dml139<drop behavior> on a REVOKE (static)
0701xop701X/O,WHENEVER SQLWARNING and scoping of C labels
0703xop703X/O,CREATE INDEX on existent/non-existent tables
0706xop706X/O,CREATE INDEX on at least 6 columns
0707xop707X/O,Limit on depth of nested sub-queries
0708xop708X/O,Limit on the total length of an Index Key
0709xop709X/O,SQL Escape Clause Processing
0710xop710X/O,Acceptance of correctly placed SQLCA
0719xop719X/O,GRANT ALL with optional PRIVILEGES omitted
0720xop720X/O,GRANT ALL with optional PRIVILEGES omitted
0721xop721X/O,REVOKE ALL with optional PRIVILEGES omitted
0722xop722X/O,REVOKE ALL with optional PRIVILEGES omitted
0723xop723X/O,DROP TABLE with outstanding grants and views
0829dml140<drop behavior> on a REVOKE (dynamic)
0831flg011FIPS Flagger - ADD (column, ...)
0832flg012FIPS Flagger - CREATE INDEX
0833flg013FIPS Flagger - INCLUDE SQLCA
0834dml144<length expression> (static)
0835dml144<character substring function> (static)
0836dml145<length expression> (dynamic)
0837dml145<character substring function> (dynamic)
0838dml146<character substring function> varchar
0839dml144Composed <length expression> and SUBSTRING
0840dml147Roll back schema manipulation
0841dml147Multiple-join and default order of joins
0842dml147Multi-column joins
0843dml148Ordering of column names in joins
0844dml148Outer join predicates
0845dml150Parameters and indicators in dynamic SQL statement
0846dml149Feature 20, CAST functions (static) nits
0847mpa009Dynamic SQL: serializability
0848dml153Query spec with subquery is now updatable
0849dml154Descriptors: datetime length in positions
0850dml155Comparing fixed vs. variable length char strings
0851ada010Errata: SQL_STANDARD changed to Interfaces.SQL
0852dml154Transitive grant in COLUMN_PRIV, TABLE_PRIV
0853dml151Exceptions not affecting position of cursor
0854dml154Informational: mixing SDL and DML
0855dml156Dynamic SQL syntax errors
0856dml157Transitional Schema Definition
0857dml158<join condition> set function, outer reference
0858dml159? (dyn parm spec) in <having clause>
0859dml160<joined table> contained in <select list>
0860dml160Domains over various data types
0862dml161CURRENT_USER etc. with set session authid
0863dml162<joined table> directly contained in cursor, view
0864dml163Intermediate Dynamic SQL syntax errors
0865dml163Result data types for case expressions
0866dml163Case expressions in other than SELECT
0867dml164LIKE enhancements: keyword search
0868dml164More <unique predicate>
0869dml164More table operations
0870dml165Non-identical descriptors in UNION
0871ada011Errata: Interfaces.SQL.Numerics--TC3 clause 23.3
0872ada012Errata: Interfaces.SQL.Varying--TC3 clause 23.3
0873dml166Dynamic schema creation
0874dml167INFORMATION_SCHEMA catalog columns
0875dml167INFORMATION_SCHEMA column coverage
0877dml169Intermediate DB, Flag at Entry level
0878dml168Keyword COLUMN in ALTER TABLE is optional
0879dml168<drop table constraint definition>
0880dml170Long constraint names, cursor names
0881dml170Long character set names, domain names
0882dml171More full outer join
0883ada013Errata: Interfaces.SQL.Varying.NCHAR--TC3, 23.3
0884dml172ASCII_FULL and SQL_TEXT in column definition
0885dml173FIPS sizing, CHAR (1000)
0886dml174FIPS sizing, VARCHAR (1000)
0887dml175FIPS sizing, NCHAR (500)
0888dml176FIPS sizing, NCHAR VARYING (500)
0889dml177FIPS sizing, INTEGER binary prec >= 31
0890dml177FIPS sizing, SMALLINT binary prec >= 15
0891dml178FIPS sizing, 250 columns, 4000 char data statement
0892dml179FIPS sizing, rowlen >= 8000, statement var >= 4000
0893dml180FIPS sizing, descriptor occurrences >= 100
0894dml181FIPS sizing, length of column lists >= 750
0895dml182FIPS sizing, columns in list >= 15
0896dml183FIPS sizing, 50 WHEN clauses in a CASE expression
0897dml184Constraint usage redux
0898dml185COLUMN_DEFAULT interpretation
0899dml186FIPS sizing, INTERVAL decimal leading field prec
7001xts700NULLIF producing NULL
7002xts798NULLIF producing non-NULL
7003xts799COALESCE with three <value expression>s
7004xts701Compound char. literal in <comparison predicate>
7005xts701Compound character literal as inserted value
7006xts701Compound character literal in a <select list>
7007xts702LIKE with unrestricted <match value>
7008xts702LIKE with general char. value for pattern + escape
7009xts702LIKE with zero-length escape
7010xts703UNIQUE predicate, single table, all values distinc
7011xts703UNIQUE PREDICATE, table subquery with non-null dup
7012xts703UNIQUE predicate, duplicates containing null
7013xts713Schema definition in an SQL statement-single table
7014xts714Schema definition named schema, implicit auth-id.
7015xts715Schema definition - explicit name and auth-id.
7016xts716SET SESSION AUTHORIZATION to current auth-id.
7017xts717SET SESSION AUTH. to current auth-id in. transactn
7018xts718SET SESSION AUTHORIZATION to different value
7019xts719Access to KEY_COLUMN_USAGE view
7020xts720Access to VIEW_TABLE_USAGE view
7021xts721Access to VIEW_COLUMN_USAGE view
7022xts722Access to CONSTRAINT_TABLE_USAGE view
7023xts723Access to CONSTRAINT_COLUMN_USAGE view
7024xts724Access to COLUMN_DOMAIN_USAGE view
7025xts725Flagging - Full SQL INSENSITIVE cursor
7026xts726Flagging Full SQL - cursor FOR UPDATE and ORDER BY
7027xts727Flagging - Full SQL - <explicit table> in <qry exp
7028xts728Flagging,Full SQL,<null predicate> with two-col ro
7029xts729Column name with 19 and 128 characters - regular.
7030xts730Table name with 19 characters - delimited.
7031xts731View name with 128 characters - delimited.
7032xts732NATURAL FULL OUTER JOIN <table ref> -- static.
7033xts733FULL OUTER JOIN <table ref> ON <search condition>
7034xts734National Character data type in comparison predica
7035xts735INSERT National character literal in NCHAR column
7036xts736Update NCHAR VARYING column with value from NCHAR
7037xts737Scrolled cursor with ORDER BY DESC, FETCH NEXT
7038xts738Scrolled cursor with ORDER BY DESC, FETCH PRIOR
7039xts739Scrolled cursor with ORDER BY int, name ASC, FETCH
7040xts740COUNT(ALL <column name>) with Nulls in column
7041xts741COUNT(ALL NULLIF...) with generated Nulls
7042xts742COUNT ALL <literal>
7044xts744Presence of SQL_CHARACTER in CHARACTER_SETS view
7045xts745Presence of ASCII_GRAPHIC in CHARACTER_SETS view
7046xts746Presence of LATIN1 in CHARACTER_SETS view
7047xts747Presence of ASCII_FULL in CHARACTER_SETS view
7048xts748Named constraint in column definition in schema de
7049xts749Named table constraint in table definition.
7050xts750Named domain constraint.
7051xts751Name of violated column constraint returned in dia
7053xts753ALTER TABLE ADD COLUMN WITH <data type>
7054xts754ALTER TABLE ADD COLUMN WITH domain and constraint
7058xts758Scrolled cursor FETCH ABSOLUTE non-literal, after
7059xts759Scrolled cursor on grouped view,FETCH RELATIVE,FIR
7060xts760MAX of column derived from <set function specifica
7061xts761Defined character set in <comparison predicate>
7062xts762Defined character set in <like predicate>
7063xts763Access to CHARACTER_SETS view
7064xts764REVOKE USAGE on character set RESTRICT
7065xts765REVOKE USAGE on character set CASCADE
7066xts766Drop character set no granted privileges
7067xts767DROP character set, outstanding granted privileges
7068xts768Presence of SQL_TEXT in CHARACTER_SETS view
7069xts769<Character set specification> of LATIN1 in <litera
7070xts770<Character set specification> of SQL_CHARACTER in
7071xts771CHARACTER SET ASCII_GRAPHIC in <data type>
7500yts750CREATE DOMAIN -SQL Procedure statement,no options
7501yts751CREATE DOMAIN as SQL proc statement with default
7502yts752CREATE DOMAIN-SQL proc statement with constraint
7504yts754DROP DOMAIN CASCADE - domain definition in use
7505yts755DROP DOMAIN CASCADE-domain w. default + constraint
7506yts756Domain Constraint Containing VALUE
7507yts757INSERT value in column defined on domain
7508yts757Put value in col defined on dom breaking constrain
7509yts759GRANT USAGE on a domain
7510yts776DROP SCHEMA - empty schema with restrict
7511yts777DROP SCHEMA - non-empty schema
7512yts783Scr.cursor,no ORDER,FETCH all,FIRST,LAST,NEXT
7513yts784Scr.cursor with joined table, FETCH ABS literal
7517yts762<query expression> with EXCEPT
7518yts763<query expression> with INTERSECT CORRESPONDING
7519yts764<query expression> with UNION ALL CORRESPONDING BY
7522yts788CREATE CHARACTER SET, implicit default collation
7524yts790GRANT USAGE on character set,WITH GRANT OPTION
7525yts791GRANT USAGE on character set,WITH GRANT OPTION
7526yts792GRANT USAGE on character set,WITH GRANT OPTION
7527yts793GRANT USAGE on character set,no WGO
7528yts794GRANT USAGE on character set, no WGO
7529yts795GRANT USAGE on character set, no WGO
7530yts796<scalar subquery> as first operand in <comp pred>
7531yts799<subqry> as<row val constructor>in<null predicate>
7532yts800<nul pred><interval value exp> as <row value cons>
7534yts760CASE expression with one simple WHEN
7535yts761CASE expression with searched WHEN
7536yts781Set local time zone - valid value
7537yts765Explicit table constrnts in TABLE_CONSTRAINTS view
7538yts765Column constraints in TABLE_CONSTRAINTS view
7539yts765Unique identification in TABLE_CONSTRAINTS view
7540yts766Explicit table constrnts - REFERENTIAL_CONSTRAINTS
7541yts766Column constraints in REFERENTIAL_CONSTRAINTS view
7542yts766Unique id in REFERENTIAL_CONSTRAINTS view
7543yts766Values in columns of REFERENTIAL_CONSTRAINTS view
7544yts767Explicit table constr. in CHECK_CONSTRAINTS view
7545yts767Column constraints in CHECK_CONSTRAINTS view
7546yts767Domain constraints in CHECK_CONSTRAINTS view
7547yts767Unique identification in CHECK_CONSTRAINTS view
7548yts802Support of SQL_FEATURES tab. in documentatn schema
7549yts803Support SQL_SIZING table in documentation schema
7550yts768Access to SCHEMATA view
7551yts769Access to DOMAINS view
7552yts770Access to DOMAIN_CONSTRAINTS view
7553yts771Access to CHARACTER_SETS view
7554yts772Access to ASSERTIONS view
7555yts773Access to SQL_LANGUAGES view
7556yts774Access to INFORMATION_SCHEMA_CATALOG_NAME base tab
7557yts775SQL host prog. with duplicate local variable names
7558yts797<scalar subquery> in SET of searched update
7559yts798<scalar subqry> in <sel.list> of single-row sel.
7560yts780<time zone interval> in literal
7561yts782Set local time zone - invalid value, exception
7562yts805Schema with crossed referential const. bet. tables
7563yts806NATURAL FULL OUTER JOIN <table ref> - dynamic
7564yts807TIMEZONE_HOUR + TIMEZONE_MINUTE in <extract expr.>
7565yts808LOCAL time zone in <datetime value expression>
7566yts809TIME ZONE in <datetime value expression>
7567yts810FULL OUTER JOIN<table ref>ON<search condition> dyn
7568yts811WHERE <search cond.> referencing column
7569yts812<null predicate>,concat. in<row value constructor>
7570yts813<null predct>, <numrc val expr> in <row val cons>
7571yts814<module character set specification>

899 rows selected

NOTE This listing was created by the following query:
select testno,prog,descr from testcase order by testno;


TEd Change File
Sample Downloaded Embedded SQL COBOL File

! ----------------------------------------------------------------
! Embedded COBOL
! ----------------------------------------------------------------
sub *.* /59-byte ID/
/59-byte ID/______________________________________________________/
! Document version of TEd used, for audit purposes.
sub *.* /TEd Version #/ -m
"TEd Version #"TEd Version $ver"
! ----------------------------------------------------------------
! ----------------------------------------------------------------
! Use the -t option on TEd to apply the official maintenance file.
! Then use a second -t option on TEd to apply this file.
! OR
! Insert the entire maintenance file in this position.
! ----------------------------------------------------------------
! ----------------------------------------------------------------
! The login problem is solved here:
! If passwords are system-login passwords, then no changes are necessary.
! For other implementations, changes may be needed.
! The call to AUTHID may be replaced with some implementor-defined syntax.
! For example, you may choose to code:
! rep *.pco /CALL "AUTHID"/
! Other changes to establish the ANSI-standard environment would be coded here.
! ----------------------------------------------------------------
! ----------------------------------------------------------------
! Install implementor-defined precision of SQLCODE
! For example, to change precision from 9 to 4, code:
! sub *.pco /SQLCODE/
! /S9(9)/S9(4)/
! Install implementor-defined data type for indicator variable
! For example, to change data type to COMP, code:
! sub *.pco /indic/
! Modify dml063 to declare vendor-reserved key words
! For example, to declare UPDATE a vendor-reserved key word, code:
! del *dml063.pco /01 UPDATE /
! del *dml063.pco /:UPDATE/MOVE UPDATE TO/
! Install vendor-supplied extensions to demonstrate FIPS Flagging:
! (Replace the underscores with vendor-supplied extensions.)
sub *{flg005,xop709}.pco //
/SUBSTR(EMPNAME,1,3) = 'Ali'/____________________/
/ABS(GRADE) = 12/________________/
! Change 18-character AUTHORIZATION ID to max supported in this DBMS/OS:
! Change COBOL programs to specify correct source and object computers
sub *.[ps]co /COMPUTER/
! ----------------------------------------------------------------
! ----------------------------------------------------------------
! Include here any changes approved by NIST for this validation:
! For example, to lengthen concurrency test #0230 (according to documentation),
! to allow interleaving for a DBMS using "burst" mode CPU allocation:
! sub *mp?001.[ps]* /keymax/ -i
! /50/300/
! ----------------------------------------------------------------
! ----------------------------------------------------------------
! If a program cannot be executed because the precompiler issues
! a fatal syntax error message for a minor deficiency which is not
! the purpose of the test case, propose a change to NIST.
! A syntax change should be made to allow an SQL implementation
! to demonstrate functionality, i.e. semantic conformance.
! A global change will be counted as a single deficiency.
! ----------------------------------------------------------------
! ----------------------------------------------------------------
! If a program cannot be executed because the precompiler issues
! a fatal syntax error message for one of the tests, either propose a
! change to NIST or delete the entire test so that the rest of the tests
! in the program may execute. If this test is required, and not a FIPS
! Flagger test, it will appear as a deficiency on REPORT 4.
! For COBOL, a performed paragraph is found at the end of the program.
! So a separate delete command is needed for a performed paragraph.
! For example, to remove test number 278, code
! del *dml061.pco /BEGIN TEST0278/END TEST0278/


SAMPLE TEd Change File
Vendor-modified File Proposed for Validation

! ----------------------------------------------------------------
! Embedded COBOL
! ----------------------------------------------------------------
sub *.* /59-byte ID/
/59-byte ID/BugFreeDB v.783, UNIX SYS X 2.2, SuperPC 440/
! Document version of TEd used, for audit purposes.
sub *.* /TEd Version #/ -m
"TEd Version #"TEd Version $ver"
! ----------------------------------------------------------------
! ----------------------------------------------------------------
! Note: maintenance is included as first TEd file, via command:
! ted -t upd400.ted -t chgpco.ted -o dmlxxx.pco
! where dmlxxx.pco is a NIST-supplied test file
! and chgpco.ted is this file.
! Note that our product uses file extension eco, instead of pco.
! ----------------------------------------------------------------
! ----------------------------------------------------------------
! The call to AUTHID is replaced with LINK statement.
rep *.pco /CALL "AUTHID"/
! Other changes to establish the ANSI-standard environment.
ins> mp*.pco /EXEC SQL LINK SCHEMA/
! ----------------------------------------------------------------
! ----------------------------------------------------------------
! We declare SQLCA which includes SQLCODE
! So replace SQLCODE with SQLCA, but outside of the DECLARE SECTION.
del *.pco /*01*SQLCODE*/ -p
ins> *.pco /WORKING-STORAGE/
! Our implementor-defined data type for indicator variable is COMP.
sub *.pco /indic/ -i
! Modify dml063 to declare vendor-reserved key words: WHERE and ROLLBACK.
del *dml063.pco /01 WHERE /
del *dml063.pco /:WHERE/MOVE WHERE TO/
del *dml063.pco /01 ROLLBACK /
del *dml063.pco /:ROLLBACK/MOVE ROLLBACK TO/
! Vendor-supplied extensions to demonstrate FIPS Flagging:
! We don't support SUBSTR, but we can demonstrate LOWER:
sub *{flg005,xop709}.pco //
/SUBSTR(EMPNAME,1,3) = 'Ali'/LOWER (EMPNAME) = 'alice'/
! No substitution for ABS(GRADE) = 12, it works for us.
! AUTHORIZATION ID max length is 8:
sub *.* /SULLIVAN1/
! Change COBOL programs to specify correct source and object computers
sub *.[ps]co /COMPUTER/
! ----------------------------------------------------------------
! ----------------------------------------------------------------
! To lengthen concurrency test #0230 (according to documentation),
! Our DBMS uses "burst" mode CPU allocation:
sub *mp?001.[ps]* /keymax/ -i
! ----------------------------------------------------------------
! ----------------------------------------------------------------
! The following syntax changes are made to allow for further testing.
! our precompiler doesn't understand END-EXEC; it wants semicolon.
sub *.pco /END-EXEC/
! our numeric literals must not end with a decimal point.
sub *dml005.pco /LONG_INT/
! ----------------------------------------------------------------
! ----------------------------------------------------------------
! UNION and UNION ALL not supported in same SQL statement
! Remove test number 160 and the paragraphs it performs
del *dml001.pco /BEGIN TEST0160/END TEST0160/
del *dml001.pco /P43./ ./


SAMPLE TEd Change File
Accepted Counter-proposed File

! ----------------------------------------------------------------
! Embedded COBOL
! ----------------------------------------------------------------
sub *.* /59-byte ID/
/59-byte ID/BugFreeDB v.783, UNIX SYS X 2.2, SuperPC 440/
! Document version of TEd used, for audit purposes.
sub *.* /TEd Version #/ -m
"TEd Version #"TEd Version $ver"
! ----------------------------------------------------------------
! ----------------------------------------------------------------
! Note: maintenance is included as first TEd file, via command:
! ted -t upd400.ted -t chgpco.ted -o dmlxxx.pco
! where dmlxxx.pco is a NIST-supplied test file
! and chgpco.ted is this file.
! Note that our product uses file extension eco, instead of pco.
! ----------------------------------------------------------------
! ----------------------------------------------------------------
! The call to AUTHID is replaced with LINK statement.
rep *.pco /CALL "AUTHID"/
! Other changes to establish the ANSI-standard environment.
ins> mp*.pco /EXEC SQL LINK SCHEMA/
! ----------------------------------------------------------------
! ----------------------------------------------------------------
! Our implementor-defined data type for indicator variable is COMP.
sub *.pco /indic/ -i
! Modify dml063 to declare vendor-reserved key words: WHERE and ROLLBACK.
del *dml063.pco /01 WHERE /
del *dml063.pco /:WHERE/MOVE WHERE TO/
del *dml063.pco /01 ROLLBACK /
del *dml063.pco /:ROLLBACK/MOVE ROLLBACK TO/
! Vendor-supplied extensions to demonstrate FIPS Flagging:
! We don't support SUBSTR, but we can demonstrate LOWER:
sub *{flg005,xop709}.pco //
/SUBSTR(EMPNAME,1,3) = 'Ali'/LOWER (EMPNAME) = 'alice'/
! No substitution for ABS(GRADE) = 12, it works for us.
! AUTHORIZATION ID max length is 8:
sub *.* /SULLIVAN1/
! Change COBOL programs to specify correct source and object computers
sub *.[ps]co /COMPUTER/
! ----------------------------------------------------------------
! ----------------------------------------------------------------
! To lengthen concurrency test #0230 (according to documentation),
! Our DBMS uses "burst" mode CPU allocation:
sub *mp?001.[ps]* /keymax/ -i
! ----------------------------------------------------------------
! ----------------------------------------------------------------
! We declare SQLCA which includes SQLCODE
! So replace SQLCODE with SQLCA, but outside of the DECLARE SECTION.
! 1. Does not support ANSI SQLCODE declaration:
del *.pco /*01*SQLCODE*/ -p
ins> *.pco /WORKING-STORAGE/
! our precompiler doesn't understand END-EXEC; it wants semicolon.
! 2. Does not support COBOL END-EXEC:
sub *.pco /END-EXEC/
! our numeric literals must not end with a decimal point.
! 3. Does not support exact numeric literal ending in decimal point.
sub *dml005.pco /LONG_INT/

! ----------------------------------------------------------------
! ----------------------------------------------------------------
! UNION and UNION ALL not supported in same SQL statement
! Remove test number 160 and the paragraphs it performs
del *dml001.pco /BEGIN TEST0160/END TEST0160/
del *dml001.pco /P43./ ./


Sample Printout from Program Execution

NIST SQL Test Suite, V6.0, Embedded COBOL, dml001.pco

BugFreeDB v.783, UNIX SYS X 2.2, SuperPC 440

Ted Version 5.1 5/17/95

Date run YYMMDD: 961231 at hhmmssff: 09514908

declare with ORDER BY < column specification > DESC
reference X3.135-1992 section 13.1 General Rules 3) a)
- - - - - - - - - - - - - - - - - - -


EMPNO=E4 and HOUR1=20
EMPNO=E3 and HOUR1=20
EMPNO=E2 and HOUR1=80
EMPNO=E1 and HOUR1=20

EMPNO=E1 , i=4
The answer should be EMPNO=E1, i=4 &
order by EMPNO DESC.

*** pass ***


Sample Summary Reports

************************* PROBLEMS *************************

>>>>>>>>>>>>>>>>>>> FIPS 127-2 Entry SQL <<<<<<<<<<<<<<<<<<<

--------------- FIPS 127-2 Entry Syntax Flags --------------

0296 Test 0296 in flg005: FIPS Flagger - vendor provided character function
0296 MAD = fail

0832 Test 0832 in flg012: FIPS Flagger - CREATE INDEX
0832 MAD = missing
0832 PC = missing
0832 PCO = missing
0832 PFO = missing

---------------- ISO/IEC 9075:1992 Entry SQL ---------------

0003 Test 0003 in dml001: CURSOR with ORDER BY DESC integer, named column
0003 PC = missing
0003 PFO = missing

0005 Test 0005 in dml001: CURSOR with UNION ALL
0005 MAD = fail

0007 Test 0007 in dml003: Error for second consecutive CLOSE
0007 MAD = missing
0007 PC = fail
0007 PCO = missing
0007 PFO = fail

0399 Test 0399 in ccc009: C language storage class and class modifier comb.
0399 NOTE: visual chk
0399 PC = missing

0429 Test 0429 in ada005: Ada reminder to check SQL_STANDARD package format
0429 NOTE: visual chk
0429 MAD = missing

0503 Test 0503 in dml084: SQLSTATE 42000: syntax error or access rule vio.1
0503 NOTE: synvio_yes
0503 PFO = fail

Total number of problems: 15


Sample Summary Reports
TOTALS Listing

************************** TOTALS **************************

>>>>>>>>>>>>>>>>>>> FIPS 127-2 Entry SQL <<<<<<<<<<<<<<<<<<<

missing 3322
missing 0000
NA50 41 40 49
DL 0 000
WD 0 0 0 0

Grand total = 1944
Problem total = 15


Sample Summary Reports

*********************** TEST RESULTS ***********************

>>>>>>>>>>>>>>>>>>> FIPS 127-2 Entry SQL <<<<<<<<<<<<<<<<<<<

--------------- FIPS 127-2 Entry Syntax Flags --------------

0296 failpasspasspass
0297 passpasspasspass
0454 passpasspasspass
0830 NApasspasspass
0831 passnogonogopass
0832 missingmissingmissingmissing
0833 NApasspasspass

---------------- ISO/IEC 9075:1992 Entry SQL ---------------

0003passmissing passmissing
0007missing failmissing fail

Total number of tests: 486



"FIPS Flaggers" Examples

Program FLG005: |_______________________________________________
$SQLPRE/COBOL/ansi/flag FLG005.PCO
%SQL-I-NONSTASYN92E, (1) Nonstandard SQL92 Entry-level syntax
WHERE GRADE = '-12';
%SQL-I-NUMCMPTXT, (1) Numeric column compared with string literal as text
WHERE GRADE = '-12';
%SQL-I-NONSTACON, (2) The standard does not permit this data type conversion

Program FLG006: |_______________________________________________
$SQLPRE/COBOL/ansi/flag FLG006.PCO
%SQL-I-NONSTALNM, (1) Nonstandard long name
%SQL-I-NONSTALNM, (1) Nonstandard long name
%SQL-I-NONSTALNM, (1) Nonstandard long name
%SQL-I-NONSTALNM, (1) Nonstandard long name

Program FLG009: |_______________________________________________
$SQLPRE/COBOL/ansi/flag FLG009.PCO
%SQL-I-NONSTACUC, (1) The standard requires columns merged by UNION be identical
%SQL-I-NONSTACUC, (1) The standard requires columns merged by UNION be identical


Automated Reporting System Diagrams
Table Definitions for Reporting System Diagrams

NIST SQL Test Suite, V6.0, report.sql

-- Schema for reporting structure of SQL Test Suite, Version 6.0
-- Followed by sample data and queries

-- =========================================================
-- Static tables to define the test suite structure
-- =========================================================

-- This table is an enumeration of all features
-- and collections of features (profiles) to be tested.
-- This is a reference table of codes (FEATURE1) and a
-- lookup table of names.
-- A profile has the value P in column1 of FEATURE1.
-- A logical profile has the value L in column1 of FEATURE1.
-- A lowest-level "leaf" feature is numeric in column1 of FEATURE1.
-- [A logical profile is a convenient grouping of features or
-- profiles, for purposes of recursion, but not reporting.].


-- This table describes the reporting structure for SQL testing;
-- i.e., the network of relationships between REPORTFEATURE rows.
-- Each row is a directed arc in the network.

-- Profiles and logical profiles are nodes in the hierarchy.


-- List of programs, authorization identifiers, and
-- special notes on how to run each the program.
-- P_NOTE indicates whether a test is a concurrency test,
-- requires a subroutine, etc.


-- List of test cases, descriptions, containing program, and
-- special notes on operational problems, such as
-- may not compile, may cause segmentation error, requires
-- visual inspection (ergo no pass/fail in TESTREPORT), etc.


-- Each test is for one or more features.
-- This table describes the test cases in the programs.


-- On-line cross reference to SQL-92 (population deferred)
-- Sequence number is decimal to facilitate adding references
-- between existing references without renumbering.
-- [same idea as Dewey Decimal system used in libraries]


-- =========================================================
-- Tables to specify vendor claims follow:
-- =========================================================

-- Features supported for this testing:


-- Bindings supported for this testing:


-- =========================================================
-- Tables to generate vendor-specific requirements follow:
-- =========================================================

-- Features required, to be derived recursively,
-- including claim to be tested -- C1,
-- lowest reporting profile -- P1, and
-- lowest recursive link -- L1.
-- F1 is the feature to be tested.


-- Working version of F_REQ,
-- needed because an INSERT cannot be self-referencing.

F1 CHAR(4),


-- Tests selected for this validation,
-- corresponding to the features selected and
-- corresponding to the bindings selected.
-- Result will be derived later from TESTREPORT.










Informational Interactive Concurrency Test

Interactive SQL test files MPA001* and MPB001* through MPA008* and MPB008* have been superseded by the MPQUIC procedure, which is much more efficient. They are now considered optional. These files are still included in the Interactive SQL test suite because they may be helpful for SQL products which do not have a programming language interface. Each concurrency test has four parts. MPA*I.SQL initializes data in the test tables and is to be run first. MPA*T.SQL evaluates the test tables after the concurrency test and is to be run last. MPA*R.SQL and MPB*R.SQL are to be typed by two testers. These transactions are intended to be run concurrently. Print all of the MP*.SQL files and use them as worksheets to be filled out and retained as documentation. Instructions are provided for executing each test case.

In general, two testers are needed for each concurrency test, and the testers proceed as follows. For each concurrency test, one tester should run MPA*I.SQL. Then one tester should type as directed by the instructions in MPA*R.SQL while the other tester should type according to MPB*R.SQL. The testers should begin simultaneously. Then each tester should work at his own pace, and as the SQL implementation allows, given there will be some locking. There are explicit instructions for each test. If ROLLBACK statements are needed and it appears that no progress is being made by either tester, then after a tester types a fifth ROLLBACK statement for a given test, the testers should alternate transactions, rather than executing them concurrently, in order to complete the workload. Finally, one of the testers should run MPA*T.SQL.

It is permissible for the SQL implementation to issue error messages if its strategy for serializability detects circumstances where it cannot deliver serializable transactions. It is NOT permissible for the SQL implementation to execute non-serialized transactions without warning. Hence, the testers should be prepared to terminate (ROLLBACK) a transaction which encounters error or warning messages and to retry the transaction.

For Each Program Pair

Print the MP*.SQL files and use as worksheets.

Run the stored procedure file MPA*I.SQL to initialize.

Type or run the MPA*R.SQL and MPB*R.SQL tests as described below, following the instructions in the worksheet.

Testers may use "up arrows," function keys, and other shortcuts commonly used in the environment being tested.

Run the MPA*T.SQL file to evaluate the test results.

Concurrency Test #1

The first concurrency test has 2 simultaneous transactions attempting to insert a row, assigning the next available key number (MAX(KEYNUM) + 1).

Each tester, MPA and MPB, types a SELECT to determine the current maximum key and then an INSERT to create a new row with the next highest key number. Each tester records on the worksheet the ANUM values committed.

If SQL issues a warning message for either SQL command in the transaction, the tester types a ROLLBACK and repeats the transaction; otherwise, the tester types COMMIT. If all appears to go well for the COMMIT, the tester considers the transaction to have terminated successfully.

After 5 successful transactions, the tester waits for the other tester to finish.

Concurrency Test #2

The second concurrency test has 2 simultaneous transactions attempting to transfer money into the same account.

Each tester, MPA and MPB, types a SELECT statement to determine the dollar balance for row number 25. The tester then adds 10 to the dollar balance and types an UPDATE statement to set the balance to the new amount. Each tester records on the worksheet the DOLLARS values committed.

If SQL issues a warning message for either SQL command in the transaction, the tester types a ROLLBACK and repeats the transaction; otherwise, the tester types COMMIT. If all appears to go well for the COMMIT, the tester considers the transaction to have terminated successfully.

After 5 successful transactions, the tester waits for the other tester to finish.

Concurrency Test #3

The third concurrency test has 2 simultaneous transactions attempting to transfer money from one account to another.

Each tester, MPA and MPB, types two UPDATE statements to transfer money from a common account into separate accounts. If SQL issues a warning message for either SQL command in the transaction, the tester types a ROLLBACK and repeats the transaction; otherwise, the tester types COMMIT. If all appears to go well for the COMMIT, the tester considers the transaction to have terminated successfully. The tester then checks a box on the worksheet to count the successful transaction.

After 5 successful transactions, the tester waits for the other tester to finish.

Concurrency Test #4

The fourth concurrency test has 2 simultaneous transactions headed towards deadlock.

The two testers run their test files simultaneously. If SQL issues a warning message to a tester, then the tester types ROLLBACK and repeats the transaction; otherwise, the tester types COMMIT. If all appears to go well for the COMMIT, the tester stops.

These tests must be typed rather than run as a stored procedure if it appears that one transaction completes entirely before the other begins. There are instructions for shortening the test case if it is actually being typed.

If both testers deadlock twice, then MPB004R.SQL should wait until MPA004R.SQL completes a third attempt and commits before attempting again.

Concurrency Test #5

The fifth concurrency test determines whether uncommitted inserted rows are visible to another transaction.

The fifth concurrency test starts out with 25 rows in a table. MPA then alternately deletes and inserts batches of 5 rows until MPB has completed its workload. The MPB transaction alternately reads and counts the rows in the table. The MPB transaction expects to see results with either 20 or 25 rows.

If SQL issues a warning message for any SQL command in either of the transactions, the tester affected types a ROLLBACK and repeats the transaction; otherwise, the tester types COMMIT. If all appears to go well for the COMMIT, the tester considers the transaction to have terminated successfully.

After 6 successful typings of the MPB script, the MPB worksheet is completed and both testers may stop.

Concurrency Test #6

The sixth concurrency test is a rerun of the fourth test with the intention of reviewing the deadlock message, if any, to ensure that it notifies the use of transaction rollback or statement rejection (statement rollback), as appropriate.

The two testers type their test scripts simultaneously. If SQL issues a warning message to a tester, another query is typed to determine whether there was a statement rejection or a transaction rollback. The message must indicate which action is taken and must be accurate.

Concurrency Test #7

The seventh concurrency test starts with 1 row in table AA and 1 row in table BB. MPA attempts to transfer (READ/INSERT/DELETE) all rows from table AA to table BB; whereas MPB attempts to transfer all rows from table BB to table AA.

If SQL issues a warning message for any SQL command in the transaction, the tester types a ROLLBACK and repeats the transaction; otherwise, the tester types COMMIT. If all appears to go well for the COMMIT, the tester considers the transaction to have terminated successfully. The tester then checks a box on the worksheet to count the successful transaction.

After 2 successful transactions, the tester waits for the other tester to finish.

Concurrency Test #8

The eighth concurrency test determines whether MPB can insert a row with primary key which MPA has deleted, but not committed.

MPA deletes row with primary key value of 3. MPA then inserts a different row with primary key 3 and COMMITs. MPA repeats this scenario twice and should see no error messages. MPB meanwhile tries to insert a row with primary key value of 3. MPB should never be successful because the primary key value of 3 already exists. MPB notes error messages and quits after three attempts.