ISO/IEC TR 19075-3:2015

Information technology — Database languages — SQL Technical Reports — Part 3: SQL Embedded in Programs using the JavaTM programming language

This Technical Report describes the support for the use of SQL within programs written in Java. The Report discusses the following features of the SQL Language: — The embedding of SQL expressions and statements in programs written in the Java programming language.

Technologies de l'information — Langages de base de données — SQL rapports techniques — Partie 3: SQL intégrées dans des programmes utilisant le langage de programmation de JavaTM

General Information

• Status: Withdrawn
• Publication Date: 16-Jun-2015
• Withdrawal Date: 16-Jun-2015
• 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: 01-Sep-2021

Relations

• Revised: ISO/IEC 19075-3:2021 - Information technology — Guidance for the use of database language SQL — Part 3: SQL embedded in programs using the JavaTM programming language
• Effective Date: 23-Apr-2020
• Consolidated By: ISO 8502-9:2020 - Preparation of steel substrates before application of paints and related products — Tests for the assessment of surface cleanliness — Part 9: Field method for the conductometric determination of water-soluble salts
• Effective Date: 06-Jun-2022

Standards Content (Sample)

TECHNICAL ISO/IEC TR
REPORT 19075-3
First edition
2015-07-01
Information technology — Database
languages — SQL Technical Reports —
Part 3:
SQL Embedded in Programs using
TM
the Java programming language
Technologies de l’information — Langages de base de données — SQL
rapports techniques —
Partie 3: SQL intégrées dans des programmes utilisant le langage
TM
de programmation de Java
Reference number
ISO/IEC TR 19075-3:2015(E)
©
ISO/IEC 2015

---------------------- Page: 1 ----------------------
ISO/IEC TR 19075-3:2015(E)

COPYRIGHT PROTECTED DOCUMENT
© ISO/IEC 2015, Published in Switzerland
All rights reserved. Unless otherwise specified, no part of this publication may be reproduced or utilized otherwise in any form
or by any means, electronic or mechanical, including photocopying, or posting on the internet or an intranet, without prior
written permission. Permission can be requested from either ISO at the address below or ISO’s member body in the country of
the requester.
ISO copyright office
Ch. de Blandonnet 8 • CP 401
CH-1214 Vernier, Geneva, Switzerland
Tel. [phone]
Fax [phone]
[e-mail]
www.iso.org
ii © ISO/IEC 2015 – All rights reserved

---------------------- Page: 2 ----------------------
ISO/IEC TR 19075-3:2015(E)
DTR 19075-3:2014(E)
Contents Page
Foreword. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . v
Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . vi
1 Scope. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
2 Normative references. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3
2.1 ISO and IEC standards. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
2.2 Other international standards. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
3 Use of SQL in programs written in Java. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
3.1 Design goals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5
3.2 Advantages of SQL/OLB over JDBC. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
3.3 Consistency with existing embedded SQL languages. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
3.4 Profile customization overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
3.4.1 Profile customization process. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
3.4.2 Profile customization utilities. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
3.5 Examples. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
3.5.1 Example of Profile generation and naming. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
3.5.2 Example of a JAR manifest file. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
3.5.3 Host variables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9
3.5.4 Host expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
3.5.5 SQL/OLB clauses. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
3.5.6 Connection contexts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .10
3.5.7 Default connection context. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
3.5.8 Iterators. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
3.5.8.1 Positional bindings to columns. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
3.5.8.2 Named bindings to columns. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
3.5.8.3 Providing names for columns of queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
3.5.9 Invoking SQL-invoked routines. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
3.5.10 Using multiple SQL/OLB contexts and connections. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
3.5.11 SQL execution control and status. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
3.5.12
Multiple java.sql.ResultSet objects from SQL-invoked procedure calls. . . . . . . . . . . . . . . . . . . . . . 16
3.5.13 Creating an SQL/OLB iterator object from a java.sql.ResultSet object. . . . . . . . . . . . . . . . . . . . . . 17
3.5.14 Obtaining a java.sql.ResultSet object from an iterator object. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
3.5.15 Working with user-defined types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
3.5.16 Batching. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
3.5.17 Example program. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .19
3.5.18 Host variable definition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
©ISO/IEC 2015 – All rights reserved Contents iii

