ISO/IEC PRF 19075-3

Information technology -- Guidance for the use of database language SQL

Titre manque

General Information

• Status: Published
• 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/WG 3 - Database language

RELATIONS

• Revised: ISO/IEC TR 19075-3:2015 - Information technology -- Database languages -- SQL Technical Reports
• Effective Date: 23-Apr-2020

Standards Content (sample)

INTERNATIONAL ISO/IEC
STANDARD 19075-3
First edition
Information technology — Guidance
for the use of database language
SQL —
Part 3:
SQL embedded in programs using the
Java programming language
PROOF/ÉPREUVE
Reference number
ISO/IEC 19075-3:2021(E)
ISO/IEC 2021
---------------------- Page: 1 ----------------------
ISO/IEC 19075-3:2021(E)
COPYRIGHT PROTECTED DOCUMENT
© ISO/IEC 2021

All rights reserved. Unless otherwise specified, or required in the context of its implementation, 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
CP 401 • Ch. de Blandonnet 8
CH-1214 Vernier, Geneva
Phone: [phone]
Email: [e-mail]
Website: www.iso.org
Published in Switzerland
ii © ISO/IEC 2021 – All rights reserved
---------------------- Page: 2 ----------------------
ISO/IEC19075-3:2021(E)
Contents Page

Foreword......................................................................................v

Introduction..................................................................................vii

1 Scope....................................................................................1

2 Normativereferences....................................................................2

3 Termsanddefinitions....................................................................3

4 UseofSQLinprogramswritteninJava...................................................4

4.1 ContextofSQLprogramswritteninJava....................................................4

4.2 Designgoals............................................................................4

4.3 AdvantagesofSQL/OLBoverJDBC.........................................................4

4.4 ConsistencywithexistingembeddedSQLlanguages...........................................5

4.5 Profilecustomizationoverview............................................................5

4.5.1 Introductiontoprofilecustomization.......................................................5

4.5.2 Profilecustomizationprocess.............................................................6

4.5.3 Profilecustomizationutilities.............................................................7

4.6 Examples..............................................................................7

4.6.1 Exampleofprofilegenerationandnaming...................................................7

4.6.2 ExampleofaJARmanifestfile.............................................................7

4.6.3 Host variables..........................................................................8

4.6.4 Hostexpressions........................................................................8

4.6.5 SQL/OLBclauses........................................................................8

4.6.6 Connection contexts.....................................................................9

4.6.7 Defaultconnectioncontext................................................................9

4.6.8 Iterators..............................................................................10

4.6.8.1 Positionalbindingstocolumns..........................................................10

4.6.8.2 Namedbindingstocolumns............................................................10

4.6.8.3 Providingnamesforcolumnsofqueries..................................................11

4.6.9 InvokingSQL-invokedroutines...........................................................12

4.6.10 UsingmultipleSQL/OLBcontextsandconnections..........................................12

4.6.11 SQLexecutioncontrolandstatus.........................................................13

4.6.12 Multiplejava.sql.ResultSetobjectsfromSQL-invokedprocedurecalls......................14

4.6.13 CreatinganSQL/OLBiteratorobjectfromajava.sql.ResultSetobject.......................15

4.6.14 Obtainingajava.sql.ResultSetobjectfromaniteratorobject..............................15

4.6.15 Workingwithuser-definedtypes.........................................................16

4.6.16 Batching..............................................................................17

4.6.17 Example program......................................................................17

4.6.18 Hostvariabledefinition.................................................................18

Bibliography................................................................................20

©ISO/IEC2021–Allrightsreserved iii
---------------------- Page: 3 ----------------------
ISO/IEC19075-3:2021(E)

Index........................................................................................21

iv ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 4 ----------------------
ISO/IEC19075-3:2021(E)
Foreword

ISO(theInternationalOrganizationforStandardization)andIEC(theInternationalElectrotechnical

Commission)formthespecializedsystemforworldwidestandardization.Nationalbodiesthatare

membersofISOorIECparticipateinthedevelopmentofInternationalStandardsthroughtechnical

committeesestablishedbytherespectiveorganizationtodealwithparticularfieldsoftechnicalactivity.

ISOandIECtechnicalcommitteescollaborateinfieldsofmutualinterest.Otherinternationalorganizations,

governmentalandnon-governmental,inliaisonwithISOandIEC,alsotakepartinthework.

