ISO/IEC 9075-5:1999
(Main)Information technology — Database languages — SQL — Part 5: Host Language Bindings (SQL/Bindings)
Information technology — Database languages — SQL — Part 5: Host Language Bindings (SQL/Bindings)
Technologies de l'information — Langages de base de données — SQL — Partie 5: Liants de langage d'hôte (SQL/Liants)
General Information
Relations
Standards Content (Sample)
INTERNATIONAL ISO/IEC
STANDARD 9075-5
First edition
1999-12-01
Information technology — Database
languages — SQL —
Part 5:
Host Language Bindings (SQL/Bindings)
Technologies de l’information— Langages de base de données— SQL —
Partie 5: Liants de langage d'hôte (SQL/Liants)
Reference number
ISO/IEC 9075-5:1999(E)
©
ISO/IEC 1999
---------------------- Page: 1 ----------------------
ISO/IEC 9075-5:1999(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 1999
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 734 10 79
E-mail copyright@iso.ch
Web www.iso.ch
Printed in Switzerland
i-2 © ISO/IEC 1999 – All rights reserved
---------------------- Page: 2 ----------------------
ISO/IEC 9075-5:1999(E)
© ISO/IEC 1999 – All rights reserved i-3
---------------------- Page: 3 ----------------------
ISO/IEC 9075-5:1999 (E)
Contents Page
Foreword . vii
Introduction . . . . ix
1 Scope . 1
2 Normative references. 3
3 Definitions, notations, and conventions . 5
3.1 Definitions . 5
3.2 Notation. 5
3.3 Conventions . 5
3.3.1 Use of terms. 5
3.3.1.1 Other terms . . 5
3.3.2 Relationships to other parts of ISO/IEC 9075 . 5
3.3.2.1 Clause, Subclause, and Table relationships . . . 5
3.4 Object identifier for Database Language SQL . 12
4 Concepts. 15
4.1 Catalogs . 15
4.2 SQL-client modules . 15
4.3 SQL-invoked routines . 15
4.4 Locators . 15
4.5 Cursors . 15
4.6 SQL-statements . . . 16
4.6.1 Classes of SQL-statements . . . . 16
4.6.2 SQL-statements classified by function . . . 16
4.6.3 SQL-statements and transaction states . . 18
4.6.4 Embeddable SQL-statements . . 19
4.6.5 Preparable and immediately executable SQL-statements . . . . 20
4.6.6 Directly executable SQL-statements . . . . 22
4.7 Standard programming languages . 23
4.8 Embedded syntax . . 24
4.9 SQL dynamic statements . 24
4.10 Direct invocation of SQL . 27
4.11 Privileges and roles . 27
4.12 SQL-transactions . . 27
ii Host Language Bindings (SQL/Bindings)
---------------------- Page: 4 ----------------------
©ISO/IEC ISO/IEC 9075-5:1999 (E)
4.13 SQL-connections . . . 27
4.14 SQL-sessions . 28
4.15 Client-server operation. 29
5 Lexical elements . 31
5.1 and . 31
5.2 . 33
5.3 Names and identifiers . 34
6 Scalar expressions . 37
6.1 and . . . 37
6.2 . 39
6.3 . . . 40
7 Query expressions . 41
7.1 . . 41
7.2 . 42
8 Additional common elements . 43
8.1 . 43
9 Data assignment rules and routine determination . 45
9.1 Retrieval assignment . 45
9.2 Store assignment . . 46
9.3 Data types of results of aggregations . . . . 47
10 Schema definition and manipulation. 49
10.1 . . 49
10.2 . . 50
10.3 . 51
10.4 . 52
10.5 . 53
11 SQL-client modules . 55
11.1 . 55
11.2 Calls to an . 57
11.3 . . 59
12 Data manipulation . 61
12.1 . 61
12.2 . 62
13 Transaction management . 63
13.1 . 63
Contents iii
---------------------- Page: 5 ----------------------
ISO/IEC 9075-5:1999 (E) ©ISO/IEC
14 Session management . 65
14.1 . 65
14.2 . 66
14.3 . 67
14.4 . 68
14.5 . 69
15 Dynamic SQL. 71
15.1 Description of SQL descriptor areas. 71
15.2 . 80
15.3 . 82
15.4 . . . 83
15.5 . . . . 86
15.6 . 91
15.7 . 100
15.8 . 101
15.9 . 107
15.10 . 111
15.11 . 116
15.12 . 118
15.13 . . . . 119
15.14 . . . 121
15.15 . . . 123
15.16 . . . 124
15.17 . . 125
15.18 . . . 126
15.19 . 127
15.20 . 129
15.21 . 131
15.22 . 132
16 Embedded SQL . 133
16.1 . 133
16.2 . 145
16.3 . 149
16.4 . . 154
16.5 . 161
16.6 . 167
16.7 . . . . 172
16.8 . 176
16.9 . 181
17 Direct invocation of SQL . 187
17.1 . 187
17.2 . 191
iv Host Language Bindings (SQL/Bindings)
---------------------- Page: 6 ----------------------
©ISO/IEC ISO/IEC 9075-5:1999 (E)
18 Diagnostics management. 193
18.1 . . . 193
19 Definition Schema . 197
19.1 SQL_LANGUAGES base table. 197
20 Status codes. 199
20.1 SQLSTATE. 199
21 Conformance . 201
21.1 General conformance requirements . 201
21.2 Claims of conformance . 201
Annex A SQL Conformance Summary . 203
Annex B Implementation-defined elements . 217
Annex C Implementation-dependent elements . 221
Annex D Deprecated features . 223
Annex E Incompatibilities with ISO/IEC 9075:1992 . 225
Annex F SQL feature and package taxonomy . 227
Index . 229
Contents v
---------------------- Page: 7 ----------------------
ISO/IEC 9075-5:1999 (E) ©ISO/IEC
TABLES
Tables Page
1 Clause, Subclause, and Table relationships . 5
2 Data types of s used in the header of SQL descriptor areas . . 75
3 Data types of s used in SQL item descriptor areas . 75
4 Codes used for SQL data types in Dynamic SQL . . . 77
5 Codes associated with datetime data types in Dynamic SQL . . 78
6 Codes used for s in Dynamic SQL . 78
7 Codes used for input/output SQL parameter modes in Dynamic SQL . . . . 79
8 s for use with . 193
9 SQL-statement codes . 194
10 SQLSTATE class and subclass values . . . 199
11 Implied feature relationships . . 201
12 SQL/Bindings feature taxonomy and definition for Core SQL . 228
13 SQL/Bindings feature taxonomy for features outside Core SQL. 228
vi Host Language Bindings (SQL/Bindings)
---------------------- Page: 8 ----------------------
©ISO/IEC ISO/IEC 9075-5:1999 (E)
Foreword
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-5 was prepared by Joint Technical Committee ISO/IEC JTC
1, Information technology, Subcommittee SC 32, Data management and interchange.
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)
Annexes A, B, C, D, E, and F of this part of ISO/IEC 9075 are for information only.
Foreword vii
---------------------- Page: 9 ----------------------
©ISO/IEC ISO/IEC 9075-5:1999 (E)
Introduction
The organization of this part of ISO/IEC 9075 is as follows:
1) Clause 1, ‘‘Scope’’, specifies the scope of this part of ISO/IEC 9075.
2) Clause 2, ‘‘Normative references’’, identifies additional standards that, through reference in this
part of ISO/IEC 9075, constitute provisions of this part of ISO/IEC 9075.
3) Clause 3, ‘‘Definitions, notations, and conventions’’, defines the notations and conventions used
in this part of ISO/IEC 9075.
4) Clause 4, ‘‘Concepts’’, presents concepts used in the definition of Persistent SQL modules.
5) Clause 5, ‘‘Lexical elements’’, defines the lexical elements of the language.
6) Clause 6, ‘‘Scalar expressions’’, defines the elements of the language that produce scalar values.
7) Clause 7, ‘‘Query expressions’’, defines the elements of the language that produce rows and
tables of data.
8) Clause 8, ‘‘Additional common elements’’, defines additional language elements that are used in
various parts of the language.
9) Clause 9, ‘‘Data assignment rules and routine determination’’, specifies the rules for assignments
that retrieve data from or store data into SQL-data, and formation rules for set operations.
10) Clause 10, ‘‘Schema definition and manipulation’’, defines facilities for creating and managing a
schema.
11) Clause 11, ‘‘SQL-client modules’’, defines modules and procedures.
12) Clause 12, ‘‘Data manipulation’’, defines the data manipulation statements.
13) Clause 13, ‘‘Transaction management’’, defines the SQL-transaction management statements.
14) Clause 14, ‘‘Session management’’, defines the SQL-session management statements.
15) Clause 15, ‘‘Dynamic SQL’’, defines the SQL dynamic statements.
16) Clause 16, ‘‘Embedded SQL’’, defines the host language embeddings.
17) Clause 17, ‘‘Direct invocation of SQL’’, defines direct invocation of SQL language.
18) Clause 18, ‘‘Diagnostics management’’, defines the diagnostics management facilities.
19) Clause 19, ‘‘Definition Schema’’, defines base tables on which the viewed tables containing
schema information depend.
20) Clause 20, ‘‘Status codes’’, defines values that identify the status of the execution of SQL-
statements and the mechanisms by which those values are returned.
21) Clause 21, ‘‘Conformance’’, defines the criteria for conformance to this part of ISO/IEC 9075.
Introduction ix
---------------------- Page: 10 ----------------------
ISO/IEC 9075-5:1999 (E) ©ISO/IEC
22) Annex A, ‘‘SQL Conformance Summary’’, is an informative Annex. It summarizes the confor-
mance requirements of the SQL language.
23) Annex B, ‘‘Implementation-defined elements’’, is an informative Annex. It lists those features
for which the body of this part of 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.
24) Annex C, ‘‘Implementation-dependent elements’’, is an informative Annex. It lists those features
for which the body of this part of 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.
25) Annex D, ‘‘Deprecated features’’, is an informative Annex. It lists features that the responsible
Technical Committee intends will not appear in a future revised version of ISO/IEC 9075.
26) Annex E, ‘‘Incompatibilities with ISO/IEC 9075:1992’’, is an informative Annex. It lists the
incompatibilities between this version of ISO/IEC 9075 and ISO/IEC 9075:1992.
27) Annex F, ‘‘SQL feature and package taxonomy’’, is an informative Annex. It identifies features
of the SQL language specified in this part of ISO/IEC 9075 by a numeric identifier and a short
descriptive name. This taxonomy is used to specify conformance to Core SQL and may be used
to develop other profiles involving the SQL language.
In the text of this part of ISO/IEC 9075, Clauses begin a new odd-numbered page, and in Clause 5,
‘‘Lexical elements’’, through Clause 21, ‘‘Conformance’’, Subclauses begin a new page. Any resulting
blank space is not significant.
x Host Language Bindings (SQL/Bindings)
---------------------- Page: 11 ----------------------
INTERNATIONAL STANDARD ©ISO/IEC ISO/IEC 9075-5:1999 (E)
Information technology — Database languages — SQL —
Part 5:
Host Language Bindings (SQL/Bindings)
1 Scope
This part of ISO/IEC 9075 specifies:
— Syntax for embedding SQL-statements in a compilation unit that otherwise conforms to the
standard for a particular programming language (host language).
— How an equivalent compilation unit may be derived that conforms to the particular program-
ming language standard. In that equivalent compilation unit, each embedded SQL-statement
has been replaced by one or more statements in the host language, some of which invoke an
SQL externally-invoked procedure that, when executed, has an effect equivalent to executing
the SQL-statement.
— Syntax for direct invocation of SQL-statements.
— SQL language to support dynamic preparation and execution of SQL-statements.
Scope 1
---------------------- Page: 12 ----------------------
ISO/IEC 9075-5:1999 (E) ©ISO/IEC
2 Host Language Bindings (SQL/Bindings)
---------------------- Page: 13 ----------------------
©ISO/IEC ISO/IEC 9075-5:1999 (E)
2 Normative references
Insert this paragraph The following standards contain provisions that, through reference in this text,
constitute provisions of this part of ISO/IEC 9075. At the time of publication, the editions indicated
were valid. All standards are subject to revision, and parties to agreements based on this part of
ISO/IEC 9075 are encouraged to investigate the possibility of applying the most recent editions
of the standards indicated below. Members of IEC and ISO maintain registers of currently valid
International Standards.
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 8652:1995, Information technology — Programming languages — Ada.
ISO/IEC 9075-1, Information technology — Database languages — SQL — Part 1: Framework
(SQL/Framework).
ISO/IEC 9075-2, Information technology — Database languages — SQL — Part 2: Foundation
(SQL/Foundation).
ISO/IEC 9075-3:1995, Information technology — Database languages — SQL — Part 3: Call-
Level Interface (SQL/CLI).
ISO/IEC 9075-4, Information technology — Database languages — SQL — Part 4: Persistent
Stored Modules (SQL/PSM).
ISO/IEC 9899:1990, Programming languages — C.
ISO/IEC 9899:1990/Amendment 1:1995, Amendment to ISO/IEC 9899:1990 — C Integrity.
ISO/IEC 10206:1991, Information technology — Programming languages—Extended Pascal.
ISO/IEC 11756:1992, Information technology—Programming languages — MUMPS.
Normative references 3
---------------------- Page: 14 ----------------------
ISO/IEC 9075-5:1999 (E) ©ISO/IEC
4 Host Language Bindings (SQL/Bindings)
---------------------- Page: 15 ----------------------
©ISO/IEC ISO/IEC 9075-5:1999 (E)
3 Definitions, notations, and conventions
3.1 Definitions
Insert this paragraph For the purposes of this part of ISO/IEC 9075, the definitions given in ISO/IEC
9075-1 and ISO/IEC 9075-2 apply.
3.2 Notation
Insert this paragraph The notation used in this part of ISO/IEC 9075 is defined in ISO/IEC 9075-1.
3.3 Conventions
Insert this paragraph
The conventions used in this part of ISO/IEC 9075 is defined in ISO/IEC
9075-1.
3.3.1 Use of terms
3.3.1.1 Other terms
Insert this paragraph An SQL-statement S1 is said to be executed as a direct result of executing
an SQL-statement if S1 is the value of an referenced by an
immediate statement> contained in an that has been executed, or
if S1 was the value of the that was associated with an
name> by a and that same is referenced by an
contained in an that has been executed.
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
Corresponding Clause,
Clause, Subclause, or Table in Subclause, or Table from an- Part containing corre-
this part of ISO/IEC 9075 other part spondence
Clause 1, ‘‘Scope’’ Clause 1, "Scope" ISO/IEC 9075-2
Clause 2, ‘‘Normative references’’ Clause 2, "Normative references" ISO/IEC 9075-2
Clause 3, ‘‘Definitions, notations, Clause 3, "Definitions, notations, ISO/IEC 9075-2
and conventions’’ and conventions"
Definitions, notations, and conventions 5
---------------------- Page: 16 ----------------------
ISO/IEC 9075-5:1999 (E) ©ISO/IEC
3.3 Conventions
Table 1—Clause, Subclause, and Table relationships (Cont.)
Corresponding Clause,
Clause, Subclause, or Table in Subclause, or Table from an- Part containing corre-
this part of ISO/IEC 9075 other part spondence
Subclause 3.1, ‘‘Definitions’’ Subclause 3.1, "Definitions" ISO/IEC 9075-1
Subclause 3.2, ‘‘Notation’’ Subclause 6.1, "Notation" ISO/IEC 9075-1
Subclause 3.3, ‘‘Conventions’’ Subclause 6.2, "Conventions" ISO/IEC 9075-1
Subclause 3.3.1, ‘‘Use of terms’’ Subclause 6.2.3, "Use of terms" ISO/IEC 9075-1
Subclause 3.3.1.1, ‘‘Other terms’’ Subclause 6.2.3.7, "Other terms" ISO/IEC 9075-1
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 6.3, "Object identifier for ISO/IEC 9075-1
Database Language SQL’’ Database Language SQL"
Clause 4, ‘‘Concepts’’ Clause 4, "Concepts" ISO/IEC 9075-2
Subclause 4.1, ‘‘Catalogs’’ Subclause 4.2.6.1, "Catalogs" ISO/IEC 9075-1
Subclause 4.2, ‘‘SQL-client modules’’ Subclause 4.21, "SQL-client mod- ISO/IEC 9075-2
ules"
Subclause 4.3, ‘‘SQL-invoked rou- Subclause 4.23, "SQL-invoked ISO/IEC 9075-2
tines’’ routines"
Subclause 4.4, ‘‘Locators’’ Subclause 4.26.4, "Locators" ISO/IEC 9075-2
Subclause 4.5, ‘‘Cursors’’ Subclause 4.29, "Cursors" ISO/IEC 9075-2
Subclause 4.6, ‘‘SQL-statements’’ Subclause 4.30, "SQL-statements" ISO/IEC 9075-2
Subclause 4.6.1, ‘‘Classes of SQL- Subclause 4.30.1, "Classes of SQL- ISO/IEC 9075-2
statements’’ statements"
Subclause 4.6.2, ‘‘SQL-statements Subclause 4.30.2, "SQL-statements ISO/IEC 9075-2
classified by function’’ classified by function"
Subclause 4.6.3, ‘‘SQL-statements Subclause 4.30.3, "SQL-statements ISO/IEC 9075-2
and transaction states’’ and transaction states"
Subclause 4.6.4, ‘‘Embeddable SQL- none none
statements’’
Subclause 4.6.5, ‘‘Preparable and none none
immediately executable SQL-
statements’’
Subclause 4.6.6, ‘‘Directly exe- none none
cutable SQL-statements’’
Subclause 4.7, ‘‘Standard program- Subclause 4.28, "Standard program- ISO/IEC 9075-2
ming languages’’ ming languages"
Subclause 4.8, ‘‘Embedded syntax’’ none none
6 Host Language Bindings (SQL/Bindings)
---------------------- Page: 17 ----------------------
©ISO/IEC ISO/IEC 9075-5:1999 (E)
3.3 Conventions
Table 1—Clause, Subclause, and Table relationships (Cont.)
Corresponding Clause,
Clause, Subclause, or Table in Subclause, or Table from an- Part containing corre-
this part of ISO/IEC 9075 other part spondence
Subclause 4.9, ‘‘SQL dynamic state- none none
ments’’
Subclause 4.10, ‘‘Direct invocation of none none
SQL’’
Subclause 4.11, ‘‘Privileges and Subclause 4.31, "Basic security ISO/IEC 9075-2
roles’’ model"
Subclause 4.12, ‘‘SQL-transactions’’ Subclause 4.32, "SQL-transactions" ISO/IEC 9075-2
Subclause 4.13, ‘‘SQL-connections’’ Subclause 4.33, "SQL-connections" ISO/IEC 9075-2
Subclause 4.14, ‘‘SQL-sessions’’ Subclause 4.34, "SQL-sessions" ISO/IEC 9075-2
Subclause 4.15, ‘‘Client-server Subclause 4.36, "Client-server ISO/IEC 9075-2
operation’’ operation"
Clause 5, ‘‘Lexical elements’’ Clause 5, "Lexical elements" ISO/IEC 9075-2
Subclause 5.1, ‘‘ and and
rator>’’ rator>"
Subclause 5.2, ‘‘’’ Subclause 5.3, "" ISO/IEC 9075-2
Subclause 5.3, ‘‘Names and identi- Subclause 5.4, "Names and identi- ISO/IEC 9075-2
fiers’’ fiers"
Clause 6, ‘‘Scalar expressions’’ Clause 6, "Scalar expressions" ISO/IEC 9075-2
Subclause 6.1, ‘‘
tion> and ’’ tion> and "
Subclause 7.1, ‘‘
Subclause 6.2, ‘‘’’ Subclause 6.6, "" ISO/IEC 9075-2
Subclause 6.3, ‘‘ expression>’’ expression>"
Clause 7, ‘‘Query expressions’’ Clause 7, "Query expressions" ISO/IEC 9075-2
Subclause 7.2, ‘‘ tion>’’ tion>"
Clause 8, ‘‘Additional common Clause 10, "Additional common ISO/IEC 9075-2
elements’’ elements"
Subclause 8.1, ‘‘ tion>’’ tion>"
Clause 9, ‘‘Data assignment rules Clause 9, "Data assignment rules ISO/IEC 9075-2
and routine determination’’ and routine determination"
Subclause 9.1, ‘‘Retrieval assign- Subclause 9.1, "Retrieval assign- ISO/IEC 9075-2
ment’’ ment"
Subclause 9.2, ‘‘Store assignment’’ Subclause 9.2, "Store assignment" ISO/IEC 9075-2
Definitions, notations, and conventions 7
---------------------- Page: 18 ----------------------
ISO/IEC 9075-5:1999 (E) ©ISO/IEC
3.3 Conventions
Table 1—Clause, Subclause, and Table relationships (Cont.)
Corresponding Clause,
Clause, Subclause, or Table in Subclause, or Table from an- Part containing corre-
this part of ISO/IEC 9075 other part spondence
Clause 10, ‘‘Schema definition and Clause 11, "Schema definition and ISO/IEC 9075-2
manipulation’’ manipulation"
Subclause 10.1, ‘‘ definition>’’ definition>"
Subclause 10.2, ‘‘<
...
Questions, Comments and Discussion
Ask us and Technical Secretary will try to provide an answer. You can facilitate discussion about the standard in here.