ISO/IEC 9075-13:2002

INTERNATIONAL ISO/IEC
STANDARD 9075-13
First edition
2002-07-01


Information technology — Database
languages — SQL —
Part 13:
TM
SQL Routines and Types Using the Java
Programming Language (SQL/JRT)
Technologies de l'information — Langages de base de données — SQL —
TM
Partie 13: Routines et types utilisant le langage de programmation Java
(SQL/JRT)




Reference number
ISO/IEC 9075-13:2002(E)
©
 ISO/IEC 2002

---------------------- Page: 1 ----------------------
ISO/IEC 9075-13:2002(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 2002
All rights reserved. Unless otherwise specified, no part of this publication may be reproduced or utilized in any form or by any means, electronic
or mechanical, including photocopying and microfilm, without permission in writing from either ISO at the address below or ISO's member body
in the country of the requester.
ISO copyright office
Case postale 56 • CH-1211 Geneva 20
Tel. + 41 22 749 01 11
Fax + 41 22 749 09 47
E-mail copyright@iso.ch
Web www.iso.ch
Printed in Switzerland

ii © ISO/IEC 2002 – All rights reserved

---------------------- Page: 2 ----------------------
ISO/IEC 9075-13:2002(E)

Contents Page
Foreword. . . . . . . . . . . . . . . . viii
Introduction. . . . . . . . . . . ix
1  Scope. . . . . 1
2   Normative references . 3
2.1 JTC1 standards . . 3
2.2 Publicly-available specifications. 3
3Definitions, notations, and conventions . 5
3.1 Definitions . 5
3.1.1 Definitions provided in Part 13 . 5
3.1.2 Definitions taken from Java. . 6
3.2 Notations. 6
3.3 Conventions. 7
3.3.1 Specification of built-in procedures . 7
3.3.2 Specification of deployment descriptor files . 7
3.3.3 Relationships to other parts of ISO/IEC 9075 . 8
3.3.3.1 Clause, Subclause, and Table relationships . . 8
3.4 Object identifier for Database Language SQL .12
4 Concepts.17
4.1 The Java programming language.17
4.2 SQL-invoked routines .18
4.3 Java class name resolution . .20
4.4 SQL result sets . .20
4.5 Parameter mapping.21
4.6 Unhandled Java exceptions . .22
4.7 Data types .23
4.8 User-defined types .23
4.8.1 User-defined type comparison and assignment . . .26
4.8.2 Accessing static fields.26
4.8.3 Converting objects between SQL and Java .27
4.8.3.1 SERIALIZABLE.27
4.8.3.2 SQLDATA . .27
4.8.3.3 Developing for portability .28
© ISO/IEC 2002 – All rights reserved iii

---------------------- Page: 3 ----------------------
ISO/IEC 9075-13:2002(E)
4.9 Built-in procedures.28
4.10 Privileges.29
4.11 JARs . .29
4.11.1 Deployment descriptor files .30
5 Lexical elements.31
5.1 and .31
5.2 Names and identifiers .32
6 Scalar expressions .35
6.1 .35
6.2 .36
7 Predicates .37
7.1 .37
8 Additional common elements .39
8.1 .39
8.2 .40
8.3 .42
8.4 .51
8.5 Java routine signature determination . .52
9 Schema definition and manipulation.59
9.1 .59
9.2 .60
9.3 .61
9.4 .65
9.5 .69
9.6 . .70
9.7 .71
9.8 . . .74
9.9 . . .75
9.10 .76
9.11 .78
10 Access control.79
10.1 . .79
10.2 .80
10.3 .81
11 Built-in procedures .83
11.1 SQLJ.INSTALL_JAR procedure.83
11.2 SQLJ.REPLACE_JAR procedure .85
11.3 SQLJ.REMOVE_JAR procedure.87
11.4 SQLJ.ALTER_JAVA_PATH procedure . .89
iv © ISO/IEC 2002 – All rights reserved

---------------------- Page: 4 ----------------------
ISO/IEC 9075-13:2002(E)
12 Java topics.91
12.1 Java facilities supported by this part of ISO/IEC 9075 .91
12.1.1 Package java.sql. .91
12.1.2 System properties .91
12.2 Deployment descriptor files .92
13 Information Schema.95
13.1 JAR_JAR_USAGE view.95
13.2 JARS view .96
13.3 METHOD_SPECIFICATIONS view.97
13.4 ROUTINE_JAR_USAGE view .98
13.5 TYPE_JAR_USAGE view.99
13.6 USER_DEFINED_TYPES view .100
13.7 Short name views.101
14 Definition Schema .103
14.1 JAR_JAR_USAGE base table.103
14.2 JARS base table. .104
14.3 METHOD_SPECIFICATIONS base table.105
14.4 ROUTINE_JAR_USAGE base table.106
14.5 ROUTINES base table.107
14.6 TYPE_JAR_USAGE base table .108
14.7 USAGE_PRIVILEGES base table .109
14.8 USER_DEFINED_TYPES base table . . .110
15 Status codes.113
15.1 Class and subclass values for uncaught Java exceptions.113
15.2 SQLSTATE.114
16 Conformance.115
16.1 Claims of conformance.115
Annex A SQL Conformance Summary .117
Annex B Implementation-defined elements.123
Annex C Implementation-dependent elements .127
Annex D SQL Feature Taxonomy .129
Annex E Routines tutorial .131
E.1 Technical components.131
E.2 Overview .132
E.3 Example Java methods: region and correctStates .133
E.4 Installing region and correctStates in SQL .133
E.5 Defining SQL names for region and correctStates .135
E.6 A Java method with output parameters: bestTwoEmps.136
E.7 A CREATE PROCEDURE best2 for bestTwoEmps .137
© ISO/IEC 2002 – All rights reserved v

---------------------- Page: 5 ----------------------
ISO/IEC 9075-13:2002(E)
E.8 Calling the best2 procedure . .138
E.9 A Java method returning a result set: orderedEmps.138
E.10 A CREATE PROCEDURE rankedEmps for orderedEmps . . .140
E.11 Calling the rankedEmps procedure .141
E.12 Overloading Java method names and SQL names .141
E.13 Java main methods .143
E.14 Java method signatures in the CREATE statements.144
E.15 Null argument values and the RETURNS NULL clause .145
E.16 Static variables . .147
E.17 Dropping SQL names of Java methods .148
E.18 Removing Java classes from SQL.148
E.19 Replacing Java classes in SQL.149
E.20 Visibility.150
E.21 Exceptions .150
E.22 Deployment descriptors .151
E.23 Paths . .154
E.24 Privileges.156
E.25 Information Schema.156
Annex F Types tutorial.157
F.1 Overview .157
F.2 Example Java classes.157
F.3 Installing Address and Address2Line in an SQL system.159
F.4 CREATE TYPE for Address and Address2Line . . .160
F.5 Multiple SQL types for a single Java class .162
F.6 Collapsing subclasses.162
F.7 GRANT and REVOKE statements for data types .164
F.8 Deployment descriptors for classes.164
F.9 Using Java classes as data types.166
F.10 SELECT, INSERT, and UPDATE.166
F.11 Referencing Java fields and methods in SQL.167
F.12 Extended visibility rules .168
F.13 Logical representation of Java instances in SQL . .168
F.14 Static methods . . .170
F.15 Static fields .170
F.16 Instance-update methods.171
F.17 Subtypes in SQL/JRT data. . .173
F.18 References to fields and methods of null instances .174
F.19 Ordering of SQL/JRT data . . .175
Annex G Incompatibilities with ANSI NCITS 331.177
G.1 References .177
G.2    Incompatibilities . . . . . . 1 77
Index. . . . . . . . . . . . . . . 181
vi © ISO/IEC 2002 – All rights reserved

---------------------- Page: 6 ----------------------
ISO/IEC 9075-13:2002(E)

TABLES
Tables Page
1 Clause, Subclause, and Table relationships . 8
2 System properties .91
3 SQLSTATE class and subclass values . .114
4 Feature taxonomy for features outside Core SQL .129
© ISO/IEC 2002 – All rights reserved vii

---------------------- Page: 7 ----------------------
ISO/IEC 9075-13:2002(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 international 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.
International Standards are drafted in accordance with the rules given in the ISO/IEC Directives, Part 3.
The main task of the joint technical committee is to prepare International Standards. 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.
Attention is drawn to the possibility that some of the elements of this part of ISO/IEC 9075 may be the subject of
patent rights. ISO and IEC shall not be held responsible for identifying any or all such patent rights.
ISO/IEC 9075-13 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)
 Part 9: Management of External Data (SQL/MED)
 Part 10: Object Language Bindings (SQL/OLB)
 Part 11: Information and definition schemas (SQL/Schemata)
TM
 Part 13: SQL Routines and Types Using the Java Programming Language (SQL/JRT)
Annexes A, B, C, D, E, F and G of this part of ISO/IEC 9075 are for information only.

viii © ISO/IEC 2002 – All rights reserved

---------------------- Page: 8 ----------------------
ISO/IEC 9075-13:2002(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 Java routines and types.
5) Clause 5, ‘‘Lexical elements’’, defines a number of lexical elements used in the definition of Java
routines and types.
6) Clause 6, ‘‘Scalar expressions’’, defines the elements of the language that produce scalar values.
7) Clause 7, ‘‘Predicates’’, defines the predicates of the language.
8) Clause 8, ‘‘Additional common elements’’, defines additional language elements that are used in
various parts of the language.
9) Clause 9, ‘‘Schema definition and manipulation’’, defines the schema definition and manipulation
statements associated with the definition of Java routines and types.
10) Clause 10, ‘‘Access control’’, defines facilities for controlling access to SQL-data.
11) Clause 11, ‘‘Built-in procedures’’, defines new built-in procedures used in the definition of Java
routines and types.
12) Clause 12, ‘‘Java topics’’, defines the facilities supported by implementations of this part of
ISO/IEC 9075 and the conventions used in deployment descriptor files.
13) Clause 13, ‘‘Information Schema’’, defines viewed tables that contain schema information.
14) Clause 14, ‘‘Definition Schema’’, defines base tables on which the viewed tables containing
schema information depend.
15) Clause 15, ‘‘Status codes’’, defines SQLSTATE values related to Java routines and types.
16) Clause 16, ‘‘Conformance’’, defines the criteria for conformance to this part of ISO/IEC 9075.
17) Annex A, ‘‘SQL Conformance Summary’’, is an informative Annex. It summarizes the confor-
mance requirements of the SQL language.
18) 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.
© ISO/IEC 2002 – All rights reserved ix