Theproceduresusedtodevelopthisdocumentandthoseintendedforitsfurthermaintenanceare

describedintheISO/IECDirectives,Part1.Inparticular,thedifferentapprovalcriterianeededforthe

differenttypesofdocumentshouldbenoted.Thisdocumentwasdraftedinaccordancewiththeeditorial

rulesoftheISO/IECDirectives,Part2(seewww.iso.org/directivesorwww.iec.ch/mem-
bers_experts/refdocs).

Attentionisdrawntothepossibilitythatsomeoftheelementsofthisdocumentmaybethesubjectof

patentrights.ISOandIECshallnotbeheldresponsibleforidentifyinganyorallsuchpatentrights.Details

ofanypatentrightsidentifiedduringthedevelopmentofthedocumentwillbeintheIntroductionand/or

ontheISOlistofpatentdeclarationsreceived(seewww.iso.org/patents),ortheIEClistofpatent

declarationsreceived(seepatents.iec.ch).

Anytradenameusedinthisdocumentisinformationgivenfortheconvenienceofusersanddoesnot

constituteanendorsement.

Foranexplanationofthevoluntarynatureofstandards,themeaningofISOspecifictermsandexpressions

relatedtoconformityassessment,aswellasinformationaboutISO’sadherencetotheWorldTrade

Organization(WTO)principlesintheTechnicalBarrierstoTrade(TBT)seewww.iso.org/iso/fore-

word.html.IntheIEC,seewww.iec.ch/understanding-standards.

ThisdocumentwaspreparedbyTechnicalCommitteeISO/IECJTC1,Informationtechnology,Subcom-

mitteeSC32,Datamanagementandinterchange.
ThisfirsteditionofISO/IEC19075-3cancelsandreplacesISO/IECTR19075-3:2015.

ThisdocumentisintendedtobeusedinconjunctionwiththefollowingeditionsofthepartsoftheISO/IEC

9075series:
— ISO/IEC9075-1,sixtheditionorlater;
— ISO/IEC9075-2,sixtheditionorlater;
— ISO/IEC9075-3,sixtheditionorlater;
— ISO/IEC9075-4,seventheditionorlater;
— ISO/IEC9075-9,fiftheditionorlater;
— ISO/IEC9075-10,fiftheditionorlater;
— ISO/IEC9075-11,fiftheditionorlater;
— ISO/IEC9075-13,fiftheditionorlater;
— ISO/IEC9075-14,sixtheditionorlater;
— ISO/IEC9075-15,secondeditionorlater;
— ISO/IEC9075-16,firsteditionorlater.
©ISO/IEC2021–Allrightsreserved v
---------------------- Page: 5 ----------------------
ISO/IEC19075-3:2021(E)
AlistofallpartsintheISO/IEC19075seriescanbefoundontheISOandIECwebsites.

Anyfeedbackorquestionsonthisdocumentshouldbedirectedtotheuser’snationalstandardsbody.A

completelistingofthesebodiescanbefoundatwww.iso.org/members.htmlandwww.iec.ch/-
national-committees.
vi ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 6 ----------------------
ISO/IEC19075-3:2021(E)
Introduction
Theorganizationofthisdocumentisasfollows:
1) Clause1,“Scope”,specifiesthescopeofthisdocument.

2) Clause2,“Normativereferences”,identifiesadditionalstandardsthat,throughreferenceinthis

document,constituteprovisionsofthisdocument.

3) Clause3,“Termsanddefinitions”,definesthetermsanddefinitionsusedinthisdocument.

4) Clause4,“UseofSQLinprogramswritteninJava”,providesatutorialontheembeddingofSQL

expressionsandstatementsinprogramswrittenintheJava™ programminglanguage.

1Java™isthetrademarkofaproductsuppliedbyOracle.Thisinformationisgivenfortheconvenienceofusersofthisdocument

anddoesnotconstituteanendorsementbyISOorIECoftheproductnamed.
©ISO/IEC2021–Allrightsreserved vii
---------------------- Page: 7 ----------------------
INTERNATIONAL STANDARD ISO/IEC 19075-3:2021(E)
Informationtechnology—GuidancefortheuseofdatabaselanguageSQL—
Part3:
SQLembeddedinprogramsusingtheJava™programminglanguage
1 Scope
ThisdocumentdescribesthesupportfortheuseofSQLwithinprogramswritteninJava.
ThisdocumentdiscussesthefollowingfeaturesoftheSQLlanguage:
— TheembeddingofSQLexpressionsandstatementsinprogramswrittenintheJavaprogramming
language.
©ISO/IEC2021–Allrightsreserved 1
---------------------- Page: 8 ----------------------
ISO/IEC19075-3:2021(E)
2 Normativereferences

