Andrew Eisenberg

SQL:2003 has been published

SQL:2003 has finally achieved final publication as an International Standard, replacing SQL:1999. SQL:2003 is popularly believed to be largely a “bugfix release” of the SQL standard — except, of course, for the SQL/XML work on which we have previously reported. However, as you will learn from this and future columns, there are many compelling new features in the 2003 edition of the SQL standard. We are pleased that three of the more active SQL proposal writers have joined forces to present several of those new features in this month’s column. Krishna Kulkarni is, among other responsibilities, the formal International Representative for the INCITS H2 Technical Committee for Database. Jan-Eike Michels is a frequent USA representative to the corresponding international group, ISO/IEC JTC1/SC32/WG3. Fred Zemke is widely acknowledged as a principle expert in many areas of the SQL standard and also a regular USA representative to WG3. In the coming months, we will provide information about even more of SQL:2003’s new features.

SQL multimedia and application packages (SQL/MM)

Regular readers of this column will have become familiar with database language SQL -- indeed, most readers are already familiar with it. We have also discussed the fact that the SQL standard is being published in multiple parts and have even discussed one of those parts in some detail[l].Another standard, based on SQL and its structured user-defined types[2], has been developed and published by the International Organization for Standardization (ISO). This standard, like SQL, is divided into multiple parts (more independent than the parts of SQL, in fact). Some parts of this other standard, known as SQL/MM, have already been published and are currently in revision, while others are still in preparation for initial publication.In this issue, we introduce SQL/MM and review each of its parts, necessarily at a high level.

Advancements in SQL/XML

Since we last wrote about SQL/XML in [2], the first edition of that new part of the SQL standard has been officially published as an international standard [1], commonly called SQL/XML:2003. At the time of that earlier column, SQL/XML was just entering its first official ballot, meaning that (possibly significant) changes to the text were expected in response to ballot comments submitted by the various participants in the SQL standardization process.

The WS-DAI family of specifications for web service data access and integration

This month, we are pleased to provide to our readers a column that addresses an important aspect of grid computing: data access.

Standards for databases on the grid

Relational databases supported applications in a centralized environment in the 1960s and 1970s. They progressed to a client/server environment in the 1980s. The 1990s saw application servers with a multi-tiered architecture, in most cases supported by an RDBMS. Most recently we have seen the emergence of XML, XML storage in DBMSs, navigation within an XML document via XPath, and the XQuery query language for XML. In this article, Susan provides an introduction to the Grid and describes how databases will be used in this new environment. The Global Grid Forum (GGF) is producing technical specification to enable both Relational and XML databases to be located, accessed, and replicated in this environment. They make use of a variety of existing an emerging database, file, networking, and web services standards. Susan is a Senior Technical Staff Member at IBM. She is a member of the GGF DAIS (Data Access and Integration Services) working group. Jim Melton and Andrew Eisenberg

New standard for stored procedures in SQL

In the example above, it is possible that the first insert statement succeeds, and the second insert statement fails due to the failure of a uniqueness constraint. Any partial work of the second insert statement is undone, because SQL considers all DML statements to be atomic. The work of the first statement would not be undone, because the compound statement is a non-atomic statement (as are all of the other flow-of-control statements that will be discussed shortly). PSM-96 provides a second form of the compound statement that is atomic.

The SQL Standard

Temporal database support was added to the SQL standard in 2011. This lengthy chapter explains that support in detail and compares and contrasts it with the ideas introduced in previous chapters. It discusses “periods” (SQL’s analog of intervals, represented by explicit from/to pairs); an SQL base table can have at most one application time period (corresponding to valid time) and at most one system time period (corresponding to transaction time). SQL supports analogs of certain of the interval operators discussed in previous chapters; unfortunately, however, it has nothing analogous to PACK and UNPACK. The chapter discusses all of these operators, also database design considerations, queries, and updates in the SQL context. In particular, it explains how queries and updates work on “tables with system time” (especially system-versioned tables) and on “bitemporal tables” (tables with both application time and system time). The chapter concludes with a detailed analysis and assessment of the SQL temporal features.

XML schema

This month’s column deals with metadata for XML, primarily the W3C’s XML Schema Recommendation. XML Schema is often seen as highly complex, but quite powerful. We have worked with Chuck Campbell in several standards arenas, including the SQL standard and XML Query. Chuck is an invited expert to the W3C’s XML Schema WG, so we invited him to write a column outlining the features and futures of XML Schema. Jim Melton and Andrew Eisenberg

SQLJ Part 1: SQL routines using the Java programming language

The SQLJ informal group of companies has continued to work productively since last we wrote about them in December 1998 [1]. At that time we discussed SQLJ Part 0 [2], which had just been approved as NCITS standard SQL Part 10: ObjectLanguage Bindings (SQL/OLB). This standard allows SQL statements to be embedded in the Java Programming Language. In the first half of 1999, the SQLJ group (consisting of Cloudscape, Compaq (Tandem), IBM, Informix, Oracle, Sun, and Sybase) completed its work on the SQLJ Part 1 specification and requested that it be adopted by NCITS under its Fast Track process. In Sept. 1999 SQLJ Part 1 was adopted as NCITS 331.1-1999 [3] and it is now available for purchase from NCITS. It is worth mentioning that this specification is extremely approachable, with a lengthy tutorial section that introduces its more normative elements. Sybase brought SQLJ Part 1 to the SQLJ group in early 1997. Phil Shaw, of Sybase, has acted as editor of this document throughout its development. SQLJ Part 1 allows Java classes, contained in Jar files, to be brought into a DBMS. Methods in these classes may then be used as the implementation of SQL stored procedures and stored functions (together referred to as stored routines). Given how these methods are used, we’ll provide a brief introduction to SQL routines before we discuss the features of SQLJ Part 1.

An early look at XQuery

XQuery is a query language for real and virtual XML documents and collections of these documents. Its development began in the second half of 1999. With roughly 3 years of work completed, it’s high time that we provided an initial description of this language, and a sense of where it is in its development cycle. XQuery is being developed within W3C. Every consortium of this type has its own rules and its own ways of getting its work done. W3C provides visibility to the public by making available drafts of the specifications that it has under development at relatively frequent intervals. Mailing lists are established for each specification to allow the public to provide feedback on these drafts. Unfortunately, the W3C process does not allow us to publicly discuss the internal workings of the XML Query WG, including schedules, proposals that are being considered, and discussions that have taken place. Even so, with the amount of material that is contained in the most recent public drafts of these specifications, we have more than enough to discuss.