ISO/IEC 9075-9:2001

Information technology — Database languages — SQL — Part 9: Management of External Data (SQL/MED)

Technologies de l'information — Langages de base de données — SQL — Partie 9: Gestion des données externes (SQL/MED)

General Information

• Status: Withdrawn
• Publication Date: 06-Jun-2001
• Withdrawal Date: 06-Jun-2001
• ICS: 35.060 - Languages used in information technology
• Technical Committee: ISO/IEC JTC 1/SC 32 - Data management and interchange
• Drafting Committee: ISO/IEC JTC 1/SC 32/WG 3 - Database language
• Current Stage: 9599 - Withdrawal of International Standard
• Completion Date: 15-Dec-2003

Relations

• Parent: ISO/IEC 9075-9:2001/Cor 1:2003 - Information technology — Database languages — SQL — Part 9: Management of External Data (SQL/MED) — Technical Corrigendum 1
• Effective Date: 15-Apr-2008
• Corrected By: ISO/IEC 9075-9:2001/Cor 1:2003 - Information technology — Database languages — SQL — Part 9: Management of External Data (SQL/MED) — Technical Corrigendum 1
• Effective Date: 06-Jun-2022

Standards Content (Sample)

INTERNATIONAL ISO/IEC
STANDARD 9075-9
First edition
2001-05-15
Information technology — Database
languages — SQL —
Part 9:
Management of External Data (SQL/MED)
Technologies de l'information — Langages de base de données — SQL —
Partie 9: Gestion des données externes (SQL/MED)
Reference number
ISO/IEC 9075-9:2001(E)
©
ISO/IEC 2001

---------------------- Page: 1 ----------------------
ISO/IEC 9075-9:2001(E)
PDF disclaimer
This PDF file may contain embedded typefaces. In accordance with Adobe's licensing policy, this file may be printed or viewed but shall not
be edited unless the typefaces which are embedded are licensed to and installed on the computer performing the editing. In downloading this
file, parties accept therein the responsibility of not infringing Adobe's licensing policy. The ISO Central Secretariat accepts no liability in this
area.
Adobe is a trademark of Adobe Systems Incorporated.
Details of the software products used to create this PDF file can be found in the General Info relative to the file; the PDF-creation parameters
were optimized for printing. Every care has been taken to ensure that the file is suitable for use by ISO member bodies. In the unlikely event
that a problem relating to it is found, please inform the Central Secretariat at the address given below.
© ISO/IEC 2001
All rights reserved. Unless otherwise specified, no part of this publication may be reproduced or utilized in any form or by any means, electronic
or mechanical, including photocopying and microfilm, without permission in writing from either ISO at the address below or ISO's member body
in the country of the requester.
ISO copyright office
Case postale 56 � CH-1211 Geneva 20
Tel. + 41 22 749 01 11
Fax + 41 22 749 09 47
E-mail [e-mail]
Web www.iso.ch
Printed in Switzerland
ii © ISO/IEC 2001 – All rights reserved

---------------------- Page: 2 ----------------------
ISO/IEC 9075-9:2001 (E)
Contents Page
Foreword . . xiv
Introduction . . . . . . . . xv
1 Scope . 1
2 Normative references. 3
2.1 ISO/IEC JTC 1 standards . . . . 3
2.2 Publicly-available specifications . 4
3 Definitions, notations, and conventions . 5
3.1 Definitions . 5
3.1.1 Definitions provided in Part 9 . 5
3.1.2 Definitions taken from XML . . 6
3.2 Notations . 6
3.3 Conventions . 6
3.3.1 Relationships to other parts of ISO/IEC 9075 . 6
3.3.1.1 Clause, Subclause, and Table relationships . . . 6
3.4 Object identifier for Database Language SQL . 22
4 Concepts. 27
4.1 SQL-environments and their components . 27
4.1.1 SQL-environments . 27
4.1.2 User mapping concepts . 27
4.1.3 Catalogs and schemas . 27
4.1.3.1 Catalogs . 27
4.1.4 Foreign servers and descriptors . 27
4.1.5 Foreign-data wrappers and descriptors . . 28
4.2 Data types . 28
4.3 Foreign servers . . . 28
4.4 Foreign-data wrappers . 29
4.5 User mappings . . . . 30
4.6 Generic options . . . 30
4.7 Capabilities and options information . . . . 31
4.8 Datalinks . 31
4.8.1 Operations involving datalinks . 34
4.8.1.1 Operators that operate on datalinks . 34
© ISO/IEC 2001 – All rights reserved Contents iii