Thefollowingdocumentsarereferredtointhetextinsuchawaythatsomeoralloftheircontentconsti-

tutesrequirementsofthisdocument.Fordatedreferences,onlytheeditioncitedapplies.Forundated

references,thelatesteditionofthereferenceddocument(includinganyamendments)applies.

ISO/IEC9075-1,Informationtechnology—Databaselanguages—SQL—Part1:Framework
(SQL/Framework)
ISO/IEC9075-10,Informationtechnology—Databaselanguages—SQL—Part10:ObjectLanguage
Bindings(SQL/OLB)

JavaCommunityProcess.TheJava™LanguageSpecification[online].JavaSE13Edition.Redwood

Shores,California,USA:Oracle,Availableathttps://docs.oracle.com/javase/specs/jls/-

se13/jls13.pdf

JavaCommunityProcess.JDBC™4.3Specification[online].Edition4.3.RedwoodShores,California,

USA:Oracle,Availableathttps://download.oracle.com/otn-pub/jcp/-
jdbc-4_3-mrel3-eval-spec/jdbc4.3-fr-spec.pdf
2 ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 9 ----------------------
ISO/IEC19075-3:2021(E)
3 Termsanddefinitions
Forthepurposesofthisdocument,thetermsanddefinitionsgiveninISO/IEC9075-1apply.

ISOandIECmaintainterminologicaldatabasesforuseinstandardizationatthefollowingaddresses:

— IECElectropedia:availableathttp://www.electropedia.org/
— ISOOnlinebrowsingplatform:availableathttp://www.iso.org/obp
©ISO/IEC2021–Allrightsreserved 3
---------------------- Page: 10 ----------------------
ISO/IEC19075-3:2021(E)
4 UseofSQLinprogramswritteninJava
4.1 ContextofSQLprogramswritteninJava

TherequirementsforthematerialdiscussedinthisdocumentshallbeasspecifiedinISO/IEC9075-1

andISO/IEC9075-10.
4.2 Designgoals

ThefollowingitemsrepresentthemajordesignfeaturesofISO/IEC9075-1.ISO/IEC9075-10,Java,and

JDBCallspecifyrequirementsforthematerialdiscussedinthisdocument.

— Provideaconcise,legiblemechanismforembeddingSQL-statementsinaprogramthatotherwise

conformstoJava.
— SyntacticandsemanticcheckofSQL-statementspriortoprogramexecution.
SQL/OLBcanuseanimplementation-definedmechanismattranslatetimetocheckembeddedSQL-
statementstomakesurethattheyaresyntacticallyandsemanticallycorrect.
— AllowthesyntaxandsemanticsofSQL-statementstobelocation-independent.
ThesyntaxandsemanticsofSQL-statementsinanSQL/OLBprogramdonotdependontheconfigu-
rationunderwhichSQL/OLBisrunning.ThismakesitpossibletoimplementSQL/OLBprograms
thatrunontheclient,intheSQL-server,orinamiddletier.

— ProvidefacilitiesthatenabletheprogrammertomovebetweentheSQL/OLBandJDBCenvironments

bysharingasingleSQL-connectioninbothenvironments.

— ProvideforbinaryportabilityoftranslatedandcompiledJavaSQL-clientapplicationssuchthatthey

canbeusedtransparentlywithmultipleSQL-servers.Inaddition,binaryportabilityprofilesallow

forcustomizationandoptimizationofSQL-statementswithinanSQL/OLBapplication.
4.3 AdvantagesofSQL/OLBoverJDBC

JDBCprovidesacomplete,low-levelSQLinterfacefromJavatoSQL-implementations.SQL/OLBisdesigned

tofillacomplementaryrolebyprovidingahigher-levelprogramminginterfacetoSQL-implementations

insuchamannerastofreetheprogrammerfromthetediousandcomplexprogramminginterfacesfound

