Docstoc

ISO ANSI

Document Sample
ISO ANSI Powered By Docstoc
					                                                                       WG3:YGJ-017
                                                                        X3H2-99-085
                                                                            March, 1999


                                         ISO
                International Organization for Standardization

                                        ANSI
                     American National Standards Institute




                                    ANSI TC X3H2
                             ISO/IEC JTC 1/SC 21/WG 3
                                       Database



Title: (ISO Working Draft) Management of External Data (SQL/MED)
Author: Jim Melton (Editor)
References:
1) ANSI X3H2-99-078/WG3:YGJ-010, (ANSI/ISO Working Draft) Framework (SQL/Framework),
   March, 1999

2) ANSI X3H2-99-079/WG3:YGJ-011, (ANSI/ISO Working Draft) Foundation (SQL/Foundation),
   March, 1999

3) ANSI X3H2-99-080/WG3:YGJ-012, (ANSI/ISO Working Draft) Call-Level Interface (SQL/CLI),
   March, 1999

4) ANSI X3H2-99-081/WG3:YGJ-013, (ANSI/ISO Working Draft) Persistent Stored Modules
   (SQL/PSM), March, 1999

5) ANSI X3H2-99-082/WG3:YGJ-014, (ANSI/ISO Working Draft) Host Language Bindings
   (SQL/Bindings), March, 1999

6) ANSI X3H2-99-083/WG3:YGJ-015, (ANSI/ISO Working Draft) XA Specialization (SQL/Transaction),
   March, 1999

7) ANSI X3H2-99-084/WG3:YGJ-016, (ANSI/ISO Working Draft) Temporal (SQL/Temporal),
   March, 1999

8) ANSI X3H2-99-085/WG3:YGJ-017, (ANSI/ISO Working Draft) Management of External Data
   (SQL/MED), March, 1999
9) ANSI X3H2-99-086/WG3:YGJ-018, (ANSI/ISO Working Draft) Object Language Bindings
   (SQL/OLB), March, 1999




2
1 Possible problems with SQL/MED

I observe some possible problems with SQL/MED as defined in this document. These are noted
below. Further contributions to this list are welcome. Deletions from the list (resulting from change
proposals that correct the problems or from research indicating that the problems do not, in fact,
exist) are even more welcome. Other comments may appear in the same list.
Because of the highly dynamic nature of this list (problems being removed because they are solved,
new problems being added), it has become rather confusing to have the problem numbers automati-
cally assigned by the document production facility. In order to reduce this confusion, I have instead
assigned "fixed" numbers to each possible problem. These numbers will not change from printing to
printing, but will instead develop "gaps" between numbers as problems are solved.
Possible problems related to SQL/MED
Significant Possible Problems:
999 In the body of the Working Draft, I have occasionally highlighted a point that requires urgent
attention thus:
                                         **Editor’s Note**
Text of the problem.

These items are indexed under "**Editor’s Note**".




                                                   Possible problems with SQL/MED           Notes–1
Editor’s Notes for DBL:YGJ-017 and X3H2-99-085


Minor Problems and Wordsmithing Candidates:




Notes–2 Editor’s Notes for (ISO-ANSI working draft) Management of External Data (SQL/MED)
                         Editor’s Notes for DBL:YGJ-017 and X3H2-99-085


Language Opportunities




                             Possible problems with SQL/MED     Notes–3
Editor’s Notes for DBL:YGJ-017 and X3H2-99-085




Notes–4 Editor’s Notes for (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                              Editor’s Notes for DBL:YGJ-017 and X3H2-99-085



2 Guidelines for writing "user-friendly" change proposals
This chapter of the Editor’s Notes offers guidelines to the style of the content of the SQL document
and all its Parts, as well as some guidelines for writing change proposals.
Readers should refer to the end of this Clause of the Editor’s Notes for a checklist of issues that
must be addressed by change proposals. Every change proposal must clearly indicate whether or
not the issue is relevant to the subject matter of the proposal and, if so, how it is addressed.
The Editor reserves the right to reject any change proposals that do not follow these guidelines, as
appropriate.

2.1 Style of content of SQL document

2.1.1 General

2.1.1.1   Language
Since the SQL standard will be translated into other languages, and must in any case be read
and understood by many whose first language is not English, simplicity of language is much more
important than literary elegance.
Normal rules of good style apply; if in doubt, there are several excellent references to writing style
that may be consulted.
The following specific points are noted:

— Conditions: The standard form used is ‘‘If some condition is satisfied, then something happens.’’
  This is strongly preferred to ‘‘X happens if Y is true.’’ The ‘‘otherwise’’ case always deserves
  consideration, though it doesn’t always need to be stated (particularly when the otherwise case
  involves no action or behavior).

— Number: It is clearer to stick to the singular and say, for example, ‘‘every X is destroyed’’ than
  to say ‘‘all xs are destroyed’’. Where ‘‘each’’ is acceptable, it is even better.

— Avoid the use of the word ‘‘any’’ as far as practicable. In ordinary English, it sometimes means
  ‘‘some’’ (as in ‘‘have you any shares in Company X?’’), and sometimes means ‘‘every’’ (as in ‘‘you
  can call any day, any time’’).
   In particular, avoid constructions like ‘‘Let CD be any collation descriptor that includes . . . ’’.
   Use precise quantification, such as ‘‘For every collation descriptor CD that includes . . . ’’, and
   note that it is not necessary to add ‘‘if any’’—even if there is no such collation descriptor, the
   rule still works fine.

— ‘‘which’’ versus ‘‘that’’: See X3H2-88-036 (copies available upon request). ‘‘That’’ should be used
  much more often than many people do.


2.1.1.2   Vocabulary
English is well known for having many synonyms (change, alter, amend), partially overlapping
synonyms (describe and define, change and improve), and near-synonyms with subtle differences
(edible and eatable), or even ignored distinctions (expect and anticipate). It may be assumed that
any reader has a dictionary available, but where a word can be used in more than one sense, the
correct interpretation should not depend on context any more than is absolutely necessary. If a
word can have the same meaning or translation into another language as some other word used in
SQL3 and no distinction is intended, then that other word should be used in preference.


                           Guidelines for writing "user-friendly" change proposals              Notes–5
 Editor’s Notes for DBL:YGJ-017 and X3H2-99-085
2.1 Style of content of SQL document

Particular words used in specific senses in SQL3 include:

— Definition: Either a BNF non-terminal that causes the creation of something, or a definition of
  the meaning of a term.

— Descriptor: A persistent, formal description of an SQL object. See Subclause 7.2.4,
  "Descriptors", in Part 1.

— Specify: To state explicitly, for example ‘‘If GLOBAL is specified, . . . ’’ means ‘‘If the word
  ’GLOBAL’ actually appears, . . . ’’. Note that ‘‘ . . . is specified . . . ’’ appears more frequently
  than ‘‘ . . . was specified . . . ’’.

— Case: is favored. Nested cases are preferred to complex ones. Only the first rule is applied
  for which the condition is satisfied, so the order of the subrules is important. A Case construct
  normally ends with an ‘‘Otherwise’’ subrule, but this is not necessarily required.

Other terms worth noting are:

— Database: Note: this is one word. A collection of SQL-data. The term is to be avoided, because
  it raises the question of whether databases can overlap, be nested, are each described by a
  catalog or cluster of catalogs, etc. Usually, the term ‘‘SQL-data’’ is sufficient.

— Description: An informal description; not used in a technical sense. Note that the object that
  serves as a persistent description of an SQL object is known as a descriptor.


2.1.1.3   Quantification
When universally quantifying, prefer the singular ‘‘every’’ to the plural ‘‘all’’—it nearly always works
better. Although mathematicians often say ‘‘for all x’’, it is not really good grammar (because the
upside-down A (8) of predicate calculus is considered to stand for ‘‘all’’, the first letter of ‘‘every’’
having been taken by the backwards E (9) that stands for ‘‘exists’’).
When existentially quantifying, use ‘‘some’’, not only in preference to ‘‘any’’, but also sometimes in
preference to the indefinite article ‘‘a’’ or ‘‘an’’, which can be as ambiguous as ‘‘any’’. However, this
suggestion can lead to ‘‘overkill’’ text, so we further suggest that the indefinite article is appropriate
in cases where it is clear that there can be no more than one occurrence of the thing in question,
as in : ‘‘For every collation descriptor that includes a <translation collation> . . . ’’ (a collation
descriptor includes at most one <translation collation>, so the word ‘‘some’’ instead of ‘‘a’’ could even
be misleading here).


2.1.1.4   Quotation marks
Single <quote>s (’) enclose <character string literal>s; <double quote>s (") enclose <delimited
identifier>s. In text, double quotes (") are normally used to surround quoted material.


2.1.1.5   Typography

— <key word>s (only) are in <simple Latin upper case letter>s.

— Truth values (as opposed to Boolean values) are lower-case, italicized, and underlined: true ,
  false , and unknown .




Notes–6 Editor’s Notes for (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                               Editor’s Notes for DBL:YGJ-017 and X3H2-99-085
                                                           2.1 Style of content of SQL document

— With the exception of certain index entries and certain table headings, the only bolding in the
  document is of:

    •     Ada keywords such as package, because the Ada convention is followed;

    •     The terms being defined in Subclause 3.1, "Definitions"; and

    •     Names of pseudo-procedures (visible now only in the definition of significant DB_changes,
          where the procedure proc_VC is defined).

— Clauses (‘‘chapters’’ to some) are the major divisions in the document; Subclauses are all the
  lesser divisions. The names of Clauses and Subclauses are all spelled with initial capital letters;
  subsequent words in those names are spelled with initial lower-case letters—except, of course,
  for such words as ‘‘SQL’’.


2.1.2 SQL terminology

2.1.2.1     Terms for SQL objects
Terms referring to SQL objects must be carefully chosen, and used not only correctly but wherever
possible.
Such terms are frequently prefixed with ‘‘SQL-’’ in order to distinguish them from similar terms
used with different meanings in related contexts. For example: SQL-transaction, SQL-session,
SQL-client. Note that SQL-schema is, strictly, the correct term, because it has a different meaning
from ‘‘schema’’ in the Reference Model of Data Management, but ‘‘schema’’ is used throughout the
SQL document because it is always the SQL sense that is intended.
Ambiguities such as ‘‘object identifier’’ should be avoided.


2.1.2.2     BNF non-terminal symbols
The names of BNF non-terminals do not often contain hyphens; where they do, it is normally
because the analogous English phrase would contain hyphens (‘‘form-of-use’’, ‘‘implementation-
defined’’). They sometimes contain colons to distinguish closely related constructs (for example,
<delete statement: positioned> and <delete statement: searched>).
Care should be taken not to introduce over-general names. For example, <SQL statement> is
unfortunate in that its production rule does not allow any SQL-statement.
Every new <key word> must be specified as a <reserved word> or <non-reserved word>, as appro-
priate.
Precision in referring to BNF non-terminals is important. For example: there is no such thing as a
<statement>.
Do not fall into the trap of assuming that the name of a BNF non-terminal means the same thing as
the words contained within the angle brackets. When writing a rule about <select list>, do not then
start using the phrase ‘‘select list’’ as though it has some meaning. BNF non-terminals are nothing
more than distinguished character strings whose spelling is irrelevant except for consistent use. If
‘‘<select list>’’ were replaced ‘‘automatically’’ with ‘‘<SL>’’, one would not feel comfortable discussing
the ‘‘SL’’ as a normal English phrase.




                            Guidelines for writing "user-friendly" change proposals             Notes–7
 Editor’s Notes for DBL:YGJ-017 and X3H2-99-085
2.1 Style of content of SQL document


2.1.2.3     BNF non-terminal or name of SQL object?
BNF non-terminals do not denote persistent objects. In particular, it is incorrect to say ‘‘If an
<assertion definition> exists . . . ’’; an <assertion definition> is source code, which, once processed,
ceases to exist as far as the SQL implementation is concerned. The result of processing it is an
assertion, represented by an assertion descriptor in some schema.
data type or <data type>? ‘‘data type’’ is the correct term, unless it is specifically intended to refer
to the BNF nonterminal ‘‘<data type>’’ (probably appropriate only when discussing the syntax).
Where reference is intended to a generic data type, such a character string type, then that is the
preferred term.. SQL, at the time of writing, uses ‘‘character data type’’, ‘‘<character string type>’’,
‘‘data type CHARACTER’’, and several other phrases almost synonymously.
‘‘<preparable statement>’’ is a BNF non-terminal; ‘‘prepared statement’’ is not.


2.1.2.4     Notes on specific SQL terms
‘‘contain’’ is used for syntactic containment, i.e., one BNF non-terminal is said to contain others.
‘‘data type’’ is two words. ‘‘datetime’’ is one word. ‘‘include’’ is used for specifying that one descriptor
is included in others.


2.1.2.5     Locally-defined terms and symbols
Diacritical or other marks, such as prime, are to be avoided for typographical reasons.
Other considerations involving terms and symbols:

— Symbolic names are italicized and composed of <simple Latin upper case letter>s and <digit>s.
  Examples: T, C1, and SLCC.

— Indexes or subscripts are in lower-case italic letters and digits. Examples: ‘‘the i-th column’’,
  ‘‘Ck ’’, or ‘‘at least j values’’.

— Special symbols for use as ‘‘range variables’’ (like the ‘‘CD’’ earlier) should not be introduced
  unnecessarily. For example:
          Every aardvark-reference A that contains . . . is removed from every zoological paper that
          includes A.
    is preferable to:
          For every zoological paper ZP, for every aardvark-reference A that contains . . . and is
          included in ZP, A is removed from ZP.
    Special symbols are necessary to avoid clumsiness and to ensure precision in many of the more
    complicated rules.
    These special symbols’ scope is the Subclause in which they are defined. While they may
    be defined early in a Subclause (for example, in the Syntax Rules) and used much later (for
    example, in the General Rules), it is preferable to define them in the section in which they are
    used within a Subclause.




Notes–8 Editor’s Notes for (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                     Editor’s Notes for DBL:YGJ-017 and X3H2-99-085
                                                                 2.1 Style of content of SQL document


2.1.2.6     Abbreviations
Abbreviations such as ‘‘auth-id’’ are not used in the SQL standard, however widely they may be
generally used and understood. It follows that they are not acceptable in text proposed for insertion
in the document.


2.1.3 The content of the SQL standard

2.1.3.1     Definitions
This section should conform to the (normative!) Annex B.1 of the ISO Directives, Part 3.
A term should be defined in this section if and only if:

— It is used through the Standard, rather than in any particular context; and

— Either:

    •     It is a term not widely used or that has been invented for the Standard; or

    •     It has more than one meaning and a precise meaning is consistently intended in the
          Standard.

A term should not be included simply for tutorial purposes (the Standard is already large enough).


2.1.3.2     Concepts
Although Clause 4, "Concepts", was originally introduced purely for the purpose of explanation, it
now contains much material that is definitive. There are no guidelines to what should or should
not be there. Thus Subclause 4.2.3, "Rules determining collating sequence usage", are classed as
concepts, while tables of valid casts and valid values of datetime data types are not.


2.1.3.3     Modularity
Except perhaps where the result would be a ridiculously short Subclause, the specification of a BNF
non-terminal used in more than one place should be in a separate Subclause from every one of its
uses. Otherwise, there is a serious risk of unintended effects resulting from changing the Rules in
one place without realizing that they should remain unchanged for the use elsewhere.
In particular, the construct ‘‘Every General Rule except General Rule 1 of Subclause x.y applies
here with ’something’ replaced by ’something else’’’ is to be avoided at all costs.


2.1.3.4     Format
The syntax of the version of BNF used is defined in Subclause 3.2, "Notation".
Every SQL character that is not a letter or digit has a name, defined in Subclause 5.1, "<SQL
terminal character>". Except in those BNF productions that define names for them, such characters
never appear standing for themselves, but are always represented by their names. Thus, except
where they are specified as <left bracket>, etc., the characters ‘‘[’’, ‘‘]’’, ‘‘{’’, ‘‘}’’, ‘‘ | ’’, and ‘‘ . . . ’’ in
BNF productions always serve as "punctuation".




                                Guidelines for writing "user-friendly" change proposals                     Notes–9
 Editor’s Notes for DBL:YGJ-017 and X3H2-99-085
2.1 Style of content of SQL document

Therefore, the production:

<x comma list> ::= ( <x> [ , <x> ]... )

should always be written as:

<x comma list> ::= <left paren> <x> [ <comma> <x> ]... <right paren>




2.1.3.5   Syntax Rules, Access Rules, and General Rules
The differences between Syntax and Access Rules on the one hand and General Rules on the other
are that Syntax and Access Rules specify requirements for a program to conform to (some level of)
the SQL standard, while General Rules specify what a standard-conforming SQL implementation is
required to do when it process such a standard-conforming program.
Where there are no Rules of any kind, it is usual to say ‘‘None.’’, to exclude the possibility that they
have not been thought about, or have been lost.


2.1.3.6   Syntax Rules
The correct form is ‘‘This condition shall be satisfied . . . ’’ or ‘‘If X is specified, then Y shall be
specified’’.
Syntax Rules do not deal with the privileges required to accomplish some action.


2.1.3.7   Access Rules
The correct form is as for Syntax Rules.
Access Rules do deal with the privileges required to accomplish some action.


2.1.3.8   General Rules
General Rules specify the effect of processing SQL source code that satisfies the relevant Format,
Syntax Rules, and Access Rules. The preferred form is present indicative passive, e.g., ‘‘A descriptor
is created . . . ’’.
By the time the Syntax and Access Rules have been processed, certain implications have become
explicit. Thus, if the optional <schema name> of a <table name> is omitted, it is deduced, and by
the time General Rule 1) is encountered, it can be assumed that <schema name> is known. It is not
normally necessary or desirable to mention that it is ‘‘explicit or implicit’’.


2.1.3.9   Exceptions
There are two categories of end-of-statement behavior:

— ‘‘ . . . an exception condition is raised: some condition’’; and




Notes–10 Editor’s Notes for (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                 Editor’s Notes for DBL:YGJ-017 and X3H2-99-085
                                                             2.1 Style of content of SQL document

— ‘‘ . . . a completion condition is raised: limited alternatives’’. The only acceptable alternatives for
  the completion conditions are successful completion, warning, and no data. Each of these may
  have no subcode or a specific subcode.

The exception class and subclass phrases are separated by a dash ‘‘—’’ and are always both in
italics.


2.1.3.10    Leveling Rules
The correct form is as for Syntax Rules. A Rule that illustrates a point by the use of an SQL
expression either shows the SQL expression on a separate line:
    Let B be an exact numeric result of the operation:
CAST (CAST (Y AS Q) AS E2)

or encloses the expression in double-quotes:
    ‘‘R is NULL’’ is true if and only if . . .


2.2 Writing change proposals

2.2.1 Discussion
It is helpful to the reader if the discussion part makes quite clear why the proposal is being made.
It also helps if any approaches that were considered and rejected are also mentioned, together with
the reasons for their rejection.
Proposal writers should strive to describe all changes and their implications in the discussion
part. Readers should have have to slog through the detailed language changes to understand the
consequences of the change.


2.2.2 Change proposals

A) Every separate item of the proposal should be numbered.

B) When referring to a Subclause of the document, always specify both Subclause number and the
   name of the Subclause (for example, Subclause 6.1, "<data type>").

C) When inserting, deleting, or replacing text or Rules in the document, specify both the reference,
   e.g., Syntax Rule 11)c)iv)2)B), and sufficient context to identify the specific paragraph, sentence,
   or Rule to be changed.
    For example, to replace Subclause x.y, Syntax Rule 11)c)iv)2)B), each of Syntax Rules 11), 11)c),
    11)c)iv), and 11)c)iv)2) should be unambiguously defined. Where, as for example in Subclause
    15.9, "<input using clause>", several Rules start off very similarly, say something like "Replace
    Syntax Rule 11)c)iv)2)B) (the Rule that deals with the xyz in an rst) with...".

D) Where only a few words of existing text are to be changed, it is clearer both to the reviewer and
   the Editor to flag differences in some way, e.g., by striking out deleted text and underlining
   new text or setting new text in boldface. Whatever convention is used should be explained.




                            Guidelines for writing "user-friendly" change proposals           Notes–11
 Editor’s Notes for DBL:YGJ-017 and X3H2-99-085
2.2 Writing change proposals


2.2.3 Use of Notes
Change proposals often must to draw the (proposal) reader’s attention to some detail or explanation.
This is best done by means of a note starting off with ‘‘Note to proposal reader:’’.
Similarly, change proposal sometimes need to call the Editor’s attention to some detail, such as the
need to assign an SQLSTATE class code or subclass code to a condition. This is best done by means
of a note starting off with ‘‘Note to the Editor:’’.
However, there is also the need for change proposals to insert notes into the text of the document
that they modify, so that the reader of the resulting standard has access to the note text. This is
best done by specifying something like ‘‘NOTE nnn: text of the note’’.


2.2.4 Check list
All changes to the document must be specified explicitly. It is unfair to the Editor to expect him to
make changes left implicit or unspecified.
The following list is provided in the hope of reducing the number of incomplete change proposals:

•   Conformance Rules and Appendix A, "SQL Conformance Summary",

•   Appendix B, "Implementation-defined elements" or Appendix C, "Implementation-dependent
    elements",

•   Appendix E, "Incompatibilities with X3.135-1992 and X3.135.4-1996", especially for new <re-
    served word>s,

•   Appendix E, "Incompatibilities with X3.135-1992 and X3.135.4-1996", for new class or subclass
    values—also please remind the Editor to index the new SQLSTATE values; also update the table
    of Ada package values in SQL/Foundation, Subclause 13.4, "Calls to an <externally-invoked
    procedure>".

•   The Dynamic SQL Descriptor Area

•   Information and Definition Schemas.

A proposal that solves a Possible Problem, Minor Problem, or Opportunity, whether intentionally or
not, should say so prominently, so that the Editor’s Notes may be kept up-to-date.
A proposal that introduces a Possible Problem, for example because it is admittedly incomplete,
should specify an appropriate addition to the Possible Problems list. The same applies to language
opportunities.
A proposal to insert lengthy pieces of text into the document should, either before or as soon as
practicable after it has been adopted, be provided to the Editor in machine-readable form, either
on diskette or by electronic mail (see Clause 3, ‘‘Machine readable change proposals’’ for more
information).
It is accepted that this machine-readable version will be in ‘‘plain text’’ form and without many
formatting attributes (e.g., bolding, underlining, font changes), but it is nevertheless of great help to
the Editor.




Notes–12 Editor’s Notes for (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                             Editor’s Notes for DBL:YGJ-017 and X3H2-99-085
                                                                 2.2 Writing change proposals


2.2.5 The End Of The Paper
Not everybody’s printers and computers work perfectly every time. It sometimes happens that an
attempt to print a change proposal or other paper terminates prematurely, but it is not always
obvious to the reader that the paper is not completely printed. There are ways to combat this. It is
very strongly suggested that you end your change proposals (and, indeed, even discussion papers)
with some notation like:
= == == == == == == == == == == == == == == == == == == == ==
End of paper.
= == == == == == == == == == == == == == == == == == == == ==
Alternatively, you could choose to number each page with the formula "Page n of m", where "m" is
the total number of pages. This approach has the disadvantage that many word processors happily
print an incorrect value for "m". If you choose this approach, you will have to be extremely careful
that the value is correct. Perhaps you could combine this approach with the previous suggestion for
maximum benefit.


2.2.6 Format for Possible Problems and Language Opportunities
All Possible Problems and Language Opportunities should be provide, preferably in electronic,
machine-readable form, to the Editor using the following format:
   Severity: One of Major Technical, Minor Technical, and Major Editorial.
   Reference: A specific document and either Clause or Subclause, identified by both number and
   name.

   •   The document number should be of the form "Pnn", where "nn" represents the part number,
       including leading zeros to make it a 2-digit number. For example, P02 would be used for
       SQL/Foundation. Terminate this with a period to separate it from the Clause or Subclause
       number.

   •   The Clause or Subclause number should have two digits per level (including leading zeros)
       such as "03: or "04.21.01".

   •   The name should be spelled exactly as it is currently specified in the base documents, but
       not enclosed in quotation marks. You should not use a comma between the number and the
       name.

   •   The word Clause or Subclause should not be used in this item.

   •   If the PP or LO doesn’t apply to a specific Clause or Subclause in the document, then use
       the phrase "No specific location".
   Examples of properly formatted references are:

   •   P02.No specific location

   • P05.04.06.01 Classes of SQL-statements
   Note at: A specific location within an identified Clause or Subclause. This would identify the
   paragraph, Function, Format production, Syntax Rule, Access Rule, General Rule, Leveling
   Rule, or Description at which the Editor should put a note referencing the Possible Problem or
   Language Opportunity.
   If the PP or LO does not apply to such a specific place, then the word "None" should be specified
   here.


                          Guidelines for writing "user-friendly" change proposals         Notes–13
 Editor’s Notes for DBL:YGJ-017 and X3H2-99-085
2.2 Writing change proposals

   Source: The name of the person or the paper number that proposed the PP or LO, as well as
   the date on which it was submitted.
   Possible Problem: or Language Opportunity: This is the actual text of the PP or LO (use
   only one of those two phrases, of course!), taking as many paragraphs as you need.
   Proposed Solution: This is the actual text of a proposed solution. If none is proposed, then
   "None submitted with comment" should be specified.

Because of the increasing size and partitioning of the SQL3 document and the great complexity of
dealing with proposals that make substantial changes to the document’s structure and content, I
reserve the right to decline instructions to change the document when those instructions are not
complete and that do not generally follow these guidelines.
Specifically, if a change proposal is accepted that fails to cite the number and title of the document
being changed by the proposal, the proper Clause and/or Subclause numbers and titles affected by
the proposal, and the Rule numbers and partial text of Rules that are changed by the proposal, then
I will not make any changes in the next edition of the base document, but will bring the paper back
to the Committee for revision and completion. Thanks!



2.3 A Checklist of Issues to be Addressed by Change Proposals

1) Concepts — If a proposal introduces new concepts into the SQL language or substantially mod-
   ifies support for existing concepts, then Clause 4, "Concepts", and/or Part 1, SQL/Framework,
   must be updated to reflect the new reality.

2) Access Rules — If new schema objects are introduced, then formulating their relevant Access
   Rules may be obvious; however, in other cases, it may be less obvious that Access Rules are
   required to be specified or modified. The impact on <schema definition> referring to new
   schema objects shall be addressed, as well as access control such as <grant statement> and
   <revoke statement>.