---------------------- Page: 3 ----------------------
ISO/IEC 9075-9:2001 (E)
4.8.1.2 Other operators involving datalinks. 35
4.9 Type conversion and mixing of data types . 35
4.10 Columns, fields, and attributes . 35
4.11 Tables . . . 35
4.11.1 Types of tables . . . . 36
4.12 Functional dependencies . 36
4.12.1 Known functional dependencies in a foreign table . . 36
4.13 SQL-schemas . 36
4.14 SQL-statements . . . 36
4.14.1 SQL-statements classified by function . . . 36
4.15 SQL-sessions . 37
4.16 Privileges . 38
4.17 SQL-transactions . . 38
4.18 Foreign-data wrapper interface . 38
4.18.1 Handles . 39
4.18.2 Foreign server sessions . 40
4.18.3 Foreign-data wrapper interface routines . 40
4.18.3.1 Handle routines . 41
4.18.3.2 Initialization routines . . . 43
4.18.3.3 Access routines . 44
4.18.3.4 Termination routines . . . . 44
4.18.3.5 Decomposition and pass-through modes. 45
4.18.3.6 Sequence of actions during the execution of requests involving foreign tables and
foreign servers . 45
4.18.4 Return codes . 57
4.18.5 Foreign-data wrapper diagnostics areas . . 58
4.18.6 Null pointers . 59
4.18.7 Foreign-data wrapper descriptor areas . . 60
4.19 Introduction to SQL/CLI . 63
5 Lexical elements . 65
5.1 and . 65
5.2 Names and identifiers . 67
6 Scalar expressions . 69
6.1 . 69
6.2 . 72
6.3 . . . 73
6.4 . 74
6.5 . . . . 77
6.6 . 79
6.7 . 81
6.8 . . 82
iv Management of External Data (SQL/MED) © ISO/IEC 2001 – All rights reserved

---------------------- Page: 4 ----------------------
ISO/IEC 9075-9:2001 (E)
7 Query expressions . 83
7.1 . . 83
7.2 . . . . 89
7.3 . 90
7.4 . 91
7.5 . 92
8 Predicates . 93
8.1 . 93
9 URLs. 95
9.1 URL format . 95
10 Data assignment rules and routine determination . 99
10.1 Retrieval assignment . 99
10.2 Store assignment . . 100
10.3 Data types of results of aggregations . . . . 101
10.4 Type precedence list determination . 102
10.5 Determination of identical values . 103
11 Additional common elements . 105
11.1 . 105
11.2 . . 106
11.3 . 107
12 Schema definition and manipulation. 109
12.1 . 109
12.2 . . . . 110
12.3

. . 111
12.4 . 112
12.5 . 113
12.6 . 114
12.7 . 115
12.8 . 116
12.9 . 117
12.10 . 118
12.11 . 119
12.12 . 120
12.13 . 123
12.14 . 125
12.15 . 127
12.16 . 128
12.17 . 130
© ISO/IEC 2001 – All rights reserved Contents v

---------------------- Page: 5 ----------------------
ISO/IEC 9075-9:2001 (E)
13 Catalog manipulation. 133
13.1 . . . . 133
13.2 . 135
13.3 . 136
13.4 . 137
13.5 . 139
13.6 . . 140
13.7 . 141
14 Access control . 143
14.1 . 143
14.2 . . . . 144
14.3 . 146
14.4 . 147
15 SQL-client modules . 149
15.1 . 149
15.2 . 150
15.3 Calls to an . 151
15.4 . . 153
15.5 Data type correspondences . . . . 154
16 Data manipulation . 157
16.1 . . . 157
16.2 Effect of deleting rows from base tables . . 158
16.3 Effect of inserting tables into base tables. 159
16.4 Effect of replacing rows in base tables . . . 160
17 Session management . 163
17.1 . . 163
18 Dynamic SQL. 165
18.1 Description of SQL descriptor areas. 165
18.2 . 166
18.3 . 168
18.4 . 169
18.5 . 171
18.6 . 175
18.7 . 178
18.8 . . . . 179
18.9 . . . 180
18.10 . . . 181
18.11 . . . 182
18.12 . . . 183
vi Management of External Data (SQL/MED) © ISO/IEC 2001 – All rights reserved