---------------------- Page: 9 ----------------------
ISO/IEC 9075-13:2002(E)
19) 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.
20) Annex D, ‘‘SQL Feature 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.
21) Annex E, ‘‘Routines tutorial’’, is an informative Annex. It provides a tutorial on using the
features defined in this part of ISO/IEC 9075 for defining and using SQL-invoked routines based
on Java static methods.
22) Annex F, ‘‘Types tutorial’’, is an informative Annex. It provides a tutorial on using the features
defined in this part of ISO/IEC 9075 for defining and using SQL structured types based on Java
classes.
23) Annex G, ‘‘Incompatibilities with ANSI NCITS 331’’, is an informative Annex. It lists the
incompatibilities between this edition of this part of ISO/IEC 9075 and NCITS 331.1 and NCITS
331.2.
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 16, ‘‘Conformance’’, Subclauses begin a new page. Any resulting
blank space is not significant.
x © ISO/IEC 2002 – All rights reserved

---------------------- Page: 10 ----------------------
INTERNATIONAL STANDARD ISO/IEC 9075-13:2002(E)
Informationtechnology—Databaselanguages—SQL—
Part 13: SQL Routines and Types Using the Java™ Programming
Language (SQL/JRT)
1 Scope
This part of International Standard ISO/IEC 9075 specifies the ability to invoke static methods
written in the Java™ programming language as SQL-invoked routines and to use classes defined
in the Java programming language as SQL structured user-defined types. (Java is a registered
trademark of Sun Microsystems, Inc.)
© ISO/IEC 2002 – All rights reserved
Scope 1