---------------------- Page: 3 ----------------------
ISO/IEC TR 19075-3:2015(E)
DTR 19075-3:2014(E)
Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
iv  SQL Embedded in Programs Using the the Java™ Programming Language ©ISO/IEC 2015 – All rights reserved

---------------------- Page: 4 ----------------------
ISO/IEC TR 19075-3:2015(E)
DTR 19075-3:2014(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 2.
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 Interna-
tional Standard requires approval by at least 75 % of the national bodies casting a vote.
In exceptional circumstances, when the joint technical committee has collected data of a different kind from
that which is normally published as an International Standard (“state of the art”, for example), it may decide
to publish a Technical Report. A Technical Report is entirely informative in nature and shall be subject to
review every five years in the same manner as an International Standard.
Attention is drawn to the possibility that some of the elements of this document 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 TR 19075-3 was prepared by Joint Technical Committee ISO/IEC JTC 1, Information technology,
Subcommittee SC 32, Data management and interchange.
ISO/IEC TR 19075 consists of the following parts, under the general title Information technology — Database
languages — SQL Technical Reports:
— Part 1: XQuery Regular Expression Support in SQL
— Part 2: SQL Support for Time-Related Information
— Part 3: SQL Embedded in Programs Using the Java™ Programming Language
— Part 4: SQL With Routines and Types Using the Java™ Programming Language
— Part 5: Row Pattern Recognition in SQL
NOTE 1 — The individual parts of multi-part technical reports are not necessarily published together. New editions of one or more
parts may be published without publication of new editions of other parts.
©ISO/IEC 2015 – All rights reserved Foreword v

---------------------- Page: 5 ----------------------
ISO/IEC TR 19075-3:2015(E)
DTR 19075-3:2014(E)
Introduction
The organization of this part of ISO/IEC 19075 is as follows:
1) Clause 1, “Scope”, specifies the scope of this part of ISO/IEC 19075.
2) Clause 2, “Normative references”, identifies additional standards that, through reference in this part of
ISO/IEC 19075, constitute provisions of this part of ISO/IEC 19075.
3) Clause 3, “Use of SQL in programs written in Java”, provides a tutorial on the embedding of SQL
expressions and statements in programs written in the Java programming language.
vi  SQL Embedded in Programs Using the the Java™ Programming Language ©ISO/IEC 2015 – All rights reserved

---------------------- Page: 6 ----------------------
ISO/IEC TR 19075-3:2015(E)
TECHNICAL REPORT ISO/IEC TR 19075-3:2014
Information technology — Database languages — SQL Technical Reports —
Part 3:
SQL Embedded in Programs Using the Java™ Programming Language
1 Scope
This Technical Report describes the support for the use of SQL within programs written in Java.
The Report discusses the following features of the SQL Language:
— The embedding of SQL expressions and statements in programs written in the Java programming language
©ISO/IEC 2015 – All rights reserved Scope 1

---------------------- Page: 7 ----------------------
ISO/IEC TR 19075-3:2015(E)
DTR 19075-3:2014(E)
(Blank page)
2  SQL Embedded in Programs Using the the Java™ Programming Language ©ISO/IEC 2015 – All rights reserved

