Get documents about "ISO ANSI
Shared by: xumiaomaio
-
Stats
- views:
- 18
- posted:
- 9/21/2011
- language:
- English
- pages:
- 150
Document Sample
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)