---------------------- Page: 11 ----------------------
ISO/IEC 9075-13:2002 (E)
(Blank page)
© ISO/IEC 2002 – All rights reserved
2 SQL Routines and Types Using Java (SQL/JRT)

---------------------- Page: 12 ----------------------
ISO/IEC 9075-13:2002 (E)
2 Normative references
The following normative documents contain provisions which, through reference in this text, constitute provisions
of this part of ISO/IEC 9075. For dated references, subsequent amendments to, or revisions of, any of these
publications do not apply. However, 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 normative documents indicated below. For
undated references, the latest edition of the normative document referred to applies. Members of ISO and IEC
maintain registers of currently valid International Standards.
2.1 JTC 1 standards
ISO 8824-1:1998, Information technology — Abstract Syntax Notation One
(ASN.1): Specification of basic notation
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-5:1999, Information technology — Database languages — SQL — Part 5:
Host Language Bindings (SQL/Bindings)
ISO/IEC 9075-10:2000, Information technology — Database languages — SQL — Part 10:
Object Language Bindings (SQL/OLB)
2.2 Publicly-availablespecifications
The Java Language Specification, Second Edition, Bill Joy (Editor), Guy Steele, James Gosling,
and Gilad Bracha, Addison-Wesley, 2000, ISBN 0-201-31008-2.
The Java Virtual Machine Specification, Second Edition, Tim Lindholm and Frank Yellin,
Addison-Wesley, 1999, ISBN 0-201-43294-3.
Java 2 Platform, Standard Edition, v1.2.2, API Specification,
http://web2.java.sun.com/products/jdk/1.2/docs/api/.
Java Object Serialization Specification,
http://web2.java.sun.com/products/jdk/1.2/docs/guide/serialization/spec/serialTOC.doc.html.
The JavaBeans™ 1.01 Specification,
http://java.sun.com/products/javabeans/docs/spec.html.
© ISO/IEC 2002 – All rights reserved
Normative references 3