---------------------- Page: 8 ----------------------
ISO/IEC TR 19075-3:2015(E)
DTR 19075-3:2014(E)
2.1 ISO and IEC standards
2 Normative references
The following referenced documents are indispensable for the application of this document. For dated references,
only the edition cited applies. For undated references, the latest edition of the referenced document (including
any amendments) applies.
2.1 ISO and IEC standards
[ISO9075-1] ISO/IEC 9075-1:2011, Information technology — Database languages — SQL — Part 1:
Framework (SQL/Framework).
[ISO9075-2] ISO/IEC 9075-2:2011, Information technology — Database languages — SQL — Part 2:
Foundation (SQL/Foundation).
[ISO9075-10] ISO/IEC 9075-10:2008, Information technology — Database languages — SQL — Part 10:
Object Language Bindings (SQL/OLB).
2.2 Other international standards
[Unicode] The Unicode Consortium, The Unicode Standard. (Information about the latest version of the
Unicode standard can be found by using the "Latest Unicode Version" link on the "Enumerated Versions of
The Unicode Standard" page.)
http://www.unicode.org/versions/enumeratedversions.html
[Java] The Java™ Language Specification, Third Edition, James Gosling, Bill Joy, Guy Steele, and Gilad
Bracha, Prentice Hall, June 14, 2005, ISBN 0-321-24678-0.
[JDBC] JDBC™ 4.0 Specification, Final v1.0, Lance Andersen, Sun Microsystems, Inc., November 7, 2006.
[JNDI] Java Naming and Directory Interface™, Sun Microsystems, Inc. http://java.sun.com/-
j2se/1.5.0/docs/guide/jndi/index.html.
[JavaBeans] The JavaBeans™ 1.01 Specification
http://java.sun.com/products/javabeans/docs/spec.html
©ISO/IEC 2015 – All rights reserved Normative references 3

---------------------- Page: 9 ----------------------
ISO/IEC TR 19075-3:2015(E)
DTR 19075-3:2014(E)
(Blank page)
4  SQL Embedded in Programs Using the the Java™ Programming Language ©ISO/IEC 2015 – All rights reserved

---------------------- Page: 10 ----------------------
ISO/IEC TR 19075-3:2015(E)
DTR 19075-3:2014(E)
3.1 Design goals
3 Use of SQL in programs written in Java
3.1 Design goals
The following items represent the major design features of [ISO9075-10].
— Provide a concise, legible mechanism for embedding SQL-statements in a program that otherwise conforms
to [Java].
— Syntactic and semantic check of SQL-statements prior to program execution.
SQL/OLB can use an implementation-defined mechanism at translate time to check embedded SQL-
statements to make sure that they are syntactically and semantically correct.
— Allow the syntax and semantics of SQL-statements to be location-independent.
The syntax and semantics of SQL-statements in an SQL/OLB program do not depend on the configuration
under which SQL/OLB is running. This makes it possible to implement SQL/OLB programs that run on
the client, in the SQL-server, or in a middle tier.
— Provide facilities that enable the programmer to move between the SQL/OLB and JDBC environments by
sharing a single SQL-connection in both environments.
— Provide for binary portability of translated and compiled Java SQL-client applications such that they can
be used transparently with multiple SQL-servers. In addition, binary portability profiles allow for customiza-
tion and optimization of SQL-statements within an SQL/OLB application.
3.2 Advantages of SQL/OLB over JDBC
JDBC provides a complete, low-level SQL interface from Java to SQL-implementations. SQL/OLB is designed
to fill a complementary role by providing a higher-level programming interface to SQL-implementations in
such a manner as to free the programmer from the tedious and complex programming interfaces found in lower-
level APIs.
The following are some major differences between the two:
— SQL/OLB source programs are smaller than equivalent JDBC programs since the translator can implicitly
handle many of the tedious programming chores that dynamic interfaces require.
— SQL/OLB programs can type-check SQL code at translate time using an implementation-dependent
mechanism. JDBC, being a completely dynamic API, can not.
— SQL/OLB programs allow direct embedding of Java host expressions within SQL-statements. JDBC
requires a separate call statement for each bind variable and specifies the binding by position number.
— SQL/OLB enforces strong typing of query outputs and values returned and allows type checking on calls.
JDBC passes values to and from SQL without compile time type checking.
©ISO/IEC 2015 – All rights reserved Use of SQL in programs written in Java 5

---------------------- Page: 11 ----------------------
ISO/IEC TR 19075-3:2015(E)
DTR 19075-3:2014(E)
3.2 Advantages of SQL/OLB over JDBC
— SQL/OLB provides simplified rules for invoking SQL-invoked routines. [JDBC] requires a generic call
to an SQL-invoked routine, fun, to have the following syntax:
prepStmt.prepareCall("{call fun(.)}"); // For SQL-invoked procedures
prepStmt.prepareCall("{? = call fun(.)}");  // For SQL-invoked functions
SQL/OLB provides simplified notations:
#sql { CALL fun(.) }; // SQL-invoked procedure
// Declare x
...
#sql x = { VALUES(fun(.)) }; // SQL-invoked function
// VALUES is an SQL construct
3.3 Consistency with existing embedded SQL languages
Programming languages containing embedded SQL are called host languages. Java differs from the traditional
host languages (Ada, C, COBOL, Fortran, MUMPS (M), Pascal, PL/I) in ways that significantly affect its
embedding of SQL.
— Java has automatic storage management (also known as “garbage collection”) that simplifies the management
of storage for data retrieved from SQL-implementations.
— All Java types representing composite data, and data of varying sizes, have a distinguished value null,
which can be used to represent the SQL NULL value. This gives Java programs an alternative to the indi-
cator variables that are part of the interfaces to other host languages.
— Java is designed to support programs that are automatically heterogeneously portable (also called “super
portable” or simply “downloadable”). That, along with Java's type system of classes and interfaces, enables
component software. In particular, an SQL/OLB translator, written in Java, can call components that are
specialized by SQL-implementations, in order to leverage the existing authorization, schema checking,
type checking, transactional, and recovery capabilities that are traditional of SQL-implementations, and
to generate code optimized for particular SQL-implementations.
— Java is designed for binary portability in heterogeneous networks, which promises to enable binary porta-
bility for applications that use SQL.
— SQL/OLB extends the traditional concept of embedded host variables by allowing generalized host
expressions.
3.4 Profile customization overview
This Subclause describes how implementation-specific “customized” SQL execution control can be added to
SQL/OLB applications. The SQL/OLB runtime framework uses the following interfaces:
— SQLJ.runtime.profile.RTStatement to execute SQL-statements.
— SQLJ.runtime.profile.RTResultSet to describe query results.
6  SQL Embedded in Programs Using the the Java™ Programming Language ©ISO/IEC 2015 – All rights reserved

---------------------- Page: 12 ----------------------
ISO/IEC TR 19075-3:2015(E)
DTR 19075-3:2014(E)
3.4 Profile customization overview
— SQLJ.runtime.profile.ConnectedProfile to create RTStatement objects corresponding to particular SQL-
statements.
An implementation is able to control SQL execution by providing an implementation of the RTStatement,
RTResultSet, and ConnectedProfile interfaces. An implementation is able to redirect control to their imple-
mentation by registering customization hooks with the application profiles.
For example, if the client connects to SQL-server A, then a customization that understands SQL-server A's
system will be used. If the client connects to SQL-server B, then SQL-server B's customization will be used.
In the absence of a connection specific customization, the default JDBC based customization will be used. Like
the profile object, customization objects are serializable. This allows the customization state to be stored and
restored with the profile. In this manner, an implementation-dependent deployment tool is able to load the
profile, inspect and precompile the SQL-statements it contains, register an appropriate customization, and store
the profile in persistent storage. Then at application runtime, the profile and the registered implementation-
dependent customization will both be restored, and the customization will be used to execute the SQL-statements.
3.4.1 Profile customization process
The profile customization process is the act of registering profile customization objects with the profile(s)
associated with an application. The profile customization process can be generalized to the following steps:
1) Discover the profile objects within a JAR file.
2) For each profile, deserialize the profile object from the appropriate JAR entry.
3) Create an SQL-connection with which the profile will be customized.
4) Create and register a profile customization with the profile.
5) Serialize the customized profile back to persistent storage.
6) Recreate the JAR contents using the customized serialized profiles.
Of the above steps, only step 4) is likely to change from implementation to implementation. While step 3) is
implementation-dependent, it can be done using a parameterized tool and JDBC. The rest of the steps involve
actions that can be performed by any generic utility without specific knowledge of the customization being
performed.
The act of creating and registering a customization object with a profile (step 4 above) is abstractly defined by
the Java interface SQLJ.runtime.profile.util.ProfileCustomizer. The intent of defining this interface is to
allow SQL implementations to concentrate on writing profile customizers and customization objects (step 4
above), while tools and application implementations concentrate on writing generic tools that apply customizers
to application profiles (steps 1 – 3 and 5 – 6 above).
The profile customizer interface is able to support most customization registration requirements. However, it
is not required that all utilities that register customization objects with a profile implement this interface.
SQL/OLB applications will be able to run and leverage all implementation-specific customization objects reg-
istered with a profile, regardless of whether or not they were registered by a profile customizer. The primary
benefit of conforming to the profile customizer interface is to be able to take advantage of existing and future
automated profile customization utilities that are able to load, call and manipulate profile customizers.
©ISO/IEC 2015 – All rights reserved Use of SQL in programs written in Java 7