---------------------- Page: 6 ----------------------
ISO/IEC 9075-9:2001 (E)
19 Embedded SQL . 185
19.1 . 185
19.2 . . 186
19.3 . 187
19.4 . 188
19.5 . . . . 189
19.6 . 190
19.7 . 191
20 Call-Level Interface specifications . 193
20.1 . . . . 193
20.2 Implicit DESCRIBE USING clause . 193
20.2.1 CLI-specific status codes . 194
20.2.2 Description of CLI item descriptor areas . 194
20.2.3 Other tables associated with CLI. 195
20.3 SQL/CLI data type correspondences. 197
21 SQL/CLI routines . 199
21.1 BuildDataLink . . . . 199
21.2 GetDataLinkAttr . . 200
21.3 GetInfo . . 202
22 SQL/MED common specifications . 203
22.1 Description of foreign-data wrapper item descriptor areas . . . . 203
22.2 Implicit cursor . . . . 207
22.3 Implicit DESCRIBE INPUT USING clause . 209
22.4 Implicit DESCRIBE OUTPUT USING clause . 212
22.5 Implicit EXECUTE USING and OPEN USING clauses. 216
22.6 Implicit FETCH USING clause . 219
22.7 Character string retrieval . . . . 223
22.8 Binary large object string retrieval . 224
22.9 Tables used with SQL/MED . . . 225
23 Foreign-data wrapper interface routines . 235
23.1 . 235
23.2 invocation . 239
23.3 Foreign-data wrapper interface wrapper routines . . 241
23.3.1 AllocWrapperEnv . . 241
23.3.2 Close . . . 243
23.3.3 ConnectServer . . . . 244
23.3.4 FreeExecutionHandle. 246
23.3.5 FreeFSConnection . 248
23.3.6 FreeReplyHandle . . 249
23.3.7 FreeWrapperEnv . . 250
23.3.8 GetNumReplySelectElems . . . . 251
23.3.9 GetNumReplyTableRefs . 252
23.3.10 GetOpts . 253
© ISO/IEC 2001 – All rights reserved Contents vii

---------------------- Page: 7 ----------------------
ISO/IEC 9075-9:2001 (E)
23.3.11 GetReplySelectElem. 255
23.3.12 GetReplyTableRef . 256
23.3.13 GetSPDHandle . . . . 257
23.3.14 GetSRDHandle . . . . 258
23.3.15 GetStatistics . 259
23.3.16 GetWPDHandle . . . 261
23.3.17 GetWRDHandle . . . 262
23.3.18 InitRequest . 263
23.3.19 Iterate . . 267
23.3.20 Open . . . 269
23.3.21 ReOpen . 273
23.3.22 TransmitRequest . . 274
23.4 Foreign-data wrapper interface SQL-server routines . 277
23.4.1 AllocDescriptor . . . . 277
23.4.2 FreeDescriptor . . . . 278
23.4.3 GetAuthorizationId . 279
23.4.4 GetDescriptor. 280
23.4.5 GetNumSelectElems . 282
23.4.6 GetNumServerOpts . 283
23.4.7 GetNumTableColOpts . 284
23.4.8 GetNumTableOpts . 286
23.4.9 GetNumTableRefElems . 287
23.4.10 GetNumUserOpts . 288
23.4.11 GetNumWrapperOpts . 289
23.4.12 GetSelectElem . . . . 290
23.4.13 GetSelectElemType . 291
23.4.14 GetServerName . . . 292
23.4.15 GetServerOpt. 293
23.4.16 GetServerOptByName . 295
23.4.17 GetServerType . . . . 297
23.4.18 GetServerVersion . . 298
23.4.19 GetSQLString . . . . 299
23.4.20 GetTableColOpt . . . 300
23.4.21 GetTableColOptByName . 302
23.4.22 GetTableOpt. 304
23.4.23 GetTableOptByName . 306
23.4.24 GetTableRefElem . . 308
23.4.25 GetTableRefElemType . 309
23.4.26 GetTableRefTableName . 310
23.4.27 GetTableServerName . 310
23.4.28 GetTRDHandle . . . 312
23.4.29 GetUserOpt . 313
23.4.30 GetUserOptByName . 315
23.4.31 GetValExprColName . 317
23.4.32 GetWrapperLibraryName. 318
viii Management of External Data (SQL/MED) © ISO/IEC 2001 – All rights reserved