3) Conformance Rules, including the relevant Annexes — Every new feature, however small, must
   be either explicitly excluded from Core SQL by means of a Conformance Rule or included in
   Core SQL by defining its capabilities

4) Packages, including the relevant Annexes — New features of SQL should be considered for in-
   clusion in either an existing package of SQL or a new package; absent such inclusion, proposals
   should explicitly state that the feature is not to be included in any package.

5) Lists of SQL-statements by category — Adding new SQL statements to the language requires
   that they be considered for membership in one or more of several categories of statements found
   in the Concepts.

6) Table of identifiers used by diagnostics statements — Every new SQL statement must have a
   character string identifier and a numeric identifier assigned for use by diagnostics statements
   and by dynamic SQL statements.

7) Collation coercibility determination for changes related to character strings — Any time new
   character string-related facilities are added to the language (including, for example, a new type
   of expression), the collation coercibility characteristic must be addressed.




Notes–14 Editor’s Notes for (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                            Editor’s Notes for DBL:YGJ-017 and X3H2-99-085
                               2.3 A Checklist of Issues to be Addressed by Change Proposals

 8) Closing Possible Problems when a proposal resolves them — Many proposal resolve previously-
    discovered problems in the SQL language or fulfil a previously-stated desire for a new feature.
    Proposals must state whether their acceptance can result in closing existing Possible Problems
    or Language Opportunities.

 9) Any new Possible Problems clearly identified — If a proposal identifies, but does not solve, a
    previously-unidentified (or unrecorded) problem, it must clearly instruct the Editor to enter a
    new Possible Problem.

10) Reserved and non-reserved keywords — Many, if not most, new SQL facilities depend on
    keywords and often introduce new keywords. Every new use of an existing keyword must
    consider whether it causes a previously un-reserved word to have to become reserved; every
    introduction of a new keyword must insert it into either the list of <reserved word>s or the list
    of <non-reserved word>s.

11) SQLSTATE tables and Ada package — Whenever any new exception condition or completion
    condition is used in the documents, it must be accompanied by a specification to include it in
    the SQLSTATE value tables (considering whether a new class code is required or whether it can
    be done by means of a new subclass code within an existing class code); simultaneously, the Ada
    package specifying Ada identifiers for SQLSTATE values must be updated to include the new
    condition.

12) Information and Definition Schemas — Every new schema object must be reflected in the
    Information and Definition Schemas. This must include the "short names" tables and views in
    those Schemas, too!

13) Implementation-defined and -dependent Annexes — Every new specification of some aspect
    of SQL as implementation-defined or implementation-dependent must be accompanied by a
    corresponding entry in the appropriate Annexes.

14) Incompatibilities Annex — Whenever an incompatibility with a previous version of the language
    is introduced, it must be documented in the appropriate Annex.

15) Embedded SQL bindings and host language implications — If a proposal affects host language
    bindings in any way (including, but not limited to, addition of new data types or mappings of
    existing data types), the embedded SQL facilities must be examined for impacts.

16) Dynamic SQL issues: including Dynamic descriptor areas — Many changes to host language
    bindings, introduction of new SQL statements or new data types, and other categories of
    changes have implications on Dynamic SQL; proposals must address this area of the language
    explicitly.

17) CLI issues — Most proposals that have Dynamic SQL implications also cause changes to be
    required in CLI; such proposals must explicitly make the required changes to SQL/CLI in order
    to be considered complete. This requires inclusion of a second checklist, indicated below.




                           Guidelines for writing "user-friendly" change proposals          Notes–15
Editor’s Notes for DBL:YGJ-017 and X3H2-99-085




Notes–16 Editor’s Notes for (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                             Editor’s Notes for DBL:YGJ-017 and X3H2-99-085




3 Machine readable change proposals

I would like to thank those ISO and ANSI participants who have provided change proposals in
machine-readable form. This practice reduces the editorial workload considerably and makes it
much easier to ensure accurate reflection of the change proposals in the base document. I continue
to urge those participants who can supply machine-readable versions of lengthier proposals (i.e., a
page or more of new text) to post those proposals directly to the SQL Standards Archives or, failing
that, to bring those diskettes to meetings, in order to avoid mail delays. I will of course either
return the diskettes or replace them with blank diskettes on request.
The best mechanism, as we’ve seen recently, is to post proposals in several forms (PDF,
PS, and plain text are all desirable) into the SQL Standards Archives (currently located at
jerry.ece.umassd.edu).
If you cannot post your proposals, then you are encouraged to provide them to the Editor on
diskette. I prefer 3.5 inch DS/HD soft-sectored diskettes, with DOS files (clearly labeled in any
case!). I may be able to find ways to copy other types of diskette and file, but would prefer to avoid
the hassles if possible. Please include both unformatted and formatted versions of each file, if
possible. Fully-justified text (with the extra blanks inserted for alignment purposes) do not appear
to be a problem for my document processor.
It may be preferable for you to electronically mail me your change proposals. My Internet mail
address is:

       jim.melton@sybase.com

Thanks!




                                               Machine readable change proposals          Notes–17
                                                Index
   Index entries appearing in boldface indicate the page where the word, phrase, or BNF nonterminal was
   defined; index entries appearing in italics indicate a page where the BNF nonterminal was used in a Format;
   and index entries appearing in roman type indicate a page where the word, phrase, or BNF nonterminal
   was used in a heading, Function, Syntax Rule, Access Rule, General Rule, Leveling Rule, Table, or other
   descriptive text.



999 • 1




                                                                                                 Index 1
                       ISO Working Draft
Database Language SQL — Part 9: SQL/MED
                                «Part 9»




                            March 1999
Contents                                                                                                                                    Page

Foreword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . vii
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix

1      Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

2      Normative references . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

3     Definitions, notations, and conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .                              7
3.1     Definitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .        7
3.1.1 Definitions provided in Part 9 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .                     7
3.2     Notations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .       8
3.3     Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .         8
3.3.1 Relationships to other parts of ANSI X3.135 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .                             8
3.3.2 Relationships to other parts of ISO/IEC 9075 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .                              9
3.3.2.1      Clause, Subclause, and Table relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .                             9
3.4     Object identifier for Database Language SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .                             18

4     Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .     21
4.1     Data types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .       21
4.2     Abstract LOB types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .             21
4.3     Datalinks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .      22
4.3.1 Operations involving datalink . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .                    23
4.3.1.1      Operators that operate on datalink . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .                        23
4.3.1.2      Other operators involving datalink . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .                        23
4.4     Type conversion and mixing of data types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .                         23
4.5     Columns, fields, and attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .                   23
4.6     Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .   25
4.7     SQL statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .           25
4.7.1 SQL-statements classified by function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .                         26
4.8     Introduction to SQL/CLI . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .                26

5   Lexical elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
5.1   <token> and <separator> . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27




 ii    (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                                                                                                    WG3:YGJ-017 = X3H2-99-085


6   Scalar expressions . . . . . . . .              .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   29
6.1   <data type> . . . . . . . . . . . . .         .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   29
6.2   <numeric value function> . .                  .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   31
6.3   <string value function> . . . .               .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   33
6.4   <datalink value function> . .                 .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   36
6.5   <cast specification> . . . . . . .             .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   37
6.6   <value expression> . . . . . . . .            .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   39
6.7   <datalink value expression>                   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   40

7   Predicates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
7.1   <comparison predicate> . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41

8      Data assignment rules and routine determination                                                                          .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   43
8.1      Retrieval assignment . . . . . . . . . . . . . . . . . . . . . . . . .                                                 .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   43
8.2      Store assignment . . . . . . . . . . . . . . . . . . . . . . . . . . . .                                               .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   44
8.3      Type precedence list determination . . . . . . . . . . . . . .                                                         .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   45

9   Schema definition and manipulation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .                                                                                                                           47
9.1   <schema definition> . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .                                                                                                            47
9.2   <column definition> . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .                                                                                                            48
9.3   <drop table statement> . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .                                                                                                              50
9.4   <abstract LOB type definition> . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .                                                                                                                   51
9.5   <abstract table definition> . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .                                                                                                              59
9.6   <drop user-defined type statement> . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .                                                                                                                     64

10 SQL-client modules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
10.1 Data type correspondences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65

11 Data manipulation . . . . . . . . . . . . . . . . . . . . . .                                            .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   69
11.1 Effect of deleting some rows from a base table                                                         .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   69
11.2 Effect of inserting tables into base tables . . . . .                                                  .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   70
11.3 Effect of replacing rows in base tables . . . . . . .                                                  .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   71

12 Transaction management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
12.1 <commit statement> . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
12.2 <rollback statement> . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74

13 Dynamic SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
13.1 Description of SQL item descriptor areas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
13.2 <describe statement> . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76

14 Embedded SQL . . . . . . . . . . . . . . . . .                           .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   77
14.1 <embedded SQL Ada program> . . . .                                     .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   77
14.2 <embedded SQL C program> . . . . . .                                   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   78
14.3 <embedded SQL COBOL program> .                                         .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   79
14.4 <embedded SQL Fortran program> .                                       .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   80
14.5 <embedded SQL MUMPS program>                                           .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   81
14.6 <embedded SQL Pascal program> . .                                      .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   82


                                                                                                                                                                                                        Contents iii
 WG3:YGJ-017 = X3H2-99-085


14.7       <embedded SQL PL/I program> . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83

15 Call-Level Interface specifications . . . .                                                          .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   85
15.1   <CLI routine> . . . . . . . . . . . . . . . . . . . .                                           .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   85
15.2   SQL/CLI common elements . . . . . . . . . .                                                     .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   85
15.3   Implicit DESCRIBE USING clause . . . .                                                          .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   85
15.3.1 CLI-specific status codes . . . . . . . . . . . .                                                .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   86
15.3.2 Description of CLI item descriptor areas                                                        .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   86
15.3.3 Other tables associated with CLI . . . . . .                                                    .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   87

16 SQL/CLI routines . . .                  .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   89
16.1 BuildDataLink . . . . .               .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   89
16.2 ExtractDataLinkAttr                   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   90
16.3 GetInfo . . . . . . . . . . .         .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   92

17 Information Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
17.1 COLUMNS view . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93

18 Definition Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
18.1 COLUMNS base table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95

19     Conformance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99

Annex A              Implementation-defined elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101

Annex B              Implementation-dependent elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103

Annex C              Deprecated features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105

Annex D              Incompatibilities with X3.135-1992 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108

Annex E    Typical header files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
E.1  C Header File SQLCLI.H . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
E.2  COBOL Library Item SQLCLI . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110

Annex F              SQL Feature Taxonomy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Index1




 iv     (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                                            WG3:YGJ-017 = X3H2-99-085




                                                            TABLES

Tables                                                                                                                                                          Page

1        Clause, Subclause, and Table relationships . . . . . . . . . . . . . . . . . . . . .               .   .   .   .   .   .   .   .   .   .   .   .   .   .   .    9
2        Valid datalink file control options . . . . . . . . . . . . . . . . . . . . . . . . . . . .         .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   24
3        Data type correspondences for Ada . . . . . . . . . . . . . . . . . . . . . . . . . . .            .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   65
4        Data type correspondences for C . . . . . . . . . . . . . . . . . . . . . . . . . . . . .          .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   65
5        Data type correspondences for COBOL . . . . . . . . . . . . . . . . . . . . . . . .                .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   66
6        Data type correspondences for Fortran . . . . . . . . . . . . . . . . . . . . . . . .              .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   66
7        Data type correspondences for MUMPS . . . . . . . . . . . . . . . . . . . . . . . .                .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   67
8        Data type correspondences for Pascal . . . . . . . . . . . . . . . . . . . . . . . . .             .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   67
9        Data type correspondences for PL/I . . . . . . . . . . . . . . . . . . . . . . . . . . .           .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   68
10       Codes used for SQL data types in Dynamic SQL . . . . . . . . . . . . . . . . .                     .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   75
11       Abbreviated SQL/CLI generic names . . . . . . . . . . . . . . . . . . . . . . . . . .              .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   85
12       SQLSTATE class and subclass values for SQL/CLI-specific conditions .                                .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   86
13       Codes used for implementation data types in SQL/CLI . . . . . . . . . . . .                        .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   87
14       Codes used for application data types in SQL/CLI . . . . . . . . . . . . . . . .                   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   87
15       Codes used to identify SQL/CLI routines . . . . . . . . . . . . . . . . . . . . . . .              .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   87
16       Codes and data types for implementation information . . . . . . . . . . . . .                      .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   87
17       Codes used for datalink attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . .         .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   87
18       Data types of attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   88




                                                                                                                                    Contents v
                                                                   WG3:YGJ-017 = X3H2-99-085




Foreword




ISO Only—caused by ANSI changes not yet considered by ISO


ISO (the International Organization for Standardization) and IEC (the International Electrotechnical
Commission) form the specialized system for worldwide standardization. National bodies that are
members of ISO or IEC participate in the development of International Standards through technical
committees established by the respective organization to deal with particular fields of technical
activity. ISO and IEC technical committees collaborate in fields of mutual interest. Other interna-
tional organizations, governmental and non-governmental, in liaison with ISO and IEC, also take
part in the work.
In the field of information technology, ISO and IEC have established a joint technical committee,
ISO/IEC JTC 1. Draft International Standards adopted by the joint technical committee are circu-
lated to national bodies for voting. Publication as an International Standard requires approval by
at least 75% of the national bodies casting a vote.
International Standard ISO/IEC 9075, Part 9, was prepared by Joint Technical Committee ISO/IEC
JTC 1, Information technology.
ISO/IEC 9075 consists of the following parts, under the general title Information technology —
Database languages — SQL:

— Part 1: Framework (SQL/Framework)

— Part 2: Foundation (SQL/Foundation)

— Part 3: Call-Level Interface (SQL/CLI)

— Part 4: Persistent Stored Modules (SQL/PSM)

— Part 5: Host Language Bindings (SQL/Bindings)

— Part 6: XA Specialization (SQL/Transaction)

— Part 7: Temporal (SQL/Temporal)

— Part 9: Management of External Data (SQL/MED)

— Part 10: Object Language Bindings (SQL/OLB)



ANSI Only—caused by ISO changes not yet considered by ANSI

  To be supplied if required.




                                                                                   Foreword vii
                                                                       WG3:YGJ-017 = X3H2-99-085




 Introduction


 The organization of this Part of this
 ANSI American
 ISO International
 Standard is as follows:

 1) Clause 1, ‘‘Scope’’, specifies the scope of
    ANSI ANSI X3.135.9.
    ISO ISO/IEC 9075-9.



 2) Clause 2, ‘‘Normative references’’, identifies additional standards that, through reference in
    ANSI ANSI X3.135.9,
    ISO ISO/IEC 9075-9,
    constitute provisions of
    ANSI ANSI X3.135.9.
    ISO ISO/IEC 9075-9.



 3) Clause 3, ‘‘Definitions, notations, and conventions’’, defines the notations and conventions used
    in
     ANSI ANSI X3.135.9.
     ISO ISO/IEC 9075-9.



 4) Clause 4, ‘‘Concepts’’, presents concepts related to
    ANSI ANSI X3.135.9.
    ISO ISO/IEC 9075-9.



 5) Clause 5, ‘‘Lexical elements’’, defines the lexical elements of the language specified in
    ANSI ANSI X3.135.9.
    ISO ISO/IEC 9075-9.



 6) Clause 6, ‘‘Scalar expressions’’, defines the elements of the language that produce scalar values.

 7) Clause 7, ‘‘Predicates’’, defines the predicates of the language.

 8) Clause 8, ‘‘Data assignment rules and routine determination’’, specifies the rules for assignments
    that retrieves data from or store data into SQL-data, and formation rules for set operations.

 9) Clause 11, "Schema definition and manipulation", defines facilities related to abstract tables,
    abstract LOBs, and datalink data type support for creating and managing a schema.

10) Clause 13, "SQL-client modules", defines SQL-client modules and externally-invoked procedures.

11) Clause 14, "Data manipulation", defines the data manipulation statements.




                                                                                   Introduction ix
 WG3:YGJ-017 = X3H2-99-085


12) Clause 16, "Transaction management", defines enhancement to the SQL-transaction manage-
    ment statements related to the management of external data.

13) Clause 13, ‘‘Dynamic SQL’’, defines the dynamic SQL statements.

14) Clause 14, ‘‘Embedded SQL’’, defines the embedded SQL statements.

15) Clause 15, ‘‘Call-Level Interface specifications’’, defines facilities for using SQL through a
    Call-Level Interface.

16) Clause 16, ‘‘SQL/CLI routines’’, defines each of the routines that comprise the Call-Level
    Interface.

17) Clause 17, ‘‘Information Schema’’, defines viewed tables that contain schema information.

18) Clause 18, ‘‘Definition Schema’’, defines base tables on which the viewed tables containing
    schema information depend.

19) Clause 19, ‘‘Conformance’’, specifies the way in which conformance to
    ANSI ANSI X3.135.9
    ISO ISO/IEC 9075-9
    may be claimed.

20) Annex A, ‘‘Implementation-defined elements’’, is an informative Annex. It lists those features
    for which this part of
     ANSI ANSI X3.135
     ISO ISO/IEC 9075
    states that the syntax, the meaning, the returned results, the effect on SQL-data and/or
    schemas, or any other behavior is partly or wholly implementation-defined.

21) Annex B, ‘‘Implementation-dependent elements’’, is an informative Annex. It lists those features
    for which this part of
     ANSI ANSI X3.135
     ISO ISO/IEC 9075
    states that the syntax, the meaning, the returned results, the effect on SQL-data and/or
    schemas, or any other behavior is partly or wholly implementation-dependent.

22) Annex C, ‘‘Deprecated features’’, is an informative Annex. It lists features that the responsible
    Technical Committee intend will not appear in a future revised version of
    ANSI ANSI X3.135.9.
    ISO ISO/IEC 9075-9.



23) Annex D, ‘‘Incompatibilities with X3.135-1992’’, is an informative Annex. It lists incompatibili-
    ties with the previous version of
    ANSI ANSI X3.135.
    ISO ISO/IEC 9075.



 In the text of
 ANSI ANSI X3.135.9,
 ISO ISO/IEC 9075-9,
 Clauses begin a new odd-numbered page. Any resulting blank space is not significant.




 x   (ISO-ANSI working draft) Management of External Data (SQL/MED)
Information technology — Database languages — SQL —
Part 9: SQL/MED


1 Scope


ANSI  ANSI X3.135.9
ISO   ISO/IEC 9075-9
defines extensions of Database language SQL to support management of external data through the
use of abstract tables, abstract large objects (lobs), and datalink data types.
NOTE 1 – The framework for
ANSI ANSI X3.135.9
ISO ISO/IEC 9075-9
is described by the Reference Model of Data Management (ISO/IEC 10032:1993).




                                                                                   Scope    1
WG3:YGJ-017 = X3H2-99-085




2 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                         WG3:YGJ-017 = X3H2-99-085




2 Normative references

The following standards contain provisions that, through reference in this text, constitute provisions
of
ANSI ANSI X3.135.9.
ISO ISO/IEC 9075-9.
At the time of publication, the editions indicated were valid. All standards are subject to revision,
and parties to agreements based on
ANSI ANSI X3.135.9
ISO ISO/IEC 9075-9
are encouraged to investigate the possibility of applying the most recent editions of the stan-
dards listed below. Members of IEC and ISO maintain registers of currently valid International
Standards.



ANSI Only—caused by ISO changes not yet considered by ANSI



— ANSI X3.9-1978, American National Standard Programming Language FORTRAN.

— ANSI X3.23-1985, American National Standard for Information Systems—Programming
  Language—COBOL.

— ANSI X3.53-1976, American National Standard Programming Language PL/I.

— ANSI X3.135-1992, American National Standard for Information Systems — Database Language
  — SQL.

— ANSI X3.159-1989, American National Standard for Information Systems—Programming
  Language—C.

— ANSI X3.198-1991, American National Standard for Information Systems—Programming
  Language—Fortran.
  NOTE 2 – ANSI X3.198-1991 introduces no incompatibilities with ANSI X3.9-1978 that affect the
   binding between Fortran and SQL; therefore, wherever ‘‘Fortran’’ is specified in this
   ANSI American
   ISO International
   Standard, ANSI X3.198-1991 is implicit.

— ANSI/MDC X11.1-1990, American National Standard for Information Systems—Programming
  Language—MUMPS.

— ANSI/IEEE 770/X3.97-1983, American National Standard for Information Systems—
  Programming Language—Pascal.

— ANSI/IEEE 770/X3.160-1989, American National Standard for Information Systems—
  Programming Language—Extended Pascal.




                                                                             Normative references   3
WG3:YGJ-017 = X3H2-99-085


— ANSI/MIL-STD-1815A-1983, American National Standard for Information Systems—Reference
  Manual for the Ada® Programming Language.

   ISO Only—caused by ANSI changes not yet considered by ISO



— ISO/IEC 1539-1:1997, Information technology — Programming languages — Fortran — Part 1:
  Base language.


— ISO 1989:1985, Programming languages — COBOL.
  (Endorsement of ANSI X3.23-1985).


— ISO 6160:1979, Programming languages — PL/I
  (Endorsement of ANSI X3.53-1976).


— ISO/IEC 7185:1990, Information technology — Programming languages — Pascal.


— ISO/IEC 8652:1995, Information technology — Programming languages — Ada.


— ISO/IEC 9075-1:1999, Information technology — Database languages — SQL — Part 1:
  Framework (SQL/Framework).


— ISO/IEC 9075-2:1999, Information technology — Database languages — SQL — Part 2:
  Foundation (SQL/Foundation).


— ISO/IEC 9075-3:1999, Information technology — Database languages — SQL — Part 3: Call-
  level interface (SQL/CLI).


— ISO/IEC 9075-4:1999, Information technology — Database languages — SQL — Part 4:
  Persistent stored modules (SQL/PSM).


— ISO/IEC 9075-5:1999, Information technology — Database languages — SQL — Part 5: Host
  language bindings (SQL/Bindings).


— ISO/IEC 9899:1990, Programming languages — C.


— ISO/IEC 10206:1991, Information technology — Programming languages — Extended Pascal.


— ISO/IEC 11404:1996, Information technology — Programming languages, their environments
  and system software interraces — Language-independent datatypes.




4 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                           WG3:YGJ-017 = X3H2-99-085


— ISO/IEC 11756:1992, Information technology — Programming languages — MUMPS.




                                                              Normative references   5
WG3:YGJ-017 = X3H2-99-085




6 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                           WG3:YGJ-017 = X3H2-99-085




3 Definitions, notations, and conventions


3.1 Definitions

3.1.1 Definitions provided in Part 9
  Insert this paragraph For   the purposes of this part of
ANSI ANSI X3.135,
ISO ISO/IEC 9075,
the definitions given in
ANSI ANSI X3.135.1, ANSI X3.135.2, and ANSI X3.135.3
ISO ISO/IEC 9075-1, ISO/IEC 9075-2, and ISO/IEC 9075-3
and the following definitions apply.

a) abstract LOB type: A set of routines that store and retrieve data, and present it to SQL oper-
   ations as LOB data. An abstract LOB type is created by a <abstract LOB type definition>. The
   actions defined for LOB data items, such as comparison, assignment, concatenation, substring,
   etc. are specified by user-defined routines named in the <abstract LOB type definition>. These
   routines are referred to as abstract LOB type routines. The abstract LOB type routines define
   the interface between the SQL-implementation and the abstract LOB type.

b) abstract LOB type routine: A routine that is specified in a <abstract LOB type definition>.
   An abstract LOB type routine can be an SQL routine or an external routine. An abstract LOB
   type routine that is a procedure (function) is referred to as an abstract LOB type procedure
   (abstract LOB type function).

c) abstract table: An SQL table defined with a <create abstract table definition>. The actions
   defined for SQL tables, such as INSERT, UPDATE, DELETE, SELECT FROM, etc., are specified
   by user-defined routines named in the <create abstract table definition>. These routines are
   referred to as access routines. The access routines define the content and representation of the
   abstract table.

d) access routine: A routine that is specified in a <create abstract table definition>. An access
   routine can be an SQL routine or an external routine. An access routine that is a procedure
   (function) is referred to as an access procedure (access function).

e) datalink: A value, of data type DATALINK, representing some external object (i.e., one that
   is not part of the SQL-environment). The object in question is assummed to be contained in an
   external data repository, accessible through some external data server. A datalink conceptually
   consists of a link type, a scheme, an external data server name, a data identification path, and
   a comment.

f) datalinker: an implementation-dependent component for controlling access and referential
   integrity to external objects.

g) integrity option: Specifies the level of integrity of the link between a datalink and the actual
   object.




                                                             Definitions, notations, and conventions   7
 WG3:YGJ-017 = X3H2-99-085
3.1 Definitions

h) link control: A property of a column of data type DATALINK, specifying the extent to which
   the links between datalinks in that column and the external objects they reference are to be
   monitored (in various specific sense).

i) read permission option: A link control option specifying how permission to read external
   objects referenced by certain datalinks is determined.

j) recovery option: A link control option specifying whether or not point in time recovery is
   required for the external objects referenced by certain datalinks.

k) unlink option: A link control option specifying the action to be taken when certain sites
   occupied by datalinks are updated or deleted.

l) write permission option: A link control option specifying how permission to write external
   objects referenced by certain datalinks is determined.


3.2 Notations

  Insert this paragraph The   syntax notation used in this part of
ANSI ANSI X3.135
ISO ISO/IEC 9075
is an extended version of BNF ("Backus Normal Form" or "Backus Naur Form"). This version of
BNF is fully described in Subclause 7.1, "Notation", of
 ANSI ANSI X3.135.1.
 ISO ISO/IEC 9075-1.




3.3 Conventions

  Insert this paragraph Except   as otherwise specified in this part of
ANSI ANSI X3.135,
ISO ISO/IEC 9075,
the convensions used in this part of
ANSI ANSI X3.135
ISO ISO/IEC 9075
are identical to those described in
ANSI ANSI X3.135.1 and ANSI X3.135.2.
ISO ISO/IEC 9075-1 and ISO/IEC 9075-2.



ANSI Only—caused by ISO changes not yet considered by ANSI




3.3.1 Relationships to other parts of ANSI X3.135


ISO Only—caused by ANSI changes not yet considered by ISO




8 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                             WG3:YGJ-017 = X3H2-99-085
                                                                                       3.3 Conventions


3.3.2 Relationships to other parts of ISO/IEC 9075