---------------------- Page: 13 ----------------------
ISO/IEC TR 19075-3:2015(E)
DTR 19075-3:2014(E)
3.4 Profile customization overview
3.4.2 Profile customization utilities
Profile customizers can be instantiated and used by automated general-purpose profile customization utilities.
An implementation might include a command-line based tool that serves as a customization utility prototype.
In addition to a command line-based utility, other useful customization utilities might include:
— GUI-based IDEs used to drag-and-drop customizations into profiles.
— Tight integration of customization utilities with SQL-implementations to automatically customize the
profiles loaded into the SQL-server.
— Background “SQL/OLB installer” process used as administrative tool to discover and customize SQL/OLB
applications for available SQL-schemas.
NOTE 2 — Implementors are encouraged to implement utilities using these and other ideas. Making such tools publically available
will greatly benefit and facilitate the SQL/OLB binary-portability effort.
3.5 Examples
3.5.1 Example of Profile generation and naming
Suppose we have the following file, Bar.SQLJ, which defines package COM.foo, and contains three clause>s associated with two s.
package COM.foo;
#sql context MyContext;
public class Bar
{
 public static void doSQL(MyContext ctx) throws SQLException
 {
   // 1: explicit context
   #sql [ctx] { UPDATE TAB1 SET COL1 = COL1 + 2 };
   // 2: implicit context
   #sql { INSERT INTO TAB2 VALUES(3, 'Hello there') };
   // 3: explicit context again
   #sql [ctx] { DELETE FROM TAB1 WHERE COL1 > 500 };
 }
}
Two profiles are created for this file; they are named COM.foo.Bar_SJProfile0 and COM.foo.Bar_SJProfile1.
COM.foo.Bar_SJProfile0 contains information describing s 1 and 3, and is stored in a
file called Bar_SJProfile0.ser. Com.foo.Bar_SJProfile1 describes clause 2, and is stored in file
Bar_SJProfile1.ser.
3.5.2 Example of a JAR manifest file
Working again with the file Bar.SQLJ from the last example, if the Bar application were packaged for
deployment as a JAR file, the JAR's manifest can be used by SQL/OLB customization utilities to locate the
8  SQL Embedded in Programs Using the the Java™ Programming Language ©ISO/IEC 2015 – All rights reserved