inlower-levelAPIs.
Thefollowingaresomemajordifferencesbetweenthetwo:
— SQL/OLBsourceprogramsaresmallerthanequivalentJDBCprogramssincethetranslatorcan
implicitlyhandlemanyofthetediousprogrammingchoresthatdynamicinterfacesrequire.

— SQL/OLBprogramscantype-checkSQLcodeattranslatetimeusinganimplementation-dependent

mechanism.JDBC,beingacompletelydynamicAPI,cannot.
4 ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 11 ----------------------
ISO/IEC19075-3:2021(E)
4.3 AdvantagesofSQL/OLBoverJDBC

— SQL/OLBprogramsallowdirectembeddingofJavahostexpressionswithinSQL-statements.JDBC

requiresaseparatecallstatementforeachbindvariableandspecifiesthebindingbypositionnumber.

— SQL/OLBenforcesstrongtypingofqueryoutputsandvaluesreturnedandallowstypecheckingon

calls.JDBCpassesvaluestoandfromSQLwithoutcompiletimetypechecking.

— SQL/OLBprovidessimplifiedrulesforinvokingSQL-invokedroutines.JDBCrequiresagenericcall

toanSQL-invokedroutine,fun,tohavethefollowingsyntax:
prepStmt.prepareCall("{call fun(...)}"); // For SQL-invoked procedures
prepStmt.prepareCall("{? = call fun(...)}"); // For SQL-invoked functions
SQL/OLBprovidessimplifiednotations:
#sql { CALL fun(...) }; // SQL-invoked procedure
// Declare x
...
#sql x = { VALUES(fun(...)) }; // SQL-invoked function
// VALUES is an SQL construct
4.4 ConsistencywithexistingembeddedSQLlanguages

ProgramminglanguagescontainingembeddedSQLarecalledhostlanguages.Javadiffersfromthetradi-

tionalhostlanguages(Ada,C,COBOL,Fortran,MUMPS(M),Pascal,PL/I)inwaysthatsignificantlyaffect

itsembeddingofSQL.

— Javahasautomaticstoragemanagement(alsoknownas“garbagecollection”)thatsimplifiesthe

managementofstoragefordataretrievedfromSQL-implementations.

— AllJavatypesrepresentingcompositedata,anddataofvaryingsizes,haveadistinguishedvalue

null,whichcanbeusedtorepresenttheSQLNULLvalue.ThisgivesJavaprogramsanalternative
totheindicatorvariablesthatarepartoftheinterfacestootherhostlanguages.

— Javaisdesignedtosupportprogramsthatareautomaticallyheterogeneouslyportable(alsocalled

“superportable”orsimply“downloadable”).That,alongwithJava’stypesystemofclassesand

interfaces,enablescomponentsoftware.Inparticular,anSQL/OLBtranslator,writteninJava,can

callcomponentsthatarespecializedbySQL-implementations,inordertoleveragetheexisting

authorization,schemachecking,typechecking,transactional,andrecoverycapabilitiesthataretra-

ditionalofSQL-implementations,andtogeneratecodeoptimizedforparticularSQL-implementations.

— Javaisdesignedforbinaryportabilityinheterogeneousnetworks,whichpromisestoenablebinary

portabilityforapplicationsthatuseSQL.

— SQL/OLBextendsthetraditionalconceptofembeddedhostvariablesbyallowinggeneralizedhost

expressions.
4.5 Profilecustomizationoverview
4.5.1 Introductiontoprofilecustomization

Thissubclausedescribeshowimplementation-specific“customized”SQLexecutioncontrolcanbeadded

toSQL/OLBapplications.TheSQL/OLBruntimeframeworkusesthefollowinginterfaces:
©ISO/IEC2021–Allrightsreserved 5
---------------------- Page: 12 ----------------------
ISO/IEC19075-3:2021(E)
4.5 Profilecustomizationoverview
— SQLJ.runtime.profile.RTStatementtoexecuteSQL-statements.
— SQLJ.runtime.profile.RTResultSettodescribequeryresults.

— SQLJ.runtime.profile.ConnectedProfiletocreateRTStatementobjectscorrespondingtoparticular

SQL-statements.
AnSQL-implementationisabletocontrolSQLexecutionbyprovidinganimplementationofthe

RTStatement,RTResultSet,andConnectedProfileinterfaces.AnSQL-implementationisabletoredirect

