ISO/IEC 9075:1992/Cor 3:1999
(Corrigendum)Information technology — Database languages — SQL — Technical Corrigendum 3
Information technology — Database languages — SQL — Technical Corrigendum 3
Technologies de l'information — Langages de base de données — SQL — Rectificatif technique 3
General Information
Relations
Standards Content (Sample)
INTERNATIONAL STANDARD ISO/IEC 9075:1992
Technical Corrigendum 3
ISO/IEC 9075-3:1995
Technical Corrigendum 1
ISO/IEC 9075-4:1996
Technical Corrigendum 1
TECHNICAL CORRIGENDUM
Published 1999-11-15
INTERNATIONAL ORGANIZATION FOR STANDARDIZATION � МЕЖДУНАРОДНАЯОРГАНИЗАЦИЯПОСТАНДАРТИЗАЦИИ � ORGANISATION INTERNATIONALE DE NORMALISATION
INTERNATIONAL ELECTROTECHNICAL COMMISSION � МЕЖДУНАРОДНАЯ ЭЛЕКТРОТЕХНИЧЕСКАЯ КОМИССИЯ � COMMISSION ÉLECTROTECHNIQUE INTERNATIONALE
Information technology — Database languages — SQL
TECHNICAL CORRIGENDUM
Technologies de l'information — Languages de base de données — SQL
RECTIFICATIF TECHNIQUE
Technical Corrigendum 3 to International Standard ISO/IEC 9075:1992, Technical Corrigendum 1 to International
Standard ISO/IEC 9075-3:1995 and Technical Corrigendum 1 to International Standard ISO/IEC 9075-4:1996 were
prepared by Joint Technical Committee ISO/IEC JTC 1, Information technology, Subcommittee SC 32, Data
management services.
Relation to previous technical corrigenda:
This Corrigendum contains the cumulative set of corrections to ISO/IEC 9075:1992, ISO/IEC 9075-3:1995 and
ISO/IEC 9075-4:1996.
It completely subsumes and replaces all previous corrigenda for ISO/IEC 9075.
Statement of purpose for rationale:
A statement indicating the rationale for each change to ISO/IEC 9075 is included. This is to inform the users of
that standard as to the reason why it was judged necessary to change the original wording. In many cases the
reason is editorial or to clarify the wording; in some cases it is to correct an error or an omission in the original
wording.
Notes on rule numbering:
This Corrigendum introduces some new Syntax, Access, General and Leveling Rules. The new Rules in this
Corrigendum have been numbered as follows:
Rules inserted between, for example, Rules 7) and 8) (in ISO/IEC 9075:1992) are numbered 7.1), 7.2), etc. [or
7) a.1), 7) a.2), etc.]. Those inserted before Rule 1) are numbered 0.1), 0.2), etc.
ICS 35.060 Ref. No. ISO/IEC 9075/Cor:1999(E)
© ISO/IEC 1999 – All rights reserved
Printed in Switzerland
---------------------- Page: 1 ----------------------
ISO/IEC 9075/Cor:1999(E) ©ISO/IEC
Contents Page
ISO/IEC 9075:1992
Database Languages - SQL
.................................................................................... 7
Introduction . 7
2 Normative references . 7
3.3.4.3 Terms denoting rule requirements . 8
4.2 Character strings . 8
4.2.1 Character strings and collating sequences . 8
4.2.3 Rules determining collating sequence usage . 9
4.3.1 Bit string comparison and assignment . 9
4.4 Numbers . 9
4.4.1 Characteristics of numbers . 9
4.5 Datetimes and intervals . 10
4.5.1 Datetimes . 10
4.5.2 Intervals . 11
4.5.3 Operations involving datetimes and intervals . 11
4.6 Type conversion and mixing of data types . 12
4.8 Columns . 12
4.9 Tables . 12
4.10.2 Table constraints . 13
4.18.1 Status parameters . 13
4.21 Cursors . 13
4.22.6 SQL-statements and transaction states . 14
4.24 SQL dynamic statements . 14
4.26 Privileges . 14
4.28 SQL-transactions . 14
4.29 SQL-connections . 14
4.31 Client-server operation . 15
5.2 and . 15
5.3 . 16
5.4 Names and identifiers . 17
6.1 . 17
6.2 and . 18
6.3 . 18
6.4 . 19
6.5 . 20
6.6 . 20
6.7 . 21
6.8 . 21
6.10 . 22
6.11 . 25
6.12 . 25
6.14 . 25
6.15 . 27
7.1 . 28
7.4 . 29
7.5 . 30
7.6 . 31
7.7 . 31
7.8 . 32
7.9 . 33
7.10 . 35
7.11 , , and
2
---------------------- Page: 2 ----------------------
©ISO/IEC ISO/IEC 9075/Cor:1999(E)
8 . 2 < c omp a r isio n p r e d ic ate> . . 36
8 . 3 < b etwe en p r e d ic ate> . . 36
8 . 7 < q uantifie d c omp a r iso n p r e d ic ate> . . 37
8 . 1 1 < o ve r la p s p r e d ic ate> . . 37
9 . 1 Retr ieval assignment . . 37
9 . 2 Sto r e assignment . . 38
9 . 3 Set o p e r atio n r esult d ata typ es . . 39
1 0 . 1 . . 40
10.2 , Table 16 . 41
1 0 . 4 < cha r a cte r set sp e cific atio n> . . 41
1 0 . 5 < c o llate clause> . . 41
10.6 and . 41
11.1 . 42
1 1 . 2 < d r o p schema statement> . . 42
11.4 . 42
1 1 . 5 < d efault clause> . . 42
11.6
11.8 . 45
11.9 . 45
11.11 . 45
11.15 . 46
11.16 . 47
11.17 . 47
1 1 . 1 8 < d r o p ta b le statement> . . 48
11.19 . 48
11.21 . 49
11.25 . 49
11.26 . 50
1 1 . 2 7 < d r o p d omain statement> . . 50
11.28 . 50
1 1 . 2 9 < d r o p cha r a cte r set statement> . . 51
11.30 . 51
1 1 . 3 1 < d r o p c o llatio n statement> . . 51
11.32 . 52
11.34 . 53
1 1 . 3 6 . . 53
1 1 . 3 7 < r evo ke statement> . . 55
1 2 . 3 < p r o c e d ur e> . . 60
1 2 . 4 Calls to a < p r o c e d ur e> . . 61
1 2 . 5 . . 71
1 3 . 1 < d e cla r e cur so r> . . 72
1 3 . 2 < o p en statement> . . 72
1 3 . 3 . . 72
1 3 . 4 < clo se statement> . . 73
1 3 . 5 . . 73
13.6 . 74
1 3 . 7 < d ele te statement: se a r che d> . . 74
1 3 . 8 . . 74
13.9 . 75
1 3 . 1 0 . . 75
1 3 . 1 1 . . 76
1 4 . 1 . . 76
1 4 . 2 . . 76
1 4 . 3 < c ommit statement> . . 76
3
---------------------- Page: 3 ----------------------
ISO/IEC 9075/Cor:1999(E) ©ISO/IEC
15.1 . 76
15.2 . 77
15.3 . 77
16.5 . 77
17.1 Description of SQL item descriptor areas . 77
17.2 . 77
17.3 . 78
17.4 . 78
17.5 . 78
17.6 . 78
17.9 . 82
17.10 . 82
17.11 . 82
17.15 . 83
17.18 . 83
17.19 . 83
17.20 . 84
18.1 . 84
19.1 . 86
19.3 . 86
19.5 . 86
20.1 . 86
21.1 Introduction . 87
21.2.2 INFORMATION_SCHEMA_CATALOG_NAME base table . 87
21.2.3 INFORMATION_SCHEMA_CATALOG_NAME_CARDINALITY assertion . 88
21.2.4 SCHEMATA view . 88
21.2.5 DOMAINS view . 88
21.2.6 DOMAIN_CONSTRAINTS view . 88
21.2.9 COLUMNS view . 89
21.2.17 ASSERTIONS view . 89
21.2.23 CONSTRAINT_TABLE_USAGE view . 89
21.2.24 CONSTRAINT_COLUMN_USAGE view . 90
21.2.27 SQL_IDENTIFIER domain . 91
21.3.5 DATA_TYPE_DESCRIPTOR base table . 91
21.3.6 DOMAINS base table . 94
21.3.8 TABLES base table . 94
21.3.10 COLUMNS base table . 95
21.3.11 VIEW_TABLE_USAGE base table . 96
21.3.12 VIEW_COLUMN_USAGE base table . 96
21.3.13 TABLE_CONSTRAINTS base table . 97
21.3.15 REFERENTIAL_CONSTRAINTS base table . 97
21.3.17 CHECK_TABLE_USAGE base table . 97
21.3.18 CHECK_COLUMN_USAGE base table. 98
21.3.21 COLUMN_PRIVILEGES base table . 98
21.3.22 USAGE_PRIVILEGES base table . 99
21.3.23 CHARACTER_SETS base table . 99
21.3.24 COLLATIONS base table . 99
21.3.25 TRANSLATIONS base table . 99
21.3.26 SQL_LANGUAGES base table . 100
22.1 SQLSTATE . 100
22.2 SQLCODE . 102
22.3 Remote Database Access SQLSTATE Subclasses . 102
23.2 Claims of Conformance . 104
23.3 Extensions and options . 105
A.1 Intermediate SQL Specifications . 105
4
---------------------- Page: 4 ----------------------
©ISO/IEC ISO/IEC 9075/Cor:1999(E)
A.2 Entry SQL Specifications . 107
Annex B: Implementation-defined elements . 110
Annex C Implementation-dependent elements . 111
Annex E Incompatibilities with ISO/IEC 9075:1989 . 112
Annex F Maintenance and interpretation of SQL . 113
ISO/IEC 9075-3:1995
Database Languages - SQL-Part 3:Call-Level Interface
(SQL/CLI)
.................................................................................. 115
Contents . 115
4.1 Introduction . 116
4.3 Diagnostics areas . 116
4.4.7 CLI descriptor areas . 116
5.1 . 116
5.2 invocation . 117
5.3 SQL/CLI common elements . 117
5.3.3 Implicit using clause . 118
5.3.4 Character string retrieval . 122
5.3.7 CLI-specific status codes . 122
5.3.8 Description of CLI item descriptor areas . 122
5.3.9 . 124
5.4 Data type correspondences . 125
6.4 AllocStmt . 125
6.5 BindCol . 126
6.6 BindParam . 126
6.9 ColAttribute . 128
6.13 DescribeCol . 128
6.17 ExecDirect . 128
6.18 Execute . 128
6.24 FreeStmt . 129
6.27 GetData . 129
6.30 GetDiagField . 129
6.31 GetDiagRec . 131
6.34 GetInfo . 132
6.34.1 GetParamData . 133
6.36 Get TypeInfo . 137
6.38 ParamData . 138
6.39 Prepare . 139
6.40 PutData . 139
6.41 RowCount . 139
6.38 ParamData . 140
6.42 SetConnectAttr . 140
6.43 SetCursorName . 140
6.44 SetDescField . 141
6.45 SetDescRec . 143
A.1 C Header File SQLCLI.H . 143
A.2 COBOL Library Item SQLCLI . 148
B.1 Create table, insert, select . 150
B.2 Interactive Query . 152
B.3 Providing long dynamic arguments at Execute() time . 153
Annex C . 154
Annex D Implementation-dependent elements . 154
Index . 154
5
---------------------- Page: 5 ----------------------
ISO/IEC 9075/Cor:1999(E) ©ISO/IEC
ISO/IEC 9075-4:1996
Database Languages - SQL-Part 4:Persistent Stored Modules
(SQL/PSM)
.................................................................................. 155
2 Normative references . 155
3.3 Conventions . 155
6.2 . 155
6.3 . 155
6.6 . 156
7.3 . 156
9.1 . 157
10.3 . 157
10.7 . 158
10.4 . 158
10.8 . 158
10.9 . 158
10.13 . 159
10.16 . 159
10.18 . 159
10.20 . 159
10.21 . 160
11.4 . 162
12.2 . 162
13.2 . 163
13.3 . 163
13.9 . 163
13.14 . 163
17.3 . 164
19.1.5 ROUTINES view . 164
19.2.4 ROUTINES base table. 165
Annex A . 165
Annex B . 167
6
---------------------- Page: 6 ----------------------
©ISO/IEC ISO/IEC 9075/Cor:1999(E)
ISO/IEC 9075:1992
Database Languages - SQL
Introduction
1. Rationale: In the list of significant new features, the wording incorrectly implies that all the examples
listed in item 10) are referential integrity facilities.
On page xiv, in Significant new feature 10), replace "referential integrity" with "integrity".
2 Normative references
1. Rationale: Editorial. The (non-extended) Pascal standard should be identified as ISO/IEC 7185 rather
than ISO 7185. The designation was changed in 1990 when the standard was revised.
Change "ISO 7185:1990" to "ISO/IEC 7185:1990"
2. Rationale: Editorial.
Add the following reference after the reference to "ISO 8601:1988":
— ISO 8649:1988, Information Processing Systems — Open Systems Interconnection — Service
Definition for the Association Control Service Element.
3. Rationale: The newly revised Ada language standard (ISO/IEC-8652:1995, Information technology —
Programming languages — Ada) contains support for decimal-encoded numeric data and variable length
character strings. The revised interface allows newly written applications in the revised Ada language
access to these features of SQL; previously written Ada applications, conformant with the earlier Ada
interface, are conformant with the revised interface.
Replace the reference to ISO/IEC 8652:1987) with:
— ISO/IEC 8652:1995, Information technology — Programming languages — Ada.
4. Rationale: Editorial.
Add the following reference after the reference to ISO/IEC 8824:1990:
— ISO/IEC 9579-1:1993, Information technology — Open Systems Interconnection — Remote
Database Access, Part 1: Generic Model, Service, and Protocol.
Add the following reference after the reference to ISO/IEC 9899:
— ISO/IEC 10026-21, Information technology — Open Systems Interconnection — Distributed
Transaction Processing — Part 2: Service Definition.
7
---------------------- Page: 7 ----------------------
ISO/IEC 9075/Cor:1999(E) ©ISO/IEC
3.3.4.3 Terms denoting rule requirements
1. Rationale: The following unifies the SQLSTATE returned for the different ways of invoking an SQL
statement.
In the first and second paragraphs, replace "syntax error or access rule violation (if this situation occurs during
dynamic execution of an SQL-statement, then the exception that is raised is syntax error or access rule violation
in dynamic SQL statement; if the situation occurs during direct invocation of an SQL-statement, then the
exception that is raised is syntax or access rule violation in direct SQL statement)" with "syntax error or access
rule violation".
4.2 Character strings
1. Rationale: Editorial.
In the second paragraph, replace the last sentence with:
Character sets defined by standards or by implementations reside in the Information Schema (named
INFORMATION_SCHEMA) in each catalog, as do collations and translations defined by standards and
collations, translations, and form-of-use conversions defined by implementations.
4.2.1 Character strings and collating sequences
1. Rationale: The following changes make the definitions of character set and collation descriptors more
precise.
Replace the text on page 17 that occurs after the first paragraph with:
A character set is described by a character set descriptor. A character set descriptor includes:
— the name of the character set or character repertoire,
— if the character set is a character repertoire, then the name of the form-of-use,
— an indication of what characters are in the character set, and
— whether or not the character set uses the DEFAULT collation for its character repertoire, and,
— if the character set does not utilize the DEFAULT collation for its character repertoire, then the
contained in the character set's , if any, the name> contained in the character set's or , if any, and,
whether or not DESC was specified in the reference to the collation
For every character set, there is at least one collation. A collation is described by a collation descriptor. A
collation descriptor includes:
— the name of the collation,
— the name of the character repertoire on which the collation operates,
— whether the collation has the NO PAD or the PAD SPACE attribute, and
— whether or not this collation utilizes the DEFAULT collation for its character repertoire,
8
---------------------- Page: 8 ----------------------
©ISO/IEC ISO/IEC 9075/Cor:1999(E)
— if the collation does not utilize the DEFAULT collation for its character repertoire, then the
contained in the collation's , if any, the
contained in the collation's , if any, and whether or not DESC was specified in the
definition of the collation.
4.2.3 Rules determining collating sequence usage
1. Rationale: Editorial. The second bull
...
Questions, Comments and Discussion
Ask us and Technical Secretary will try to provide an answer. You can facilitate discussion about the standard in here.