3.3.2.1   Clause, Subclause, and Table relationships


                          Table 1—Clause, Subclause, and Table relationships
 Clause, Subclause, or Table in
 this part of
 ANSI ANSI X3.135                        Corresponding Clause,
 ISO ISO/IEC 9075                        Subclause, or Table from an-             Part containing corre-
                                         other part                               spondence

 Clause 1, ‘‘Scope’’                     Clause 1, "Scope"
                                                                                  ANSI ANSI X3.135.2
                                                                                  ISO ISO/IEC 9075-2


 Clause 2, ‘‘Normative references’’      Clause 2, "Normative references"
                                                                                  ANSI ANSI X3.135.2
                                                                                  ISO ISO/IEC 9075-2


 Clause 3, ‘‘Definitions, notations,      Clause 3, "Definitions, notations,
 and conventions’’                       and conventions"                         ANSI ANSI X3.135.2
                                                                                  ISO ISO/IEC 9075-2


 Subclause 3.1, ‘‘Definitions’’           Subclause 3.1, "Definitions"
                                                                                  ANSI ANSI X3.135.2
                                                                                  ISO ISO/IEC 9075-2


 Subclause 3.2, ‘‘Notations’’            Subclause 7.1, "Notation"
                                                                                  ANSI ANSI X3.135.1
                                                                                  ISO ISO/IEC 9075-1


 Subclause 3.3, ‘‘Conventions’’          Subclause 3.3, "Conventions"
                                                                                  ANSI ANSI X3.135.2
                                                                                  ISO ISO/IEC 9075-2


 Subclause 3.3.2, ‘‘Relationships to     none                                     none
 other parts of ISO/IEC 9075’’
 Subclause 3.3.2.1, ‘‘Clause,            none                                     none
 Subclause, and Table relationships’’
 Subclause 3.4, ‘‘Object identifier for   Subclause 7.3, "Object identifier for
 Database Language SQL’’                 Database Language SQL"                   ANSI ANSI X3.135.1
                                                                                  ISO ISO/IEC 9075-1




                                                        Definitions, notations, and conventions             9
 WG3:YGJ-017 = X3H2-99-085
3.3 Conventions

                      Table 1—Clause, Subclause, and Table relationships (Cont.)
 Clause, Subclause, or Table in
 this part of
 ANSI ANSI X3.135                        Corresponding Clause,
 ISO ISO/IEC 9075                        Subclause, or Table from an-        Part containing corre-
                                         other part                          spondence
 Clause 4, ‘‘Concepts’’                  Clause 4, "Concepts"
                                                                             ANSI ANSI X3.135.2
                                                                             ISO ISO/IEC 9075-2


 Subclause 4.1, ‘‘Data types’’           Subclause 4.1, "Data types"
                                                                             ANSI ANSI X3.135.2
                                                                             ISO ISO/IEC 9075-2


 Subclause 4.2, ‘‘Abstract LOB types’’   none                                none
 Subclause 4.3, ‘‘Datalinks’’            none                                none
 Subclause 4.3.1, ‘‘Operations involv-   none                                none
 ing datalink’’
 Subclause 4.3.1.1, ‘‘Operators that     none                                none
 operate on datalink’’
 Subclause 4.3.1.2, ‘‘Other operators    none                                none
 involving datalink’’
 Subclause 4.4, ‘‘Type conversion and    Subclause 4.12, "Type conversions
 mixing of data types’’                  and mixing of data types"           ANSI ANSI X3.135.2
                                                                             ISO ISO/IEC 9075-2


 Subclause 4.5, ‘‘Columns, fields, and    Subclause 4.15, "Columns, fields,
 attributes’’                            and attributes"                     ANSI ANSI X3.135.2
                                                                             ISO ISO/IEC 9075-2


 Subclause 4.6, ‘‘Tables’’               Subclause 4.16, "Tables"
                                                                             ANSI ANSI X3.135.2
                                                                             ISO ISO/IEC 9075-2


 Subclause 4.7, ‘‘SQL statements’’       Subclause 4.30, "SQL-statements"
                                                                             ANSI ANSI X3.135.2
                                                                             ISO ISO/IEC 9075-2


 Subclause 4.7.1, ‘‘SQL-statements       Subclause 4.30.2, "SQL-statements
 classified by function’’                 classified by function"              ANSI ANSI X3.135.2
                                                                             ISO ISO/IEC 9075-2


 Subclause 4.8, ‘‘Introduction to        Subclause 4.1, "Introduction to
 SQL/CLI’’                               SQL/CLI"                            ANSI ANSI X3.135.3
                                                                             ISO ISO/IEC 9075-3




10 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                            WG3:YGJ-017 = X3H2-99-085
                                                                                      3.3 Conventions

                     Table 1—Clause, Subclause, and Table relationships (Cont.)
Clause, Subclause, or Table in
this part of
ANSI ANSI X3.135                         Corresponding Clause,
ISO ISO/IEC 9075                         Subclause, or Table from an-            Part containing corre-
                                         other part                              spondence
Clause 5, ‘‘Lexical elements’’           Clause 5, "Lexical elements"
                                                                                 ANSI ANSI X3.135.2
                                                                                 ISO ISO/IEC 9075-2


Subclause 5.1, ‘‘<token> and <sepa-      Subclause 5.2, "<token> and <sepa-
rator>’’                                 rator>"                                 ANSI ANSI X3.135.2
                                                                                 ISO ISO/IEC 9075-2


Subclause 6.1, ‘‘<data type>’’           Subclause 6.1, "<data type>"
                                                                                 ANSI ANSI X3.135.2
                                                                                 ISO ISO/IEC 9075-2


Subclause 6.2, ‘‘<numeric value          Subclause 6.17, "<numeric value
function>’’                              function>"                              ANSI ANSI X3.135.2
                                                                                 ISO ISO/IEC 9075-2


Subclause 6.3, ‘‘<string value func-     Subclause 6.18, "<string value
tion>’’                                  function>"                              ANSI ANSI X3.135.2
                                                                                 ISO ISO/IEC 9075-2


Subclause 6.4, ‘‘<datalink value         none                                    none
function>’’
Subclause 6.5, ‘‘<cast specification>’’   Subclause 6.22, "<cast specifica-
                                         tion>"                                  ANSI ANSI X3.135.2
                                                                                 ISO ISO/IEC 9075-2


Subclause 6.6, ‘‘<value expression>’’    Subclause 6.23, "<value expres-
                                         sion>"                                  ANSI ANSI X3.135.2
                                                                                 ISO ISO/IEC 9075-2


Subclause 6.7, ‘‘<datalink value         none                                    none
expression>’’
Clause 7, ‘‘Predicates’’                 Clause 8, "Predicates"
                                                                                 ANSI ANSI X3.135.2
                                                                                 ISO ISO/IEC 9075-2


Subclause 7.1, ‘‘<comparison predi-      Subclause 8.2, "<comparison predi-
cate>’’                                  cate>"                                  ANSI ANSI X3.135.2
                                                                                 ISO ISO/IEC 9075-2




                                                       Definitions, notations, and conventions             11
 WG3:YGJ-017 = X3H2-99-085
3.3 Conventions

                     Table 1—Clause, Subclause, and Table relationships (Cont.)
 Clause, Subclause, or Table in
 this part of
 ANSI ANSI X3.135                        Corresponding Clause,
 ISO ISO/IEC 9075                        Subclause, or Table from an-           Part containing corre-
                                         other part                             spondence
 Clause 8, ‘‘Data assignment rules       Clause 9, "Data assignment rules
 and routine determination’’             and routine determination"             ANSI ANSI X3.135.2
                                                                                ISO ISO/IEC 9075-2


 Subclause 8.1, ‘‘Retrieval assign-      Subclause 9.1, "Retrieval assign-
 ment’’                                  ment"                                  ANSI ANSI X3.135.2
                                                                                ISO ISO/IEC 9075-2


 Subclause 8.2, ‘‘Store assignment’’     Subclause 9.2, "Store assignment"
                                                                                ANSI ANSI X3.135.2
                                                                                ISO ISO/IEC 9075-2


 Subclause 8.3, ‘‘Type precedence list   Subclause 9.5, "Type precedence list
 determination’’                         determination"                         ANSI ANSI X3.135.2
                                                                                ISO ISO/IEC 9075-2


 Clause 9, ‘‘Schema definition and        Clause 11, "Schema definition and
 manipulation’’                          manipulation"                          ANSI ANSI X3.135.2
                                                                                ISO ISO/IEC 9075-
                                                                                2
 Subclause 9.1, ‘‘<schema defini-         Subclause 11.1, "<schema defini-
 tion>’’                                 tion>"                                 ANSI ANSI X3.135.2
                                                                                ISO ISO/IEC 9075-2


 Subclause 9.2, ‘‘<column definition>’’   Subclause 11.4, "<column defini-
                                         tion>"                                 ANSI ANSI X3.135.2
                                                                                ISO ISO/IEC 9075-2


 Subclause 9.3, ‘‘<drop table state-     Subclause 11.20, "<drop table state-
 ment>’’                                 ment>"                                 ANSI ANSI X3.135.2
                                                                                ISO ISO/IEC 9075-2


 Subclause 9.4, ‘‘<abstract LOB type     none                                   none
 definition>’’
 Subclause 9.5, ‘‘<abstract table        none                                   none
 definition>’’
 Subclause 9.6, ‘‘<drop user-defined      Subclause 11.48, "<drop data type
 type statement>’’                       statement>"                            ANSI ANSI X3.135.2
                                                                                ISO ISO/IEC 9075-2




12 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                            WG3:YGJ-017 = X3H2-99-085
                                                                                      3.3 Conventions

                    Table 1—Clause, Subclause, and Table relationships (Cont.)
Clause, Subclause, or Table in
this part of
ANSI ANSI X3.135                        Corresponding Clause,
ISO ISO/IEC 9075                        Subclause, or Table from an-             Part containing corre-
                                        other part                               spondence
Clause 10, ‘‘SQL-client modules’’       Clause 13, "SQL-client modules"
                                                                                 ANSI ANSI X3.135.2
                                                                                 ISO ISO/IEC 9075-2


Subclause 10.1, ‘‘Data type corre-      Subclause 13.6, "Data type corre-
spondences’’                            spondences"                              ANSI ANSI X3.135.2
                                                                                 ISO ISO/IEC 9075-2


Clause 11, ‘‘Data manipulation’’        Clause 14, "Data manipulation"
                                                                                 ANSI ANSI X3.135.2
                                                                                 ISO ISO/IEC 9075-2


Subclause 11.1, ‘‘Effect of deleting    Subclause 14.14, "Effect of deleting
some rows from a base table’’           rows from base tables"                   ANSI ANSI X3.135.2
                                                                                 ISO ISO/IEC 9075-2


Subclause 11.2, ‘‘Effect of inserting   Subclause 14.17, "Effect of inserting
tables into base tables’’               tables into base tables"                 ANSI ANSI X3.135.2
                                                                                 ISO ISO/IEC 9075-2


Subclause 11.3, ‘‘Effect of replacing   Subclause 14.20, "Effect of replacing
rows in base tables’’                   rows in base tables"                     ANSI ANSI X3.135.2
                                                                                 ISO ISO/IEC 9075-2


Clause 12, ‘‘Transaction manage-        Clause 16, "Transaction manage-
ment’’                                  ment"                                    ANSI ANSI X3.135.2
                                                                                 ISO ISO/IEC 9075-2


Subclause 12.1, ‘‘<commit state-        Subclause 16.6, "<commit state-
ment>’’                                 ment>"                                   ANSI ANSI X3.135.2
                                                                                 ISO ISO/IEC 9075-2


Subclause 12.2, ‘‘<rollback state-      Subclause 16.7, "<rollback state-
ment>’’                                 ment>"                                   ANSI ANSI X3.135.2
                                                                                 ISO ISO/IEC 9075-2


Clause 13, ‘‘Dynamic SQL’’              Clause 15, "Dynamic SQL"
                                                                                 ANSI ANSI X3.135.5
                                                                                 ISO ISO/IEC 9075-5




                                                      Definitions, notations, and conventions              13
 WG3:YGJ-017 = X3H2-99-085
3.3 Conventions

                     Table 1—Clause, Subclause, and Table relationships (Cont.)
 Clause, Subclause, or Table in
 this part of
 ANSI ANSI X3.135                       Corresponding Clause,
 ISO ISO/IEC 9075                       Subclause, or Table from an-          Part containing corre-
                                        other part                            spondence
 Subclause 13.1, ‘‘Description of SQL   Subclause 15.1, "Description of SQL
 item descriptor areas’’                descriptor areas"                     ANSI ANSI X3.135.5
                                                                              ISO ISO/IEC 9075-5


 Subclause 13.2, ‘‘<describe state-     Subclause 15.8, "<describe state-
 ment>’’                                ment>"                                ANSI ANSI X3.135.5
                                                                              ISO ISO/IEC 9075-5


 Clause 14, ‘‘Embedded SQL’’            Clause 16, "Embedded SQL"
                                                                              ANSI ANSI X3.135.5
                                                                              ISO ISO/IEC 9075-5


 Subclause 14.1, ‘‘<embedded SQL        Subclause 16.3, "<embedded SQL
 Ada program>’’                         Ada program>"                         ANSI ANSI X3.135.5
                                                                              ISO ISO/IEC 9075-5


 Subclause 14.2, ‘‘<embedded SQL C      Subclause 16.4, "<embedded SQL C
 program>’’                             program>"                             ANSI ANSI X3.135.5
                                                                              ISO ISO/IEC 9075-5


 Subclause 14.3, ‘‘<embedded SQL        Subclause 16.5, "<embedded SQL
 COBOL program>’’                       COBOL program>"                       ANSI ANSI X3.135.5
                                                                              ISO ISO/IEC 9075-5


 Subclause 14.4, ‘‘<embedded SQL        Subclause 16.6, "<embedded SQL
 Fortran program>’’                     Fortran program>"                     ANSI ANSI X3.135.5
                                                                              ISO ISO/IEC 9075-5


 Subclause 14.5, ‘‘<embedded SQL        Subclause 16.7, "<embedded SQL
 MUMPS program>’’                       MUMPS program>"                       ANSI ANSI X3.135.5
                                                                              ISO ISO/IEC 9075-5


 Subclause 14.6, ‘‘<embedded SQL        Subclause 16.8, "<embedded SQL
 Pascal program>’’                      Pascal program>"                      ANSI ANSI X3.135.5
                                                                              ISO ISO/IEC 9075-5


 Subclause 14.7, ‘‘<embedded SQL        Subclause 16.9, "<embedded SQL
 PL/I program>’’                        PL/I program>"                        ANSI ANSI X3.135.5
                                                                              ISO ISO/IEC 9075-5




14 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                         WG3:YGJ-017 = X3H2-99-085
                                                                                   3.3 Conventions

                    Table 1—Clause, Subclause, and Table relationships (Cont.)
Clause, Subclause, or Table in
this part of
ANSI ANSI X3.135                       Corresponding Clause,
ISO ISO/IEC 9075                       Subclause, or Table from an-           Part containing corre-
                                       other part                             spondence
Clause 15, ‘‘Call-Level Interface      Clause 5, "Call-Level Interface
specifications’’                        specifications"                         ANSI ANSI X3.135.3
                                                                              ISO ISO/IEC 9075-3


Subclause 15.1, ‘‘<CLI routine>’’      Subclause 5.1, "<CLI routine>"
                                                                              ANSI ANSI X3.135.3
                                                                              ISO ISO/IEC 9075-3


Subclause 15.2, ‘‘SQL/CLI common       none                                   none
elements’’
Subclause 15.3, ‘‘Implicit DESCRIBE    Subclause 5.5, "Implicit DESCRIBE
USING clause’’                         USING clause"                          ANSI ANSI X3.135.3
                                                                              ISO ISO/IEC 9075-3


Subclause 15.3.1, ‘‘CLI-specific        Subclause 5.12, "CLI-specific status
status codes’’                         codes"                                 ANSI ANSI X3.135.3
                                                                              ISO ISO/IEC 9075-3


Subclause 15.3.2, ‘‘Description of     Subclause 5.13, "Description of CLI
CLI item descriptor areas’’            item descriptor areas"                 ANSI ANSI X3.135.3
                                                                              ISO ISO/IEC 9075-3


Subclause 15.3.3, ‘‘Other tables       Subclause 5.14, "Other tables asso-
associated with CLI’’                  ciated with CLI"                       ANSI ANSI X3.135.3
                                                                              ISO ISO/IEC 9075-3


Clause 16, ‘‘SQL/CLI routines’’        Clause 6, "SQL/CLI routines"
                                                                              ANSI ANSI X3.135.3
                                                                              ISO ISO/IEC 9075-3


Subclause 16.1, ‘‘BuildDataLink’’      none                                   none
Subclause 16.2, ‘‘ExtractDataLinkAttr’’ none                                  none
Subclause 16.3, ‘‘GetInfo’’            Subclause 6.37, "GetInfo"
                                                                              ANSI ANSI X3.135.5
                                                                              ISO ISO/IEC 9075-5


Clause 17, ‘‘Information Schema’’      Clause 20, "Information Schema"
                                                                              ANSI ANSI X3.135.2
                                                                              ISO ISO/IEC 9075-2




                                                     Definitions, notations, and conventions            15
 WG3:YGJ-017 = X3H2-99-085
3.3 Conventions

                     Table 1—Clause, Subclause, and Table relationships (Cont.)
 Clause, Subclause, or Table in
 this part of
 ANSI ANSI X3.135                     Corresponding Clause,
 ISO ISO/IEC 9075                     Subclause, or Table from an-          Part containing corre-
                                      other part                            spondence
 Subclause 17.1, ‘‘COLUMNS view’’     Subclause 20.18, "COLUMNS view"
                                                                            ANSI ANSI X3.135.2
                                                                            ISO ISO/IEC 9075-2


 Clause 18, ‘‘Definition Schema’’      Clause 19, "Definition Schema"
                                                                            ANSI ANSI X3.135.5
                                                                            ISO ISO/IEC 9075-5


 Subclause 18.1, ‘‘COLUMNS base       Subclause 21.14, "COLUMNS base
 table’’                              table"                                ANSI ANSI X3.135.2
                                                                            ISO ISO/IEC 9075-2


 Clause 19, ‘‘Conformance’’           Clause 23, "Conformance"
                                                                            ANSI ANSI X3.135.5
                                                                            ISO ISO/IEC 9075-5


 Annex A, ‘‘Implementation-defined     Appendix B, "Implementation-
 elements’’                           defined elements"                      ANSI ANSI X3.135.2
                                                                            ISO ISO/IEC 9075-2


 Annex B, ‘‘Implementation-           Appendix C, "Implementation-
 dependent elements’’                 dependent elements"                   ANSI ANSI X3.135.2
                                                                            ISO ISO/IEC 9075-2


 Annex C, ‘‘Deprecated features’’     Appendix D, "Deprecated features"
                                                                            ANSI ANSI X3.135.2
                                                                            ISO ISO/IEC 9075-2


 Annex D, ‘‘Incompatibilities with    Appendix E, "Incompatibilities with
 X3.135-1992’’                        X3.135-1992 and X3.135.4-1996"        ANSI ANSI X3.135.2
                                                                            ISO ISO/IEC 9075-2


 Annex E, ‘‘Typical header files’’     Appendix A, "Typical header files"
                                                                            ANSI ANSI X3.135.3
                                                                            ISO ISO/IEC 9075-3


 Subclause E.1, ‘‘C Header File       Subclause A.1, "C header file
 SQLCLI.H’’                           SQLCLI.H"                             ANSI ANSI X3.135.3
                                                                            ISO ISO/IEC 9075-3




16 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                          WG3:YGJ-017 = X3H2-99-085
                                                                                    3.3 Conventions

                    Table 1—Clause, Subclause, and Table relationships (Cont.)
Clause, Subclause, or Table in
this part of
ANSI ANSI X3.135                        Corresponding Clause,
ISO ISO/IEC 9075                        Subclause, or Table from an-           Part containing corre-
                                        other part                             spondence
Subclause E.2, ‘‘COBOL Library          Subclause A.2, "COBOL library item
Item SQLCLI’’                           SQLCLI"                                ANSI ANSI X3.135.3
                                                                               ISO ISO/IEC 9075-3


Annex F, ‘‘SQL Feature Taxonomy’’       Appendix F, "SQL feature and
                                        package taxonomy"                      ANSI ANSI X3.135.2
                                                                               ISO ISO/IEC 9075-2


Table 1, ‘‘Clause, Subclause, and       none                                   none
Table relationships’’
Table 2, ‘‘Valid datalink file control   none                                   none
options’’
Table 3, ‘‘Data type correspondences    Table 18, "Data type correspon-
for Ada’’                               dences for Ada"                        ANSI ANSI X3.135.2
                                                                               ISO ISO/IEC 9075-2


Table 4, ‘‘Data type correspondences    Table 19, "Data type correspon-
for C’’                                 dences for C"                          ANSI ANSI X3.135.2
                                                                               ISO ISO/IEC 9075-2


Table 5, ‘‘Data type correspondences    Table 20, "Data type correspon-
for COBOL’’                             dences for COBOL"                      ANSI ANSI X3.135.2
                                                                               ISO ISO/IEC 9075-2


Table 6, ‘‘Data type correspondences    Table 21, "Data type correspon-
for Fortran’’                           dences for Fortran"                    ANSI ANSI X3.135.2
                                                                               ISO ISO/IEC 9075-2


Table 7, ‘‘Data type correspondences    Table 22, "Data type correspon-
for MUMPS’’                             dences for MUMPS"                      ANSI ANSI X3.135.2
                                                                               ISO ISO/IEC 9075-2


Table 8, ‘‘Data type correspondences    Table 23, "Data type correspon-
for Pascal’’                            dences for Pascal"                     ANSI ANSI X3.135.2
                                                                               ISO ISO/IEC 9075-2


Table 9, ‘‘Data type correspondences    Table 24, "Data type correspon-
for PL/I’’                              dences for PL/I"                       ANSI ANSI X3.135.2
                                                                               ISO ISO/IEC 9075-2




                                                     Definitions, notations, and conventions             17
 WG3:YGJ-017 = X3H2-99-085
3.3 Conventions

                      Table 1—Clause, Subclause, and Table relationships (Cont.)
 Clause, Subclause, or Table in
 this part of
 ANSI ANSI X3.135                         Corresponding Clause,
 ISO ISO/IEC 9075                         Subclause, or Table from an-           Part containing corre-
                                          other part                             spondence
 Table 10, ‘‘Codes used for SQL data      Table 4, "Codes used for SQL data
 types in Dynamic SQL’’                   types in Dynamic SQL"                  ANSI ANSI X3.135.5
                                                                                 ISO ISO/IEC 9075-5


 Table 11, ‘‘Abbreviated SQL/CLI          Table 4, "Abbreviated SQL/CLI
 generic names’’                          generic names"                         ANSI ANSI X3.135.3
                                                                                 ISO ISO/IEC 9075-3


 Table 12, ‘‘SQLSTATE class and           Table 5, "SQLSTATE class and
 subclass values for SQL/CLI-specific      subclass values for SQL/CLI-specific    ANSI ANSI X3.135.3
 conditions’’                             conditions"                            ISO ISO/IEC 9075-3


 Table 13, ‘‘Codes used for implemen-     Table 7, "Codes used for implemen-
 tation data types in SQL/CLI’’           tation data types in SQL/CLI"          ANSI ANSI X3.135.3
                                                                                 ISO ISO/IEC 9075-3


 Table 14, ‘‘Codes used for applica-      Table 8, "Codes used for application
 tion data types in SQL/CLI’’             data types in SQL/CLI"                 ANSI ANSI X3.135.3
                                                                                 ISO ISO/IEC 9075-3


 Table 15, ‘‘Codes used to identify       Table 26, "Codes used to identify
 SQL/CLI routines’’                       SQL/CLI routines"                      ANSI ANSI X3.135.3
                                                                                 ISO ISO/IEC 9075-3


 Table 16, ‘‘Codes and data types for     Table 27, "Codes and data types for
 implementation information’’             implementation information"            ANSI ANSI X3.135.3
                                                                                 ISO ISO/IEC 9075-3


 Table 17, ‘‘Codes used for datalink      none                                   none
 attributes’’
 Table 18, ‘‘Data types of attributes’’   Table 19, "Data types of attributes"
                                                                                 ANSI ANSI X3.135.3
                                                                                 ISO ISO/IEC 9075-3




3.4 Object identifier for Database Language SQL

The object identifier for Database Language SQL is defined in
 ANSI ANSI X3.135.1
 ISO ISO/IEC 9075-1
in Subclause 7.3, "Object identifier for Database Language SQL", with the following additions:




18 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                            WG3:YGJ-017 = X3H2-99-085
                                        3.4 Object identifier for Database Language SQL

Format

<Part 9 yes> ::= <Part 9 conformance>

<Part 9 conformance> ::= 9 | sqlmed199x <left paren> 9 <right paren>



Syntax Rules

   None.




                                            Definitions, notations, and conventions   19
WG3:YGJ-017 = X3H2-99-085




20 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                         WG3:YGJ-017 = X3H2-99-085




4 Concepts


4.1 Data types

  Insert after 7th paragraph   SQL defines a predefined data type named by the following <key word>:
DATALINK.
  Insert after 8th paragraph   For reference purposes, the data type DATALINK is referred to as a (or
the) datalink type.

4.2 Abstract LOB types

An abstract LOB type (ALT) is a large object string type described by an abstract LOB type descrip-
tor. An abstract LOB type descriptor specifies a set of routines that perform the actions defined for
large object strings.
An abstract LOB type specifies an apparent LOB type, which is either a character string large object
type, a national character string large object type, or a binary large object type. All operations
described in Subclause 4.2.2.3, "Operations involving large object character strings", in
ANSI ANSI X3.135.2,
ISO ISO/IEC 9075-2,
are supported for abstract LOB types whose apparent LOB type is a character string or national
character string LOB type. All operations described in Subclause 4.3, "Binary strings", in
ANSI ANSI X3.135.2,
ISO ISO/IEC 9075-2,
are supported for abstract LOB types whose apparent LOB type is a binary LOB type.
An abstract LOB type descriptor describes an abstract LOB type. An abstract LOB type descriptor
includes:

— The name of the abstract LOB type.

— The apparent LOB type.

— The LOB surrogate data type.

— The specific name of the LOB store routine.

— The specific name of the LOB release routine.