controltoitsowncodebyregisteringcustomizationhookswiththeapplicationprofiles.

Forexample,iftheclientconnectstoSQL-serverA,thenacustomizationthatunderstandsSQL-server

A’ssystemwillbeused.IftheclientconnectstoSQL-serverB,thenSQL-serverB’scustomizationwillbe

used.Intheabsenceofaconnectionspecificcustomization,thedefaultJDBCbasedcustomizationwill

beused.Liketheprofileobject,customizationobjectsareserializable.Thisallowsthecustomization

statetobestoredandrestoredwiththeprofile.Inthismanner,animplementation-dependentdeployment

toolisabletoloadtheprofile,inspectandprecompiletheSQL-statementsitcontains,registeran

appropriatecustomization,andstoretheprofileinpersistentstorage.Thenatapplicationruntime,the

profileandtheregisteredimplementation-dependentcustomizationwillbothberestored,andthecus-

tomizationwillbeusedtoexecutetheSQL-statements.
4.5.2 Profilecustomizationprocess

Theprofilecustomizationprocessistheactofregisteringprofilecustomizationobjectswiththeprofile(s)

associatedwithanapplication.Theprofilecustomizationprocesscanbegeneralizedtothefollowing

steps:
1) DiscovertheprofileobjectswithinaJARfile.
2) Foreachprofile,deserializetheprofileobjectfromtheappropriateJARentry.
3) CreateanSQL-connectionwithwhichtheprofilewillbecustomized.
4) Createandregisteraprofilecustomizationwiththeprofile.
5) Serializethecustomizedprofilebacktopersistentstorage.
6) RecreatetheJARcontentsusingthecustomizedserializedprofiles.

Oftheabovesteps,onlyStep4)islikelytochangefromSQL-implementationtoSQL-implementation.

WhileStep3)isimplementation-dependent,itcanbedoneusingaparameterizedtoolandJDBC.The

restofthestepsinvolveactionsthatcanbeperformedbyanygenericutilitywithoutspecificknowledge

ofthecustomizationbeingperformed.

Theactofcreatingandregisteringacustomizationobjectwithaprofile(step4above)isabstractly

definedbytheJavainterfaceSQLJ.runtime.profile.util.ProfileCustomizer.Theintentofdefiningthis

interfaceistoallowSQL-implementationstoconcentrateonwritingprofilecustomizersandcustomization

objects(step4above),whiletoolsandapplicationimplementationsconcentrateonwritinggenerictools

thatapplycustomizerstoapplicationprofiles(steps1–3and5–6above).

Theprofilecustomizerinterfaceisabletosupportmostcustomizationregistrationrequirements.However,

itisnotrequiredthatallutilitiesthatregistercustomizationobjectswithaprofileimplementthisinterface.

SQL/OLBapplicationswillbeabletorunandleverageallimplementation-specificcustomizationobjects

registeredwithaprofile,regardlessofwhetherornottheywereregisteredbyaprofilecustomizer.The

primarybenefitofconformingtotheprofilecustomizerinterfaceistobeabletotakeadvantageof

existingandfutureautomatedprofilecustomizationutilitiesthatareabletoload,callandmanipulate

profilecustomizers.
6 ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 13 ----------------------
ISO/IEC19075-3:2021(E)
4.5 Profilecustomizationoverview
4.5.3 Profilecustomizationutilities

Profilecustomizerscanbeinstantiatedandusedbyautomatedgeneral-purposeprofilecustomization

utilities.AnSQL-implementationmightincludeacommand-linebasedtoolthatservesasacustomization

utilityprototype.Inadditiontoacommandline-basedutility,otherusefulcustomizationutilitiesmight

include:
— GUI-basedIDEsusedtodrag-and-dropcustomizationsintoprofiles.

— TightintegrationofcustomizationutilitieswithSQL-implementationstoautomaticallycustomize

theprofilesloadedintotheSQL-server.

— Background“SQL/OLBinstaller”processusedasadministrativetooltodiscoverandcustomize

SQL/OLBapplicationsforavailableSQL-schemas.

NOTE1—Implementorsareencouragedtoimplementutilitiesusingtheseandotherideas.Makingsuchtoolspublicly

availablewillgreatlybenefitandfacilitatetheSQL/OLBbinary-portabilityeffort.
4.6 Examples
4.6.1 Exampleofprofilegenerationandnaming

