ISO/IEC 9075-3:1995

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

• Status: Withdrawn
• Publication Date: 26-Dec-1995
• Withdrawal Date: 26-Dec-1995
• 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 - Data management and interchange
• Current Stage: 9599 - Withdrawal of International Standard
• Completion Date: 16-Dec-1999

Relations

• Revised: ISO/IEC 9075-3:1999 - Information technology — Database languages — SQL — Part 3: Call-Level Interface (SQL/CLI)
• Effective Date: 15-Apr-2008

Standards Content (Sample)

INTERNATIONAL
ISOJIEC
STANDARD 9075-3
First edition
1995-I 2-l 5
Information technology - Database
languages - SQL -
Part 3:
Call-Level Interface (SQL/CLl)
Technologies de /‘information - Langages de base de don&es - SQL -
Pat-tie 3: Interface de niveau d ‘appel (SQL/CL/)
Reference number
ISO/I EC 9075-3: 1995(E)

---------------------- Page: 1 ----------------------
ISO/IEC 9075-3:1995 (E)
Page
Contents
1 Scope .~.,. 1
Norrmativereferences. 3
2
.....................................
5
3 Definitions, notations, and conventions
5
. Definitions .
31
5
Notations .
32 .
5
..........................................................
33 Conventions.
...........................................
5
313.1 Specification of routine definitions
6
Subclausenaming .
3.32
7
Concepts.~ .
4
7
41 Introduction.” .
9
Returncodes .
4:2
........................................................ 10
43 . Diagnostics areas
............................................... 12
44 Miscellaneous characteristics
..12
414.1 Handles .
................................................... 12
4.4.2 Null terminated strings
........................................................... 12
4.4.3 Null pointers
................................................... 13
4.4.4 Environment attributes
..................................................... 13
4.4.5 Connection attributes
..................................................... 13
4.4.6 Statement attributes
..................................................... 14
4.4.7 CL1 descriptor areas
..........................................
17
5 Call-Level Interface specifications
17
51 . .
..................................................
23
52 . invocation
................................................
26
53 SQL/CL1 common elements
....................................................
26
513.1 Implicit set connection
.......................................................... 26
5.3.2 Implicit cursor
27
.....................................................
5.3.3 Implicit using clause
................................................. 34
5.3.4 Character string retrieval
................................................. 35
5.3.5 Deferred parameter check
................................................... 35
5.3.6 Client-server operation.
.................................................. 35
5.3.7 CLI-specific status codes
......................................
37
5.3.8 Description of CL1 item descriptor areas
............................................
42
5.3.9 Other tables associated with CL1
................................................ 58
54 . Data type correspondences.
0 ISO/IEC 1995
All rights reserved. Unless otherwise specified, no part of this publication may be repro-
duced or utilized in any form or by any means, electronic or mechanical, including photo-
copying 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 Call-Level Interface (SQLKLI)

---------------------- Page: 2 ----------------------
ISO/IEC 9075-3:1995 (E)
OISO/IEC
6 SQLJCLIroutines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
65
...........................................................
61 . AIlocConnect
62 . AllocEnv.6 6
..~.....................................6 7
AIlocHandle
63 .
....................
70
..............................................................
64 . AIlocStmt
..7 1
65 . Bind&l .
73
.............................................................
66 BindParam
77
................................................................
6:7 Cancel
79
............................................................
68 . CloseCursor
80
ColAttribute .
69
82
Connect .
6'10
86
Copy&x. .
6'11
DataSources.8 7
6'12
89
DescribeCol .
6‘13
..9 1
6'14 Disconnect .
6'15 EndTran.g 3
96
.................................................................
6'16 Error
..9 8
6'17 ExecDirect .
..10 1
Execute
6'18 .
..lO 3
Fetch
6’19 .
6:20 FetchScroll.lO 5
FreeConnect.lO 8
6.21
FreeEnv.lO 9
6.22
..llO
FreeHandle
6.23 .
..113
FreeStmt
6.24 .
..114
GetConnectAttr
6.25 .
......................................................... 115
GetCursorName
6.26
6.27 GetData. .*.*.116
...
..l2 l
6.28 GetDescField .
6.29 GetDescRec .".12 3
..
..12 5
6.30 GetDiagField .
..13 1
6.31 GetDiagRec .
..13 3
6.32 GetEnvAttr .
..13 4
6.33 GetFunctions .
6.34 GetInfo.l3 5
6.35 GetStmtAttr.~.l4 3
..14 5
6.36 GetTypeInfo
..........................................................
..14 9
6.37 NumResuItCols .
6.38 ParamData.l5 0
6.39 Prepare ~.15 5
6.40 PutData.l5 7
..15 9
6e41 RowCount .
..16 0
6.42 SetConnectAttr .
......................................................... 161
6.43 SetCursorName
..16 3
6.44 SetDescField .
Table of Contents iii