— The specific name of the LOB value routine.

— The specific name of the LOB equals routine.

— The specific name of the LOB locator routine.

— The specific name of the LOB hold locator routine.

— The specific name of the LOB free locator routine.

— The specific name of the concat routine.


                                                                                        Concepts        21
 WG3:YGJ-017 = X3H2-99-085
4.2 Abstract LOB types

— The specific name of the convert routine.

— The specific name of the length routine.

— The specific name of the like routine.

— The specific name of the lower routine;.

— The specific name of the overlay routine.

— The specific name of the position routine.

— The specific name of the substring routine.

— The specific name of the translate routine.

— The specific name of the trim routine.

— The specific name of the upper routine.


4.3 Datalinks

The datalink is a value of some datalink type. A datalink represents some external object (i.e., one
that is not part of the SQL-environment). The object in question is assummed to be contained in a
file, accessible through some external file server. A datalink conceptually consists of the following
parts:

— LinkType: The type of linkage to an external file. The value is ‘‘URL’’.

— Scheme: The value is either ‘‘HTTP’’ or ‘‘FILE’’.

— File Server: The name of a file server through which the external object can be accessed.

— File Path: A reference to an external object.

— Comment: An arbitrary character string.

These conceptual parts are accessible by invoking built-in scalar functions defined in this part of
ANSI ANSI X3.135.
ISO ISO/IEC 9075.


A datalink data type is described by a datalink data type descriptor. A data link data type descrip-
tor contains:

— The name of the data type (DATALINK).

— The maximum length of the datalink data type (in octets).

— A host variable, a datalink value is materialized as an N-octet value, where N is implementation-
  defined.




22 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                          WG3:YGJ-017 = X3H2-99-085
                                                                                       4.3 Datalinks


4.3.1 Operations involving datalink

4.3.1.1     Operators that operate on datalink
<datalink comment expression> returns a comment, if any, from a datalink.
<file size exact> returns the size (in bytes) of a currently linked object.
<file size expression> returns the size (in kilobytes) of a currently linked object.
<link type expression> returns the link type from a datalink.
<url complete expression> returns the complete URL from a URL type of datalink.
<url path expression> returns the path and external file name necessary to access an object through
a given file server from an URL type of datalink.
<url path only expression> returns the path and external file name from an URL type of datalink.
<url scheme expression> returns the scheme from a datalink whose link type is URL.
<url server expression> returns the external file server name from a datalink whose link type is
URL.


4.3.1.2     Other operators involving datalink
<datalink value expression> creates a datalink.

4.4 Type conversion and mixing of data types

 New paragraph A datalink can be compared only with other datalinks and assigned only to sites of
data type DATALINK.


4.5 Columns, fields, and attributes

  Insert following 7th paragraph   The column descriptor of a column of the datalink type additionally
includes:

— The link type of the datalink type (URL).

— The link control (NO LINK CONTROL or FILE LINK CONTROL).

— The integrity control option (ALL or SELECTIVE).

— The read permission option (FS or DB).

— The write permission option (FS or BLOCKED).

— The recovery option (NO or YES).

— The unlink option (RESTORE or DELETE).




                                                                                         Concepts        23
 WG3:YGJ-017 = X3H2-99-085
4.5 Columns, fields, and attributes

 New paragraph   The meanings of the various link control options are:

— NO LINK CONTROL: Although every file path must conform to the syntax for such identi-
  fiers as specified by the external file server, it is permitted for there to be no external object
  referenced by that file path.

— FILE LINK CONTROL: Every file path must reference an existing external object. Further file
  control depends on the link control options.

— INTEGRITY ALL: External objects referenced by file paths cannot be deleted or renamed,
  except possibly through the use of SQL operators.

— INTEGRITY SELECTIVE: External objects referenced by file paths can be deleted or renamed
  using operators provided by the file server, unless a datalinker is installed in connection with
  the file server.

— READ PERMISSION FS: Permission to read external objects referenced by datalinks is deter-
  mined by the file server.

— READ PERMISSION DB: Permission to read external objectes referenced by datalinks is deter-
  mined by the SQL-implementation.

— WRITE PERMISSION FS: Permission to write external objects referenced by datalinks is
  determined by the file server.

— WRITE PERMISSION BLOCKED: Write access to external objects referenced by datalinks is
  not available. Updates can, however, arise indirectly through the use of some implementation-
  defined mechanism.

— RECOVERY YES: Enables point in time recovery of external objects referenced by datalinks.

— RECOVERY NO: Point in time recovery of external objects referenced by datalinks is disabled.

— ON UNLINK RESTORE: When an external object referenced by a datalink is unlinked, the
  external file server attempts to reinstate the ownership and permissions that existed when that
  object was linked.

— ON UNLINK DELETE: An external object referenced by a datalink is deleted when it is un-
  linked.

 New paragraph Table 2, ‘‘Valid datalink file control options’’, specifies what combinations of datalink
file control options are allowed.
 New paragraph The default value of a column of data type DATALINK cannot be the null value, nor
can such a column be a member of any foreign key.

                              Table 2—Valid datalink file control options
                  Read permis-     Write per-
 Integrity        sion             mission          Recovery     Unlink

 ALL              FS               FS               NO           not applicable
 ALL              FS               BLOCKED          NO           RESTORE
 ALL              FS               BLOCKED          YES          RESTORE




24 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                      WG3:YGJ-017 = X3H2-99-085
                                                                4.5 Columns, fields, and attributes

                         Table 2—Valid datalink file control options (Cont.)
                Read permis-       Write per-
  Integrity     sion               mission           Recovery     Unlink
  ALL           DB                 BLOCKED           NO           RESTORE
  ALL           DB                 BLOCKED           NO           DELETE
  ALL           DB                 BLOCKED           YES          RESTORE
  ALL           DB                 BLOCKED           YES          DELETE
  SELECTIVE     FS                 FS                NO           not applicable



4.6 Tables

  Replaces 3rd paragraph A table is either a base table, a derived table, or an abstract table. A base
table is either a persistent base table, a global temporary table, a created local temporary table, or
a declared local temporary table.
  Replaces 10th paragraph A table is described by a table descriptor. A table descriptor is either a base
table descriptor, a view descriptor, a derived table descriptor (for a derived table that is not a view),
or an abstract table descriptor.
  Insert this paragraph An abstract table descriptor describes an abstract table. In addition to the
components of every table descriptor, an abstract table descriptor includes:

— The name of the abstract table.

— The iteration state data type.

— The routine name of the initialize routine.

— The routine name of the iterate routine.

— The routine name of the terminate routine.

— The routine name of the count routine.

— If the abstract table has an insert routine, then the routine name of that routine.

— If the abstract table has an update routine, then the routine name of that routine.

— If the abstract table has a delete routine, then the routine name of that routine.

— If the abstract table has a commit routine, then the routine name of that routine.

— If the abstract table has a rollback routine, then the routine name of that routine.


4.7 SQL statements




                                                                                        Concepts       25
 WG3:YGJ-017 = X3H2-99-085
4.7 SQL statements


4.7.1 SQL-statements classified by function
 New paragraph   The following are additional SQL-schema statements:

— <abstract LOB type definition>

— <abstract table definition>


4.8 Introduction to SQL/CLI

 Insert this paragraph The BuildDataLink routine can be used to build a datalink value. The
ExtractDataLinkAttr routine can be used to extract the attributes of a datalink value.




26 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                    WG3:YGJ-017 = X3H2-99-085




5 Lexical elements


5.1 <token> and <separator>

Function
Specify lexical units (tokens and separators) that participate in SQL language.
Format

<non-reserved word> ::=


       ANSI Only---caused by ISO changes not yet considered by ANSI

         !! All alternatives from ANSI X3.135.2
       | !! All alternatives from ANSI X3.135.5

       ISO Only---caused by ANSI changes not yet considered by ISO

         !! All alternatives from ISO/IEC 9075-2
       | !! All alternatives from ISO/IEC 9075-5



       | BLOCKED

       | CONTROL

       | DB | DLCOMMENT | DLFILESIZE | DLFILESIZEEXACT | DLLINKTYPE
       | DLURLCOMPLETE | DLURLPATH | DLURLPATHONLY | DLURSCHEME
       | DLURLSERVER | DLVALUE

       | FILE | FS

       | INTEGRITY

       | LINK

       | PERMISSION

       | RECOVERY | RESTORE

       | SELECTIVE

       | UNLINK

       | YES

       <reserved word> ::=


       ANSI Only---caused by ISO changes not yet considered by ANSI




                                                                            Lexical elements   27
 WG3:YGJ-017 = X3H2-99-085
5.1 <token> and <separator>

        !! All alternatives from ANSI X3.135.2
      | !! All alternatives from ANSI X3.135.5

      ISO Only---caused by ANSI changes not yet considered by ISO

        !! All alternatives from ISO/IEC 9075-2
      | !! All alternatives from ISO/IEC 9075-5



      | DATALINK

      | INITIALIZE | ITERATE

      | STATE

      | TERMINATE



Syntax Rules

   No additional Syntax Rules.

Access Rules

   No additional Access Rules.

General Rules

   No additional General Rules.




28 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                          WG3:YGJ-017 = X3H2-99-085




6 Scalar expressions


6.1 <data type>

Function
Specify a data type.
Format

<predefined type> ::=


         ANSI Only---caused by ISO changes not yet considered by ANSI

           !! All alternatives from ANSI X3.135.2
         | !! All alternatives from ANSI X3.135.5

         ISO Only---caused by ANSI changes not yet considered by ISO

           !! All alternatives from ISO/IEC 9075-2
         | !! All alternatives from ISO/IEC 9075-5


         | <abstract LOB type name>
         | <datalink type>

<datalink type> ::=
       DATALINK [ <left paren> <datalink length> <right paren> ]

<datalink length> ::= <length>



Syntax Rules
1)    Insert this SR   DATALINK specifies the data type datalink.

2)    Insert this SR   If <datalink length> is omitted, then an implementation-defined <datalink length>
     is implicit.

3)                 The maximum value of <datalink length> is implementation-defined. <datalink
      Insert this SR
     length> shall not be greater than this maximum value.

4)    Insert this SR   If a <data type> specifies an <abstract LOB type name> ALTN, then:

     a) There shall exist an abstract LOB type descriptor ALTD specifying the <abstract LOB type
        name> ALTN.

     b) The <data type> is considered to be the <apparent LOB type> of ALTD.
        NOTE 3 – This is intended to specify that for all type-checking rules, a data item of abstract LOB
         type ALT is considered to have the datatype of the apparent type of ALT. E.g., the data item is
         assumed to be of the apparent type for all rules of assignment, parameter passing, etc.




                                                                                Scalar expressions         29
 WG3:YGJ-017 = X3H2-99-085
6.1 <data type>

Access Rules

   No additional Access Rules.

General Rules

   No additional General Rules.




30 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                        WG3:YGJ-017 = X3H2-99-085
                                                                       6.2 <numeric value function>


6.2 <numeric value function>

Function
Specify a function yielding a value of type numeric.
Format

<numeric value function> ::=


         ANSI Only---caused by ISO changes not yet considered by ANSI

           !! All alternatives from ANSI X3.135.2
         | !! All alternatives from ANSI X3.135.5

         ISO Only---caused by ANSI changes not yet considered by ISO

           !! All alternatives from ISO/IEC 9075-2
         | !! All alternatives from ISO/IEC 9075-5


         | <file size exact expression>
         | <file size expression>

<file size exact expression> ::=
       DLFILESIZEEXACT <left paren> <datalink expression> <right paren>

<file size expression> ::=
       DLFILESIZE <left paren> <datalink expression> <right paren>



Syntax Rules
1)               If <file size exact expression> is specified, then the data type of the result is exact
      Insert this SR
     numeric with implementation-defined precision and scale 0 (zero).

2)    Insert this SR If <file size expression> is specified, then the data type of the result is exact
     numeric with implementation-defined precision and scale 0 (zero).

Access Rules

     No additional Access Rules.

General Rules

1)    Replaces GR1) If the value of one or more <string value expression>s, <datetime value expres-
     sions>s, <interval value expression>s, <collection value expression>s, and <datalink expres-
     sion>s that are simply contained in <numeric value function> are the null value, then the result
     of the <numeric value function> is the null value.

2)    Insert this GR If a <file size exact expression> is specified, then let D be the <datalink expres-
     sion>. The result of the <file size exact expression> is the smallest integer not less than the
     quotient of the division (BIT_LENGTH(D/8)).




                                                                             Scalar expressions        31
 WG3:YGJ-017 = X3H2-99-085
6.2 <numeric value function>

3)    Insert this GR If a <file size expression> is specified, then let D be the <datalink expression>.
     The result of the <file size expression> is the smallest integer not less than the quotient of the
     division (BIT_LENGTH(D/8) * 1024).




32 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                       WG3:YGJ-017 = X3H2-99-085
                                                                        6.3 <string value function>


6.3 <string value function>

Function
Specify a function yielding a value of type character string or bit string.
Format

<string value function> ::=


       ANSI Only---caused by ISO changes not yet considered by ANSI

         !! All alternatives from ANSI X3.135.2
       | !! All alternatives from ANSI X3.135.5

       ISO Only---caused by ANSI changes not yet considered by ISO

         !! All alternatives from ISO/IEC 9075-2
       | !! All alternatives from ISO/IEC 9075-5


       |   <datalink comment expression>
       |   <link type expression>
       |   <url complete expression>
       |   <url path expression>
       |   <url path only expression>
       |   <url scheme expression>
       |   <url server expression>

<datalink comment expression> ::=
       DLCOMMENT <left paren> <datalink expression> <right paren>

<link type expression> ::=
       DLLINKTYPE <left paren> <datalink expression> <right paren>

<url complete expression> ::=
       DLURLCOMPLETE <left paren> <datalink expression> <right paren>

<url path expression> ::=
       DLURLPATH <left paren> <datalink expression> <right paren>

<url path only expression> ::=
       DLURLPATHONLY <left paren> <datalink expression> <right paren>

<url scheme expression> ::=
       DLURLSCHEME <left paren> <datalink expression> <right paren>

<url server expression> ::=
       DLURLSERVER <left paren> <datalink expression> <right paren>




                                                                              Scalar expressions   33
 WG3:YGJ-017 = X3H2-99-085
6.3 <string value function>

Syntax Rules
1)    Insert this SR If <datalink comment expression> is specified, then the data type of the result is a
     variable-length character string with an implementation-defined maximal length.

2)    Insert this SR If <link type expression> is specified, then the data type of the result is a variable-
     length character string with an implementation-defined maximal length.

3)                 If <url complete expression> is specified, then the data type of the result is a
      Insert this SR
     variable-length character string with an implementation-defined maximal length.

4)    Insert this SR If <url path expression> is specified, then the data type of the result is a variable-
     length character string with an implementation-defined maximal length.

5)    Insert this SR If <url path only expression> is specified, then the data type of the result is a
     variable-length character string with an implementation-defined maximal length.

6)                 If <url scheme expression> is specified, then the data type of the result is a
      Insert this SR
     variable-length character string with an implementation-defined maximal length.

7)    Insert this SR If <url server expression> is specified, then the data type of the result is a
     variable-length character string with an implementation-defined maximal length.

Access Rules

     No additional Access Rules.

General Rules
1)    Insert this GR If the value of the <datalink expression> simply contained in <string value func-
     tion> is the null value, then the result of the <string value function> is the null value.

2)    Insert this GR If <datalink comment expression> is specified, then the result is the datalink
     comment of a datalink.

3)    Insert this GR   If <link type expression> is specified, then the result is the link type of a datalink.


4)    Insert this GR If <url complete expression> is specified, then the result is the concatenation of
     scheme, file server name, and file path.
     NOTE 4 – The file path consists of the operating system path, external file name, and access token.
     The access token is only valid if READ PERMISSION DB was specified.

5)    Insert this GR If <url path expression> is specified, then the result is the concatenation of oper-
     ating system path, external file name, and access token of a datalink.
     NOTE 5 – The access token is only valid if READ PERMISSION DB was specified.

6)    Insert this GR If <url path only expression> is specified, then the result is the concatenation of
     operating system path and external file name of a datalink.

7)    Insert this GR   If <url scheme expression> is specified, then the result is the scheme of a datalink
     value.




34 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                        WG3:YGJ-017 = X3H2-99-085
                                                                         6.3 <string value function>

8)    Insert this GR If <url server expression> is specified, then the result is the external file server
     name of a datalink.




                                                                              Scalar expressions      35
 WG3:YGJ-017 = X3H2-99-085
6.4 <datalink value function>


6.4 <datalink value function>

Function
Specify a function yielding a value of type datalink.
Format

<datalink value function> ::=
       <datalink value constructor>

<datalink value constructor> ::=
       DLVALUE <left paren> <data location> <comma> <linktype>
         [ <comma> <datalink comment> ] <right paren>

<datalink expression> ::=
         <item reference>
       | <datalink value constructor>

<data location> ::= <character value expression>

<datalink comment> ::= <character value expression>



Syntax Rules
1) If <datalink value constructor> is specified, then the data type of the result is a datalink value
   whose type is datalink.

Access Rules

   No additional Access Rules.

General Rules
1) If the value of the <data location> or <linktype> simply contained in <datalink value function>
   is the null value, then the result of the <datalink value function> is the null value.

2) If <datalink value constructor> is specified, then the result is a datalink value.




36 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                           WG3:YGJ-017 = X3H2-99-085
                                                                               6.5 <cast specification>


6.5 <cast specification>

Function
Specify a data conversion.
Format

No additional Format items.



Syntax Rules
1)    Augments SR6) If the <cast operand> is a <value expression>, then the valid combinations of TD
     and SD in a <cast specification> are given by the following table.



        <data type>
        SD of                   <data type> of TD
        <value
        expression>        EN   AN   VC   FC   VB   FB   D   T   TS   YM    DT   BO UDT   CL   BL   RT   DL

             EN            Y    Y    Y    Y    N    N    N   N   N    M     M    N   M    Y    N    N    N
             AN            Y    Y    Y    Y    N    N    N   N   N    N     N    N   M    Y    N    N    N
             C             Y    Y    Y    Y    Y    Y    Y   Y   Y    Y     Y    Y   M    Y    N    N    N
             B             N    N    Y    Y    Y    Y    N   N   N    N     N    N   M    Y    N    N    N
             D             N    N    Y    Y    N    N    Y   N   Y    N     N    N   M    Y    N    N    N
             T             N    N    Y    Y    N    N    N   Y   Y    N     N    N   M    Y    N    N    N
             TS            N    N    Y    Y    N    N    Y   Y   Y    N     N    N   M    Y    N    N    N
             YM            M    N    Y    Y    N    N    N   N   N    Y     N    N   M    Y    N    N    N
             DT            M    N    Y    Y    N    N    N   N   N    N     Y    N   M    Y    N    N    N
             BO            N    N    Y    Y    N    N    N   N   N    N     N    Y   M    Y    N    N    N
            UDT            M    M    M    M    M    M    M   M   M    M     M    M   M    M    M    N    N
             BL            N    N    N    N    N    N    N   N   N    N     N    N   M    N    Y    N    N
             RT            N    N    N    N    N    N    N   N   N    N     N    N   N    N    N    Y    N
             DL            N    N    N    N    N    N    N   N   N    N     N    N   N    N    N    N    Y

        Where:

             EN   =   Exact Numeric
             AN   =   Approximate Numeric
             C    =   Character (Fixed- or Variable-length, or character large object)
             FC   =   Fixed-length Character
             VC   =   Variable-length Character
             CL   =   Character Large Object
             B    =   Bit String (Fixed- or Variable-length)
             FB   =   Fixed-length Bit String
             VB   =   Variable-length Bit String
             D    =   Date
             T    =   Time
             TS   =   Timestamp
             YM   =   Year-Month Interval
             DT   =   Day-Time Interval
             BO   =   Boolean
            UDT   =   User-Defined Type
             BL   =   Binary Large Object
             RT   =   Reference type
             DL   =   Datalink



                                                                                 Scalar expressions      37
 WG3:YGJ-017 = X3H2-99-085
6.5 <cast specification>



Access Rules

     No additional Access Rules.

General Rules
1)    Insert this GR   If TD and SD are datalink types, then TV is SV.




38 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                    WG3:YGJ-017 = X3H2-99-085
                                                                         6.6 <value expression>


6.6 <value expression>

Function
Specify a value.
Format

<value expression> ::=


        ANSI Only---caused by ISO changes not yet considered by ANSI

          !! All alternatives from ANSI X3.135.2
        | !! All alternatives from ANSI X3.135.5

        ISO Only---caused by ANSI changes not yet considered by ISO

          !! All alternatives from ISO/IEC 9075-2
        | !! All alternatives from ISO/IEC 9075-5


        | <datalink value expression>



Syntax Rules

1)    Replaces SR1) The declared type of a <value expression> is the declared type of the <numeric
     value expression>, <string value expression>, <datetime value expression>, <interval value
     expression>, <boolean value expression>, <user-defined type value expression>, <row value
     expression>, <collection value expression>, <reference value expression>, or <datalink value
     expression>, respectively.

Access Rules

     No additional Access Rules.

General Rules

     No additional General Rules.




                                                                          Scalar expressions     39
 WG3:YGJ-017 = X3H2-99-085
6.7 <datalink value expression>


6.7 <datalink value expression>

Function
Specify a datalink value.
Format

<datalink value expression> ::=
       <datalink value function>



Syntax Rules
1) The data type of <datalink value function> shall be a datalink type.

Access Rules

   None.

General Rules

   None.




40 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                        WG3:YGJ-017 = X3H2-99-085




7 Predicates


7.1 <comparison predicate>

Function
Specify a comparison of two row values.
Format

No additional Format items.



Syntax Rules

1)    Insert after SR5)a) If the declared type of Xi or Yi is a datalink, then <comp op> shall be either
     <equals operator> or <not equals operator>.

Access Rules

     No additional Access Rules.

General Rules

1)     Insert after GR1)b)iii) If the declared types of XV and YV are datalink types, then X is equal
     to Y if and only if DLCOMMENT(X) is equal to DLCOMMENT(Y) and DLLINKTYPE(X)
     is equal to DLLINKTYPE(Y) and DLURLPATHONLY(X) is equal to DLURLPATHONLY(Y)
     and DLURLSCHEME(X) is equal to DLURLSCHEME(Y) and DLURLSERVER(X) is equal to
     DLURLSERVER(Y).




                                                                                       Predicates       41
WG3:YGJ-017 = X3H2-99-085




42 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                            WG3:YGJ-017 = X3H2-99-085




8 Data assignment rules and routine determination


8.1 Retrieval assignment

Function
Specify rules for value assignments that retrieve SQL-data.
Syntax Rules
1)                If the data type of T is a datalink type, then V shall be a mutually assignable
      Insert this SR
     datalink type.

Access Rules

     No additional Access Rules.

General Rules

1)    Insert after GR4)u)   If the most specific type of T is datalink, then the value of T is set to V.




                                             Data assignment rules and routine determination              43
 WG3:YGJ-017 = X3H2-99-085
8.2 Store assignment


8.2 Store assignment

Function
Specify rules for value assignments that store SQL-data or store into host parameters or SQL
parameters.
Syntax Rules
1)    Insert this SR   If the data type of T is datalink, then the data type of V shall be datalink.

Access Rules

     No additional Access Rules.

General Rules
1)    Insert after GR2)b)xxi)   If the most specific type of T is datalink, then the value of T is set to V.




44 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                    WG3:YGJ-017 = X3H2-99-085
                                                          8.3 Type precedence list determination


8.3 Type precedence list determination

Function
Determine the type precedence list of a given type.
Syntax Rules
1)    Insert this SR   If DT is datalink, then TPL is
         DATALINK DT

Access Rules

     No additional Access Rules.

General Rules

     No additional General Rules.




                                            Data assignment rules and routine determination   45
WG3:YGJ-017 = X3H2-99-085




46 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                            WG3:YGJ-017 = X3H2-99-085




9 Schema definition and manipulation


9.1 <schema definition>

Function
Define a schema.
Format

<schema element> ::=
         !! All alternatives from part 2 of ISO/IEC 9075
       | !! All alternatives from part 5 of ISO/IEC 9075
       | <abstract LOB type definition>
       | <abstract table definition>



Syntax Rules

   No additional Syntax Rules.

Access Rules

   No additional Access Rules.

General Rules

   No additional General Rules.




                                                 Schema definition and manipulation   47
 WG3:YGJ-017 = X3H2-99-085
9.2 <column definition>


9.2 <column definition>

Function
Define a column of a table.
Format

<column constraint> ::=


        ANSI Only---caused by ISO changes not yet considered by ANSI

          !! All alternatives from ANSI X3.135.2
        | !! All alternatives from ANSI X3.135.5

        ISO Only---caused by ANSI changes not yet considered by ISO

          !! All alternatives from ISO/IEC 9075-2
        | !! All alternatives from ISO/IEC 9075-5


        | <datalink control definition>

<datalink control definition> ::=
       <link type specification> <datalink control option>

<link type specification> ::= LINKTYPE <linktype>

<linktype> ::= URL

<datalink control option> ::=
         NO LINK CONTROL
       | FILE LINK CONTROL <datalink file control options>...

<datalink file control options> ::=
         INTEGRITY ALL
       | INTEGRITY SELECTIVE
       | READ PERMISSION FS
       | READ PERMISSION DB
       | WRITE PERMISSION FS
       | WRITE PERMISSION BLOCKED
       | RECOVERY NO
       | RECOVERY YES
       | ON UNLINK RESTORE
       | ON UNLINK DELETE



Syntax Rules
1)    Insert this SR If <datalink control definition> is specified, then <data type> shall specify ei-
     ther DATALINK or the <user-defined type name> of a distinct type whose source data type is
     DATALINK.
     NOTE 6 – ‘‘Source data type’’ is defined in Subclause 11.40, "<user-defined type definition>", in
     ANSI ANSI X3.135.2.
     ISO ISO/IEC 9075-2.




48 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                              WG3:YGJ-017 = X3H2-99-085
                                                                                  9.2 <column definition>

2)    Insert this SR If <data type> specifies DATALINK or the <user-defined type name> of a distinct
     type whose source data type is DATALINK and <datalink control definition> is not specified,
     then NO LINK CONTROL is implicit.
     NOTE 7 – ‘‘Source data type’’ is defined in Subclause 11.40, "<user-defined type definition>", in
     ANSI ANSI X3.135.2.
     ISO ISO/IEC 9075-2.