Supposethereexiststhefollowingfile,Bar.SQLJ,whichdefinespackageCOM.foo,andcontainsthree

sassociatedwithtwos.
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 };
}

Twoprofilesarecreatedforthisfile;theyarenamedCOM.foo.Bar_SJProfile0andCOM.foo.Bar_SJPro-

file1.COM.foo.Bar_SJProfile0containsinformationdescribings1and3,andis

storedinafilecalledBar_SJProfile0.ser.Com.foo.Bar_SJProfile1describesclause2,andisstoredin

fileBar_SJProfile1.ser.
4.6.2 ExampleofaJARmanifestfile

WorkingagainwiththefileBar.SQLJfromthelastexample,iftheBarapplicationwerepackagedfor

deploymentasaJARfile,theJAR’smanifestcanbeusedbySQL/OLBcustomizationutilitiestolocatethe

application’sprofilefiles.Toallowthatuse,theprofilesectionofthemanifestfilewouldhavethefollowing

entries:
— Name:COM/foo/Bar_SJProfile0.serSQLJProfile:TRUE
©ISO/IEC2021–Allrightsreserved 7
---------------------- Page: 14 ----------------------
ISO/IEC19075-3:2021(E)
4.6 Examples
— Name:COM/foo/Bar_SJProfile1.serSQLJProfile:TRUE
4.6.3 Hostvariables

Thefollowingquerycontainshostvariable:x(whichistheJavavariable,Javafield,orparameterxvisible

inthescopecontainingthequery):
SELECT COL1, COL2 FROM TABLE1 WHERE :x > COL3
4.6.4 Hostexpressions
Hostexpressionsareevaluatedfromlefttorightandcancausesideeffects.Forexample:
SELECT COL1, COL2 FROM TABLE1 WHERE :(x++) > COL3

HostexpressionsarealwayspassedtoandretrievedfromtheSQL-serverusingpurevaluesemantics.

Forinstance,intheaboveexample,thevalueofx++isdeterminedpriortostatementexecutionandits

determinedvalueisthevaluethatispassedtotheSQL-serverforstatementexecution.
SELECT COL1, COL2 FROM TABLE1 WHERE :(x[--i]) > COL3

Intheaboveexample,priortostatementexecution,thevalueofiisdecrementedby1(one)andthen

thevalueofthei-thelementofxisdeterminedandpassedtotheSQL-serverforstatementexecution.

ConsiderthefollowingexampleofanSQL/PSM:
SET :(z[i++]) = :(x[i++]) + :(y[i++])
Assumethatihasaninitialvalueof1(one).Hostexpressionsareevaluatedinlexicalorder.

Therefore,thearrayindexusedtodeterminethelocationinthearrayzis1(one),afterwhichthevalue

ofiisincrementedby1(one).Consequently,thearrayindexusedtodeterminethelocationinthearray

xis2,afterwhichthevalueofiisincrementedby1(one).Asaresult,thearrayindexusedtodetermine

thelocationinthearrayyis3,afterwhichthevalueofiisincrementedby1(one).Thevalueofiinthe

Javaspaceisnow4.Thestatementisthenexecuted.Afterstatementexecution,theoutputvalueisassigned

toz[1].

Assignmentstooutputhostexpressionsarealsoperformedinlexicalorder.Forexample,considerthe

followingcalltoanSQL-invokedprocedurefoothatreturnsthevalues2and3.
CALL foo( :OUT x, :OUT x )
Afterexecution,xhasthevalue3.
4.6.5 SQL/OLBclauses

ThefollowingSQL/OLBclauseispermittedtoappearwhereveraJavastatementcanlegallyappearand

itspurposeistodeletealloftherowsinthetablenamedTAB:
#sql { DELETE FROM TAB };

ThefollowingJavamethod,wheninvoked,insertsitsargumentsintoanSQLtable.Themethodbody

consistsofanSQL/OLBexecutableclausecontainingthehostexpressionsx,y,andz.
8 ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 15 ----------------------
ISO/IEC19075-3:2021(E)
4.6 Examples
void m (int x, String y, float z) throws SQLException
#sql { INSERT INTO TAB1 VALUES (:x, :y, :z ) };
Thefollowingmethodselectstheaddressofthepersonwhosenameisspecifiedbytheinputhost

