ISO/IEC 9075-3:1999
(Main)Information technology — Database languages — SQL — Part 3: Call-Level Interface (SQL/CLI)
Information technology — Database languages — SQL — Part 3: Call-Level Interface (SQL/CLI)
Technologies de l'information — Langages de base de données — SQL — Partie 3: Interface de niveau d'appel (SQL/CLI)
General Information
Relations
Standards Content (Sample)
INTERNATIONAL ISO/IEC
STANDARD 9075-3
Second edition
1999-12-01
Information technology— Database
languages — SQL —
Part 3:
Call-Level Interface (SQL/CLI)
Technologies de l'information— Langages de base de données— SQL —
Partie 3: Interface de niveau d'appel (SQL/CLI)
Reference number
ISO/IEC 9075-3:1999(E)
©
ISO/IEC 1999
---------------------- Page: 1 ----------------------
ISO/IEC 9075-3: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-3:1999(E)
© ISO/IEC 1999 – All rights reserved i-3
---------------------- Page: 3 ----------------------
ISO/IEC 9075-3:1999 (E)
Contents Page
Foreword . ix
Introduction . . . . xi
1 Scope . 1
2 Normative references. 3
3 Definitions, notations, and conventions . 5
3.1 Definitions . 5
3.1.1 Definitions provided in Part 3 . 5
3.2 Notations . 5
3.3 Conventions . 5
3.3.1 Specification of routine definitions . 5
3.3.2 Relationships to other parts of ISO/IEC 9075 . 6
3.3.2.1 Clause, Subclause, and Table relationships . . . 6
3.4 Object identifier for Database Language SQL . 13
4 Concepts. 15
4.1 Introduction to SQL/CLI . 15
4.2 Return codes . 18
4.3 Diagnostics areas in SQL/CLI . 19
4.3.1 Setting of ROW_NUMBER and COLUMN_NUMBER fields . . 22
4.4 Miscellaneous characteristics . . 22
4.4.1 Handles . 22
4.4.2 Null terminated strings . 23
4.4.3 Null pointers . 23
4.4.4 Environment attributes . 23
4.4.5 Connection attributes. 24
4.4.6 Statement attributes . 24
4.4.7 CLI descriptor areas . 25
4.4.8 Obtaining diagnostics during multi-row fetch . 26
4.5 Client-server operation. 26
ii Call-Level Interface (SQL/CLI)
---------------------- Page: 4 ----------------------
©ISO/IEC ISO/IEC 9075-3:1999 (E)
5 Call-Level Interface specifications . 27
5.1 . . . . 27
5.2 invocation . 34
5.3 Implicit set connection . 37
5.4 Implicit cursor . . . . 38
5.5 Implicit DESCRIBE USING clause . 40
5.6 Implicit EXECUTE USING and OPEN USING clauses. 46
5.7 Implicit CALL USING clause . . 52
5.8 Implicit FETCH USING clause . 56
5.9 Character string retrieval . . . . 62
5.10 Binary large object string retrieval . 63
5.11 Deferred parameter check . . . . 64
5.12 CLI-specific status codes . 65
5.13 Description of CLI item descriptor areas . 67
5.14 Other tables associated with CLI. 77
5.15 Data type correspondences . . . . 100
6 SQL/CLI routines . 109
6.1 AllocConnect . 109
6.2 AllocEnv. 110
6.3 AllocHandle . 111
6.4 AllocStmt . 114
6.5 BindCol . 115
6.6 BindParameter . . . . 117
6.7 Cancel . . 122
6.8 CloseCursor . 124
6.9 ColAttribute . 125
6.10 ColumnPrivileges . . 127
6.11 Columns. 133
6.12 Connect . 143
6.13 CopyDesc . 147
6.14 DataSources. 148
6.15 DescribeCol . 150
6.16 Disconnect . 152
6.17 EndTran. 154
6.18 Error . . . 159
6.19 ExecDirect . 161
6.20 Execute . 164
6.21 Fetch . . . 166
6.22 FetchScroll . 169
6.23 ForeignKeys . 173
6.24 FreeConnect. 186
6.25 FreeEnv . 187
6.26 FreeHandle . 188
6.27 FreeStmt . 191
6.28 GetConnectAttr . . . 193
Contents iii
---------------------- Page: 5 ----------------------
ISO/IEC 9075-3:1999 (E) ©ISO/IEC
6.29 GetCursorName . . . 195
6.30 GetData . 196
6.31 GetDescField . 203
6.32 GetDescRec . 205
6.33 GetDiagField . 207
6.34 GetDiagRec . 216
6.35 GetEnvAttr . 218
6.36 GetFeatureInfo . . . . 220
6.37 GetFunctions . 223
6.38 GetInfo . . 224
6.39 GetLength . 232
6.40 GetParamData . . . . 234
6.41 GetPosition . 240
6.42 GetSessionInfo . . . . 242
6.43 GetStmtAttr. 244
6.44 GetSubString . 247
6.45 GetTypeInfo . 249
6.46 MoreResults . 253
6.47 NextResult . 254
6.48 NumResultCols . . . 255
6.49 ParamData. 256
6.50 Prepare . 262
6.51 PrimaryKeys . 264
6.52 PutData . 269
6.53 RowCount . 272
6.54 SetConnectAttr . . . 273
6.55 SetCursorName . . . 275
6.56 SetDescField . 277
6.57 SetDescRec. 282
6.58 SetEnvAttr. 284
6.59 SetStmtAttr . 286
6.60 SpecialColumns . . . 290
6.61 StartTran . 297
6.62 TablePrivileges . . . . 299
6.63 Tables . . . 304
7 Definition Schema . 311
7.1 SQL_IMPLEMENTATION_INFO base table . 311
7.2 SQL_SIZING base table. 313
7.3 SQL_LANGUAGES base table. 315
8 Conformance . 317
8.1 Conformance to SQL/CLI . 317
8.2 Claims of conformance . 317
iv Call-Level Interface (SQL/CLI)
---------------------- Page: 6 ----------------------
©ISO/IEC ISO/IEC 9075-3:1999 (E)
8.3 Extensions and options . 318
Annex A Typical header files . 319
A.1 C header file SQLCLI.H. 319
A.2 COBOL library item SQLCLI . . 333
Annex B Sample C programs . 343
B.1 Create table, insert, select . . . . 343
B.2 Interactive Query . . 346
B.3 Providing long dynamic arguments at Execute time . 350
Annex C Implementation-defined elements . 355
Annex D Implementation-dependent elements . 369
Annex E Incompatibilities with ISO/IEC 9075-3:1995 . 375
Annex F Deprecated features . 377
Annex G SQL feature and package taxonomy. 379
Index . 381
Contents v
---------------------- Page: 7 ----------------------
ISO/IEC 9075-3:1999 (E) ©ISO/IEC
TABLES
Tables Page
1 Clause, Subclause, and Table relationships . 6
2 Fields in SQL/CLI diagnostics areas . . . . 20
3 Supported calling conventions of SQL/CLI routines by language . 30
4 Abbreviated SQL/CLI generic names . . . . 30
5 SQLSTATE class and subclass values for SQL/CLI-specific conditions . . . . 65
6 Fields in SQL/CLI row and parameter descriptor areas. 72
7 Codes used for implementation data types in SQL/CLI . 74
8 Codes used for application data types in SQL/CLI . . 75
9 Codes associated with datetime data types in SQL/CLI. 76
10 Codes associated with in SQL/CLI . 76
11 Codes associated with in SQL/CLI . 76
12 Codes used for diagnostic fields . 77
13 Codes used for handle types . . . 78
14 Codes used for transaction termination . . 78
15 Codes used for environment attributes . . 79
16 Codes used for connection attributes . . . . 79
17 Codes used for statement attributes . . . . 79
18 Codes used for FreeStmt options . 80
19 Data types of attributes . 80
20 Codes used for descriptor fields . 81
21 Ability to set SQL/CLI descriptor fields . . 83
22 Ability to retrieve SQL/CLI descriptor fields . 85
23 SQL/CLI descriptor field default values . . 87
24 Codes used for fetch orientation. 89
25 Multi-row fetch status codes . . 89
26 Miscellaneous codes used in CLI . 90
27 Codes used to identify SQL/CLI routines . 90
28 Codes and data types for implementation information. 92
29 Codes and data types for session implementation information. 94
30 Values for ALTER TABLE with GetInfo . . 94
31 Values for FETCH DIRECTION with GetInfo . 95
32 Values for GETDATA EXTENSIONS with GetInfo . 95
33 Values for OUTER JOIN CAPABILITIES with GetInfo . 95
34 Values for SCROLL CONCURRENCY with GetInfo . 95
35 Values for TRANSACTION ISOLATION OPTION with GetInfo and StartTran. 95
36 Values for TRANSACTION ACCESS MODE with StartTran . . 96
37 Codes used for concise data types . 96
38 Codes used with concise datetime data types in SQL/CLI . . . . 97
vi Call-Level Interface (SQL/CLI)
---------------------- Page: 8 ----------------------
©ISO/IEC ISO/IEC 9075-3:1999 (E)
39 Codes used with concise interval data types in SQL/CLI . 97
40 Concise codes used with datetime data types in SQL/CLI . . . . 98
41 Concise codes used with interval data types in SQL/CLI . 98
42 Special parameter values . 99
43 Column types and scopes used with SpecialColumns. 99
44 Data type correspondences for Ada . 100
45 Data type correspondences for C . 102
46 Data type correspondences for COBOL . . 103
47 Data type correspondences for Fortran . . 105
48 Data type correspondences for MUMPS . . 106
49 Data type correspondences for Pascal . . . 107
50 Data type correspondences for PL/I . 108
51 SQL/CLI feature taxonomy . . . 379
Contents vii
---------------------- Page: 9 ----------------------
©ISO/IEC ISO/IEC 9075-3: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-3 was prepared by Joint Technical Committee ISO/IEC JTC
1, Information technology, Subcommittee SC 32, Data management and interchange.
This second edition of this part of ISO/IEC 9075 cancels and replaces the first edition, ISO/IEC
9075-3:1995.
ISO/IEC 9075 comprises 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, F, and G of this part of ISO/IEC 9075 are for information only.
Foreword ix
---------------------- Page: 10 ----------------------
©ISO/IEC ISO/IEC 9075-3: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 the Call-Level Interface.
5) Clause 5, ‘‘Call-Level Interface specifications’’, defines facilities for using SQL through a Call-
Level Interface.
6) Clause 6, ‘‘SQL/CLI routines’’, defines each of the routines that comprise the Call-Level
Interface.
7) Clause 7, ‘‘Definition Schema’’, specifies extensions to the Definition Schema required for
support of the Call-Level Interface.
8) Clause 8, ‘‘Conformance’’, defines the criteria for conformance to this part of ISO/IEC 9075.
9) Annex A, ‘‘Typical header files’’, is an informative Annex. It provides examples of typical
definition files for application programs using the SQL Call-Level Interface.
10) Annex B, ‘‘Sample C programs’’, is an informative Annex. It provides examples of using the SQL
Call-Level Interface from the C programming language.
11) Annex C, ‘‘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.
12) Annex D, ‘‘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.
13) Annex E, ‘‘Incompatibilities with ISO/IEC 9075-3:1995’’, is an informative Annex. It identifies
incompatibilities with ISO/IEC 9075-3:1995.
14) Annex F, ‘‘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.
15) Annex G, ‘‘SQL feature and package taxonomy’’, is an informative Annex. It contains a taxon-
omy of features of the SQL language that are specified in this part of ISO/IEC 9075.
Introduction xi
---------------------- Page: 11 ----------------------
ISO/IEC 9075-3:1999 (E) ©ISO/IEC
In the text of this part of ISO/IEC 9075, Clauses begin a new odd-numbered page, and in Clause 5,
‘‘Call-Level Interface specifications’’, through Clause 8, ‘‘Conformance’’, Subclauses begin a new
page. Any resulting blank space is not significant.
xii Call-Level Interface (SQL/CLI)
---------------------- Page: 12 ----------------------
INTERNATIONAL STANDARD ©ISO/IEC ISO/IEC 9075-3:1999 (E)
Information technology — Database languages — SQL —
Part 3: Call-Level Interface (SQL/CLI)
1 Scope
This part of ISO/IEC 9075 defines the structures and procedures that may be used to execute state-
ments of the database language SQL from within an application written in a standard programming
language in such a way that procedures used are independent of the SQL statements to be executed.
Scope 1
---------------------- Page: 13 ----------------------
ISO/IEC 9075-3:1999 (E) ©ISO/IEC
2 Call-Level Interface (SQL/CLI)
---------------------- Page: 14 ----------------------
©ISO/IEC ISO/IEC 9075-3:1999 (E)
2 Normative references
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:1991, Information technology — Programming languages — FORTRAN.
ISO 1989:1985, Programming languages — COBOL.
ISO 6160:1979, Programming languages — PL/I.
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-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 11756:1992, Information technology—Programming languages—MUMPS.
Normative references 3
---------------------- Page: 15 ----------------------
ISO/IEC 9075-3:1999 (E) ©ISO/IEC
4 Call-Level Interface (SQL/CLI)
---------------------- Page: 16 ----------------------
©ISO/IEC ISO/IEC 9075-3:1999 (E)
3 Definitions, notations, and conventions
3.1 Definitions
3.1.1 Definitions provided in Part 3
Insert this paragraph For the purposes of this part of ISO/IEC 9075, the definitions given in ISO/IEC
9075-1, ISO/IEC 9075-2, and ISO/IEC 9075-5 and the following definitions apply.
a) handle: A CLI object returned by an SQL/CLI implementation when a CLI resource is allocated
and used by an SQL/CLI application to reference that CLI resource.
b) inner table: The second operand of a left outer join or the first operand of a right outer join.
c) pseudo-column: A column in a table that is not part of the descriptor for that table. An
example of such a pseudo-column is an implementation-defined row identifier.
3.2 Notations
Insert this paragraph The syntax notation used in this part of 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 6.1, "Notation", of ISO/IEC 9075-1.
3.3 Conventions
Insert this paragraph
Except as otherwise specified in this part of ISO/IEC 9075, the conventions
used in this part of ISO/IEC 9075 are identical to those described in ISO/IEC 9075-1 and ISO/IEC
9075-2.
3.3.1 Specification of routine definitions
The routines in this part of ISO/IEC 9075 are specified in terms of:
— Function: A short statement of the purpose of the routine.
— Definition: The name of the routine and the names, modes, and data types of its parameters.
— General Rules: A specification of the run-time effect of the routine. Where more than one
General Rule is used to specify the effect of a routine, the required effect is that which would be
obtained by beginning with the first General Rule and applying the Rules in numeric sequence
until a Rule is applied that specifies or implies a change in sequence or termination of the
application of the Rules. Unless otherwise specified or implied by a specific Rule that is applied,
application of General Rules terminates when the last in the sequence has been applied.
Definitions, notations, and conventions 5
---------------------- Page: 17 ----------------------
ISO/IEC 9075-3:1999 (E) ©ISO/IEC
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 Corresponding Clause, Subclause, Part containing
part of ISO/IEC 9075 or Table from another part correspondence
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, and Clause 3, "Definitions, notations, and ISO/IEC 9075-2
conventions’’ conventions"
Subclause 3.1, ‘‘Definitions’’ Subclause 3.1, "Definitions" ISO/IEC 9075-2
Subclause 3.1.1, ‘‘Definitions provided Subclause 3.1.5, "Definitions provided ISO/IEC 9075-2
in Part 3’’ in Part 2"
Subclause 3.2, ‘‘Notations’’ Subclause 3.2, "Notation" ISO/IEC 9075-2
Subclause 3.3, ‘‘Conventions’’ Subclause 3.3, "Conventions" ISO/IEC 9075-2
Subclause 3.3.1, ‘‘Specification of rou- (none) (none)
tine definitions’’
Subclause 3.3.2, ‘‘Relationships to other (none) (none)
parts of ISO/IEC 9075’’
Subclause 3.3.2.1, ‘‘Clause, Subclause, (none) (none)
and Table relationships’’
Subclause 3.4, ‘‘Object identifier for none none
Database Language SQL’’
Clause 4, ‘‘Concepts’’ Clause 4, "Concepts" ISO/IEC 9075-2
Subclause 4.1, ‘‘Introduction to none none
SQL/CLI’’
Subclause 4.2, ‘‘Return codes’’ none none
Subclause 4.3, ‘‘Diagnostics areas in none none
SQL/CLI’’
Subclause 4.3.1, ‘‘Setting of ROW_ none none
NUMBER and COLUMN_NUMBER
fields’’
Subclause 4.4, ‘‘Miscellaneous charac- none none
teristics’’
Subclause 4.4.1, ‘‘Handles’’ none none
Subclause 4.4.2, ‘‘Null terminated none none
strings’’
Subclause 4.4.3, ‘‘Null pointers’’ none none
Subclause 4.4.4, ‘‘Environment at- none none
tributes’’
6 Call-Level Interface (SQL/CLI)
---------------------- Page: 18 ----------------------
©ISO/IEC ISO/IEC 9075-3:1999 (E)
3.3 Conventions
Table 1—Clause, Subclause, and Table relationships (Cont.)
Clause, Subclause, or Table in this Corresponding Clause, Subclause, Part containing
part of ISO/IEC 9075 or Table from another part correspondence
Subclause 4.4.5, ‘‘Connection at- none none
tributes’’
Subclause 4.4.6, ‘‘Statement attributes’’ none none
Subclause 4.4.7, ‘‘CLI descriptor areas’’ none none
Subclause 4.4.8, ‘‘Obtaining diagnostics none none
during multi-row fetch’’
Subclause 4.5, ‘‘Client-server operation’’ Subclause 4.36, "Client-server opera- ISO/IEC 9075-2
tion"
Clause 5, ‘‘Call-Level Interface specifi- none none
cations’’
Subclause 5.1, ‘‘’’ none none
Subclause 5.2, ‘‘ invoca- none none
tion’’
Subclause 5.3, ‘‘Implicit set connection’’ none none
Subclause 5.4, ‘‘Implicit cursor’’ none none
Subclause 5.5, ‘‘Implicit DESCRIBE none none
USING clause’’
Subclause 5.6, ‘‘Implicit EXECUTE none none
USING and OPEN USING clauses’’
Subclause 5.7, ‘‘Implicit CALL USING none none
clause’’
Subclause 5.8, ‘‘Implicit FETCH none none
USING clause’’
Subclause 5.9, ‘‘Character string re- none none
trieval’’
Subclause 5.10, ‘‘Binary large object none none
string retrieval’’
Subcla
...
Questions, Comments and Discussion
Ask us and Technical Secretary will try to provide an answer. You can facilitate discussion about the standard in here.