3)    Insert this SR   If <datalink file control options> is specified, then:

     a) Only one of INTEGRITY ALL or INTEGRITY SELECTIVE shall be specified. If neither
        INTEGRITY ALL nor INTEGRITY SELECTIVE is specified, then INTEGRITY ALL is
        implicit.

     b) Only one of READ PERMISSION FS or READ PERMISSION DB shall be specified. If
        neither READ PERMISSION FS nor READ PERMISSION DB is specified, then READ
        PERMISSION FS is implicit.

     c) Only one of WRITE PERMISSION FS or WRITE PERMISSION BLOCKED shall be speci-
        fied. If neither WRITE PERMISSION FS nor WRITE PERMISSION BLOCKED is specified,
        then WRITE PERMISSION FS is implicit.

     d) Only one of RECOVERY NO or RECOVERY YES shall be specified. If neither RECOVER
        NO nor RECOVERY YES is specified, then RECOVERY NO is implicit.

     e) Only one of ON UNLINK RESTORE or ON UNLINK DELETE shall be specified. If nei-
        ther ON UNLINK RESTORE nor ON UNLINK DELETE is specified, then ON UNLINK
        RESTORE is implicit.
        NOTE 8 – Valid combinations of <datalink file control options> are specified in Table 2, ‘‘Valid
         datalink file control options’’.

Access Rules

     No additional Access Rules.

General Rules
1)    Insert after GR5)f) If <datalink control definition> is specified, then <datalink control definition>,
     <datalink control option>, and the <datalink file control options> are determined by the Syntax
     Rules of this Subclause.




                                                            Schema definition and manipulation            49
 WG3:YGJ-017 = X3H2-99-085
9.3 <drop table statement>


9.3 <drop table statement>

Function
Destroy a table.
Format

No additional Format items.



Syntax Rules

     No additional Syntax Rules.

Access Rules

     No additional Access Rules.

General Rules
1)    Insert this GR If <datalink control definition> is FILE LINK CONTROL, then all files
     corresponding to the datalink values of a column whose descriptor is included in the descriptor
     of T, with <data type> of DATALINK or a user-defined data type with a source data type of
     DATALINK are unlinked.
     NOTE 9 – The files are unlinked according to the <datalink file control options> of ON UNLINK
     RESTORE or ON UNLINK DELETE where applicable, as specified in Table 2, ‘‘Valid datalink file control
     options’’.




50 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                              WG3:YGJ-017 = X3H2-99-085
                                                        9.4 <abstract LOB type definition>


9.4 <abstract LOB type definition>

Function
Define an abstract LOB type.
Format

<abstract LOB type definition> ::=
       CREATE ABSTRACT LOB TYPE <abstract LOB type name>
       FOR <apparent LOB type> REPRESENTED BY <LOB surrogate type>
       STORE <LOB store routine>
       RELEASE <LOB release routine>
       VALUE <LOB value routine>
       EQUALS <LOB equals routine>
       LOCATOR <LOB locator routine>
       HOLD LOCATOR <LOB hold locator routine>
       FREE LOCATOR <LOB free locator routine>
       CONCATENATE <LOB concat routine>
       CONVERT <LOB convert routine>
       LENGTH <LOB length routine>
       LIKE <LOB like routine>
       LOWER <LOB lower routine>
       OVERLAY <LOB overlay routine>
       POSITION <LOB position routine>
       SUBSTRING <LOB substring routine>
       TRANSLATE <LOB translate routine>
       TRIM <LOB trim routine>
       UPPER <LOB upper routine>

<apparent LOB type> ::=
         <character LOB type> [ CHARACTER SET <character set specification> ]
       | <national character LOB type>
       | <binary LOB type>

<character LOB type> ::=
         CHARACTER LARGE OBJECT
       | CHAR LARGE OBJECT
       | CLOB

<national character LOB type> ::=
         NATIONAL CHARACTER LARGE OBJECT
       | NCHAR LARGE OBJECT
       | NCLOB

<binary LOB type> ::=
         BINARY LARGE OBJECT
       | BLOB

<LOB surrogate type> ::= <data type>

<LOB store routine> ::= <specific routine designator>

<LOB release routine> ::= <specific routine designator>

<LOB value routine> ::= <specific routine designator>

<LOB equals routine> ::= <specific routine designator>




                                               Schema definition and manipulation      51
 WG3:YGJ-017 = X3H2-99-085
9.4 <abstract LOB type definition>

<LOB locator routine> ::= <specific routine designator>

<LOB hold locator routine> ::= <specific routine designator>

<LOB free locator routine> ::= <specific routine designator>

<LOB concat routine> ::= <specific routine designator>

<LOB convert routine> ::= <specific routine designator>

<LOB fold routine> ::= <specific routine designator>

<LOB length routine> ::= <specific routine designator>

<LOB like routine> ::= <specific routine designator>

<LOB overlay routine> ::= <specific routine designator>

<LOB position routine> ::= <specific routine designator>

<LOB substring routine> ::= <specific routine designator>

<LOB translate routine> ::= <specific routine designator>

<LOB trim routine> ::= <specific routine designator>



Syntax Rules
1) If an <abstract LOB type definition> is contained in a <schema definition> SD and the <abstract
   LOB type name> contains a <local or schema qualifier>, then that <local or schema qualifier>
   shall be the same as the implicit or explicit <schema name> of SD.

2) Let ALTN be the <abstract LOB type name>.

3) The schema identified by the explicit or implicit schema name of the <abstract LOB type name>
   shall not include an abstract LOB type descriptor whose abstract LOB type name is ALTN.

4) If the <abstract LOB type definition> appears in a <schema definition>, then let A be the
   explicit or implicit <authorization identifier> of the <schema definition>. Otherwise, let A be
   the <authorization identifier> of the current SQL-session.

5) Let APLT be the <apparent LOB type>.

6) Let LST be the <LOB surrogate type>.

7) The routine identified by the <LOB store routine> is called the store routine of the abstract
   LOB type. The store routine shall be a function with the following <SQL parameter list> and
   <returns clause>:
       (in APLT) RETURNS LST

8) The routine identified by the <LOB release routine> is called the release routine of the abstract
   LOB type. The release routine shall be a procedure with the following <SQL parameter list>:
       (in LST)




52 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                    WG3:YGJ-017 = X3H2-99-085
                                                              9.4 <abstract LOB type definition>

 9) The routine identified by the <LOB value routine> is called the value routine of the abstract
    LOB type. The value routine shall be a function with the following <SQL parameter list>:
        (in LST) RETURNS APLT

10) The routine identified by the <LOB equals routine> is called the equals routine of the abstract
    LOB type. The eqials routine shall be a function with the following <SQL parameter list>:
        (in LST, in LST) RETURNS BOOLEAN

11) The routine identified by the <LOB locator routine> is called the locator routine of the abstract
    LOB type. The locator routine shall be a function with the following <SQL parameter list> and
    <returns clause>:
        (in LST) RETURNS INTEGER

12) The routine identified by the <LOB hold locator routine> is called the hold locator routine of
    the abstract LOB type. The hold locator routine shall be a procedure with the following <SQL
    parameter list>:
        (in INTEGER)

13) The routine identified by the <LOB free locator routine> is called the free locator routine of
    the abstract LOB type. The free locator routine shall be a procedure with the following <SQL
    parameter list>:
        (in INTEGER)

14) The routine identified by the <LOB concat routine> is called the concat routine of the abstract
    LOB type. The concat routine shall be a function with the following <SQL parameter list> and
    <returns clause>:
        (in LST, in LST) RETURNS LST

15) The routine identified by the <LOB convert routine> is called the convert routine of the abstract
    LOB type. The convert routine shall be a function with the following <SQL parameter list> and
    <returns clause>:
        (in LST, in VARCHAR(254)) RETURNS LST

16) The routine identified by the <LOB length routine> is called the length routine of the abstract
    LOB type. The length routine shall be a function with the following <SQL parameter list> and
    <returns clause>:
        (in LST) RETURNS VARCHAR(254)

17) The routine identified by the <LOB like routine> is called the like routine of the abstract LOB
    type. The like routine shall be a function with the following <SQL parameter list> and <returns
    clause>:
        (in LST, VARCHAR(254)) RETURNS BOOLEAN

18) The routine identified by the <LOB lower routine> is called the lower routine of the abstract
    LOB type. The lower routine shall be a function with the following <SQL parameter list> and
    <returns clause>:
        (in LST) RETURNS LST




                                                      Schema definition and manipulation            53
  WG3:YGJ-017 = X3H2-99-085
 9.4 <abstract LOB type definition>

19) The routine identified by the <LOB overlay routine> is called the overlay routine of the abstract
    LOB type. The overlay routine shall be a function with the following <SQL parameter list> and
    <returns clause>:
        (in LST, in LST, in INTEGER) RETURNS LST

20) The routine identified by the <LOB position routine> is called the position routine of the ab-
    stract LOB type. The position routine shall be a function with the following <SQL parameter
    list> and <returns clause>:
        (in LST, in LST) RETURNS INTEGER

21) The routine identified by the <LOB substring routine> is called the substring routine of the ab-
    stract LOB type. The substring routine shall be a function with the following <SQL parameter
    list> and <returns clause>:
        (in LST, in INTEGER, in INTEGER) RETURNS LST

22) The routine identified by the <LOB translate routine> is called the translate routine of the
    abstract LOB type. The translate routine shall be a function with the following <SQL parameter
    list> and <returns clause>:
        (in LST, in VARCHAR(254)) RETURNS LST

23) The routine identified by the <LOB upper routine> is called the upper routine of the abstract
    LOB type. The upper routine shall be a function with the following <SQL parameter list> and
    <returns clause>:
        (in LST) RETURNS LST

 Access Rules
 1) If an <abstract LOB type definition> is contained in an <SQL-client module>, then the current
    authorization identifier shall be equal to the <authorization identifier> that owns the schema
    identified by the implicit or explicit <schema name> of the <abstract LOB type name>.

 General Rules
 1) An <abstract LOB type definition> defines an abstract LOB type ALT.

 2) An abstract LOB type descriptor ALTD is created that describes ALT. ALTD includes:

    a) The abstract LOB type name ALTN.

    b) The apparent LOB type.

     c) The LOB surrogate data type.

    d) The specific name of the LOB store routine.

     e) The specific name of the LOB release routine.

     f) The specific name of the LOB value routine.

    g) The specific name of the LOB equals routine.

    h) The specific name of the LOB locator routine.

     i) The specific name of the LOB hold locator routine.


 54 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                     WG3:YGJ-017 = X3H2-99-085
                                                               9.4 <abstract LOB type definition>

    j) The specific name of the LOB free locator routine.

   k) The specific name of the concat routine.

    l) The specific name of the convert routine.

   m) The specific name of the length routine.

   n) The specific name of the like routine.

    o) The specific name of the lower routine.

   p) The specific name of the overlay routine.

   q) The specific name of the position routine.

    r) The specific name of the substring routine.

    s) The specific name of the translate routine.

    t) The specific name of the trim routine.

   u) The specific name of the upper routine.

3) A set of privilege decriptors is created that define the USAGE privilege on this abstract LOB
   type. These privileges are grantable.

4) The grantor for each of these privilege descriptors is set to the special grantor value
   ‘‘_SYSTEM’’.

5) When a data item DI of type ALT is created, the implementation represents that data item with
   a data item SDI of type LST. SDI is called the surrogate data item of DI.

6) When Subclause 8.2, ‘‘Store assignment’’, or Subclause 8.1, ‘‘Retrieval assignment’’, assigns a
   value V to a target T,
   Case:

   a) If V has type APLT and T has type ALT, then:

        i) The store routine of ALT is invoked with parameter V, to obtain a result SV.

       ii) The value SV is assigned to the surrogate data item of T.
       NOTE 10 – This case assigns a built-in LOB value to an abstract LOB data item.

   b) If V has type ALT and T has type APLT, then:

        i) If the data item ALDI has a non-null value ALDIV, then the release routine of ALT is
           invoked with parameter ALDIV.

       ii) The value routine of ALT is invoked with parameter ALV, to obtain a result APV.

      iii) The value APV is assigned to the surrogate data item of ALDI.
       NOTE 11 – This case assigns an abstract LOB value to a built-in LOB data item.




                                                       Schema definition and manipulation             55
 WG3:YGJ-017 = X3H2-99-085
9.4 <abstract LOB type definition>

   c) If V has type ALT1 and T has type ALT2, where ALT1 and ALT2 are different abstract LOB
      types, then:

       i) The value routine of ALT1 is invoked with parameter ALV, to obtain a result APV.

      ii) The store routine of ALT2 is invoked with parameter APV, to obtain a result SV.

      iii) The value SV is assigned to the surrogate data item of ALDI.
       NOTE 12 – This case assigns a value of one abstract LOB type to a data item of another abstract
       LOB type.

   d) If V has type ALT and T is a host data item of type APLT, then:

       i) The locator routine of ALT is invoked with parameter ALV, to obtain a result LV of type
          INTEGER.

      ii) The value LV is assigned to the data item HDI.
      NOTE 13 – This case assigns an abstract LOB value to a host data item, hence generates a LOB
       locator.

7) When <comparison predicate> applies an <equals operator> to values XV and YV,
   Case:

   a) If XV and YV both have type ALT, then the equals routine of ALT is invoked with parame-
      ters XV and YV to obtain a boolean value that is the result of the <comparison predicate>.


   b) If XV has type ALT1 and YV has type ALT2, where ALT1 and ALT2 are different abstract
      LOB types, then it is implementation-defined which of the following two alternatives are
      performed:

       i) The value routine of ALT1 is invoked with parameter XV to obtain a result APV, the
          store routine of ALT2 is invoked with parameter APV to obtain a result ALT2X, and the
          equals routine of ALT2 is invoked with parameters ALT2X and YV to obtain a boolean
          value that is the result of the <comparison predicate>.

      ii) The value routine of ALT2 is invoked with parameter YV to obtain a result APV, the
          store routine of ALT1 is invoked with parameter APV to obtain a result ALT1Y, and the
          equals routine of ALT1 is invoked with parameters XV and ALT1Y to obtain a boolean
          value that is the result of the <comparison predicate>.
      NOTE 14 – This compares values of two abstract LOB types. Either we retrieve the first and pass
       it to the second for comparison, or vice versa, the choice being implementor-defined.

   c) If XV has type APLT and YV has type ALT, then it is implementation-defined which of the
      following two alternatives are performed:

       i) The value routine of ALT is invoked with parameter YV, to obtain a result APY and the
          <comparison predicate> is applied to XV and APY.

      ii) The store routine of ALT is invoked with parameter XV to obtain a result ALTX and
          the equals routine of ALT is invoked with parameters ALTX and YV to obtain a boolean
          result that is the result of the <comparison predicate>.
      NOTE 15 – This compares a built-in LOB with an abstract LOB type LOB. Either we retrieve
       the abstract LOB type LOB and compare it to the built-in LOB, or we pass the built-in LOB to the
       abstract LOB type to do the comparison.



56 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                      WG3:YGJ-017 = X3H2-99-085
                                                                9.4 <abstract LOB type definition>

    d) If XV has type ALT and YV has type APLT, then the <comparison predicate> is applied to
       YV and XV.
    NOTE 16 – If we compare an abstract LOB type LOB to a built-in LOB, simply reverse the operands
    and apply the preceding rule.

 8) When the <hold locator statement> is applied to an <item reference> LLI that denotes a data
    item whose type is an abstract LOB type ALT, the hold locator routine of ALT is invoked with
    parameter LLI. When the <free locator statement> is applied to an <item reference> LLI that
    denotes a data item whose type is an abstract LOB type ALT, the free locator routine of ALT is
    invoked with parameter LLI.

 9) Let LOB operation and ALT operation refer to any of the following:

    a) <comparison predicate> and equals routine.

    b) <concatenation> and concat routine.

     c) <form-of-use conversion> and convert routine.

    d) <length expression> and length routine.

    e) <like predicate> and like routine.

     f) <fold> and lower routine or upper routine.

    g) <character overlay function> and overlay routine.

    h) <string position expression> and position routine.

     i) <blob position expression> and position routine.

     j) <character substring function> and substring routine.

    k) <character translation> and translate routine.

10) For a given LOB operation LOP, let the supplementary parameters be as follows.
    Case

    a) If LOP is <form-of-use conversion>, then the supplementary parameter is a character string
       containing the <form-of-use conversion name>.

    b) If LOP is <character translation>, then the supplementary parameter is a character string
       containing the <translation name>.

     c) If LOP is <like predicate>, then the supplementary parameters are a character string con-
        taining the <character pattern> and a character string containing the <escape character>.


    d) If LOP is a <character overlay function>, then the supplementary parameter is an integer
       containing the <start position>.

    e) If LOP is a <character substring expression> then the supplementary parameters are an
       integer containing the <start position> and an integer containing the <string length>.

11) When one of the above LOB operations is applied to one or more operands whose type is an
    abstract LOB type,



                                                        Schema definition and manipulation         57
 WG3:YGJ-017 = X3H2-99-085
9.4 <abstract LOB type definition>

   Case:

   a) If the LOB operation has exactly one operand X that is a LOB and that operand is an
      abstract LOB type ALT, then it is implementation-defined which of the following two alter-
      natives are executed:

       i) The value routine of ALT is invoked with parameter X to obtain a value APX whose type
          is the apparent type of ALT, and the LOB operation is performed with APX.

      ii) The ALT operation corresponding with the LOB operation is invoked with X and the
          supplementary parameters (if any) of the LOB operation.

   b) If the LOB operation has two operands X and Y that are the same abstract LOB type ALT,
      then it is implementation-defined which of the following two alternatives are executed:

       i) The value routine of ALT is invoked with parameter X to obtain value APX and invoked
          again with parameter Y to obtain a value APY, whose types are the apparent type of
          ALT, and the LOB operation is performed with APX and APY.

      ii) The abstract LOB type operation corresponding with the LOB operation is invoked with
          X and Y and the supplementary parameters (if any) of the LOB operation.

   c) If the LOB operation has two operands X and Y that are different abstract LOB types ALT1
      and ALT2, then it is implementation-defined which of the following two alternatives are
      executed:

       i) The value routine of ALT1 is invoked with parameter X to obtain value APX, the store
          routine of ALT2 is invoked with parameter APX to obtain a value ALT2X, and the
          abstract LOB type operation corresponding with the LOB operation is invoked with
          ALT2X and Y and the supplementary parameters (if any) of the LOB operation.

      ii) The value routine of ALT2 is invoked with parameter Y to obtain value APY, the store
          routine of ALT1 is invoked with parameter APY to obtain a value ALT1Y, and the
          abstract LOG type operation corresponding with the LOB operation is invoked with X
          and ALT1Y and the supplementary parameters (if any) of the LOB operation.




58 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                              WG3:YGJ-017 = X3H2-99-085
                                                            9.5 <abstract table definition>


9.5 <abstract table definition>

Function
Define an abstract table.
Format

<abstract table definition> ::=
       CREATE ABSTRACT TABLE <table name>
         <abstract table description>
         <abstract table access specification>
         <abstract table count specification>
         <abstract table data manipulation specification>
         <abstract table transaction specification>

<abstract table description> ::=
       ( <abstract table column description>
         [ { <comma> <abstract table column description> }... ] )

<abstract table column description> ::=
       <column name> <data type>

<abstract table access specification> ::=
       STATE <iteration state data type>
       INITIALIZE <initialize routine name>
       ITERATE <iterate routine name>
       TERMINATE <terminate routine name>

<abstract table count specification> ::=
       COUNT <count routine name>

<abstract table   data manipulation specification> ::=
       [ INSERT   <insert routine name> ]
       [ UPDATE   <update routine name> ]
       [ DELETE   <delete routine name> ]

<abstract table transaction specification> ::=
       [ COMMIT <commit routine name> ROLLBACK <rollback routine name> ]

<iteration state data type> ::= <data type>

<initialize routine name> ::= <routine name>

<iterate routine name> ::= <routine name>

<terminate routine name> ::= <routine name>

<count routine name> ::= <routine name>

<insert routine name> ::= <routine name>

<update routine name> ::= <routine name>

<delete routine name> ::= <routine name>

<commit routine name> ::= <routine name>




                                                Schema definition and manipulation      59
  WG3:YGJ-017 = X3H2-99-085
 9.5 <abstract table definition>

 <rollback routine name> ::= <routine name>



 Syntax Rules
 1) If a <table definition> is contained in a <schema definition> SD and the <table name> contains
    a <local or schema qualifier>, then that <local or schema qualifier> shall be the same as the
    implicit or explicit <schema name> of SD.

 2) Let TN be the <table name>.

 3) The schema identified by the explicit or implicit schema name of the <table name> shall not
    include a table descriptor whose table name is TN.

 4) If the <table definition> appears in a <schema definition>, then let A be the explicit or implicit
    <authorization identifier> of the <schema definition>; otherwise, let A be the <authorization
    identifier> of the current SQL-session.

 5) Let D be the number of <abstract table column description>s.

 6) Let ATCD1 , ATCD2 , ..., ATCDn be the <abstract table column description>s. Let ATCNi and
    ATDTi be the <column name> and <data type>, respectively, specified in ATCDi .

 7) Let ATRT be the following <row type>:
        ROW (ATCN1 ATDT1 , ATCN2 ATDT2 , ..., ATCNn ATDTn )

 8) Let ISDT be the <data type> specified as the <iteration state data type>.

 9) The <initialize routine name> shall be the routine name of a routine with the following <param-
    eters>:
        ( OUT ISDT )

10) The <iterate routine name> shall be the routine name of a procedure with the following <pa-
    rameter list>:
        ( INOUT ISDT, OUT ATRT )

11) The <terminate routine name> shall be the routine name of a procedure with the following
    <parameter list>:
        ( IN ISDT )

12) The <count routine name> shall be the routine name of a procedure with the following <param-
    eter list>:
        ( OUT INTEGER )

13) If an <insert routine name> is specified, then it shall be the routine name of a procedure with
    the following <parameter list>:
        ( IN ATRT )

14) If an <update routine name> is specified, then it shall be the routine name of a procedure with
    the following <parameter list>:
        ( INOUT ISDT, IN ATRT )




 60 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                       WG3:YGJ-017 = X3H2-99-085
                                                                     9.5 <abstract table definition>

15) If a <delete routine name> is specified, then it shall be the routine name of a procedure with the
    following <parameter list>:
        ( INOUT ISDT )

16) If a <commit routine name> is specified, then it shall be the routine name of a procedure with
    an empty <parameter list>.

17) If a <rollback routine name> is specified, then it shall be the routine name of a procedure with
    an empty <parameter list>.

 Access Rules
 1) If an <abstract table definition> is contained in an <SQL-client module>, then the current
    authorization identifier shall be equal to the <authorization identifier> that owns the schema
    identified by the implicit or explicit <schema name> of the <table name>.

 General Rules
 1) An <abstract table definition> defines an abstract table AT.

 2) A table descriptor ATDS is created that describes AT. ATDS includes:

    a) The table name TN.

    b) The degree D or the table.

     c) For i ranging from 1 to D, the column descriptor of the i-th column of AT, which includes:

         i) The column name ATCNi .

        ii) The data type ADTDi .

       iii) An indication that the column is nullable.

        iv) An indication that the column has no default.

        v) An indication that the column has no collate clause.

        vi) An indication that the table has no table constraints.

    d) An indication that the table is an abstract table, together with:

         i) The iteration data type.

        ii) The initialize routine name.

       iii) The iterate routine name.

        iv) The terminate routine name.

        v) The count routine name.

        vi) If an insert routine is specified, then the insert routine name.

       vii) If an update routine is specified, then the update routine name.

      viii) If a delete routine is specified, then the delete routine name.



                                                         Schema definition and manipulation           61
 WG3:YGJ-017 = X3H2-99-085
9.5 <abstract table definition>

      ix) If a commit routine is specified, then the commit routine name.

       x) If a rollback routine is specified, then rollback routine name.

3) A set of privilege decriptors is created that define the privieges INSERT, SELECT, UPDATE,
   DELETE, and REFERENCES on this abstract table and SELECT, INSERT, UPDATE, and
   REFERENCES for every <column definition> in the table definition to the <authorization
   identifier> of the <schema definition> or <module> in which the <abstract table definition>
   appears. These privileges are grantable.

4) The grantor for each of these privilege descriptors is set to the special grantor value _SYSTEM.

5) The row type of the table AT defined by the <abstract table definition> is ATRT.

6) The type of AT is MULTISET(ATRT).

7) The count of the number of rows of AT is derived as follows:

   a) Let C be the count routine of AT. Let CI be an integer value.

   b) Perform the following <routine invocation>:
           C(CI)

    c) If the OUT value of CI is negative, then an exception condition is raised: abstract table
       exception — invalid count value.

   d) The OUT value of CI is the cardinality of AT.

8) The values of the rows of AT are derived by the following steps, which are referred to as the
   access steps for AT:

   a) Allocate an instance ISD of the iteration state data type of AT.

   b) Let INIT be the iteration routine of AT. Perform the following <routine invocation>:
           INIT(ISD)

    c) Allocate an instance ARD of the row type ATRT, whose field values are uninitialized.

   d) Let ITER be the iterate routine of AT. Perform the following <routine invocation> until the
      OUT value of ISD is null:
           ITER(ISD, ARD)

   e) Case:

        i) If the OUT value of ISD is not null, then the OUT value of ARD is the value of a row
           of AT, and the OUT value of ISD can be used to identify that row in invocations of the
           update routine and delete routine of AT.

       ii) If the OUT value of ISD is null, then the out value of ATD has no significance, and there
           are no more rows in AT.

9) Let TERM be the terminate routine of AT. When the rows of AT have been derived, perform the
   following <routine invocation>:
       TERM(ISD)




62 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                   WG3:YGJ-017 = X3H2-99-085
                                                                 9.5 <abstract table definition>

10) Let UP and DEL be the update routine and delete routine, respectively, of AT.

11) Let ISD and ARD and be the OUT values of an invocation of ITER as specified in the access
    steps.
    Case:

    a) If the ITER routine of AT has been invoked since the invocation of ITER that returned
       the values ISD and ARD, then the effect of invocations of UP or DEL specifying ISD is
       undefined.

    b) If the ITER routine of AT has not been invoked since the invocation of ITER that returned
       the values ISD and ARD, then:

         i) When row ARD of AT is deleted by the action of an SQL statement, the following
            <routine invocation> is performed:
                DEL(ISD)

        ii) Let ATRD2 be an instance of ATRT. When the value of row ARD is updated by the
            action of an SQL statement, the following <routine invocation> is performed:
                UP(ISD, ATRD2)