expressionnameandthenretrievesanassociatedaddressfromtheassumedtablePEOPLE,withcolumns

NAMEandADDRESS,intotheoutputhostexpressionsaddr,whereitisthenpermittedtobeused,for

example,inacalltoSystem.out.println:
void print_address (String name) throws SQLException
String addr;
#sql { SELECT ADDRESS INTO :addr
FROM PEOPLE
WHERE :name = NAME };
4.6.6 Connectioncontexts

InthefollowingSQL/OLBclause,theconnectioncontextisthevalueoftheJavavariablemyconn.

#sql [myconn] { SELECT ADDRESS INTO :addr
FROM PEOPLE
WHERE :name = NAME } ;

ThefollowingillustratesanSQL/OLBconnectionclausethatdefinesaconnectioncontextclassnamed

“Inventory”:
#sql context Inventory;
4.6.7 Defaultconnectioncontext

IfaninvocationofanSQL/OLBtranslatorindicatesthatthedefaultconnectioncontextclassisclassGreen,

thenallSQL/OLBclausesthatusethedefaultconnectionwillbetranslatedasiftheyusedtheexplicit

connectioncontextobjectGreen.getDefaultContext( ).Forexample,thefollowingtwoSQL/OLBclauses

areequivalentifthedefaultconnectioncontextclassisclassGreen:
#sql { UPDATE TAB SET COL = :x };
#sql [Green.getDefaultContext()] { UPDATE TAB SET COL = :x };

ProgramsarepermittedtoinstallaconnectioncontextobjectasthedefaultconnectionbycallingsetDe-

faultContext.Forexample:
Green.setDefaultContext(new Green(argv[0], autoCommit));

argv[0]isassumedtocontainaURL.autoCommitisaBooleanflagthatistrueifautocommitmodeshould

beon,andfalseotherwise.
©ISO/IEC2021–Allrightsreserved 9
---------------------- Page: 16 ----------------------
ISO/IEC19075-3:2021(E)
4.6 Examples
4.6.8 Iterators
4.6.8.1Positionalbindingstocolumns

Thefollowingisanexampleofaniteratorclassdeclarationthatbindsbyposition.Itdeclaresaniterator

classcalledByPos,withtwocolumnsoftypesStringandint.
#sql public iterator ByPos (String, int);
AssumeatablePEOPLEwithcolumnsFULLNAMEandBIRTHYEAR:
CREATE TABLE PEOPLE ( FULLNAME VARCHAR(50),
BIRTHYEAR NUMERIC(4,0) )

AniteratorobjectoftypeByPosisusedinconjunctionwithaFETCH...INTOstatementtoretrievedata

fromtablePEOPLE,asillustratedinthefollowingexample:
ByPos positer; // declare iterator object
String name = null;
int year = 0;
// populate it
#sql positer = { SELECT FULLNAME, BIRTHYEAR
FROM PEOPLE };
#sql { FETCH :positer INTO :name, :year };
while ( !positer.endFetch() )
{
System.out.println(name + " was born in " + year);
#sql { FETCH :positer INTO :name, :year };
}

ThepredicatemethodendFetch()oftheiteratorobjectreturnstrueifnomorerowsareavailablefrom

theiterator(specifically,itbecomestruefollowingthefirstFETCHthatreturnsnodata).

ThefirstSQL/OLBclauseintheblockaboveeffectivelyexecutesitsqueryandconstructsaniterator

objectcontainingtheresultsetreturnedbythequery,andassignsittovariablepositer.Thetypeofthe

iteratorobjectisderivedfromtheassignmenttarget,whichisoftypeByPos.

ThesecondSQL/OLBclauseinthatblockcontainsaFETCH...INTOstatement.TheSQL/OLBtranslator

checksthatthetypesofhostvariablesintheINTOclausematchthepositionallycorrespondingtypesof

theiteratorcolumns.ThetypesoftheSQLcolumnsinthequerymustbeconvertibletothetypesofthe

positionallycorrespondingiteratorcolumns,accordingtotheSQLtoJavatypemappingofSQL/OLB.

ThoseconversionsarestaticallycheckedatSQL/OLBtranslationtimeifanSQL-connectiontoanexemplar

schemaisprovidedtothetranslator.
4.6.8.2Namedbindingstocolumns

Thefollowingisanexampleofaniteratorclassdeclarationthatbindsbyname.Itdeclaresaniterator