---------------------- Page: 8 ----------------------
ISO/IEC 9075-9:2001 (E)
23.4.33 GetWrapperName . 319
23.4.34 GetWrapperOpt . . . 320
23.4.35 GetWrapperOptByName . 322
23.4.36 SetDescriptor . 324
23.5 Foreign-data wrapper interface general routines . . . 329
23.5.1 GetDiagnostics . . . . 329
24 Diagnostics management. 333
24.1 . . . 333
25 Information Schema. 335
25.1 ATTRIBUTES view . 335
25.2 COLUMN_OPTIONS view . . . . 337
25.3 COLUMNS view . . 338
25.4 FOREIGN_DATA_WRAPPER_OPTIONS view. 341
25.5 FOREIGN_DATA_WRAPPERS view . . . . 342
25.6 FOREIGN_SERVER_OPTIONS view . . . . 343
25.7 FOREIGN_SERVERS view . . . 344
25.8 FOREIGN_TABLE_OPTIONS view . 345
25.9 FOREIGN_TABLES view. 346
25.10 USER_MAP_OPTIONS view . . 347
25.11 USER_MAPPINGS view . 348
25.12 Short name views . . 349
26 Definition Schema . 353
26.1 COLUMN_OPTIONS base table . 353
26.2 DATA_TYPE_DESCRIPTOR base table . . 354
26.3 FOREIGN_DATA_WRAPPER_OPTIONS base table . 361
26.4 FOREIGN_DATA_WRAPPERS base table . 362
26.5 FOREIGN_SERVER_OPTIONS base table . 363
26.6 FOREIGN_SERVERS base table . 364
26.7 FOREIGN_TABLE_OPTIONS base table. 365
26.8 FOREIGN_TABLES base table . 366
26.9 SQL_SIZING base table. 367
26.10 TABLES base table . 368
26.11 USAGE_PRIVILEGES base table . 369
26.12 USER_MAPPING_OPTIONS base table . 370
26.13 USER_MAPPINGS base table . 371
27 Status codes. 373
27.1 SQLSTATE. 373
28 Conformance . 375
28.1 SQL-server conformance to SQL/MED . . . 375
28.2 Foreign-data-wrapper conformance to SQL/MED . . . 376
28.3 Claims of conformance by SQL-servers . . 376
28.4 Claims of conformance by foreign-data wrappers . . . 377
© ISO/IEC 2001 – All rights reserved Contents ix

---------------------- Page: 9 ----------------------
ISO/IEC 9075-9:2001 (E)
28.5 Extensions and options . 377
Annex A SQL Conformance Summary . 379
Annex B Implementation-defined elements . 385
Annex C Implementation-dependent elements . 393
Annex D Deprecated features . 397
Annex E Incompatibilities with ISO/IEC 9075:1992 . 399
Annex F Typical header files . 401
F.1 C Header File SQLCLI.H. 401
F.2 COBOL Library Item SQLCLI . 402
Annex G SQL feature and package taxonomy. 403
Annex H SQL/MED model. 405
Index . 411
x Management of External Data (SQL/MED) © ISO/IEC 2001 – All rights reserved

---------------------- Page: 10 ----------------------
ISO/IEC 9075-9:2001 (E)
FIGURES
Figure Page
1 SQL/MED interfaces . 405
2 SQL/MED information flow . . . 407
© ISO/IEC 2001 – All rights reserved Contents xi

---------------------- Page: 11 ----------------------
ISO/IEC 9075-9:2001 (E)
TABLES
Tables Page
1 Clause, Subclause, and Table relationships . 6
2 Valid datalink file control options . 34
3 Sequence of actions during foreign server request executions . 46
4 Fields used in foreign-data wrapper diagnostics areas. 59
5 Fields in foreign-data wrapper descriptor areas . . . . 61
6 Data type correspondences for Ada . 154
7 Data type correspondences for C . 154
8 Data type correspondences for COBOL . . 154
9 Data type correspondences for Fortran . . 155
10 Data type correspondences for MUMPS . . 155
11 Data type correspondences for Pascal . . . 155
12 Data type correspondences for PL/I . 155
13 Codes used for SQL data types in Dynamic SQL . . . 165
14 Abbreviated SQL/CLI generic names . . . . 193
15 SQLSTATE class and subclass values for SQL/CLI-specific conditions . . . . 194
16 Codes used for implementation data types in SQL/CLI . 194
17 Codes used for application data types in SQL/CLI . . 195
18 Codes used to identify SQL/CLI routines . 195
19 Codes and data types for implementation information. 195
20 Codes used for datalink attributes . 195
21 Data types of attributes . 196
22 SQL/CLI data type correspondences for Ada . 197
23 SQL/CLI data type correspondences for C . 197
24 SQL/CLI data type correspondences for COBOL . . .
...