12) Let ATRD be an instance of ATRT. Let INS be the insert routine of AT. When the value ATRD
    is inserted into AT as a new row by the action of an SQL statement, the following <routine
    invocation> is performed:
        AT(ATRD)

13) ATN references table AT.




                                                     Schema definition and manipulation         63
 WG3:YGJ-017 = X3H2-99-085
9.6 <drop user-defined type statement>


9.6 <drop user-defined type statement>

• 1 **Editor’s Note** deleted.
Function
Destroy either a user-defined type or an abstract LOB type.
Format

<drop data type statement> ::=


        ANSI Only---caused by ISO changes not yet considered by ANSI

          !! All alternatives from ANSI X3.135.2
        | !! All alternatives from ANSI X3.135.5

        ISO Only---caused by ANSI changes not yet considered by ISO

          !! All alternatives from ISO/IEC 9075-2
        | !! All alternatives from ISO/IEC 9075-5


        | DROP TYPE ABSTRACT LOB <abstract LOB type name>



Syntax Rules
1)    Replaces SR1) Let DN be the <user-defined type name> or <abstract LOB type name> and let D
     be the data type identified by DN.

Access Rules

     No additional Access Rules.

General Rules

     No additional General Rules.




64 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                                 WG3:YGJ-017 = X3H2-99-085




10 SQL-client modules


10.1 Data type correspondences

Function
Specify the data type correspondences for SQL data types and host language types.
Tables

                               Table 3—Data type correspondences for Ada

ANSI Only—caused by ISO changes not yet considered by ANSI


 SQL Data Type                               Ada Data Type

 All alternatives from ANSI X3.135.1
 All alternatives from ANSI X3.135.5



ISO Only—caused by ANSI changes not yet considered by ISO


 All alternatives from ISO/IEC
 9075-1
 All alternatives from ISO/IEC
 9075-5




 DATALINK                                    SQL_STANDARD.CHAR, with P’LENGTH of LD1
 1 Thelength LD of the character data type corresponding with SQL data type DATALINK is the smallest integer not less
 than the quotient of the division N/B, where N is the implementation-defined length in octets of the DATALINK type
 and B is the implementation-defined number of octets contained in a character of the host language.



                                Table 4—Data type correspondences for C

ANSI Only—caused by ISO changes not yet considered by ANSI


 SQL Data Type                               C Data Type

 All alternatives from ANSI X3.135.1
 All alternatives from ANSI X3.135.5




                                                                                      SQL-client modules            65
 WG3:YGJ-017 = X3H2-99-085
10.1 Data type correspondences

                            Table 4—Data type correspondences for C (Cont.)
 SQL Data Type                               C Data Type
ISO Only—caused by ANSI changes not yet considered by ISO


 All alternatives from ISO/IEC
 9075-1
 All alternatives from ISO/IEC
 9075-5




 DATALINK                                    char, with length LD4
 4 Thelength LD of the character data type corresponding with SQL data type DATALINK is the smallest integer not less
 than the quotient of the division N/B, where N is the implementation-defined length in octets of the DATALINK type
 and B is the implementation-defined number of octets contained in a character of the host language.



                             Table 5—Data type correspondences for COBOL

ANSI Only—caused by ISO changes not yet considered by ANSI


 SQL Data Type                               COBOL Data Type

 All alternatives from ANSI X3.135.1
 All alternatives from ANSI X3.135.5



ISO Only—caused by ANSI changes not yet considered by ISO


 All alternatives from ISO/IEC
 9075-1
 All alternatives from ISO/IEC
 9075-5




 DATALINK                                    alphanumeric, with length LD4
 4 Thelength LD of the character data type corresponding with SQL data type DATALINK is the smallest integer not less
 than the quotient of the division N/B, where N is the implementation-defined length in octets of the DATALINK type
 and B is the implementation-defined number of octets contained in a character of the host language.



                             Table 6—Data type correspondences for Fortran

ANSI Only—caused by ISO changes not yet considered by ANSI




66 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                                WG3:YGJ-017 = X3H2-99-085
                                                                            10.1 Data type correspondences

                        Table 6—Data type correspondences for Fortran (Cont.)

 SQL Data Type                               Fortran Data Type

 All alternatives from ANSI X3.135.1
 All alternatives from ANSI X3.135.5



ISO Only—caused by ANSI changes not yet considered by ISO


 All alternatives from ISO/IEC
 9075-1
 All alternatives from ISO/IEC
 9075-5




 DATALINK                                    CHARACTER with length LD3
 3 Thelength LD of the character data type corresponding with SQL data type DATALINK is the smallest integer not less
 than the quotient of the division N/B, where N is the implementation-defined length in octets of the DATALINK type
 and B is the implementation-defined number of octets contained in a character of the host language.



                            Table 7—Data type correspondences for MUMPS

ANSI Only—caused by ISO changes not yet considered by ANSI


 SQL Data Type                               MUMPS Data Type

 All alternatives from ANSI X3.135.1
 All alternatives from ANSI X3.135.5



ISO Only—caused by ANSI changes not yet considered by ISO


 All alternatives from ISO/IEC
 9075-1
 All alternatives from ISO/IEC
 9075-5




 DATALINK                                    character


                             Table 8—Data type correspondences for Pascal

ANSI Only—caused by ISO changes not yet considered by ANSI



                                                                                      SQL-client modules            67
 WG3:YGJ-017 = X3H2-99-085
10.1 Data type correspondences

                         Table 8—Data type correspondences for Pascal (Cont.)

 SQL Data Type                               Pascal Data Type

 All alternatives from ANSI X3.135.1
 All alternatives from ANSI X3.135.5



ISO Only—caused by ANSI changes not yet considered by ISO


 All alternatives from ISO/IEC
 9075-1
 All alternatives from ISO/IEC
 9075-5




 DATALINK                                    PACKED ARRAY[1..LD2 ] OF CHAR
 2 Thelength LD of the character data type corresponding with SQL data type DATALINK is the smallest integer not less
 than the quotient of the division N/B, where N is the implementation-defined length in octets of the DATALINK type
 and B is the implementation-defined number of octets contained in a character of the host language.



                               Table 9—Data type correspondences for PL/I

ANSI Only—caused by ISO changes not yet considered by ANSI


 SQL Data Type                               PL/I Data Type

 All alternatives from ANSI X3.135.1
 All alternatives from ANSI X3.135.5



ISO Only—caused by ANSI changes not yet considered by ISO


 All alternatives from ISO/IEC
 9075-1
 All alternatives from ISO/IEC
 9075-5




 DATALINK                                    CHARACTER VARYING(LD2 )
 2 Thelength LD of the character data type corresponding with SQL data type DATALINK is the smallest integer not less
 than the quotient of the division N/B, where N is the implementation-defined length in octets of the DATALINK type
 and B is the implementation-defined number of octets contained in a character of the host language.




68 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                     WG3:YGJ-017 = X3H2-99-085




11 Data manipulation


11.1 Effect of deleting some rows from a base table

Function
Specify the effect of deleting some rows from one or more tables.
Syntax Rules

     No additional Syntax Rules.

Access Rules

     No additional Access Rules.

General Rules
1)    Insert after GR6) For each row that is marked for deletion from T, for each column of T whose
     declared type is DATALINK or some distinct type with a source data type of DATALINK and
     whose descriptor includes a <datalink control definition> that specifies FILE LINK CONTROL,
     the external file corresponding to the datalink is unlinked.
     NOTE 17 – The file is unlinked according to the <datalink file control options> of ON UNLINK
     RESTORE or ON UNLINK DELETE, where applicable, as specified in Table 2, ‘‘Valid datalink file
     control options’’.


• 1 Subclause deleted.




                                                                          Data manipulation        69
 WG3:YGJ-017 = X3H2-99-085
11.2 Effect of inserting tables into base tables


11.2 Effect of inserting tables into base tables

Function
Specify the effect of inserting each of one or more given tables into its associated base table.
Syntax Rules

     No additional Syntax Rules.

Access Rules

     No additional Access Rules.

General Rules
1)    Insert this GR For each row inserted into T, for each column of T whose declared type is
     DATALINK or some distinct type with a source data type of DATALINK and whose descrip-
     tor includes a <datalink control definition> that specifies FILE LINK CONTROL and includes a
     <datalink file control option> that is INTEGRITY ALL or INTEGRITY SELECTIVE,
     Case:

     a) If INTEGRITY ALL is specified, then the external file corresponding to the datalink value
        is linked according to the <datalink file control options> READ PERMISSION and WRITE
        PERMISSION.

     b) If INTEGRITY SELECTIVE is specified, then the external file corresponding to the datalink
        value may be linked in an implementation-defined manner according to the <datalink file
        control options> READ PERMISSION and WRITE PERMISSION.




70 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                     WG3:YGJ-017 = X3H2-99-085
                                                     11.3 Effect of replacing rows in base tables


11.3 Effect of replacing rows in base tables

Function
Update a row of a table.
Syntax Rules

     No additional Syntax Rules.

Access Rules

     No additional Access Rules.

General Rules
1)    Insert after GR7) For each replaced row R, for each column of R whose declared type is
     DATALINK or some distinct type with a source data type of DATALINK and whose descriptor
     includes a <datalink control definition> that specifies FILE LINK CONTROL:

     a) The external file corresponding to the existing datalink value is unlinked.
        NOTE 18 – The file is unlinked according to the <datalink file control options> of ON UNLINK
        RESTORE or ON UNLINK DELETE where applicable as specified in Table 2, ‘‘Valid datalink file
        control options’’.

     b) The external file corresponding to the new datalink value is linked according to the
        <datalink file control options> of READ PERMISSION and WRITE PERMISSION.


• 1 Subclause deleted.




                                                                          Data manipulation          71
WG3:YGJ-017 = X3H2-99-085




72 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                      WG3:YGJ-017 = X3H2-99-085




12 Transaction management


12.1 <commit statement>

Function
Terminate the current SQL-transaction with commit.
Format

No additional Format items.



Syntax Rules

     No additional Syntax Rules.

Access Rules

     No additional Access Rules.

General Rules
1)    Insert this GR For each <abstract table> AT for which any access routine has been invoked
     during the current transaction, if the abstract table description of AT specifies a commit routine
     CR, then perform the <routine invocation>:
        CR( )




                                                                    Transaction management         73
 WG3:YGJ-017 = X3H2-99-085
12.2 <rollback statement>


12.2 <rollback statement>

Function
Terminate the current SQL-transaction with rollback, or rollback all actions on SQL-data and/or
schemas since the establishment of a savepoint.
Format

No additional Format items.



Syntax Rules

     No additional Syntax Rules.

Access Rules

     No additional Access Rules.

General Rules
1)    Insert this GR For each <abstract table> AT for which any access routine has been invoked
     during the current transaction, if the abstract table description of AT specifies a rollback routine
     RR, then perform the <routine invocation>:
        RR( )




74 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                               WG3:YGJ-017 = X3H2-99-085




13 Dynamic SQL


13.1 Description of SQL item descriptor areas

Function
Specify the identifiers, data types, and codes used in SQL item descriptor areas.
Syntax Rules
1)      Insert after SR6)n)    TYPE indicates DATALINK.

2)      Insert after SR7)q)    TYPE indicates DATALINK and T is specified by DATALINK.

Access Rules

       No additional Access Rules.

General Rules
1)      Replace GR1) Table 10, ‘‘Codes used for SQL data types in Dynamic SQL’’, specifies the codes
       associated with the SQL data types.




                              Table 10—Codes used for SQL data types in Dynamic SQL
     Data Type                           Code

     All alternatives from Part 5 of     All alternatives from Part 5 of ISO/IEC 9075
     ISO/IEC 9075
     DATALINK                            50




                                                                                        Dynamic SQL   75
 WG3:YGJ-017 = X3H2-99-085
13.2 <describe statement>


13.2 <describe statement>

Function
Describe the input/output variables for an <SQL dynamic statement>.
Format

No additional Format items.



Syntax Rules

     No additional Syntax Rules.

Access Rules

     No additional Access Rules.

General Rules
1)     Insert after GR8)d)ix) If TYPE indicates DATALINK, then LENGTH is set to the length of maxi-
     mum length in characters of the character string; OCTET_LENGTH is set to the maximum pos-
     sible length in octets of the character string. If the subject <language clause> specifies C, then
     the lengths specified in LENGTH and OCTET_LENGTH do not include the implementation-
     defined null character that terminates a C character string.




76 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                 WG3:YGJ-017 = X3H2-99-085




14 Embedded SQL


14.1 <embedded SQL Ada program>

Function
Specify an <embedded SQL Ada program>.
Format


• 1 production deleted.
<Ada derived type specification> ::=
         !! All alternatives from Part 5 of ISO/IEC 9075
       | <Ada DATALINK variable>

<Ada DATALINK variable> ::=
       SQL TYPE IS <dataline type>



Syntax Rules
1)    Insert after SR5)g)   The syntax
         SQL TYPE IS <dataline#type>
     for a given <Ada host identifier> aha shall be replaced by
          TYPE aha IS
           RECORD
             aha_RESERVED1 : SQL_STANDARD.INT;
             aha_LINKTYPE : SQL_STANDARD.CHAR(1..4);
             aha_URL_LENGTH : SQL_STANDARD.INT;
             aha_COMMENT_LENGTH : SQL_STANDARD.INT;
             aha_RESERVED2 : SQL_STANDARD.CHAR(1..8);
             aha_URL_PLUS_COMMENT : SQL_STANDARD.CHAR(1..<datalink length>);
           END RECORD;
     in any <Ada DATALINK variable>.

Access Rules

     No additional Access Rules.

General Rules

     No additional General Rules.




                                                                        Embedded SQL    77
 WG3:YGJ-017 = X3H2-99-085
14.2 <embedded SQL C program>


14.2 <embedded SQL C program>

Function
Specify an <embedded SQL C program>.
Format

<C derived variable> ::=
         !! All alternatives from Part 5 of ISO/IEC 9075
       | <C DATALINK variable>

<C DATALINK variable> ::=
       SQL TYPE IS <datalink type>
         <C host identifier> [ <C initial value> ]
           [ <comma> <C host identifier> [ <C initial value> ] }... ]



Syntax Rules
1)    Insert after SR6)m)   The syntax
        SQL TYPE IS <datalink type>
     for a given <C host identifier> chi shall be replaced by

        struct {
          unsigned long chi_reserved;
          char chi_linktype[4];
          unsigned long chi_url_length;
          unsigned long chi_comment_length;
          char chi_reserved2[8];
          char chi_url_plus_comment[<datalink length>];
          } chi


     in any <C DATALINK variable>.

Access Rules

     No additional Access Rules.

General Rules

     No additional General Rules.




78 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                 WG3:YGJ-017 = X3H2-99-085
                                                       14.3 <embedded SQL COBOL program>


14.3 <embedded SQL COBOL program>

Function
Specify an <embedded SQL Cobol program>.
Format

<COBOL derived type specification> ::=
         !! All alternatives from Part 5 of ISO/IEC 9075
       | <COBOL DATALINK variable>

<COBOL DATALINK variable> ::=
       [ USAGE [ IS ] ]
       SQL TYPE IS <datalink type>



Syntax Rules
1)    Insert after SR6)j)   The syntax
         SQL TYPE IS <datalink type>
     for a given <COBOL host identifier> chi shall be replaced by

         49   chi-RESERVED1 PIC S9(9) USAGE IS BINARY.
         49   chi-LINKTYPE PIC X(4).
         49   chi-URL-LENGTH PIC S9(9) USAGE IS BINARY.
         49   chi-COMMENT-LENGTH PIC S9(9) USAGE IS BINARY.
         49   chi-RESERVED2 PIC x(8).
         49   chi-URL-PLUS-COMMENT PIC X(<datalink length>).
     in any <COBOL DATALINK variable>.

Access Rules

     No additional Access Rules.

General Rules

     No additional General Rules.




                                                                       Embedded SQL    79
 WG3:YGJ-017 = X3H2-99-085
14.4 <embedded SQL Fortran program>


14.4 <embedded SQL Fortran program>

Function
Specify an <embedded SQL Fortran program>.
Format

<Fortran derived type specification> ::=
         !! All alternatives from Part 5 of ISO/IEC 9075
       | <Fortran DATALINK variable>

<Fortran DATALINK variable> ::=
       SQL TYPE IS <datalink type>



Syntax Rules
1)    Insert after SR6)i)   The syntax
         SQL TYPE ISnul1 <datalink type>
     for a given <Fortran host identifier> fhi shall be replaced by
         INTEGER fhi_RESERVED1
         CHARACTER fhi_LINKTYPE [ 4 ]
         INTEGER fhi_URL_LENGTH
         INTEGER fhi_COMMENT_LENGTH
         CHARACTER fhi_RESERVED2 [ 8 ]
         CHARACTER fhi_URL_PLUS_COMMENT [ <asterisk> <datalink length> ]
     in any <Fortran DATALINK variable>.

Access Rules

     No additional Access Rules.

General Rules

     No additional General Rules.




80 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                   WG3:YGJ-017 = X3H2-99-085
                                                         14.5 <embedded SQL MUMPS program>


14.5 <embedded SQL MUMPS program>

Function
Specify an <embedded SQL MUMPS program>.
Format

<MUMPS derived type specification> ::=
         !! All alternatives from Part 5 of ISO/IEC 9075
       | <MUMPS DATALINK variable>

<MUMPS DATALINK variable> ::=
       SQL TYPE IS <datalink type>



Syntax Rules
1)    Insert after SR9)f)   The syntax
         SQL TYPE IS <datalink type>
     for a given <MUMPS host identifier> mhi shall be replaced by

         INT                    mhi_RESERVED1
         VARCHAR                mhi_LINKTYPE 4
         INT                    mhi_URL_LENGTH
         INT                    mhi_COMMENT_LENGTH
         VARCHAR                mhi_RESERVED2 8
         VARCHAR                mhi_URL_PLUS_COMMENT <datalink length>
     in any <MUMPS DATALINK variable>.

Access Rules

     No additional Access Rules.

General Rules

     No additional General Rules.




                                                                          Embedded SQL    81
 WG3:YGJ-017 = X3H2-99-085
14.6 <embedded SQL Pascal program>


14.6 <embedded SQL Pascal program>

Function
Specify an <embedded SQL Pascal program>.
Format

<Pascal derived type specification> ::=
         !! All alternatives from Part 5 of ISO/IEC 9075
       | <Pascal DATALINK variable>

<Pascal DATALINK variable> ::=
       SQL TYPE IS <datalink type>



Syntax Rules
1)    Insert after SR5)j)   The syntax
         SQL TYPE IS <datalink type>
     for a given <Pascal host identifier> phi shall be replaced by
         VAR phi = RECORD
             phi_RESERVED1 : INTEGER;
             phi_LINKTYPE : PACKED ARRAY [1..4] OF CHAR;
             phi_URL_LENGTH : INTEGER;
             phi_COMMENT_LENGTH : INTEGER;
             phi_RESERVED2 : PACKED ARRAY [1..8] OF CHAR;
             phi_URL_PLUS_COMMENT : PACKED ARRAY [1..<datalink length>] OF CHAR;
     in any <Pascal DATALINK variable>.

Access Rules

     No additional Access Rules.

General Rules

     No additional General Rules.




82 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                    WG3:YGJ-017 = X3H2-99-085
                                                             14.7 <embedded SQL PL/I program>


14.7 <embedded SQL PL/I program>

Function
Specify an <embedded SQL PL/I program>.
Format

<PL/I derived type specification> ::=
         !! All alternatives from Part 5 of ISO/IEC 9075
       | <PL/I DATALINK variable>

<PL/I DATALINK variable> ::=
       SQL TYPE IS <datalink type>



Syntax Rules
1)    Insert after SR5)g)   The syntax
         SQL TYPE IS <datalink type>
     for a given <PL/I host identifier> phi shall be replaced by
         DCL 1    phi
             2    phi_RESERVED1 FIXED BINARY(31),
             2    phi_LINKTYPE CHARACTER(4),
             2    phi_URL_LENGTH FIXED BINARY(31),
             2    phi_COMMENT_LENGTH FIXED BINARY(31),
             2    phi_RESERVED2 CHARACTER (8),
             2    phi_URL_PLUS_COMMENT VARYING CHARACTER(<datalink length>);
     in any <PL/I DATALINK variable>.

Access Rules

     No additional Access Rules.

General Rules

     No additional General Rules.




                                                                          Embedded SQL    83
WG3:YGJ-017 = X3H2-99-085




84 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                               WG3:YGJ-017 = X3H2-99-085




15 Call-Level Interface specifications


15.1 <CLI routine>

Format
Describe a generic SQL/CLI routine.
Format

<CLI routine> ::=
         !! All alternatives from Part 3 of ISO/IEC 9075
       | BuildDataLink
       | ExtractDataLinkAttr



Syntax Rules

                         Table 11—Abbreviated SQL/CLI generic names
 Generic Name              Abbreviation

 BuildDataLink             BDL
 ExtractDataLinkAttr       EDL


Access Rules

   No additional Access Rules.

General Rules

   No additional General Rules.


15.2 SQL/CLI common elements

15.3 Implicit DESCRIBE USING clause

Function
Specify the rules for an implicit DESCRIBE USING clause.




                                                     Call-Level Interface specifications   85
 WG3:YGJ-017 = X3H2-99-085
15.3 Implicit DESCRIBE USING clause

General Rules
1)        Insert after GR5)e)v)7) If TYPE indicates DATALINK, then LENGTH is set to the length or
         maximum length in characters of the character string and OCTET_LENGTH is set to the
         maximum possible length in octets of the character string.

2)        Insert after GR8)d)iv)7) If TYPE indicates DATALINK, then LENGTH is set to the length or
         maximum length in characters of the character string and OCTET_LENGTH is set to the
         maximum possible length in octets of the character string.


15.3.1 CLI-specific status codes


               Table 12—SQLSTATE class and subclass values for SQL/CLI-specific conditions
     Category      Condition                          Class    Subcondition                   Subclass

                   All alternatives from Part 3 of
                   ISO/IEC 9075
     X             CLI-specific condition              HY       invalid datalink value         093




15.3.2 Description of CLI item descriptor areas
Function
Specify the identifiers, data types and codes for fields used in CLI item descriptor areas.
Syntax Rules
1)        Insert after SR2)i)   TYPE indicates DATALINK.

2)        Replaces SR4)c)iv) TYPE indicates DEFAULT, CHARACTER, CHARACTER LARGE OBJECT,
         BINARY LARGE OBJECT, CHARACTER LARGE OBJECT LOCATOR, BINARY LARGE
         OBJECT LOCATOR, USER-DEFINED TYPE LOCATOR, DATALINK, REF, INTEGER,
         SMALLINT, REAL, or DOUBLE PRECISION.

3)        Insert after SR9)c)v)   TYPE indicates DATALINK and one of the following is true:

         1) NULL is true.

         2) DEFERRED is true.

4)        Insert after SR10)c)iv) TYPE indicates DEFAULT, CHARACTER, CHARACTER LARGE
         OBJECT, BINARY LARGE OBJECT, CHARACTER LARGE OBJECT LOCATOR, BINARY
         LARGE OBJECT LOCATOR, USER-DEFINED TYPE LOCATOR, DATALINK, INTEGER,
         SMALLINT, REAL, or DOUBLE PRECISION.




86 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                    WG3:YGJ-017 = X3H2-99-085
                                                          15.3 Implicit DESCRIBE USING clause

General Rules

                  Table 13—Codes used for implementation data types in SQL/CLI
 Data Type                                   Code

 !! All alternatives from Part 3 of
 ISO/IEC 9075
 DATALINK                                    50


                    Table 14—Codes used for application data types in SQL/CLI
 Data Type                     Code

 !! All alternatives from
 Part 3 of ISO/IEC 9075
 DATALINK                      50



15.3.3 Other tables associated with CLI


                            Table 15—Codes used to identify SQL/CLI routines
 Generic Name                  Code

 !! All alternatives from
 Part 3 of ISO/IEC 9075
 BuildDataLink                 1029
 ExtractDataLinkAttr           1034


                  Table 16—Codes and data types for implementation information
 Information Type                           Code             Data Type

 !! All alternatives from Part 3 of
 ISO/IEC 9075
 DATALINK LENGTH                            20004            INTEGER


                              Table 17—Codes used for datalink attributes
 Attribute                     Code

 COMMENT                       1
 TYPE                          2
 URL COMPLETE                  3
 URL PATH                      4
 URL PATH ONLY                 5




                                                          Call-Level Interface specifications   87
 WG3:YGJ-017 = X3H2-99-085
15.3 Implicit DESCRIBE USING clause

                              Table 17—Codes used for datalink attributes (Cont.)
  Attribute                        Code
  URL SCHEME                       6
  URL SERVER                       7
  Implementation-defined            <0
  datalink attribute


                                         Table 18—Data types of attributes
  Attribute                        Data type            Values

  !! All alternatives from
  Part 3 of ISO/IEC 9075
                                                  Datalink attributes

  COMMENT                          CHARACTER            Datalink comment value
                                   VARYING(L)1
  TYPE                             CHARACTER            Datalink type value
                                   VARYING(L)1
  URL COMPLETE                     CHARACTER            Datalink complete URL
                                   VARYING(L)1
  URL PATH                         CHARACTER            Datalink URL path
                                   VARYING(L)1
  URL PATH ONLY                    CHARACTER            Datalink URL path only
                                   VARYING(L)1
  URL SCHEME                       CHARACTER            Datalink URL schema
                                   VARYING(L)1
  URL SERVER                       CHARACTER            Datalink URL server
                                   VARYING(L)1
  Implementation-defined            Implementation-      Implementation-defined value
  datalink attribute               defined data
                                   type
  1 Where   L is an implementation-defined integer not less than the implementation-defined length of a datalink value.



• 1 Table deleted.




88 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                      WG3:YGJ-017 = X3H2-99-085




16 SQL/CLI routines


16.1 BuildDataLink

Function
Allocate an SQL-connection and assign a handle to it.
Definition

BuildDataLink (
       StatementHandle           IN    INTEGER,
       LinkType                  IN    CHARACTER(L1),
       LinkTypeLength            IN    INTEGER,
       DataLocation              IN    CHARACTER(L2),
       DataLocationLength        IN    INTEGER,
       Comment                   IN    CHARACTER(L3),
       CommentLength             IN    INTEGER,
       DataLink                  OUT   CHARACTER(L4),
       BufferLength              IN    INTEGER,
       StringLength              OUT   INTEGER )
       RETURNS SMALLINT