---------------------- Page: 14 ----------------------
ISO/IEC TR 19075-3:2015(E)
DTR 19075-3:2014(E)
3.5 Examples
application's profile files. To allow that use, the profile section of the manifest file would have the following
entries:
— Name: COM/foo/Bar_SJProfile0.ser SQLJProfile: TRUE
— Name: COM/foo/Bar_SJProfile1.ser SQLJProfile: TRUE
3.5.3 Host variables
The following query contains host variable :x (which is the Java variable, Java field, or parameter x visible
in the scope containing the query):
SELECT COL1, COL2 FROM TABLE1 WHERE :x > COL3
3.5.4 Host expressions
Host expressions are evaluated from left to right and can cause side effects. For example:
SELECT COL1, COL2 FROM TABLE1 WHERE :(x++) > COL3
Host expressions are always passed to and retrieved from the SQL-server using pure value semantics. For
instance, in the above example, the value of x++ is determined prior to statement execution and its determined
value is the value that is passed to the SQL-server for statement execution.
SELECT COL1, COL2 FROM TABLE1 WHERE :(x[--i]) > COL3
In the above example, prior to statement execution, the value of i is decremented by 1 (one) and then the value
of the i-th element of x is determined and passed to the SQL-server for statement execution.
Consider the following example of an SQL/PSM :
SET :(z[i++]) = :(x[i++]) + :(y[i++])
Assume that i has an initial value of 1 (one). Host expressions are evaluated in lexical order.
Therefore, the array index used to determine the location in the array z is 1 (one), after which the value of i
is incremented by 1 (one). Conseqently, the array index used to determine the location in the array x is 2, after
which the value of i is incremented by 1 (one). As a result, the array index used to determine the location in
the array y is 3, after which the value of i is incremented by 1 (one). The value of i in the Java space is now
4. The statement is then executed. After statement execution, the output value is assigned to z[1].
Assignments to output host expressions are also performed in lexical order. For example, consider the following
call to an SQL-invoked procedure foo that returns the values 2 and 3.
CALL foo( :OUT x, :OUT x )
After execution, x has the value 3.
©ISO/IEC 2015 – All rights reserved Use of SQL in programs written in Java 9

