ISO/IEC 19075-3:2021
(Main)Information technology — Guidance for the use of database language SQL — Part 3: SQL embedded in programs using the JavaTM programming language
Information technology — Guidance for the use of database language SQL — Part 3: SQL embedded in programs using the JavaTM programming language
This document describes the support for the use of SQL within programs written in Java. This document 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 — Recommandations pour l'utilisation du langage de base de données SQL — Partie 3: SQL intégré dans des programmes utilisant le langage de programmation de JavaTM
General Information
Relations
Buy Standard
Standards Content (Sample)
INTERNATIONAL ISO/IEC
STANDARD 19075-3
First edition
2021-08
Information technology — Guidance
for the use of database language
SQL —
Part 3:
SQL embedded in programs using the
TM
Java programming language
Technologies de l'information — Recommandations pour l'utilisation
du langage de base de données SQL —
Partie 3: SQL intégré dans des programmes utilisant le langage de
TM
programmation de Java
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: +41 22 749 01 11
Email: copyright@iso.org
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
1
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-invokedproced
...
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
TM
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: +41 22 749 01 11
Email: copyright@iso.org
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
1
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
...
Questions, Comments and Discussion
Ask us and Technical Secretary will try to provide an answer. You can facilitate discussion about the standard in here.