---------------------- Page: 3 ----------------------
OISO/IEC
ISO/IEC 9075-3:1995 (E)
6.45 SetDescRec.16 6
6.46 SetEnvAttr.16 8
..16 9
6.47 SetStmtAttr .
7 Conformance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .173
71 . Introduction.173
72 . Claims of conformance . . . . . . . . . . . . . . . . . * . * . e . . . . . . . . . . . . . . . . . . . . . . e . . . . . . . 173
73 . Extensions and options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
..17 5
Annex A Typicalheaderfiles .
C Header File SQLCL1.H. . 175
A.1
.............................................. 185
A.2 COBOL Library Item SQLCLI
............................................... 193
Annex B Sample C programs
................................................. 193
B.l Create table, insert, select
....................................................... 196
B.2 Interactive Query.
............................ 199
B3 . Providing long dynamic arguments at Execute( ) time
Annex C Implementation-defined elements 0 . . + . . . 6 a . . . . . . e . + . - . . . . - . . . - . e . . o .203
Implementation-dependent elements . . . . y * . D . * . e 0 . o . e * . o . o . . . . . e . . . .211
Annex D
Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .215
iv Call-Level Interface (SQLKLI)

---------------------- Page: 4 ----------------------
ISO/IEC 90’75-3=1995 (E)
OISO/IEC
TABLES
Page
Table
.............................................
11
1 Fields in CL1 diagnostics areas
........................
19
2 Supported calling conventions of CL1 routines by language
.............................................
20
3 Abbreviated CL1 generic names
................... 35
4 SQLSTATE class and subclass values for CLI-specific conditions
..........................................
40
5 Fields in CL1 item descriptor areas
...............................
41
6 Codes used for implementation data types in CL1
...................................
41
7 Codes used for application data types in CL1
...........................
42
8 Codes associated with datetime data types in SQL/CL1
...........................
42
9 Codes associated with in SQL/CL1
........................ 42
10 SQL-statement integer codes for use in a diagnostics area
...................... 44
11 SQL-statement character codes for use in a diagnostics area.
.............................................
45
12 Codes used for diagnostic fields
...............................................
46
13 Codes used for handle types.
.......................................
46
14 Codes used for transaction termination
.......................................
46
15 Codes used for environment attributes
.........................................
46
16 Codes used for connection attributes
.........................................
46
17 Codes used for statement attributes
............................................
46
18 Codes used for FreeStmt options
...................................................
47
19 Data types of attributes
.............................................
48
20 Codes used for descriptor fields
............................................
49
21 Codes used for fetch orientation.
............................................
49
22 Miscellaneous codes used in CL1
..........................................
49
23 Codes used for GetData data types
......................................
49
24 Codes used to identify SQL/CL1 routines
...........................
51
25 Codes and data types for implementation information.
.......................................
52
26 Values for ALTER TABLE with GetInfo
.......................... 52
27 Values for CURSOR COMMIT BEHAVIOR with GetInfo
..................................
53
28 Values for FETCH DIRECTION with GetInfo
..............................
53
29 Values for GETDATA EXTENSIONS with GetInfo
...................................
53
Values for IDENTIFIER CASE with GetInfo
30
...........................
53
31 Values for OUTER JOIN CAPABILITIES with GetInfo
.............................
54
32 Values for SCROLL CONCURRENCY with GetInfo
.............................
54
33 Values for TRANSACTION CAPABLE with GetInfo
....................
54
34 Values for TRANSACTION ISOLATION OPTION with GetInfo
...................................
54
35 Values for NULL COLLATION with GetInfo
...........................................
55
36 Codes used for concise data types
......................... 56
37 Codes used with concise datetime data types in SQL/CL1
..........................
56
Codes used with concise interval data types in SQL/CL1
38
Table of Contents v