where L1, L2, and L3 are determined by the values of LinkTypeLength, DataLocationLength,
and CommentLength and L4 is the value of BufferLength and has a maximum value equal to the
implementation-defined maximum length of a datalink.
General Rules
1) Let SH be the value of StatementHandle.
   NOTE 19 – SH is used only if BuildDataLink issues a completion or exception condition.

2) Let DL be the datalink value whose LinkType is LinkType, whose Scheme, File Server, and File
   Path are DataLocation, and whose Comment is Comment.
   NOTE 20 – LinkType, Scheme, File Server, File Path, and Comment are defined in Subclause 4.3,
   ‘‘Datalinks’’.

3) Let DLL be the length in octets of DL.

4) If DLL is greater than the implementation-defined length for a datalink value, then an exception
   condition is raised: CLI-specific condition — invalid datalink value.

5) Apply the General Rules of Subclause 5.9, "Character string retrieval", in
   ANSI ANSI X3.135.3
   ISO ISO/IEC 9075-3
   with DataLink, DL, BufferLength, and StringLength as TARGET, VALUE, OCTET LENGTH,
   and RETURNED OCTET LENGTH, respectively.




                                                                             SQL/CLI routines   89
 WG3:YGJ-017 = X3H2-99-085
16.2 ExtractDataLinkAttr


16.2 ExtractDataLinkAttr

Function
Extract the value of a datalink attribute.
Definition

ExtractDataLinkAttr (
       StatementHandle            IN    INTEGER,
       Attribute                  IN    SMALLINT,
       DataLink                   IN    CHARACTER(L),
       DataLinkLength             IN    INTEGER,
       Value                      OUT   ANY,
       BufferLength               IN    INTEGER,
       StringLength               OUT   INTEGER )
       RETURNS SMALLINT


where Lis determined by the value of DataLinkLength.
General Rules
1) Let SH be the value of StatementHandle.
   NOTE 21 – SH is used only if ExtractDataLinkAttr issues a completion or exception condition.

2) Let A be the value of Attribute.

3) If A is not one of the code values in Table 17, ‘‘Codes used for datalink attributes’’, then an
   exception condition is raised: CLI-specific condition — invalid attribute identifier.

4) Let DLL be the value of DataLinkLength.

5) Case:

   a) If DLL is not negative, then let L be DLL.

   b) If DLL indicates NULL TERMINATED, then let L be the number of octets of DataLink that
      precede the implementation-defined null character that terminates a C character string.

    c) Otherwise, an exception condition is raised: CLI-specific condition — invalid string length
       or buffer length.

6) Case:

   a) If L is zero, then an exception condition is raised: CLI-specific condition — invalid string
      length or buffer length.

   b) Otherwise:

        i) :et N be the number of whole characters in the first L octets of DataLink and let NO
           be the number of octets occupied by those N characters. If NO 6= L, then an exception
           condition is raised: invalid cursor name.

       ii) Otherwise, let DL be the first L octets of Datalink.

7) Let ML be the implementation-defined maximum length in characters of a datalink value.


90 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                     WG3:YGJ-017 = X3H2-99-085
                                                                       16.2 ExtractDataLinkAttr

 8) If N is greater than ML, then an exception condition is raised: CLi-specific condition — invalid
    datalink value.

 9) If DL is not a valid datalink value, then an exception condition is raised: CLi-specific condition
    — invalid datalink value.

10) Let BL be the value of BufferLength.

11) If A specifies an implementation-defined datalink attribute, then
    Case:

    a) If the data type for the datalink attribute is specified as INTEGER in Table 18, ‘‘Data types
       of attributes’’, then Value is set to the value of the implementation-defined datalink attribute
       and no further General Rules of this Subclause are applied.

    b) Otherwise:

         i) Let AV be the value of the implementation-defined datalink attribute.

        ii) The General Rules of Subclause 5.9, "Character string retrieval", in
            ANSI ANSI X3.135.3
            ISO ISO/IEC 9075-3
            are applied with Value, AV, BL, and StringLength as TARGET, VALUE, OCTET
            LENGTH, and RETURNED OCTET LENGTH, respectively.

12) Case:

    a) If A indicates COMMENT, then AV is set to the value of the datalink comment of DL.

    b) If A indicates TYPE, then AV is set to the value of datalink type of DL.

     c) If A indicates URL COMPLETE, then AV is set to the value of the concatenation of the
        datalink schema, file server name, and file path of DL.

    d) If A indicates URL PATH, then AV is set to the value of the concatenation of the datalink
       operating system path, external file name, and access token of DL.

    e) If A indicates URL PATH ONLY, then AV is set to the value of the concatenation of the
       datalink operating system path and external file name of DL.

     f) If A indicates URL SCHEME, then AV is set to the value of the datalink scheme of DL.

    g) If A indicates URL SERVER, then AV is set to the value of the datalink external file server
       of DL.
       NOTE 22 – The datalink attributes are defined in Subclause 4.3, ‘‘Datalinks’’.

13) The General Rules of Subclause 5.9, "Character string retrieval", in
    ANSI ANSI X3.135.3
    ISO ISO/IEC 9075-3
    are applied with Value, AV, BL, and StringLength as TARGET, VALUE, OCTET LENGTH, and
    RETURNED OCTET LENGTH, respectively.




                                                                            SQL/CLI routines       91
 WG3:YGJ-017 = X3H2-99-085
16.3 GetInfo


16.3 GetInfo

Function
Get information about the implementation.
Definition

No additional Definition items.



General Rules
1)    Insert after GR9)kk) If IT indicates DATALINK LENGTH, then let V be the implementation-
     defined length in octets of a datalink type defined by SS.




92 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                      WG3:YGJ-017 = X3H2-99-085




17 Information Schema


17.1 COLUMNS view

Function
Identify the columns of tables defined in this catalog that are accessible to a given user.
Definition

CREATE VIEW COLUMNS
       AS SELECT DISTINCT
         TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
         C.COLUMN_NAME, ORDINAL_POSITION,
         CASE WHEN EXISTS ( SELECT *
                FROM DEFINITION_SCHEMA.SCHEMATA AS S
                WHERE ( TABLE_CATALOG, TABLE_SCHEMA )
                    = (S.CATALOG_NAME, S.SCHEMA_NAME )
                  AND SCHEMA_OWNER = USER )
              THEN COLUMN_DEFAULT
              ELSE NULL
         END AS COLUMN_DEFAULT,
         IS_NULLABLE,
         COALESCE (D1.DATA_TYPE, D2.DATA_TYPE) AS DATA_TYPE,
         COALESCE (D1.CHARACTER_MAXIMUM_LENGTH, D2.CHARACTER_MAXIMUM_LENGTH)
           AS CHARACTER_MAXIMUM_LENGTH,
         COALESCE (D1.CHARACTER_OCTET_LENGTH, D2.CHARACTER_OCTET_LENGTH)
                      AS CHARACTER_OCTET_LENGTH,
         COALESCE (D1.NUMERIC_PRECISION, D2.NUMERIC_PRECISION)
                      AS NUMERIC_PRECISION,
         COALESCE (D1.NUMERIC_PRECISION_RADIX, D2.NUMERIC_PRECISION_RADIX)
                      AS NUMERIC_PRECISION_RADIX,
         COALESCE (D1.NUMERIC_SCALE, D2.NUMERIC_SCALE) AS NUMERIC_SCALE,
         COALESCE (D1.DATETIME_PRECISION, D2.DATETIME_PRECISION) AS DATETIME_PRECISION,
         COALESCE (D1.INTERVAL_TYPE, D2.INTERVAL_TYPE) AS INTERVAL_TYPE,
         COALESCE (D1.INTERVAL_PRECISION, D2.INTERVAL_PRECISION) AS INTERVAL_PRECISION,
         COALESCE (C1.CHARACTER_SET_CATALOG, C2.CHARACTER_SET_CATALOG)
                      AS CHARACTER_SET_CATALOG,
         COALESCE (C1.CHARACTER_SET_SCHEMA, C2.CHARACTER_SET_SCHEMA)
                      AS CHARACTER_SET_SCHEMA,
         COALESCE (C1.CHARACTER_SET_NAME, C2.CHARACTER_SET_NAME) AS CHARACTER_SET_NAME,
         COALESCE (D1.COLLATION_CATALOG, D2.COLLATION_CATALOG) AS COLLATION_CATALOG,
         COALESCE (D1.COLLATION_SCHEMA, D2.COLLATION_SCHEMA) AS COLLATION_SCHEMA,
         COALESCE (D1.COLLATION_NAME, D2.COLLATION_NAME) AS COLLATION_NAME,
         DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME,
         COALESCE (D1.USER_DEFINED_TYPE_CATALOG, D2.USER_DEFINED_TYPE_CATALOG)
                  AS USER_DEFINED_TYPE_CATALOG,
         COALESCE (D1.USER_DEFINED_TYPE_SCHEMA, D2.USER_DEFINED_TYPE_SCHEMA)
                  AS USER_DEFINED_TYPE_SCHEMA,
         COALESCE (D1.USER_DEFINED_TYPE_NAME, D2.USER_DEFINED_TYPE_NAME)
                  AS USER_DEFINED_TYPE_NAME,
         DL_LINKTYPE, DL_LINK_CONTROL, DL_INTEGRITY, DL_R_PERMISSION,
         DL_W_PERMISSION, DL_RECOVERY, DL_UNLINK
       FROM DEFINITION_SCHEMA.COLUMNS AS C
           LEFT JOIN
             DEFINITION_SCHEMA.DATA_TYPE_DESCRIPTOR AS D1



                                                                         Information Schema   93
 WG3:YGJ-017 = X3H2-99-085
17.1 COLUMNS view

               LEFT JOIN
                 DEFINITION_SCHEMA.COLLATIONS AS C1
                 ON
                 ( ( C1.COLLATION_CATALOG, C1.COLLATION_SCHEMA, C1.COLLATION_NAME )
                 = ( D1.COLLATION_CATALOG, D1.COLLATION_SCHEMA, D1.COLLATION_NAME ) )
          ON
            ( ( C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME, ’COLUMN’,
                 C.COLUMN_NAME, 0 )
            = ( D1.OBJECT_CATALOG, D1.OBJECT_SCHEMA, D1.OBJECT_NAME,
                   D1.OBJECT_TYPE, D1.COLUMN_NAME, D1.ORDINAL_POSITION ) )
          LEFT JOIN
            DEFINITION_SCHEMA.DATA_TYPE_DESCRIPTOR AS D2
               LEFT JOIN
                 DEFINITION_SCHEMA.COLLATIONS AS C2
                 ON
                 ( ( C2.COLLATION_CATALOG, C2.COLLATION_SCHEMA, C2.COLLATION_NAME )
                 = ( D2.COLLATION_CATALOG, D2.COLLATION_SCHEMA, D2.COLLATION_NAME ) )
          ON
             ( ( C.DOMAIN_CATALOG, C.DOMAIN_SCHEMA, C.DOMAIN_NAME, ’DOMAIN’,
                 ’’, 0 )
            = ( D2.OBJECT_CATALOG, D2.OBJECT_SCHEMA, D2.OBJECT_NAME,
                   D2.OBJECT_TYPE, D2.COLUMN_NAME, D2.ORDINAL_POSITION ) )
          WHERE ( C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME )
            IN
               ( SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
                 FROM DEFINITION_SCHEMA.COLUMN_PRIVILEGES
                 WHERE GRANTEE IN ( ’PUBLIC’, CURRENT_USER ) )
          AND C.TABLE_CATALOG
            = ( SELECT CATALOG_NAME FROM INFORMATION_SCHEMA_CATALOG_NAME )




94 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                 WG3:YGJ-017 = X3H2-99-085



18 Definition Schema


18.1 COLUMNS base table

Function
The COLUMNS table has one row for each non-inherited column. It effectively contains a represen-
tation of the column descriptors.
Definition

CREATE TABLE COLUMNS
       (
       TABLE_CATALOG                  INFORMATION_SCHEMA.SQL_IDENTIFIER,
       TABLE_SCHEMA                   INFORMATION_SCHEMA.SQL_IDENTIFIER,
       TABLE_NAME                     INFORMATION_SCHEMA.SQL_IDENTIFIER,
       COLUMN_NAME                    INFORMATION_SCHEMA.SQL_IDENTIFIER,
       ORDINAL_POSITION               INFORMATION_SCHEMA.CARDINAL_NUMBER
         CONSTRAINT COLUMN_POSITION_NOT_NULL NOT NULL,
       DOMAIN_CATALOG                 INFORMATION_SCHEMA.SQL_IDENTIFIER,
       DOMAIN_SCHEMA                  INFORMATION_SCHEMA.SQL_IDENTIFIER,
       DOMAIN_NAME                    INFORMATION_SCHEMA.SQL_IDENTIFIER,
       COLUMN_DEFAULT                 INFORMATION_SCHEMA.CHARACTER_DATA,
       IS_NULLABLE                    INFORMATION_SCHEMA.CHARACTER_DATA
       CONSTRAINT IS_NULLABLE_NOT_NULL NOT NULL
       CONSTRAINT IS_NULLABLE_CHECK
         CHECK ( IS_NULLABLE IN (’YES’, ’NO’ ) ),
       USER_DEFINED_TYPE_CATALOG      INFORMATION_SCHEMA.CHARACTER_DATA,
       USER_DEFINED_TYPE_SCHEMA       INFORMATION_SCHEMA.CHARACTER_DATA,
       USER_DEFINED_TYPE_NAME         INFORMATION_SCHEMA.CHARACTER_DATA,
       DL_LINKTYPE                    INFORMATION_SCHEMA.CHARACTER_DATA,
       DL_LINK_CONTROL                INFORMATION_SCHEMA.CHARACTER_DATA,
       DL_INTEGRITY                   INFORMATION_SCHEMA.CHARACTER_DATA,
       DL_R_PERMISSION                INFORMATION_SCHEMA.CHARACTER_DATA,
       DL_W_PERMISSION                INFORMATION_SCHEMA.CHARACTER_DATA,
       DL_RECOVERY                    INFORMATION_SCHEMA.CHARACTER_DATA,
       DL_UNLINK                      INFORMATION_SCHEMA.CHARACTER_DATA,

       CONSTRAINT COLUMNS_PRIMARY_KEY
         PRIMARY KEY ( TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME ),

       CONSTRAINT COLUMNS_UNIQUE
         UNIQUE ( TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION ),

       CONSTRAINT COLUMNS_FOREIGN_KEY_TABLES
         FOREIGN KEY ( TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME )
           REFERENCES TABLES,

       CONSTRAINT COLUMNS_CHECK_REFERENCES_DOMAIN
         CHECK ( DOMAIN_CATALOG
                 NOT IN ( SELECT CATALOG_NAME FROM SCHEMATA )
            OR
               ( DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME ) IN
               ( SELECT DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME
                 FROM DOMAINS ) ),

       CONSTRAINT COLUMNS_DL_LINKTYPE
         CHECK ( DL_LINKTYPE IN (’URL’) ),



                                                                       Definition Schema      95
 WG3:YGJ-017 = X3H2-99-085
18.1 COLUMNS base table

          CONSTRAINT COLUMNS_DL_LINK_CONTROL
            CHECK ( DL_LINK_CONTROL IN (’YES, ’NO’) ),

          CONSTRAINT COLUMNS_DL_INTEGRITY
            CHECK ( DL_INTEGRITY IN (’ALL’, ’SELECTIVE’, ’NONE’) ),

          CONSTRAINT COLUMNS_DL_R_PERMISSION
            CHECK ( DL_R_PERMISSION IN (’FS’, ’DB’) ),

          CONSTRAINT COLUMNS_DL_W_PERMISSION
            CHECK ( DL_W_PERMISSION IN (’FS’, ’BLOCKED’) ),

          CONSTRAINT COLUMNS_DL_RECOVERY
            CHECK ( DL_RECOVERY IN (’YES’, ’NO’) ),

          CONSTRAINT COLUMNS_DL_UNLINK
            CHECK ( DL_UNLINK IN (’DELETE’, ’RESTORE’, ’NONE’) ),

          CONSTRAINT COLUMN_CHECK_DATA_TYPE
            CHECK ( DOMAIN_CATALOG
                    NOT IN ( SELECT CATALOG_NAME FROM SCHEMATA )
               OR
              ( ( DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME ) IS NOT NULL
                  AND
                ( TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME ) NOT IN
                ( SELECT OBJECT_CATALOG, OBJECT_SCHEMA,
                           OBJECT_NAME, COLUMN_NAME
                    FROM DATA_TYPE_DESCRIPTOR )
               OR
                ( DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME ) IS NULL
                  AND
                ( TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME ) IN
                ( SELECT OBJECT_CATALOG, OBJECT_SCHEMA,
                           OBJECT_NAME, COLUMN_NAME
                    FROM DATA_TYPE_DESCRIPTOR )
              ) )
          )



Description
1)     Insert this Description If the data type of the column being defined is not a datalink data
     type, then the values of DL_LINKTYPE, DL_LINK_CONTROL, DL_INTEGRITY, DL_R_
     PERMISSION, DL_W_PERMISSION, DL_RECOVER, and DL_UNLINK are the null value;
     otherwise, the values of DL_LINKTYPE, DL_LINK_CONTROL, DL_INTEGRITY, DL_R_
     PERMISSION, DL_W_PERMISSION, DL_RECOVER, and DL_UNLINK are the link type,
     link control, integrity control option, read permission option, write permission option, recovery
     option, and unlink option of the datalink data type used by the column being described.

2)    Insert this Description   The values of DL_LINKTYPE have the following meanings:
     URL             The datalink value in the column is a URL value to an external file.

3)    Insert this Description   The values of DL_LINK_CONTROL have the following meanings:
     YES             The datalink value in the column is under file link control.
     NO              The datalink value in the column is not under file link control.




96 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                               WG3:YGJ-017 = X3H2-99-085
                                                                                18.1 COLUMNS base table

4)    Insert this Description   The values of DL_INTEGRITY have the following meanings:
     ALL             The external file corresponding to the datalink value in the column is under the control of
                     the SQL-implementation.
     SELECTIVE The external file corresponding to the datalink value in the column is under the control of
               the SQL-implementation in an implementation-dependent manner.
     NONE            The external file corresponding to the datalink value in the column is not under the control
                     of the SQL-implementation.

5)    Insert this Description   The values of DL_R_PERMISSION have the following meanings:
     FS              The external file corresponding to the datalink value in the column is under the operating
                     system’s file programming permissions for read access.
     DB              The external file corresponding to the datalink value in the column is under the SQL-
                     implementation’s control for read access.

6)    Insert this Description   The values of DL_W_PERMISSION have the following meanings:
     FS              The external file corresponding to the datalink value in the column is under the operating
                     system’s file programming permissions for write access.
     BLOCKED         Write access to the external file corresponding to the datalink value in the column is blocked.

7)    Insert this Description   The values of DL_RECOVERY have the following meanings:
     YES             The coordinated recovery of SQL-implementation data and external files is supported.
     NO              The coordinated recovery of SQL-implementation data and external files is not supported.

8)    Insert this Description   The values of DL_UNLINK have the following meanings:
     DELETE          On unlink, the external file corresponding to the datalink value in the column is deleted.
     RESTORE         On unlink, the file attributes of the external file corresponding to the datalink value in the
                     column are restored to those at the time when the file was linked.
     NONE            The external file corresponding to the datalink value in the column is not under SQL-
                     implementation control.




                                                                                     Definition Schema            97
WG3:YGJ-017 = X3H2-99-085




98 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                  WG3:YGJ-017 = X3H2-99-085




19 Conformance

To be supplied.




                           Conformance   99
WG3:YGJ-017 = X3H2-99-085




100 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                                 WG3:YGJ-017 = X3H2-99-085




                                                       Annex A
                                                     (informative)

                                  Implementation-defined elements


This Annex references those features that are identified in the body of ISO/IEC 9075 as
implementation-defined.
The term implementation-defined is used to identify characteristics that may differ between imple-
mentations, but that shall be defined for each particular implementation.

1)                        Subclause 4.3, ‘‘Datalinks’’: In a host variable, a datalink type is material-
      Insert this list element
     ized as an N-octet value, where N is implemenation-defined.

2)        Augment list element 15)       Subclause 6.1, "<data type>":

     a)      Insert this list element If <datalink length> is omitted, then an implemenation-defined
            <datalink length> is implicit.

     b)                         The maximum value of <datalink length> is implemenation-defined.
              Insert this list element
            <datalink length> shall not be greater than this maximum value.

3)        Augment list element 18)       Subclause 6.17, "<numeric value function>":

     a)       Insert this list element If <file size exact expression> is specified, then the data type of the
            result is exact numeric with implementation-defined precision and scale 0 (zero).

     b)       Insert this list element If <file size expression> is specified, then the data type of the result is
            exact numeric with implementation-defined precision and scale 0 (zero).

4)        Augment list element 19)       Subclause 6.18, "<string value function>":

     a)       Insert this list element

     b) If <datalink comment expression> is specified, then the data type of the result is a variable-
        length character string with an implementation-defined maximal length.

     c)       Insert this list element If <link type expression> is specified, then the data type of the result
            is a variable-length character string with an implementation-defined maximal length.

     d)       Insert this list element If <url complete expression> is specified, then the data type of the
            result is a variable-length character string with an implementation-defined maximal length.


     e)       Insert this list element If <url path expression> is specified, then the data type of the result is
            a variable-length character string with an implementation-defined maximal length.



                                                                     Implementation-defined elements 101
WG3:YGJ-017 = X3H2-99-085


     f)     Insert this list element If <url path only expression> is specified, then the data type of the
          result is a variable-length character string with an implementation-defined maximal length.


     g)     Insert this list element If <url scheme expression> is specified, then the data type of the result
          is a variable-length character string with an implementation-defined maximal length.

     h)     Insert this list element If <url server expression> is specified, then the data type of the result
          is a variable-length character string with an implementation-defined maximal length.

5)       Augment list element 16), in ISO/IEC 9075-5 Subclause 15.8, "<describe statement>": If TYPE
     indicates DATALINK, then LENGTH is set to the length of maximum length in characters
     of the character string; OCTET_LENGTH is set to the maximum possible length in octets
     of the character string; CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, and
     CHARACTER_SET_NAME are set to the <character set name> of the character string’s char-
     acter set; and the <collation name> of the character string’s collation. If the subject <language
     clause> specifies C, then the lengths specified in LENGTH and OCTET_LENGTH do not include
     the implementation-defined null character that terminates a C character string.




102 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                   WG3:YGJ-017 = X3H2-99-085




                                          Annex B
                                        (informative)

                     Implementation-dependent elements


This Annex references those places where
ANSI ANSI X3.135.9
ISO ISO/IEC 9075-9
states explicitly that the actions of a conforming implementation are implementation-dependent.
The term implementation-dependent is used to identify characteristics that may differ between
implementations, but that are not necessarily specified for any particular implementation.

   None.




                                                    Implementation-dependent elements 103
WG3:YGJ-017 = X3H2-99-085




104 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                     WG3:YGJ-017 = X3H2-99-085




                                           Annex C
                                         (informative)

                                  Deprecated features


It is intended that the following features will be removed at a later date from a revised version of
ISO/IEC 9075:

   No additional deprecated items.




                                                                        Deprecated features 105
WG3:YGJ-017 = X3H2-99-085




ANSI Only—caused by ISO changes not yet considered by ANSI




106 (ISO-ANSI working draft) Management of External Data (SQL/MED)
WG3:YGJ-017 = X3H2-99-085




  Deprecated features 107
WG3:YGJ-017 = X3H2-99-085




                                         Annex D
                                       (informative)

                      Incompatibilities with X3.135-1992




ISO Only—caused by ANSI changes not yet considered by ISO




ISO/IEC 9075 introduces some incompatibilities with the earlier version of Database Language
SQL as specified in ISO/IEC 9075:1992. Unless specified in this Annex, features and capabilities of
Database Language SQL are compatible with the earlier version of ISO/IEC 9075.

1) A number of additional <reserved word>s have been added to the language. These <reserved
   word>s are:

   — DATALINK

   — INITIALIZE

   — ITERATE

   — STATE

   — TERMINATE




108 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                         WG3:YGJ-017 = X3H2-99-085




                                   Annex E
                                 (informative)

                             Typical header files



E.1 C Header File SQLCLI.H

    /* API declaration data types                                */
    typedef unsigned char   SQLDATALINK;

    /* datalink attributes                                       */
    #define SQL_ATTR_DL_COMMENT                         1
    #define SQL_ATTR_DL_TYPE                            2
    #define SQL_ATTR_DL_URL_COMPLETE                    3
    #define SQL_ATTR_DL_URL_PATH                        4
    #define SQL_ATTR_DL_URL_PATH_ONLY                   5
    #define SQL_ATTR_DL_URL_SCHEME                      6
    #define SQL_ATTR_DL_URL_SERVER                      7

    /* SQL data type codes                                       */
    #define SQL_DATALINK                               50

    /*      GetFunctions values to identify CLI routines         */
    #define SQL_API_SQLBUILDDATALINK                 1029
    #define SQL_API_SQLEXTRACTDATALINKATTR           1034

    /*      Information requested by GetInfo( )                      */
    #define SQL_MAXIMUM_DATALINK_LENGTH             20004



    /* Function prototypes                                       */

    SQLRETURN   SQLBuildDataLink(SQLHSTMT *StatenmentHandle,
                SQLCHAR *LinkType, SQLINTEGER LinkTypeLength,
                SQLCHAR *DataLocation, SQLINTEGER DataLocationLength,
                SQLCHAR *Comment, SQLINTEGER CommentLength,
                SQLCHAR *DataLink, SQLINTEGER DataLinkLength,
                SQLINTEGER *StringLength);

    SQLRETURN   SQLExtractDataLinkAttr(SQLHSTMT *StatenmentHandle,
                SQLSMALLINT Attribute,
                SQLCHAR *DataLink, SQLINTEGER DataLinkLength,
                SQLPOINTER Value, SQLINTEGER BufferLength,
                SQLINTEGER *StringLength);




                                                             Typical header files 109
WG3:YGJ-017 = X3H2-99-085
E.1 C Header File SQLCLI.H


