wecker@cookie.dec.com (DAVE TANSTAAFL WECKER) (11/11/86)
The following is in response to the comments concerning the SQL standard. Please note that these are personal comments of Jim Melton (not myself) and do not represent official positions of either the ANSI committee or Digital Equipment Corporation. Jim can be contacted at: decwrl!cookie.dec.com!melton --------------------------------------------------------------------------- >>> After looking briefly at the proposed ANSI standard for SQL and >>>talking to one member of the committee, I am mystified! The standard >>>does not include any definition of an interactive query language. >>>Gosh, I thought SQL started as a query language. >>> This does not mean that vendors will throw away their query language >>>implementations, just that there is no standard for an interactive >>>version of the language. The newly approved ANSI standard, embodied in ANSI X3.135-1986, clearly specifies (I believe it's in Section 1.7 (Conformance), Paragraph 3): An implementation claiming SQL DML conformance shall process, either at level 1 or level 2: a. Direct invocation of SQL data manipulation language statements (<SQL statement>); and/or b. Module language (<module>); and/or c. one or more of [a list of 4 embedded languages]. Statement a. above indicates pretty reasonable to me that an interactive query language is defined by the standard. Of course, Paragraph 5 of the same section states: A conforming implementation may provide additional facilities or options not specified by this standard. An implementation remains conforming even if it provides user options to process non-conforming SQL language or to process conforming SQL language in a non-conforming manner. >>>- "select * from employee" (where employee is a valid table) is NOT a >>> valid command. Neither is "select empname from employee" (where empname >>> is a valid column). (They are valid as part of a 'cursor declaration', >>> but not as stand-alone statements). I disagree with this statement as well. Section 3.25 (<query specification>) lists the syntax for queries, and: SELECT * FROM EMPLOYEE is clearly permitted by the syntax, as is: SELECT EMPNAME FROM EMPLOYEE Now, I admit that <query specification> is not the same as a statement. Section 6.10 (<select statement>) defines a statement which has the same syntax as a <query specification> with the exception of a clause which assigns values to parameters. Now, obviously parameters are interesting for procedures, and not interesting for an interactive query language. Consequently, all the implementations of which I am aware have deviated from the standard (as in Section 1.7, Paragraph 5) to remove the parameter- oriented clause from the statement when used interactively. >>>- There is no way to delete a table, you can delete all the rows (records), >>> but not the table. You're absolutely right about this. Of course, most (probably all, but I hate making absolute statements without having verified them!) implementations provide a DROP TABLE statement to do just what you described. The reason it's not in the standard is (big surprise here) political: not enough people could agree on the syntax, so they left it to the implementations (Section 1.7, Paragraph 5, again). >>>- The only place the "create table ..." expression is valid is as PART of >>> a "create schema" statement. This is the only statement in the 'data >>> definition language'. Yes, a different language (with only one >>> statement type) is required when you create tables. Again, you're absolutely right. All my comments about deleting a table apply here, too. The only thing that the committee could agree on was initial creation of a database. Modification of the database was left to implementor discretion. >>> What does this mean? I think it means that there will be no guarantee >>>of portability for programs written in the interactive versions of SQL >>>that are out there. But, take heart, they are standardizing the >>>interface to PL/1. (OK, this is a cheap shot, they also are >>>standardizing the interfaces to C, FORTRAN, Pascal, ...). There's a certain amount of justification for your apprehension. However, this apprehension applies equally well to embedded SQL! Remember that infamous Paragraph 5. It applies to embedded and module SQL as well as to interactive SQL. A comparison of various vendors' SQL implementations will quickly reveal differences along these lines. >>> Hey committee: is it intentional that there are no delimiters between >>><schema elements> and no terminator for a <schema> statement? I'm going to have to make some semi-educated guesses here, since I wasn't on the committee when this was discussed. But, I infer that the committee didn't intend for SQL DDL statements and DML statements to be mixed in a single "program". Therefore, the delimiters and terminator were viewed to be unnecessary, since a parser can easily determine the separation between each <schema element> from keywords alone. In addition, if there is no mixing of DDL and DML, then the end of the DDL-containing program will serve nicely as the end of the <schema>. Further discussion welcomed, of course.