classcalledByName,thenamedaccessormethodsfullNAMEandbirthYEARofwhichcorrespondtothe

columnsFULLNAMEandBIRTHYEAR:
#sql public iterator ByName (String fullNAME,
int birthYEAR);
10 ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 17 ----------------------
ISO/IEC19075-3:2021(E)
4.6 Examples
Thatiteratorclasscanthenbeusedasfollows:
ByName namiter; // define iterator object
#sql namiter = { SELECT FULLNAME, BIRTHYEAR
FROM PEOPLE };
String s;
int i;
// advances to next row
while ( namiter.next() )
{
i = namiter.birthYEAR(); // returns column named BIRTHYEAR
s = namiter.fullNAME(); // returns column named FULLNAME
System.out.println(s + " was born in "+i);
}

Inthisexample,thefirstSQL/OLBclauseconstructsaniteratorobjectoftypeByName,asthatisthetype

oftheassignmenttargetinthatclause.ThatiteratorhasgeneratedaccessormethodsbirthYEAR()and

fullNAME()thatreturnthedatafromtheresultsetcolumnswiththosenames.

Thenamesofthegeneratedaccessormethodsareanexactcase-sensitivematchwiththeirdefinitionson

theiteratordeclarationclause.Matchingaspecificaccessormethodtoaspecificcolumnnameinthe

SELECTlistexpressionsisperformedusingacase-insensitivematch.

TwocolumnnamesthatdifferonlyinthecaseofoneormorecharactersmustusetheSQLASclauseto

avoidambiguity,evenifoneorbothofthosecolumnnamesarespecifiedusingdelimitedidentifiers.

Methodnext()advancestheiteratorobjecttosuccessiverowsoftheresultset.Itreturnstrueifanext

rowisavailableandfalseifitfailstoretrieveanextrowbecausetheiteratorcontainsnomorerows.

AJavacompilerwilldetecttypemismatcherrorsintheusesofnamedaccessormethods.Additionally,

ifaconnectiontoanexemplarschemaisprovidedattranslatetime,thentheSQL/OLBtranslatorwill

staticallycheckthevalidityofthetypesandnamesoftheiteratorcolumnsagainsttheSQLqueries

associatedwithit.
4.6.8.3Providingnamesforcolumnsofqueries

Iftheexpressionsselectedbyaqueryareunnamed,orhaveSQLnamesthatarenotlegalJavaidentifiers,

thenSQLcolumnaliasescanbeusedtonamethem.Consideratablenamed"Trouble!"withacolumn
called"Not a legal Java identifier":
CREATE TABLE "Trouble!" (
"Not a legal Java identifier" VARCHAR(10),
col2 FLOAT )
ThefollowinglinegeneratesaniteratorclasscalledxY.
#sql iterator xY (String x, double Y);

TheSQL/OLBclauseinthefollowingblockusescolumnaliasestoassociatethatcolumn’snamewithan

expressioninthequery:
xY it;
#sql it = { SELECT "Not a legal Java identifier" AS "x",
COL2 * COL2 AS Y
FROM "Trouble!" };
©ISO/IEC2021–Allrightsreserved 11
---------------------- Page: 18 ----------------------
ISO/IEC19075-3:2021(E)
4.6 Examples
while (it.next()) { System.out.println(it.x() + it.Y());
}
Thefirstlinedeclaresalocalvariableofthatiteratorclass.

Thesecondlineinitializesthatvariabletocontainaresultsetobtainedfromthespecifiedquery.

Thewhile( )loopcallsthenamedaccessormethodsoftheiteratortoobtainandprintdatafromits

rows.
4.6.9 InvokingSQL-invokedroutines

AnSQL/OLBexecutableclause,appearingasaJavastatement,cancallanSQL-invokedprocedureby

meansoftheSQLCALLstatement.Forexample:
#sql { CALL SOME_PROC(:INOUT myarg) };
SupportforinvokingSQL-invokedroutinesisnotrequiredforconformancetoCoreSQL/OLB.

SQL-invokedprocedurescanhaveIN,OUT,orINOUTparameters.Intheabovecase,thevalueofhost

variablemyargischangedbytheexecutionofthatclause.
AnSQL/OLBexecutableclausecaninvokeanSQL-invokedfunctionbymeansoftheSQLVALUE
...