---------------------- Page: 13 ----------------------
ISO/IEC 9075-13:2002 (E)
2.2 Publicly-available specifications
JDBC™ 2.0 API, Version 1.0, Seth White & Mark Hapner, Sun Microsystems, Inc., 30 May,
1999.
JDBC 2.0 Standard Extension API, Version 1.0, Seth White & Mark Hapner, Sun Microsystems,
Inc., 7 December, 1998.
JDBC API Tutorial and Reference, Second Edition: Universal Data Access for the Java 2
Platform, Seth White, Maydene Fisher, Rick Cattell, Graham Hamilton, and Mark Hapner,
Addison Wesley, Reading MA, 1999, ISBN 0-201-43328-1.
W3C Architecture domain: Naming and Addressing (URLs),
http://www.w3.org/Addressing/Activity.html.
RFC 1738, Uniform Resource Locators (URL), T. Berners-Lee, L. Maxinter, M. McCahill, Decem-
ber, 1994.
RFC 1808, Relative Uniform Resource Locators, R. Fielding, June, 1995.
© ISO/IEC 2002 – All rights reserved
4 SQL Routines and Types Using Java (SQL/JRT)

---------------------- Page: 14 ----------------------
ISO/IEC 9075-13:2002 (E)
3Definitions,notations,andconventions
3.1 Definitions
3.1.1 DefinitionsprovidedinPart13
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, ISO/IEC 9075-5, and ISO/IEC 9075-10, and the following definitions, apply.
a) classfile:A file containing the compiled byte code for a Java class.
b) default connection: a JDBC connection to the current SQL-implementation, SQL-session, and
SQL-transaction established with the data source URL ’jdbc:default:connection’.
c) deployment descriptor: one or more SQL-statements that specify and
to be taken, respectively, by the SQLJ.INSTALL_JAR and SQLJ.REMOVE_JAR
procedures and that are contained in a deplyment descriptor file. For example, when a JAR
is installed, one or more s that specify LANGUAGE JAVA and either
PROCEDURE or FUNCTION and the associated s can be specified
in the deployment descriptor and executed as part of the install process.
d) deployment descriptorfile: a text file containing deployment descriptors that is contained in
a JAR, for which the JAR’s manifest entry, as described by the java.util.jar section of Java
2 Platform, Standard Edition, v1.2.2, API Specification, specifies SQLJDeploymentDescriptor:
TRUE.
e) external Java data type: an SQL user-defined type defined with a tion> that specifies an .
f) external Java routine: an external routine defined with an that
specifies LANGUAGE JAVA and either PROCEDURE or FUNCTION, or defined with a defined type definition> that specifies an .
g) installed JAR: a JAR whose existence has been registered with the SQL-environment and
whose contents have been copied into that SQL-environment due to execution of one of the
procedures SQLJ.INSTALL_JAR and SQLJ.REPLACE_JAR.
h) Java Archive (JAR): a zip formatted file, as described by the java.util.zip section of Java
2 Platform, Standard Edition, v1.2.2, API Specification, containing zero or more Java class
and ser files, and zero or more deployment descriptor files. JARs are a normal vehicle for
distributing Java programs and the mechanism specified by this International Standard to
provide the implementation of external Java routines and external Java data types to an
SQL-environment.
i) JVM: A Java Virtual Machine, as defined by The Java Virtual Machine Specification, Second
Edition.
j) ser file:A file containing representations of Java objects in the form defined in Java Object
Serialization Specification.
© ISO/IEC 2002 – All rights reserved
Definitions, notations, and conventions 5

---------------------- Page: 15 ----------------------
ISO/IEC 9075-13:2002 (E)
3.1 Definitions
k) subject Java class: the Java class uniquely identified by the combination of the class’s subject
Java class name and its containing JAR.
l) subject Java class name: the fully-qualified package and class name of a Java class.
m) system class: any Java class provided by a conforming implementation of this part of ISO/IEC
9075 that can be referenced by an external Java routine or an external Java data type without
that class having been included in an installed JAR.
3.1.2 DefinitionstakenfromJava
This part of ISO/IEC 9075 makes use of the following terms defined in The Java Language Specifi-
cation, Second Edition:
a) block
b) class declaration
c) class instance
d) class variable
e) field
f) instance initializer
g) instance variable
h) interface
i) local variable
j) nested class
k) package
l) static initializer
m) subpackage
This part of ISO/IEC 9075 makes use of the following terms defined in The Java Virtual Machine
Specification, Second Edition:
a) classfile
b) Java Virtual Machine
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.
6 SQL Routines and Types Using Java (SQL/JRT)
© ISO/IEC 2002 – All rights reserved

---------------------- Page: 16 ----------------------
ISO/IEC 9075-13:2002 (E)
3.3 Conventions
3.3 Conventions
Insert this paragraph Except as otherwise specified in this part of ISO/IEC 9075, the conventions
used in thi
...