---------------------- Page: 5 ----------------------
ISO/IEC 9075-3:1995 (E)
OISO/IEC
39
Concise codes used with datetime data types in SQL/CL1 . 57
40 Concise codes used with interval data types in SQL/CL1 .
57
41
Data type correspondences for Ada . 58
42 Data type correspondences for C .
59
43 Data type correspondences for COBOL . 60
44 Data type correspondences for Fortran .
61
45 Data type correspondences for MUMPS . 62
46 Data type correspondences for Pascal .
63
47 Data type correspondences for PL/I .
64
vi Call-Level Interface (SQIKLI)

---------------------- Page: 6 ----------------------
OISOLCEC ISO/IEC 907593:1995 (E)
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 inter-
national 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 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 21, Open systems interconnection, data management and
open distributed processing.
ISO/IEC 9075 consists of the following parts, under the general title Information technozogy -
Database languages - SQL:
- Part 3: Call-Level Interface (SQLICLI)
- Part 4: Persistent Stored Modules (SQLIPSM)
Parts 1 and 2 are currently published as ISO/IEC 9075:1992.
Annexes A to D of this part of ISO/IEC 9075 are for information only.
Foreword vii

---------------------- Page: 7 ----------------------
ISO/IEC 90763tl995 (E) 01s0/rEc
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
identifies additional standards that, through reference in this
2) Clause 2, “Normative references”,
part of ISO/IEC 9075, constitute provisions of this part of ISO/IEC 9075.
defines the notations and conventions used
3) Clause 3, “Definitions, notations, and conventions”,
in this part of ISO/IEC 9075.
4) Clause 4, “Concepts”, presents concepts used in the definition of the Call-Level Interface.
defines facilities for using SQL through a Call-
5) Clause 5, “Call-Level Interface specifications”,
Level Interface.
6) Clause 6, “SQL/CL1 routines”, defines each of the routines that comprise the Call-Level
Interface.
defines the criteria for conformance to this part of ISO/IEC 9075.
7) Clause 7, “Conformance”,
8) Annex A, “Typical header files”, is an informative Annex. It provides examples of typical header
files for application programs using the SQL Call-Level Interface.
Annex B, “Sample C programs” is an informative Annex. It provides a sample of using the SQL
9)
Call-Level Interface from the C programming language.
is an informative Annex. It lists those features
10) Annex C, “Implementation-defined elements”,
for which the body of this part of the standard states that the syntax or meaning or effect on
the database is partly or wholly implementation-defined, and describes the defining information
that an implementor shall provide in each case.
11) Annex D, “Implementation-dependent elements”, is an informative Annex. It lists those features
for which the body of this part of the standard states that the syntax or meaning or effect on
the database is partly or wholly implementation-dependent.
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 7, “Conformance”, Subclauses begin a new
page. Any resulting blank space is not significant.
viii Introduction

---------------------- Page: 8 ----------------------
INTERNATIONAL STANDARD OISO/IEC
ISO/IEC 9075-3:1995 (E)
Information technology - Database languages - SQL -
Part 3:
Call-Level Interface (SQWCLI)
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: 9 ----------------------
ISO/IEC 9075~3r1995 (E) 01s0/IEc
2 Call-Level Interface (SQLKLI)

