SQL:1999

INCITS

SQL:1999 was developed by the INCITS Technical Committee H2 on Database. This committee develops standards for the syntax and semantics of database languages.

SQL:1999š specifies the SQL:1999 object model. It adds User Defined Types (UDTs) to SQL. There are two types of UDTs: distinct types and structured types. A distinct type is based on a built-in data type, such as INTEGER, but whose values cannot be directly mixed in operation with that built-in type. A structured type has an internal structure such as an address type that might have street, state, and postal code attributes as part of the structure. Structured types can be given specific user-defined functions and participate in type hierarchies with other structured types. Single inheritance is supported. Multiple inheritance of interfaces (as in Java) is not supported. Both distinct and structured types can be used as the data type for columns, SQL variables, a field of a row type, or as attribute of another UDT. Java objects can be either stored in a serialized byte stream as an SQL BLOB (Binary Large OBject) or mapped to a row in typed tables. With the SQL BLOB approach, an object is stored in a cell of a table or stored as a UDT in a cell of a table. It is possible to define methods that operate on the object using either approach.

The SQL:1999 object model also allows you to declare that a UDT is the type of an entire table. Each row is an instance of the type. Each attribute of the type is transformed into a column of the table. The methods associated with the type are also associated with the table. Typed tables can have an additional self-referencing column. This is similar to the object identification (OID) used in classical object models. See object identification (new window). The self-referencing column holds a value that uniquely identifies that row of the table. In SQL:1999, instead of using the term OID, it uses REF value with a special data type called REF type. One type can contain a REF to another type, similar to the way OIDs are used in object models. REFs can only reference rows in typed tables and cannot reference type instances in cells of a table, in variables, or anywhere else.

The SQL:1999 object model also provides for table hierarchies in addition to type hierarchies. The relationships of the table hierarchy and the type hierarchy are not necessarily one-to-one. Basically, table hierarchies allow you to modify the behavior and appearance of the default behavior for typed tables. For example, most object models assume that if you query a super type, you will also get results from any of its subtypes. Table hierarchies allow you to modify that behavior so that when you query on the super type, you do not get results from any of its subtypes.

With SQL:1999 you have multiple ways to model your data:

Regular SQL tables and columns of built-in types
Regular SQL tables with some columns of built-in types and some of UDTs
Regular SQL tables with one column whose data type is a UDT
Typed tables

SQL:1999 is also used by SQLJ Part 2. See SQLJ (new window).

Because of backward compatibility to SQL-92, SQL:1999 supports a database sublanguage. This results in a programming style illustrated by the embedded SQL statements in the following diagram.

Embedded database sublanguage

An example of an embedded SQL statement that creates a new Person instance would be:

EXEC SQL BEGIN DECLARE SECTION;
    char SQLSTATE[6];
    char ssan[9];
    char name[30];
EXEC SQL END DECLARE SECTION

EXEC SQL
INSERT INTO person VALUES (:ssan, :name);

This code would be in addition to any host programming code. The variables :ssan and :name would need to be set by the host program using the host programming language before execution. If you also wanted to manipulate this new Person instance in the host program, you would need programming code in addition to this code fragment that populates the instance in the programming language along with the instance in the database.

Also see the NCITS H2 web site (new window).

š SQL:1999 is also known as SQL3 and SQL-99. SQL:1999 is the proper term. SQL3 was the working term before the specification became a standard.

More on the general topic: DBMS standards

SQL-92

JDBC

SQLJ

ADO.NET

Java Data Objects (JDO)

ODMG 3.0

Detailed comparison of ODMG 3.0 and JDO

Summary comparison of DBMS standards

Related books at Amazon.com

	SQL-99 Complete, Really by Peter Gulutzan, Trudy Pelzer Average Customer Review: based on 8 reviews. Customer Review: SQL-99 book has been a great help to me. It is the best technical manual i've read over the last years. Without it i could not have done my coding.
	SQL: 1999 - Understanding Relational Language Components by Jim Melton, Alan R. Simon Average Customer Review: based on 4 reviews. Customer Review: This is an important book. Most books on SQL cover simple SQL statements, and/or focus on a particular vendor's SQL implementation. Very few books attempt to cover the SQL standard in any depth, if at all. This one does. Furthermore, because one of the authors (Jim Melton) is the editor of the ISO SQL Standards Committee, the book is...
	Advanced SQL: 1999 - Understanding Object-Relational and Other Advanced Features by Jim Melton Publisher: Morgan Kaufmann Publication Date: September 2002
	SQL: 1999 - Understanding Relational Language Components (The Morgan Kaufmann Se by Jim Melton; Alan R. Simon Publisher: Morgan Kaufmann Publication Date: January 2001
	Einführung in den Sprachkern von SQL-99 (German Edition) by Wolfgang Panny, Alfred Taudes Publisher: Springer Publication Date: July 2000

More related books: Search Amazon.com for SQL 1999

SQL:1999

Read more free articles on this site

Search this site for more articles

Browse this site for more articles

Related books at Amazon.com