---------------------- Page: 15 ----------------------
ISO/IEC TR 19075-3:2015(E)
DTR 19075-3:2014(E)
3.5 Examples
3.5.5 SQL/OLB clauses
The following SQL/OLB clause is permitted to appear wherever a Java statement can legally appear and its
purpose is to delete all of the rows in the table named TAB:
#sql { DELETE FROM TAB };
The following Java method, when invoked, inserts its arguments into an SQL table. The method body consists
of an SQL/OLB executable clause containing the host expressions x, y, and z.
void m (int x, String y, float z) throws SQLException
{
  #sql { INSERT INTO TAB1 VALUES (:x, :y, :z ) };
}
The following method selects the address of the person whose name is specified by the input host expression
name and then retrieves an associated address from the assumed table PEOPLE, with columns NAME and
ADDRESS, into the output host expressions addr, where it is then permitted to be used, for example, in a call
to System.out.println:
void print_address (String name) throws SQLException
{
  String addr;
  #sql { SELECT ADDRESS INTO :addr
FROM PEOPLE
WHERE :name = NAME };
}
3.5.6 Connection contexts
In the following SQL/OLB clause, the connection context is the value of the Java variable myconn.
#sql [myconn] { SELECT ADDRESS INTO :addr
FROM PEOPLE
WHERE :name = NAME } ;
The following illustrates an SQL/OLB connection clause that defines a connection context class named
“Inventory”:
#sql context Inventory;
3.5.7 Default connection context
If an invocation of an SQL/OLB translator indicates that the default connection context class is class Green,
then all SQL/OLB clauses that use the default connection will be translated as if they used the explicit connection
context object Green.getDefaultContext( ). For example, the following two SQL/OLB clauses are
equivalent if the default connection context class is class Green:
10  SQL Embedded in Programs Using the the Java™ Programming Language ©ISO/IEC 2015 – All rights reserved

---------------------- Page: 16 ----------------------
ISO/IEC TR 19075-3:2015(E)
DTR 19075-3:2014(E)
3.5 Examples
#sql { UPDATE TAB SET COL = :x };
#sql [Green.getDefaultContext()] { UPDATE TAB SET COL = :x };
Programs are permitted to install a connection context object as the default connection by calling setDefault-
Context. For example:
Green.setDefaultContext(new Green(argv[0], autoCommit));
argv[0] is assumed to contain a URL. autoCommit is a boolean flag that is true if auto commit mode
should be on, and false otherwise.
3.5.8 Iterators
3.5.8.1 Positional bindings to columns
The following is an example of an iterator class declaration that binds by position. It declares an iterator class
called ByPos, with two columns of types String and int.
#sql public iterator ByPos (String, int);
Assume a table PEOPLE with columns FULLNAME and BIRTHYEAR:
CREATE TABLE PEOPLE ( FULLNAME VARCHAR(50),
BIRTHYEAR NU
...