---------------------- Page: 10 ----------------------
ISO/IEC 9075-3:1995 (E)
01s0/IEc
2 Normative references
The following standards contain provisions that, through reference in this text, constitute provisions
of this part of this International Standard. At the time of publication, the editions indicated were
valid. All standards are subject to revision, and parties to agreements based on this International
Standard 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 1539:1991, Information technology - Programming languages - FORTRAN.
IS0 1989:1985, Programming languages - COBOL.
IS0 6160:1979, Programming languages - PLII.
IS0 71851990, Information technology - Programming languages - Pascal.
ISOIIEC 8652: 1995, Information technology - Programming languages - Ada.
NOTE - IS0 86521987 has been superseded by a new edition (ISOLIEC 86521995). However, when this part
of ISO/IEC 9075 was under development, the previous edition was valid and this part of ISO/IEC 9075 is therefore
based on that edition, which is listed below.
IS0 8652: 1987, Programming languages - Ada.
ISO/IEC 9075:1992, Information technology - Database languages - SQL.
ISO/IEC 9899:1990, Programming languages - C.
ISO/IEC 10206:1991, Information technology - Programming languages - Extended Pascal.
ISOIIEC 11756: 1992, Information technology-Programming languages-MUMPS.
Normative references
3

---------------------- Page: 11 ----------------------
ISO/IEC 907593t1995 (E)
OISO/IEC
4 Call-Level Interface (SQLKLI)

---------------------- Page: 12 ----------------------
ISO/IEC 9075-3:1995 (E)
01s0/IEc
3 Definitions, notations, and conventions
3.1 Definitions
For the purposes of this part of ISO/IEC 9075, the definitions given in ISO/IEC 9075:1992 and the
following definitions apply.
a) handle: An opaque data value returned by an SQL/CL1 implementation when a CL1 resource
is allocated and used by an SQL/CL1 application to reference that CL1 resource.
b) inner table: The second operand of a left outer join or the first operand of a right outer join.
3.2 Notations
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 3.2, “Notation”, of ISO/IEC 9075:1992.
3.3 Conventions
The conventions used in this part of ISO/IEC 9075 are identical to those described in Subclause 3.3,
“Conventions”, of ISO/IEC 9075:1992.
The contents of this part of ISO/IEC 9075 depend wholly on ISO/IEC 9075:1992. For example,
the Syntax found in the Format portions of this part of ISO/IEC 9075 often uses symbols that are
defined in ISOAEC 9075:1992.
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 numerical
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: 13 ----------------------
OISO/IEC
ISO/IEC 9075-3:1995 (E)
3.3 Conventions
3.3.2 Subclause naming
Clauses and Subclauses in this part of ISOAEC 9075 that have names identical to Clauses or
Subclauses in ISOLIEC 9075:1992 supplement the Clause or Subclause, respectively, in ISO/IEC
9075:1992, typically by replacing Format items or Rules or by providing new Format items or Rules.
Clauses and Subclauses in this part of ISO/IEC 9075 that have names that are not identical to
Clauses or Subclauses in ISO/IEC 9075:1992 provide language specification particular to this part
of ISO/IEC 9075.
6 Call-Level Interface (SQLKLI)

---------------------- Page: 14 ----------------------
ISO/IEC 9075-3:1995 (E)
OISO/IEC
4 Concepts
4.1 Introduction
The Call-Level Interface (SQUCLI) is an alternative binding style for executing SQL statements
comprising routines that:
- Allocate and deallocate resources,
- Control connections to SQL-servers,
- Execute SQL statements using mechanisms similar to dynamic SQL,
- Obtain diagnostic information,
Control transaction termination, and
- Obtain information about the implementation.
The AllocHandle routine allocates the resources to manage an SQL-environment, an SQL-
connection, a CL1 descriptor area, or SQL-statement processing. An SQL-connection is allocated
in the context of an allocated SQL-environment. A CL1 descriptor area and an SQL-statement
are allocated in the context of an allocated SQL-connection. The FreeHandle routine deallocates
The AllocConnect, AllocEnv, and AllocStmt routines can be used to allocate
a specified resource.
the resources to manage an SQL-connection, an SQL-environment, and SQL-statement processing,
The FreeConnect, FreeEnv, and FreeStmt
respectively, instead of using the AllocHandle routine.
routines can be used to deallocate the specific resource instead of using FreeHandle.
Each allocated SQL-environment has an attribute that determines whether output character strings
are null terminated by the implementation. The application can set the value of this attribute
by using the routine SetEnvAttr and can retrieve the current value of the attribute by using the
routine GetEnvAttr.
The Connect routine establishes an SQL-connection. The Disconnect routine terminates an es-
tablished SQL-connection. Switching between established SQL-connections occurs automatically
whenever the application switches processing to a dormant SQL-connection.
The ExecDirect routine is used for a one-time execution of an SQL-statement. The Prepare routine
is used to prepare an SQL-statement for subsequent execution using the Execute routine. In each
case, the executed SQL-statement can contain dynamic parameters.
The interface for a description of dynamic parameters, dynamic parameter values, the resultant
columns of a or , and the tar-
get specifications for the resultant columns is a CL1 descriptor area. A CL1 descriptor area for
each type of interface is automatically allocated when an SQL-statement is allocated. The applica-
tion may allocate additional CL1 descriptor areas and nominate them for use as the interface for
the description of dynamic parameter values or the description of target specifications by using the
routine SetStmtAttr. The application can determine the handle value of the CL1 descriptor area cur-
rently being used for a specific interface by using the routine GetStmtAttr. The GetDescField and
Concepts 7

