ISO/IEC 9579-2:1998
(Main)Information technology — Open Systems Interconnection — Remote Database Access — Part 2: SQL Specialization
Information technology — Open Systems Interconnection — Remote Database Access — Part 2: SQL Specialization
Technologies de l'information — Interconnexion de systèmes ouverts (OSI) — Accès aux bases de données à distance — Partie 2: Spécialisation SQL
General Information
Relations
Standards Content (Sample)
INTERNATIONAL ISOAEC
STANDARD 9579-2
Second edition
1998-02-l 5
Information technology - Open Systems
Interconnection - Remote Database
Access -
Part 2:
SQL Specialization
Technologies de I’informa tion - lnterconnexion de syst&mes owe&
(03) -AC&S aux bases de donnkes 2 distance -
Partie 2: Spkialisation SQL
---------------------- Page: 1 ----------------------
ISO/IEC 9579-2: 1998(E)
Contents
1
. . . . . . . . . .I.
1 . GENERAL
1
1.1 Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .*.
1
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .~.
1.2 Normative references
2
1.3 Compatibility .
2
..................................................................................................
1.3.1 SQL Standards Supported
2
1.3.2 Upward Compatibility .
2
................................................................................................................................
1.4 Definitions
2
.............................................
1.4.1 Terms defined in ISO/IEC 9075 (Database Language SQL)
4
. . . . . .D.
1.4.2 Terms defined in this part of ISO/IEC 9579
4
1.4.2.1 SQL database resource .
4
......................................................................................................
1.4.2.2 RDA SQL statement
5
15 . Abbreviations .
5
..............................................................................................................................
16 . Conventions
6
2 . MODEL .
6
..........................................................................
. The RDA SQL Specialization Service mode1
21
/
3 6
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
A. 1.1 Mapping to the general mode1 of the RDA Service
6
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2.1.2 Mapping to the Concepts of Database Language SQL
7
. . . . . . . . . . . . . . . .*.
3 . SERVICE
7
.....................................................................................
3.1 The RDA SQL Specialization Service
7
................................................................................
3.1.1 RDA Dialogue Management services
7
3.1.1.1 RDA Dialogue Initialization functional unit .
7
.................................................................................................
3.1.1.1.1 R-Initialize Service
7
.................................................................
3.1.1.2 RDA Dialogue Termination functional unit
7
...............................................................................................
3.1.1.2.1 R-Terminate Service
8
............................................................................
3.1.2 RDA Transaction Management services
8
............................................................
3.1.2.1 RDA Transaction Management functional unit
8
...................................................................................
3.1.2.1.1 R-BeginTransaction Service
8
..................................................................................................
3.1.2.1.2 R-Commit Service
8
.................................................................................................
3.1.2.1.3 R-Rollback Service
8
........................................................................................................
3.1.3 RDA Control Services
8
3.1.3.1 Cancel functional unit .
8
3.1.3.1.1 R-Cancel Service .
8
3.1.3.2 Status functional unit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
8
. . . . . . . . . . . . . .*.
3.1.3.2.1 R-Status Service
8
...............................................................................................
3.1.4 Resource Handling services
8
3.1.4.1 Resource Handling functional unit .
8
.......................................................................................................
3.1.4.1.1 R-Open Service
11
3.1.4.1.2 R-Close Service .
11
..............................................................................................
3.1.5 Database Language Services
11
3.1.5.1 Immediate Execution DBL functional unit .
11
...........................................................................................
3.1.5.1.1 R-ExecuteDBL Service
14
..........................................................................
3.1.5.2 Stored Execution DBL functional unit
14
3.1.5.2.1 R-DefineDBL Service .
0 ISO/IEC 1998
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 the publisher.
ISO/IEC Copyright Office l Case postale 56 l CH-1211 Geneve 20 l Switzerland
Printed in Switzerland
ii
---------------------- Page: 2 ----------------------
ISO/IEC 9579-2: 1998(E)
0 ISO/IEC
15
3.1.5.2.2 R-InvokeDBL Service .
17
3.1.5.2.3 R-DropDBLService* . .
17
. .
3.1.6 SQL statements, arguments and results
17
3.1.6.1 sQLDBLStatement* . .
............................. 18
3.1.6.2 sQLDBLArgumentSpecification and sQLDBLResultSpecification
................................................. 23
3.1.6.3 sQLDBLArgumentValues and sQLDBLResultValues
24
.............................................................................................
3.1.6.4 Referencing to parameters
24
3.2 Sequencing rules .
24
.........................................................................................
3.2.1.1 RDA client sequencing rules
25
3.2.2 RDA Server Sequencing Rules .
26
4 . PROTOCOL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
26
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4.1 The RDA SQL Specialization client execution rules
26
4.1.1 RDA SQL Specialization dialogue-state model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .*.
26
4.1.1.1 SQL Transaction Entity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
26
4.1.2 General client execution rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
4.1.2.1 Generation of the SQL transaction entity
26
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .“.
4.1.2.2 Execution of an SQL-statement
26
4.1.3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
27
4.1.4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
4.1.5
27
4.1.6 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
27
4.1.7 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
27
..............................................................
4.2 The RDA SQL Specialization server execution rules
28
.................................................................
4.2.1 RDA SQL Specialization dialogue-state model
28
4.2.1.1 RDA dialogue entity .
28
..........................................................................................
4.2.1.2 Opened data resource entity
28
4.2.1.3 Defined DBL Entity .
29
....................................................................................................
4.2.1.4 RDA operation entity
29
4.2.2 General server execution rules .
29
................................................................................
4.2.3 RDA Dialogue Management services
29
.................................................................
4.2.3.1 RDA Dialogue Initialization functional unit
29
4.2.3.1.1 R-Initialize Service .
29
.................................................................
4.2.3.2 RDA Dialogue Termination functional unit
29
4.2.3.2.1 R-Terminate Service .
29
............................................................................
4.2.4 RDA Transaction Management services
............................................................ 29
4.2.4.1 RDA Transaction Management functional unit
29
..................................................................................
4.2.4.1.1 R-Begin Transaction Service
29
4.2.4.1.2 R-Commit Service .
29
.................................................................................................
4.2.4.1.3 R-Rollback Service
30
4.2.5 RDA Control services .
30
...................................................................................................
4.2.5.1 Cancel functional unit
30
4.2.5.1.1 R-Cancel Service .
30
.....................................................................................................
4.2.5.2 Status functional unit
30
4.2.5.2.1 R-Status Service .
30
...............................................................................................
4.2.6 Resource Handling services
30
4.2.6.1 Resource Handling functional unit .
30
.......................................................................................................
4.2.6.1.1 R-Open Service
31
4.2.6.1.2 R-Close Service .
31
..............................................................................................
4.2.7 Database Language services
31
...................................................................
4.2.7.1 Immediate Execution DBL functional unit
31
...........................................................................................
4.2.7.1.1 R-ExecuteDBL service
36
..........................................................................
4.2.7.2 Stored Execution DBL functional unit
36
............................................................................................
4.2.7.2.1 R-define DBL Service
39
.............................................................................................
4.2.7.2.2 R-InvokeDBL Service
43
...............................................................................................
4.2.7.2.3 R-DropDBL Service
. . .
111
---------------------- Page: 3 ----------------------
0 ISO/IEC
ISO/IEC 9579-2: 1998(E)
......................................................................... 43
4.3 Structure and Encoding of RDA SQL APDUs
43
........................................................................................................
4.3.1 Abstract Syntax Name
43
...........................................................
4.3.2 ASN. I Module for RDA SQL Specialization ASE
56
4.4 Conformance .
56
.............................................................................................................
4.4.1 Static conformance
57
4.4.2 Dynamic Conformance .
58
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
5 . THE RDA SQL SPECIALIZATION APPLICATION-CONTEXTS
58
................................................................................
5.1 The RDA SQL Basic application-context
58
5.1. I RDA SQL Basic application-context object identifier .
................................................... 58
5.1.2 Additional constraints on the RDA SQL ASE datatypes
58
...............................................................................................
5.1.3 Additional sequencing rules
58
...............................................................................
5.1.4 Additional conformance requirements
58
........................................................................................................
5.1.4. I Static conformance
58
5.1.4.2 Dynamic conformance .
58
....................................................................................
5.2 The RDA SQL TP application-context
58
...........................................................
5.2.1 RDA SQL TP application-context object identifier
58
...................................................
5.2.2 Additional constraints on the RDA SQL ASE datatypes
58
5.2.3 Additional sequencing rules .
58
...............................................................................
5.2.4 Additional conformance requirements
58
5.2.4.1 Static conformance .
58
5.2.4.2 Dynamic conformance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
59
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Annex A (informative): Encoding of Multiple Rows
iv
---------------------- Page: 4 ----------------------
ISO/IEC 9579-2: 1998(E)
0 ISO/IEC
Figures
24
Figure I. Usage of Embedded SQL Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Tables
................................................................................. 7
Table 1. R-Initialise SQL Specific Service Parameters
8
....................................................................................
Table 2. R-Cancel SQL Specific Service Parameters
...................................................................................... 8
Table 3. R-Status SQL Specific Service Parameters
9
.......................................................................................
Table 4. R-Open SQL Specific Service Parameters
I1
Table 5. R-Close SQL Specific Service Parameters .
12
.........................................................................
Table 6. R-ExecuteDBL SQL Specific Service Parameters
14
Table 7. R-Define DBL SQL Specific Service Parameters .
15
...........................................................................
Table 8. R-InvokeDBL SQL Specific Service Parameters
17
Table 9. SQLDBLStatement .
19
............................................
Table 10. sQLDBLArgumentSpecification and sQLDBLResultSpecification
23
Table 11. sQLDBLArgumentValues and sQLDBLResultValues .
33
.......................................................
Table 12. R-ExecuteDBL use of SQL argument and result parameters
......................................................... 37
Table 13. R-DefineDBL use of SQL argument and result parameters
40
........................................................
Table 14. R-InvokeDBL use of SQL argument and result parameters
---------------------- Page: 5 ----------------------
ISO/IEC 9579-2: 1998(E) 0 ISO/IEC
Foreword
IS0 (the International Organization for Standardization) and IEC (the International Electrotechnical
Commission) form the specialized system for worldwide standardization. National bodies that are
members of IS0 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.
IS0 and IEC technical committees collaborate in fields of mutual interest. Other international
organizations, governmental and non-governmental, in liaison with IS0 and IEC, also take part in the
work.
In the field of information technology, IS0 and IEC have established a joint technical committee, ISO/IEC
JTC 1. Draft International Standards adopted by the joint technical committee are circulated 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 9579-2 was prepared by Joint Technical Committee ISO/IEC JTC 1,
Information technology, Subcommittee SC 2 I, Open systems interconnection, data management and open
distributedprocessing.
This second edition cancels and replaces the first edition (ISO/IEC 9579-2: 1993), which has been
technically revised. It also incorporates Technical Corrigendum 1: 1995.
ISO/IEC 9579 consists of the following parts, under the general title Information technology - Open
Systems Interconnection - Remote Database Access:
- Part I: Generic Model, Service and Protocol
- Part 2: SQL Specialization
Annex A of this part of ISO/IEC 9579 is for information only.
VI
---------------------- Page: 6 ----------------------
0 ISO/IEC ISO/IEC 9579-2: 1998(E)
Introduction
Remote Database Access (RDA) International Standards are members of a set of International Standards
produced to facilitate the interworking of computer systems. The RDA International Standards are
positioned in the Application Layer of the Reference Model of Open Systems Interconnection (OSI) and
are related to other Open Systems International Standards in the set, as defined in IS0 7498, OSI - Basic
Reference Model.
The goal of Remote Database Access is to allow, with a minimum of technical agreement outside the
interconnection standards, the interconnection of applications and database systems:
l from different manufacturers;
0
under different managements;
0
of different levels of complexity;
0
exploiting different technologies.
An application may itself be a database system and therefore an RDA Specialization standard can be used
to support multi-database system interworking.
This part of ISO/IEC 9579 is to be used together with ISO/IEC 9579-l to provide remote data access to a
database management system conforming to ISO/IEC 9075 (Database Language SQL).
This part of ISO/IEC 9579 is provided for implementators of the protocol and service defined in
ISO/IEC 9579-2: 1993 who wish to upgrade their implementation to support those features of
ISO/IEC 9075: 1992 which are not supported by ISO/IEC 9579-2: 1993. A third edition of this part of
ISOIIEC 9579 is in preparation which will provide enhanced support more appropriate for new
implementations wishing to support ISO/IEC 9075: 1992 in conjunction with ISO/IEC 9075-3: 1995
(SQLKLI).
vii
---------------------- Page: 7 ----------------------
This page intentionally left blank
---------------------- Page: 8 ----------------------
INTERNATIONAL STANDARD 0 ISO/IEC ISO/IEC 9579-2: 1998(E)
Information technology - Open Systems Interconnection -
Remote Database Access -
Part 2:
SQL Specialization
1 GENERAL
11 . Scope
This part of ISO/IEC 9579 specifies the functionality of a database server within a distributed open
systems environment and specifies the communication service and protocol for accessing its capabilities.
The communications capabilities are positioned in the Application Layer of the Reference Model for Open
Systems Interconnection (OSI).
This part of ISO/IEC 9579 complements ISO/IEC 9579-l (RDA Generic) in order that the two parts
together:
a) define the capabilities of an RDA SQL database server supporting dialogues with clients.
b) define a model of dialogues between the RDA SQL database server and remote users.
c) define a model of a dialogue between an RDA client and an RDA server.
d) define an abstract service for the RDA SQL ASE, which models the communications facilities
supporting interaction between the RDA client and RDA server.
e) define the RDA SQL ASE protocol to support the RDA SQL Service.
f) define the characteristics of application-contexts which include the RDA SQL ASE.
g) define application contexts that support remote database access using SQL:
1) RDA Basic application-context
2) RDA TP application-context
This part of ISO/IEC 9579 does not specify individual implementations or products, nor does it constrain
the implementation of entities and interfaces within a computer system.
This part of ISO/IEC 9579 does not define a programmatic interface. The RDA server includes a database
capability as defined in ISO/IEC 9075 (Database Language SQL).
Notes:
1 The RDA client may contain an SQL application program but there is no requirement that the RDA client
shall be an application program written to the ISO/IEC 9075 (Database Language SQL) application program
interface.
2 ISO/IEC 9075: 1989, the former International Standard for Database Language SQL, has been replaced by
ISOIIEC 9075: 1992. Both the current and former standards contain conformance rules, and the RDA SQL
Specialization allows an RDA client to specify the desired level of conformance which it expects the RDA
Server to support. Throughout this part of ISO/IEC 9579, “SQL” refers to the language statements permitted
by the appropriate standard at the requested level of conformance.
12 . Normative references
The following standards contain provisions which, through reference in this text, constitute provisions of
this part of ISO/IEC 9579. 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 9579 are encouraged to
investigate the possibility of applying the most recent editions of the standards indicated below. Members
of IEC and IS0 maintain registers of currently valid International Standards.
ISOIIEC 8824: 1990, Information technology - Open Systems Interconnection - Specification of Abstract
Syntax Notation One (ASN.1).
Open Systems Interconnection - Specification of Basic
ISOIIEC 8825: 1990, Information technology -
Encoding Rules for Abstract Syntax Notation One (ASN. I).
---------------------- Page: 9 ----------------------
ISO/IEC 9579-2: 1998(E) 0 ISOAEC
ISO/IEC 9075: 1992, Information technology - Database languages - SQL.
ISOIIEC 9075-3: 1995, Information technology - Database languages - SQL - Part 3: Call-Level
Interface (SQLKLI).
ISOIIEC 9075-4: 1996, Information technology - Database languages - SQL - Part 4: Persistent Stored
Modules.
ISOIIEC 9579-1: 1993, Information technology - Open Systems Interconnection - Remote Database
Access - Part I: Generic Model, Service and Protocol.
Compatibility
13
1’3 . . 1 SQL Standards Supported
This part of ISO/IEC 9579 is fully compatible with the following editions and parts of the SQL
International Standard:
l ISO/IEC 9075: 19WSQL87
l ISO/IEC 9075: 1989 SQL89
l ISO/IEC 9075: 1992 SQL92
. ISO/IEC 9075-3: 1995 SQL/CL1
. ISO/IEC 9075-4: 1996 SQL/PSM
1.3.2 Upward Compatibility
This edition of ISO/IEC 9579-2 is upward compatible with ISO/IEC 9579-2: 1993. Where services or
protocols are provided by both this edition and ISO/IEC 9579-2: 1993, these services or protocols behave
in the same manner and provide the same functionality. .
SQL application programs which conform to SQL 87, SQL 89 or SQL 92 entry level should be able to
correctly execute without change in the following configurations:
l ISO/IEC 9579-2: 1993 client and ISO/IEC 9579-2: 1993 server - This is the reference configuration
against which the other cases are compared.
l ISO/IEC 9579-2: 1993 client and ISO/IEC 9579-2: 1997 server - The ISO/IEC 9579 -2: 1997 server
behaves as an ISO/IEC 9579-2: 1993 server.
l ISO/IEC 9579-2: 1997 client and ISO/IEC 9579-2: 1993 server - The ISO/IEC 9579 -2: 1997 client
behaves as an ISO/IEC 9579-2: 1993 client.
l ISO/IEC 9579-2: 1997 client and ISO/IEC 9579-2: 1997 server - This is the configuration provided for
by this standard.
14 . Definitions
For the purposes of this part of ISO/IEC 9579, the definitions given in ISO/IEC 9579-l and the following
definitions apply.
1.4.1 Terms defined in ISO/IEC 9075 (Database Language SQL)
This part of ISO/IEC 9579 makes use of the following terms defined in ISO/IEC 9075: 1989 (Database
Language SQL) or ISO/IEC 9075: 1992 (Database Language SQL).
applicable privileges
catalog
cluster of catalogs
Embedded SQL
ISOLATION LEVEL SERIALIZABLE
READ WRITE
SQLCODE
SQLSTATE
SYSTEM USER
-
2
---------------------- Page: 10 ----------------------
ISO/IEC 9579=2:1998(E)
0 ISOIIEC
In addition, this part of ISOIIEC 9579 references the following non-terminal production symbols from the
SQL syntax, which represent a valid string of characters that can be derived from these production
...
Questions, Comments and Discussion
Ask us and Technical Secretary will try to provide an answer. You can facilitate discussion about the standard in here.