E.2 COBOL Library Item SQLCLI

      * DATALINK ATTRIBUTE
      01 SQL-ATTR-DL-COMMENT                   PIC   S9(9)   BINARY   VALUE   IS     1.
      01 SQL-ATTR-DL-PATH                      PIC   S9(9)   BINARY   VALUE   IS     2.
      01 SQL-ATTR-DL-URL-COMPLETE              PIC   S9(9)   BINARY   VALUE   IS     3.
      01 SQL-ATTR-DL-URL-PATH                  PIC   S9(9)   BINARY   VALUE   IS     4.
      01 SQL-ATTR-DL-URL-PATH-ONLY             PIC   S9(9)   BINARY   VALUE   IS     5.
      01 SQL-ATTR-DL-URL-SCHEME                PIC   S9(9)   BINARY   VALUE   IS     6.
      01 SQL-ATTR-DL-URL-SERVER                PIC   S9(9)   BINARY   VALUE   IS     7.

      * SQL DATA TYPE CODES
      01 SQL-DATALINK                          PIC S9(4) BINARY VALUE IS            50.

      * SQLRGETFUNCTIONS VALUES TO IDENTIFY CLI ROUTINES
      01 SQL-API-SQLBUILDDATALINK             PIC S9(4) BINARY VALUE IS            1029.
      01 SQL-API-SQLEXTRACTDATALINKATTR       PIC S9(4) BINARY VALUE IS            1034.

      * INFORMATION REQUESTED BY SQLRGETINFO
      01 SQL-MAXIMUM-DATALINK-LENGTH           PIC S9(4) BINARY VALUE IS 20004.




110 (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                   WG3:YGJ-017 = X3H2-99-085




                                         Annex F
                                       (informative)

                              SQL Feature Taxonomy


This Annex describes a taxonomy of features of the SQL language.
 To be supplied.




                                                                   SQL Feature Taxonomy   111
                                                   Index
   Index entries appearing in boldface indicate the page where the word, phrase, or BNF nonterminal was
   defined; index entries appearing in italics indicate a page where the BNF nonterminal was used in a Format;
   and index entries appearing in roman type indicate a page where the word, phrase, or BNF nonterminal
   was used in a heading, Function, Syntax Rule, Access Rule, General Rule, Leveling Rule, Table, or other
   descriptive text.



                                                         BOOLEAN • 53
                      —A—                                <boolean value expression> • 39
<abstract LOB type definition> • 7, 26, 47, 51, 52, 54    BufferLength • 89, 90, 91, 109
<abstract table access specification> • 59                BY • 51
<abstract table column description> • 59, 60
<abstract table count specification> • 59                                        —C—
<abstract table data manipulation specification> • 59     C • 3, 4, 65, 76, 78, 90, 102, 109
<abstract table definition> • 26, 47, 59, 61, 62          cardinality • 62
<abstract table description> • 59                        CARDINAL_NUMBER • 95
abstract table exception • 62                            CASE • 93
<abstract table transaction specification> • 59           <cast operand> • 37
access steps • 62, 63                                    <cast specification> • 37
Ada • 4, 65, 77                                          catalog • 93
<Ada DATALINK variable> • 77                             CATALOG_NAME • 93, 94, 95, 96
<Ada derived type specification> • 77                     <C DATALINK variable> • 78
<Ada host identifier> • 77                                <C derived variable> • 78
ALL • 48, 49, 70                                         CHAR • 51
AND • 93, 94, 96                                         character • 21, 22, 33, 34, 36, 37, 51, 57, 65, 66, 67,
<apparent LOB type> • 29, 51, 52                                68, 76, 86, 90, 101, 102, 103
ARD • 62, 63, 65, 77, 95                                 CHARACTER • 51, 86
ARRAY • 68, 82                                           character large object • 37
AS • 93, 94                                              <character LOB type> • 51
assignable • 43                                          <character pattern> • 57
assignment • 7, 12, 29, 43, 44, 95                       character set • 102
associated with • 75, 87                                 <character set name> • 102
<asterisk> • 80                                          <character set specification> • 51
attribute • 18, 23, 26, 87, 88, 90, 91, 97, 109          <character substring function> • 57
Attribute • 87, 88, 90, 109                              <character translation> • 57
ATTRIBUTES • 9, 65, 66, 67, 68, 75                       <character value expression> • 36
<authorization identifier> • 52, 54, 60, 61, 62           CHARACTER_DATA • 95
                                                         CHARACTER_MAXIMUM_LENGTH • 93
                      —B—                                CHARACTER_OCTET_LENGTH • 93
based on • 3                                             CHARACTER_SET_CATALOG • 93, 102
base table • 25, 69, 70, 71, 95                          CHARACTER_SET_NAME • 93, 102
BINARY • 51, 79, 83, 86, 110                             CHARACTER_SET_SCHEMA • 93, 102
binary large object • 21                                 CHECK • 95, 96
<binary LOB type> • 51                                   CHECK_REFERENCES • 95
BIT_LENGTH • 31, 32                                      <C host identifier> • 78
BLOB • 51                                                <C initial value> • 78
<blob position expression> • 57                          <CLI routine> • 85
BLOCKED • 23, 24, 25, 27, 48, 49, 96, 97                 CLI-specific condition • 86, 89, 90
boolean • 39, 56                                         COALESCE • 93
Boolean • 37




                                                                                                   Index 1
WG3:YGJ-017 = X3H2-99-085


COBOL • 3, 4, 66, 79, 110                                 <datalink comment> • 36
<COBOL DATALINK variable> • 79                            <datalink comment expression> • 23, 33, 34, 101
<COBOL derived type specification> • 79                    <datalink control definition> • 48, 49, 50, 69, 70, 71
<COBOL host identifier> • 79                               <datalink control option> • 48, 49
code value • 90                                           <datalink expression> • 31, 32, 33, 34, 36
collation • 102                                           <datalink file control options> • 48, 49, 50, 69, 70, 71
<collation name> • 102                                    <datalink length> • 29, 77, 78, 79, 80, 81, 82, 83, 101
COLLATIONS • 94                                           <datalink type> • 29, 78, 79, 80, 81, 82, 83
COLLATION_CAT • 93, 94                                    <datalink value constructor> • 36
COLLATION_CATALOG • 93, 94                                <datalink value expression> • 23, 39, 40
COLLATION_NAME • 93, 94                                   <datalink value function> • 36, 40
COLLATION_SCHEM • 93, 94                                  <data location> • 36
COLLATION_SCHEMA • 93, 94                                 data type • 1, 7, 8, 21, 22, 23, 24, 25, 29, 31, 34, 36,
collection • 31, 39                                              40, 43, 44, 48, 49, 50, 54, 60, 61, 62, 64, 65,
<collection value expression> • 31, 39                           66, 67, 68, 69, 70, 71, 75, 86, 87, 88, 91, 96,
column • 8, 10, 16, 23, 24, 48, 50, 59, 60, 61, 62, 69,          101, 102
       70, 71, 93, 95, 96, 97                             <data type> • 29, 37, 48, 49, 50, 51, 59, 60
<column constraint> • 48                                  DATA_TYPE • 93, 94, 96
<column definition> • 48, 62                               DATA_TYPE_DESCRIPTOR • 93, 94, 96
<column name> • 59, 60                                    date • 8, 24, 25, 31, 39, 59, 60, 61, 62, 63, 71, 105
Columns • 23                                              <datetime value expression> • 39
COLUMNS • 93, 95, 96                                      DATETIME_PRECISION • 93
COLUMN_DEFAULT • 93, 95                                   DB • 27, 48, 49
COLUMN_NAME • 93, 94, 95, 96                              declared type • 39, 41, 69, 70, 71
COLUMN_POSITION • 95                                      DEFAULT • 86, 93, 95
COLUMN_PRIVILEGES • 94                                    DEFERRED • 86
<comma> • 36, 59, 78                                      DEFINED • 86, 93, 95
COMMIT • 59                                               DELETE • 7, 23, 24, 25, 48, 49, 50, 59, 62, 69, 71,
<commit routine name> • 59, 61                                   96, 97
<commit statement> • 73                                   <delete routine name> • 59, 61
<comparison predicate> • 41, 56, 57                       dependent • 4, 7, 97, 103
compatible • 108                                          depends on • 24
<comp op> • 41                                            derived table • 25
<concatenation> • 57                                      describe • 1, 8, 14, 21, 22, 25, 54, 61, 76, 96, 102,
condition • 62, 86, 89, 90, 91                                   111
CONSTRAINT • 95, 96                                       DESCRIBE • 85
contain • 3, 7, 9, 22, 31, 34, 36, 52, 54, 57, 60, 61,    <describe statement> • 76
       65, 66, 67, 68, 95                                 Description • 75, 86, 96, 97
contained in • 7, 22, 31, 34, 36, 52, 54, 60, 61, 65,     descriptor • 14, 15, 21, 22, 23, 25, 29, 50, 52, 54, 55,
       66, 67, 68                                                60, 61, 62, 69, 70, 71, 75, 86, 87, 95
containing • 9, 57                                        DESCRIPTOR • 93, 94, 96
contains • 22, 52, 60, 95                                 descriptor area • 75, 86
CONTROL • 23, 24, 27, 48, 49, 50, 69, 70, 71, 93,         distinct • 48, 49, 69, 70, 71
       95, 96                                             DISTINCT • 93
CONVERT • 51                                              distinct type • 48, 49, 70, 71
COUNT • 59                                                DLCOMMENT • 27, 33, 41
<count routine name> • 59, 60                             DLFILESIZE • 27, 31
CREATE • 51, 59, 93, 95                                   DLFILESIZEEXACT • 27, 31
created by • 7                                            DLLINKTYPE • 27, 33, 41
current • 3, 23, 52, 54, 60, 61, 73, 74                   DLURLCOMPLETE • 27, 33
current authorization identifier • 54, 61                  DLURLPATH • 27, 33, 41
CURRENT_USER • 94                                         DLURLPATHONLY • 27, 33, 41
                                                          DLURLSERVER • 27, 33, 41
                       —D—                                DLURSCHEME • 27
Data • 1, 3, 4, 18, 21, 22, 26, 37, 43, 65, 66, 67, 68,   DLVALUE • 27, 36
      69, 75, 85, 87, 88, 89, 90, 108, 109                DOMAINS • 95
DATALINK • 7, 8, 21, 22, 23, 24, 28, 29, 45, 48, 49,      DOMAIN_CATALOG • 93, 94, 95, 96
      50, 65, 66, 67, 68, 69, 70, 71, 75, 76, 77, 78,     DOMAIN_NAME • 93, 94, 95, 96
      79, 80, 81, 82, 83, 86, 87, 92, 102, 108, 109,      DOMAIN_SCHEMA • 93, 94, 95, 96
      110                                                 DOUBLE • 86




2    (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                        WG3:YGJ-017 = X3H2-99-085


DROP • 64
<drop data type statement> • 64                                   —H—
<drop table statement> • 50                handle • 89
                                           Handle • 89, 90, 109
                      —E—                  <hold locator statement> • 57
**Editor’s Note** • 64                     host language • 65, 66, 67, 68
effective • 95
effectively • 95                                                  —I—
elements • 15, 27, 85, 101, 103            identified • 52, 53, 54, 60, 61, 64, 101
embedded • 77, 78, 79, 80, 81, 82, 83      identifier • 18, 24, 52, 54, 60, 61, 62, 75, 77, 78, 79,
<embedded SQL Ada program> • 77                    80, 81, 82, 83, 86, 90
<embedded SQL COBOL program> • 79          identify • 62, 87, 101, 103, 109
<embedded SQL C program> • 78              implementation-defined • 22, 24, 29, 31, 34, 56, 58,
<embedded SQL Fortran program> • 80                65, 66, 67, 68, 70, 76, 88, 89, 90, 91, 92, 101,
<embedded SQL MUMPS program> • 81                  102
<embedded SQL Pascal program> • 82         implementation-dependent • 7, 97, 103
<embedded SQL PL/I program> • 83           Implicit • 85
empty • 61                                 IN • 89, 90, 94, 95, 96
END • 93                                   include • 21, 23, 25, 50, 52, 54, 60, 61, 69, 70, 71,
EQUALS • 51                                        76, 102
<equals operator> • 41, 56                 independent • 4
<escape character> • 57                    Information Schema • 93
exact numeric • 31, 101                    INFORMATION_SCHEMA • 94, 95
exception • 62, 89, 90, 91                 INFORMATION_SCHEMA_CATALOG_NAME • 94
exception condition • 89, 90               inherit • 95
EXISTS • 93                                inherited column • 95
external routine • 7                       INITIALIZE • 28, 59, 108
EXTRACT • 109, 110                         initialize routine • 25, 60, 61
                                           <initialize routine name> • 59, 60
                      —F—                  INOUT • 60, 61
fields • 23, 86                             INSERT • 7, 59, 62
FILE • 27                                  <insert routine name> • 59, 60
<file size exact expression> • 31, 101      instance • 62, 63
<file size expression> • 23, 31, 32, 101    INTEGER • 53, 54, 56, 60, 80, 82, 86, 87, 89, 90, 91,
<fold> • 57                                        109
FOR • 51                                   INTEGRITY • 24, 27, 48, 49, 70, 93, 95, 96, 97
FOREIGN • 95                               interface • 4, 7
form-of-use • 57                           INTERVAL • 93
form-of-use conversion • 57                <interval value expression> • 31, 39
<form-of-use conversion> • 57              INTERVAL_PRECISION • 93
<form-of-use conversion name> • 57         INTERVAL_TYPE • 93
Fortran • 3, 4, 66, 80                     invalid • 62, 86, 89, 90, 91
FORTRAN • 3                                invalid attribute identifier • 90
<Fortran DATALINK variable> • 80           invalid count value • 62
<Fortran derived type specification> • 80   invalid cursor name • 90
<Fortran host identifier> • 80              invalid string length or buffer length • 90
FREE • 51                                  IS • 77, 78, 79, 80, 81, 82, 83, 96
<free locator statement> • 57              IS_NULLABLE • 93, 95
FROM • 7, 93, 94, 95, 96                   <item reference> • 36, 57
FS • 23, 24, 25, 27, 48, 49, 96, 97        ITERATE • 28, 59, 108
FUNCTION • 110                             iterate routine • 25, 60, 61, 62
                                           <iterate routine name> • 59, 60
                      —G—                  iteration state data type • 25, 60, 62
GetFunctions • 109                         <iteration state data type> • 59, 60
GetInfo • 92, 109
global temporary table • 25                                       —J—
GRANTEE • 94                               JOIN • 93, 94




                                                                                     Index 3
WG3:YGJ-017 = X3H2-99-085



                     —K—                                                      —N—
K • 27                                                 Name • 85, 87
KEY • 95                                               NAME • 93, 94, 95, 96, 102
<key word> • 21                                        NATIONAL • 51
                                                       <national character LOB type> • 51
                     —L—                               NCHAR • 51
<language clause> • 76, 102                            NO • 48, 49
LARGE • 51, 86                                         NONE • 96, 97
large object string • 21                               <non-reserved word> • 27
large object strings • 21                              NOT • 95, 96
LEFT • 93, 94                                          <not equals operator> • 41
<left paren> • 19, 29, 31, 33, 36                      null • 24, 31, 34, 36, 55, 61, 62, 76, 90, 96, 102
Length • 89, 90, 91, 109                               NULL • 93, 95, 96
LENGTH • 31, 32, 51, 65, 76, 77, 79, 80, 81, 82, 83,   NULLABLE • 93, 95
       86, 87, 89, 91, 92, 93, 102, 109, 110           NULL TERMINATED • 90
<length> • 29                                          null value • 31, 34, 36, 55, 96
<length expression> • 57                               NUMERIC • 93
LIKE • 51                                              <numeric value expression> • 39
<like predicate> • 57                                  <numeric value function> • 31
LINK • 27                                              NUMERIC_PRECISION • 93
<linktype> • 36, 48                                    NUMERIC_PRECISION_RADIX • 93
<link type expression> • 23, 33, 34, 101               NUMERIC_SCALE • 93
<link type specification> • 48
<LOB concat routine> • 51, 52, 53                                             —O—
<LOB convert routine> • 51, 52, 53                     object • 1, 7, 8, 9, 18, 19, 21, 22, 23, 24, 37
<LOB equals routine> • 51, 53                          OBJECT • 51, 86, 94, 96
<LOB fold routine> • 52                                OBJECT_CATALOG • 94, 96
<LOB free locator routine> • 51, 52, 53                OBJECT_NAME • 94, 96
<LOB hold locator routine> • 51, 52, 53                OBJECT_SCHEMA • 94, 96
<LOB length routine> • 51, 52, 53                      OBJECT_TYPE • 94
<LOB like routine> • 51, 52, 53                        OCTET_LENGTH • 76, 86, 93, 102
<LOB locator routine> • 51, 52, 53                     ON • 48, 49, 50, 69, 71, 94
<LOB overlay routine> • 51, 52, 54                     ONLY • 27, 33, 41, 87, 88, 91, 109, 110
<LOB position routine> • 51, 52, 54                    OR • 95, 96
<LOB release routine> • 51, 52                         ORDINAL_POSITION • 93, 94, 95
<LOB store routine> • 51, 52                           OUT • 89, 90
<LOB substring routine> • 51, 52, 54                   OVERLAY • 51
<LOB surrogate type> • 51, 52
<LOB translate routine> • 51, 52, 54                                          —P—
<LOB trim routine> • 51, 52                            parameter • 29, 44, 52, 53, 54, 55, 56, 57, 58, 60, 61
<LOB value routine> • 51, 53                           Part 1 • 4
<local or schema qualifier> • 52, 60                    Part 2 • 4
local temporary table • 25                             Part 3 • 4, 85, 86, 87, 88
locator • 21, 51, 52, 53, 54, 55, 56, 57               Part 4 • 4
LOCATOR • 51                                           Part 5 • 4, 75, 77, 78, 79, 80, 81, 82, 83
LOCK • 23, 24, 25, 27, 48, 49, 96, 97                  Part 9 • 7
LOW • 51                                               <Part 9 conformance> • 19
LOWER • 51                                             <Part 9 yes> • 19
                                                       part of • 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 22
                     —M—                               Pascal • 3, 4, 67, 82
MAX • 93, 109, 110                                     <Pascal DATALINK variable> • 82
module • 4, 13, 54, 61, 62, 65                         <Pascal derived type specification> • 82
most specific type • 43, 44                             <Pascal host identifier> • 82
MUMPS • 3, 5, 67, 81                                   PERMISSION • 24, 27, 34, 48, 49, 70, 71, 93, 95, 96,
<MUMPS DATALINK variable> • 81                                97
<MUMPS derived type specification> • 81                 permitted • 24
<MUMPS host identifier> • 81                            persistent • 25
                                                       persistent base table • 25
                                                       PL/I • 3, 4, 68, 83




4    (ISO-ANSI working draft) Management of External Data (SQL/MED)
                                                                    WG3:YGJ-017 = X3H2-99-085


<PL/I DATALINK variable> • 83                          shall • 29, 40, 41, 43, 44, 48, 49, 52, 53, 54, 60, 61,
<PL/I derived type specification> • 83                         77, 78, 79, 80, 81, 82, 83, 101
<PL/I host identifier> • 83                             SIMPLE • 45, 52, 53, 54, 60, 61, 62, 63, 73, 74, 77,
POSITION • 51, 93, 94, 95                                     78, 79, 80, 81, 82, 83
precede • 45, 90                                       simply contain • 31, 34, 36
precision • 31, 101                                    simply contained in • 31, 34
PRECISION • 86, 93                                     site • 8, 23
predefined • 21, 29                                     SIZE • 27, 31
<predefined type> • 29                                  SMALLINT • 86, 89, 90, 109
PRIMARY • 95                                           source • 48, 49, 50, 69, 70, 71
privilege • 55, 62                                     source data type • 48, 49, 50, 69
PRIVILEGE • 94                                         specific name • 21, 22, 54, 55
privilege descriptor • 55, 62                          <specific routine designator> • 51, 52
PRIVILEGES • 94                                        specified by • 7, 24, 75
property • 8                                           specify • 8, 29, 48, 63
PUBLIC • 94                                            SQL • 77, 78, 79, 80, 81, 82, 83
                                                       SQLCHAR • 109
                      —R—                              SQL-client • 54, 61, 65
READ • 48, 49, 70, 71                                  SQL-client module • 54, 61, 65
REAL • 86                                              SQL-connection • 89
RECOVERY • 24, 27, 48, 49, 93, 95, 96, 97              SQL-data • 43, 44, 74
REF • 86                                               <SQL dynamic statement> • 76
REFERENCES • 62, 95                                    SQL-environment • 7, 22
<reference value expression> • 39                      SQLHSTMT • 109
referent • 7                                           SQL-implementation • 7, 24, 97
<reserved word> • 27, 108                              SQLINTEGER • 109
RESTORE • 23, 24, 25, 27, 48, 49, 50, 69, 71, 96, 97   SQL parameter • 44, 52, 53, 54
Retrieval assignment • 43                              SQL parameters • 44
RETURNS • 52, 53, 54, 89, 90                           SQLPOINTER • 109
<returns clause> • 52, 53, 54                          SQLRETURN • 109
<right paren> • 19, 29, 31, 33, 36                     SQL routine • 7
ROLLBACK • 59                                          SQL-schema • 26
<rollback routine name> • 59, 60, 61                   SQL-session • 52, 60
<rollback statement> • 74                              SQLSMALLINT • 109
ROUTINE • 110                                          SQLSTATE • 86
<routine invocation> • 62, 63, 73, 74                  SQL-statement • 26
<routine name> • 59, 60                                SQL-transaction • 73, 74
ROUTINES • 110                                         SQL_IDENTIFIER • 95
row • 39, 41, 60, 62, 63, 69, 70, 71, 95               <start position> • 57
row type • 60, 62                                      STATE • 28, 59, 86, 108
<row type> • 60                                        StatementHandle • 89, 90
<row value expression> • 39                            Store assignment • 44
                                                       StringLength • 89, 90, 91, 109
                      —S—                              <string length> • 57
SCALE • 93                                             <string position expression> • 57
schema • 12, 26, 47, 52, 54, 60, 61, 62, 74, 88, 91    strings • 21
SCHEMA • 93, 94, 95, 96, 102                           <string value expression> • 31, 39
<schema definition> • 47, 52, 60, 62                    <string value function> • 33, 34
<schema element> • 47                                  SUBSTRING • 51
<schema name> • 52, 54, 60, 61                         _SYSTEM • 55, 62
SCHEMATA • 93, 95, 96
SCHEMA_NAME • 93                                                              —T—
SCHEMA_OWNER • 93                                      table • 1, 7, 9, 10, 15, 16, 17, 25, 26, 37, 47, 48, 50,
scope • 1, 9                                                  55, 59, 60, 61, 62, 63, 69, 70, 71, 73, 74, 87,
Scope • 1                                                     93, 95, 96
SELECT • 7, 23, 24, 25, 27, 48, 49, 62, 70, 93, 94,    TABLE • 59, 95
     95, 96, 97                                        table constraint • 61
SELECTIVE • 23, 24, 25, 27, 48, 49, 70, 96, 97         <table definition> • 60
<separator> • 27                                       <table name> • 59, 60, 61
SET • 51                                               Tables • 25, 65




                                                                                                  Index 5
WG3:YGJ-017 = X3H2-99-085


TABLES • 95                                              <value expression> • 37, 39
TABLE_CAT • 93, 94, 95, 96                               VALUES • 110
TABLE_CATALOG • 93, 94, 95, 96                           VARCHAR • 53, 54, 81
TABLE_NAME • 93, 94, 95, 96                              variable-length • 34, 101, 102
TABLE_SCHEM • 93, 94, 95, 96                             VARYING • 68, 83, 88
TABLE_SCHEMA • 93, 94, 95, 96                            view • 16, 25, 93
target • 55                                              VIEW • 93
temporary • 25
temporary table • 25                                                           —W—
TERMINATE • 28, 59, 90, 108                              WHEN • 93
terminate routine • 25, 60, 61, 62                       WHERE • 93, 94
<terminate routine name> • 59, 60                        WRITE • 24, 48, 49, 70, 71
THEN • 93
TM • 109                                                                       —Y—
<token> • 27                                             YES • 27
transaction • 59, 73, 74
TRANSLATE • 51
translation • 57
<translation name> • 57
TRIM • 51
type • 1, 4, 7, 8, 10, 11, 12, 13, 18, 21, 22, 23, 24,
       25, 26, 29, 30, 31, 33, 34, 36, 37, 38, 39, 40,
       41, 43, 44, 45, 47, 48, 49, 50, 51, 52, 53, 54,
       55, 56, 57, 58, 59, 60, 61, 62, 64, 65, 66, 67,
       68, 69, 70, 71, 75, 77, 78, 79, 80, 81, 82, 83,
       86, 87, 88, 91, 92, 96, 101, 102, 109
Type • 22, 23, 37, 45, 65, 66, 67, 68, 75, 87, 89, 109
TYPE • 64, 75, 76, 77, 78, 79, 80, 81, 82, 83, 86
type precedence list • 45
types • 1, 4, 10, 18, 21, 23, 38, 41, 56, 58, 65, 75,
       86, 87, 88, 91, 109
TYPE_NAME • 93, 95

                       —U—
undefined • 63
UNIQUE • 95
UNLINK • 24, 27, 48, 49, 50, 69, 71, 93, 95, 96, 97
UPDATE • 7, 59, 62
<update routine name> • 59, 60
<url complete expression> • 23, 33, 34, 101
<url path expression> • 23, 33, 34, 101
<url path only expression> • 23, 33, 34, 102
<url scheme expression> • 23, 33, 34, 102
<url server expression> • 23, 33, 34, 35, 102
USAGE • 55, 79
USER • 86, 93, 94, 95
user-defined • 7, 39, 48, 49, 50, 64
user-defined type • 39, 48, 49, 64
<user-defined type name> • 48, 49, 64
<user-defined type value expression> • 39
USER_DEFINED_TYPE_CATALOG • 93, 95
USER_DEFINED_TYPE_NAME • 93, 95
USER_DEFINED_TYPE_SCHEMA • 93, 95
USING • 85

                       —V—
valid • 3, 17, 24, 34, 37, 49, 50, 62, 69, 71, 75, 86,
       89, 90, 91
Value • 88, 90, 91, 109
VALUE • 51




6    (ISO-ANSI working draft) Management of External Data (SQL/MED)

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:18
posted:9/21/2011
language:English
pages:150