---------------------- Page: 15 ----------------------
OISO/IEC
ISO/IEC 9075-3:1995 (E)
4.1 Introduction
GetDescRec routines enable information to be retrieved from a CL1 descriptor area. The CopyDesc
routine enables the contents of a CL1 descriptor area to be copied to another CL1 descriptor area.
When a or is prepared or exe-
cuted immediately, a description of the resultant columns is automatically provided in the applicable
CL1 descriptor area. In this case, the application may additionally retrieve information by using
the DescribeCol and/or the ColAttribute routine to obtain a description of a single resultant column
and by using the NumResultCols routine to obtain a count of the number of resultant columns. The
application sets values in the CLI descriptor area for the description of the corresponding target
specifications either explicitly using the routines SetDescField and SetDescRec or implicitly using
the routine BindCol.
When an SQL-statement is prepared or executed immediately, a description of the dynamic param-
eters is automatically provided in the applicable CLI descriptor area if this facility is supported
by the current SQL-connection. An attribute associated with the allocated SQL-connection indi-
cates whether this facility is supported. The value of the attribute may be retrieved using the
routine GetConnectAttr. The application sets values in the CL1 descriptor area for the description
of dynamic parameter values and, regardless of whether automatic population is supported, in the
CL1 descriptor area for the description of dynamic parameters either explicitly using the routines
SetDescField and SetDescRec or implicitly using the routine BindParam. The value of a dynamic
parameter may be established before SQL-statement execution (immediate parameter value) or may
be provided during SQL-statement execution (deferred parameter value). Its description in the CL1
descriptor area determines which method is in use. The ParamData routine is used to cycle through
and process deferred parameter values. The PutData routine is used to provide the deferred values.
The PutData routine also enables the values of character string parameters to be provided in pieces.
When a or is executed, a
cursor is implicitly declared and opened. The cursor name can be supplied by the application
by using the routine SetCursorName. If a cursor name is not supplied by the application, an
implementation-dependent cursor name is generated. The cursor name can be retrieved by using
the GetCursorName routine.
The Fetch and FetchScroll routines are used to position an open cursor on a row and to retrieve
the values of bound columns for that row. A bound column is one whose target specification in
the specified CL1 descriptor area defines a location for the target value. The Fetch routine always
positions the open cursor on the next row, whereas the FetchScroll routine may be used to position
the open cursor on any of its rows. The value of the CURSOR SCROLLABLE statement attribute
must be SCROLLABLE at the time that the cursor is implicitly declared in order to use FetchScroll
with a FetchOrientation other than NEXT. The application can set the value of this attribute by
using the SetStmtAttr routine and can retrieve the current value of the attribute by using the
GetStmtAttr routine.
Values for unbound columns can be individually retrieved by using the GetData routine. The
GetData routine also enables the values of character string columns to be retrieved piece by piece.
The current row of a cursor can be deleted or updated by executing a statement: positioned> or a , respectively,
for that cursor under a different allocated SQL-statement to the one under which the cursor was
opened. The CloseCursor routine enables a cursor to be closed.
The Error, GetDiagField, and GetDiagRec routines obtain diagnostic information about the most
recent routine operating on a particular resource. The Error routine always retrieves information
from the next status record, whereas the GetDiagField and GetDiagRec routines may be used to
retrieve information from any status record.
Information on the number of rows affected by the last executed S
...