ISO/IEC 19075-4:2021
(Main)Information technology — Guidance for the use of database language SQL — Part 4: Routines and types using the Java™ programming language
Information technology — Guidance for the use of database language SQL — Part 4: Routines and types using the Java™ programming language
This document provides a tutorial of SQL routines and types using the Java™ programming language. This document discusses the following features of the SQL Language: — The use of routines written in the Java programming language within SQL expressions and statements. — The use of user-defined types written in the Java programming language within SQL expressions and statements.
Technologies de l'information — Recommandations pour l'utilisation du langage de base de données SQL — Partie 4: Routines et types utilisant le langage de programmation Java™
General Information
Relations
Buy Standard
Standards Content (Sample)
INTERNATIONAL ISO/IEC
STANDARD 19075-4
First edition
2021-08
Information technology — Guidance
for the use of database language
SQL —
Part 4:
Routines and types using the Java™
programming language
Technologies de l'information — Recommandations pour l'utilisation
du langage de base de données SQL —
Partie 4: Routines et types utilisant le langage de programmation
Java™
Reference number
ISO/IEC 19075-4:2021(E)
©
ISO/IEC 2021
---------------------- Page: 1 ----------------------
ISO/IEC 19075-4: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-4:2021(E)
Contents Page
Foreword.v
Introduction.vii
1 Scope.1
2 Normativereferences.2
3 Termsanddefinitions.3
4 Routinestutorial.4
4.1 Contextofroutines.4
4.2 Technical components.4
4.3 Overview.5
4.4 ExampleJavamethods:regionandcorrectStates.5
4.5 InstallingregionandcorrectStatesinSQL.6
4.6 DefiningSQLnamesforregionandcorrectStates.7
4.7 AJavamethodwithoutputparameters:bestTwoEmps.8
4.8 ACREATEPROCEDUREbest2forbestTwoEmps.10
4.9 Callingthebest2procedure.10
4.10 AJavamethodreturningaresultset:orderedEmps.11
4.11 ACREATEPROCEDURErankedEmpsfororderedEmps.12
4.12 CallingtherankedEmpsprocedure.13
4.13 OverloadingJavamethodnamesandSQLnames.14
4.14 Javamainmethods.15
4.15 JavamethodsignaturesintheCREATEstatements.16
4.16 NullargumentvaluesandtheRETURNSNULLclause.17
4.17 Static variables.19
4.18 DroppingSQLnamesofJavamethods.20
4.19 RemovingJavaclassesfromSQL.20
4.20 ReplacingJavaclassesinSQL.21
4.21 Visibility.22
4.22 Exceptions.22
4.23 Deploymentdescriptors.23
4.24 Paths.25
4.25 Privileges.27
4.26 InformationSchema.28
5 Types tutorial.29
5.1 Overview.29
5.2 ExampleJavaclasses.29
5.3 InstallingAddressandAddress2LineinanSQLsystem.31
5.4 CREATETYPEforAddressandAddress2Line.31
5.5 MultipleSQLtypesforasingleJavaclass.33
©ISO/IEC2021–Allrightsreserved iii
---------------------- Page: 3 ----------------------
ISO/IEC19075-4:2021(E)
5.6 Collapsingsubclasses.33
5.7 GRANTandREVOKEstatementsfordatatypes.35
5.8 Deploymentdescriptorsforclasses.35
5.9 UsingJavaclassesasdatatypes.36
5.10 SELECT,INSERT,andUPDATE.37
5.11 ReferencingJavafieldsandmethodsinSQL.38
5.12 Extendedvisibilityrules.38
5.13 LogicalrepresentationofJavainstancesinSQL.39
5.14 Staticmethods.40
5.15 Static fields.41
5.16 Instance-updatemethods.41
5.17 SubtypesinSQL/JRTdata.43
5.18 Referencestofieldsandmethodsofnullinstances.44
5.19 OrderingofSQL/JRTdata.45
Bibliography.48
Index.49
iv ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 4 ----------------------
ISO/IEC19075-4: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-4cancelsandreplacesISO/IECTR19075-4: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-4:2021(E)
AlistofallpartsintheISO/IEC19075seriescanbefoundontheISOandIECwebsites.
Anyfeedbackorquestionsonthisdocumentshouldbedirectedtotheuser’snationalstandardsbody.A
completelistingofthesebodiescanbefoundatwww.iso.org/members.htmlandwww.iec.ch/-
national-committees.
vi ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 6 ----------------------
ISO/IEC19075-4:2021(E)
Introduction
Theorganizationofthisdocumentisasfollows:
1) Clause1,“Scope”,specifiesthescopeofthisdocument.
2) Clause2,“Normativereferences”,identifiesadditionalstandardsthat,throughreferenceinthis
document,constituteprovisionsofthisdocument.
3) Clause3,“Termsanddefinitions”,definesthetermsanddefinitionsusedinthisdocument.
1
4) Clause4,“Routinestutorial”,providesatutorialontheuseofroutineswrittenintheJava™ program-
minglanguagewithinSQLexpressionsandstatements.
5) Clause5,“Typestutorial”,providesatutorialontheuseofuser-definedtypeswrittenintheJava
programminglanguagewithinSQLexpressionsandstatements.
1Java™isthetrademarkofaproductsuppliedbyOracle.Thisinformationisgivenfortheconvenienceofusersofthisdocument
anddoesnotconstituteanendorsementbyISOorIECoftheproductnamed.
©ISO/IEC2021–Allrightsreserved vii
---------------------- Page: 7 ----------------------
ISO/IEC19075-4:2021(E)
viii ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 8 ----------------------
INTERNATIONAL STANDARD ISO/IEC 19075-4:2021(E)
Informationtechnology—GuidancefortheuseofdatabaselanguageSQL—
Part4:
RoutinesandtypesusingtheJava™programminglanguage
1 Scope
ThisdocumentprovidesatutorialofSQLroutinesandtypesusingtheJava™programminglanguage.
ThisdocumentdiscussesthefollowingfeaturesoftheSQLLanguage:
— TheuseofroutineswrittenintheJavaprogramminglanguagewithinSQLexpressionsandstatements.
— Theuseofuser-definedtypeswrittenintheJavaprogramminglanguagewithinSQLexpressions
andstatements.
©ISO/IEC2021–Allrightsreserved 1
---------------------- Page: 9 ----------------------
ISO/IEC19075-4:2021(E)
2 Normativereferences
Thefollowingdocumentsarereferredtointhetextinsuchawaythatsomeoralloftheircontentconsti-
tutesrequirementsofthisdocument.Fordatedreferences,onlytheeditioncitedapplies.Forundated
references,thelatesteditionofthereferenceddocument(includinganyamendments)applies.
ISO/IEC9075-2,Informationtechnology—Databaselanguages—SQL—Part2:Foundation
(SQL/Foundation)
ISO/IEC9075-13,Informationtechnology—Databaselanguages—SQL—Part13:SQLRoutinesand
TypesUsingtheJava™ProgrammingLanguage(SQL/JRT)
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: 10 ----------------------
ISO/IEC19075-4: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: 11 ----------------------
ISO/IEC19075-4:2021(E)
4 Routinestutorial
4.1 Contextofroutines
TherequirementsforthematerialdiscussedinthisdocumentshallbeasspecifiedinISO/IEC9075-1,
ISO/IEC9075-13,Java,andJDBC.
4.2 Technicalcomponents
ISO/IEC9075-13includesthefollowing:
— Newbuilt-inprocedures.
• SQLJ.INSTALL_JAR—toloadasetofJavaclassesinanSQLsystem.
• SQLJ.REPLACE_JAR—tosupersedeasetofJavaclassesinanSQLsystem.
• SQLJ.REMOVE_JAR—todeleteapreviouslyinstalledsetofJavaclasses.
• SQLJ.ALTER_JAVA_PATH—tospecifyapathfornameresolutionwithinJavaclasses.
— Newbuilt-inschema.
Thebuilt-inschemanamedSQLJisassumedtobeinallcatalogsofanSQLsystemthatimplements
theSQL/JRTfacility,andtocontainallofthebuilt-inproceduresoftheSQL/JRTfacility.
— ExtensionsofthefollowingSQLstatements:
• CREATEPROCEDURE/FUNCTION—tospecifyanSQLnameforaJavamethod.
• DROPPROCEDURE/FUNCTION—todeletetheSQLnameofaJavamethod.
• CREATETYPE—tospecifyanSQLnameforaJavaclass.
• DROPTYPE—todeletetheSQLnameofaJavaclass.
• GRANT—togranttheUSAGEprivilegeonJavaJARs.
• REVOKE—torevoketheUSAGEprivilegeonJavaJARs.
— ConventionsforreturningvaluesofOUTandINOUTparameters,andforreturningSQLresultsets.
— Newformsofreference:Qualifiedreferencestothefieldsandmethodsofcolumnswhosedatatypes
aredefinedonJavaclasses.
— AdditionalviewsandcolumnsintheInformationSchema.
4 ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 12 ----------------------
ISO/IEC19075-4:2021(E)
4.3 Overview
4.3 Overview
ThistutorialshowsaseriesofexampleJavaclasses,indicateshowtheycanbeinstalled,andshowshow
theirstatic,publicmethodscanbereferencedwithSQL/JRTfacilitiesinanSQL-environment.
TheexampleJavamethodsassumeanSQLtablenamedEMPS,withthefollowingcolumns:
— NAME—theemployee’sname.
— ID—theemployee’sidentification.
— STATE—thestateinwhichtheemployeeislocated.
— SALES—theamountoftheemployee’ssales.
— JOBCODE—thejobcodeoftheemployee.
Thetabledefinitionis:
CREATE TABLE emps (
name VARCHAR(50),
id CHARACTER(5),
state CHARACTER(20),
sales DECIMAL (6,2),
jobcode INTEGER );
Theexampleclassesandmethodsare:
— Routines1.region—AJavamethodthatmapsaUSstatecodetoaregionnumber.Thismethoddoes
notuseSQLinternally.
— Routines1.correctStates—AJavamethodthatperformsanSQLUPDATEstatementtocorrectthe
spellingofstatecodes.Theoldandnewspellingsarespecifiedbyinput-modeparameters.
— Routines2.bestTwoEmps—AJavamethodthatdeterminesthetoptwoemployeesbytheirsales,and
returnsthecolumnsofthosetwoemployeerowsasoutput-modeparametervalues.Thismethod
createsanSQLresultsetandprocessesitinternally.
— Routines3.orderedEmps—AJavamethodthatcreatesanSQLresultsetconsistingofselected
employeerowsorderedbythesalescolumn,andreturnsthatresultsettotheclient.
— Over1.isOddandOver2.isOdd—ContrivedJavamethodstoillustrateoverloadingrules.
— Routines4.job1andRoutines5.job2—Javamethodsthatreturnastringvaluecorrespondingto
anintegerjobcodevalue.Thesemethodsillustratethetreatmentofnullarguments.
— Routines6.job3—AnotherJavamethodthatreturnsastringvaluecorrespondingtoaninteger
jobcodevalue.ThismethodillustratesthebehaviorofstaticJavavariables.
Unlessotherwisenoted,themethodsthatinvokeSQLuseJDBC.Oneofthemethodsisshowninboth
aversionusingJDBCandaversionusingSQL/OLB.TheotherscouldalsobecodedwithSQL/OLB.
Itisassumedthattheimportstatementsimport java.sql.*;andjava.math.*;havebeenincludedin
allclasses.
4.4 ExampleJavamethods:regionandcorrectStates
ThisclauseshowsanexampleJavaclass,Routines1,withtwosimplemethods.
©ISO/IEC2021–Allrightsreserved 5
---------------------- Page: 13 ----------------------
ISO/IEC19075-4:2021(E)
4.4 ExampleJavamethods:regionandcorrectStates
— Theint-valuedstaticmethodregioncategorizes9statesinto3geographicregions,returningan
integerindicatingtheregionassociatedwithavalidstateorthrowinganexceptionforinvalidstates.
ThismethodwillbecalledasafunctioninSQL.
— ThevoidmethodcorrectStatesupdatestheEMPStabletocorrectspellingerrorsinthestatecolumn.
ThismethodwillbecalledasaprocedureinSQL.
public class Routines1 {
//An int method that will be called as a function
public static int region(String s) throws SQLException {
if (s.equals("MN") || s.equals("VT") || s.equals("NH")) return 1;
else if (s.equals("FL") || s.equals("GA") || s.equals("AL")) return 2;
else if (s.equals("CA") || s.equals("AZ") || s.equals("NV")) return 3;
else throw new SQLException("Invalid state code", "38001");
}
//A void method that will be called as a stored procedure
public static void correctStates (String oldSpelling, String newSpelling)
throws SQLException {
Connection conn = DriverManager.getConnection ("jdbc:default:connection");
PreparedStatement stmt = conn.prepareStatement
("UPDATE emps SET state = ? WHERE state = ?");
stmt.setString(1, newSpelling);
stmt.setString(2, oldSpelling);
stmt.executeUpdate();
stmt.close();
conn.close();
return;
}
}
4.5 InstallingregionandcorrectStatesinSQL
ThesourcecodeforJavaclassessuchasRoutines1willnormallybeinoneormoreJavafiles(i.e.,,files
withfile-type“java”).Whentheyarecompiled(usingthejavaccompilecommand),theresultingcode
willbeinoneormoreclassfiles(i.e.,,fileswithfile-type“class”).Asetofclassfilesisthencollectedinto
aJavaJAR,whichisaZIP-codedcollectionoffiles.
TouseJavaclassesinSQL,aJARcontainingthemisloadedintotheSQLsystembycallingtheSQL
SQLJ.INSTALL_JARprocedure.TheSQLJ.INSTALL_JARprocedureisanewbuilt-inSQLprocedurethat
makesthecollectionofJavaclassescontainedinaspecifiedJARavailableforuseinthecurrentSQLcatalog.
Forexample,assumethattheaboveRoutines1classhasbeenassembledintoaJARwithlocalfilename
“~/classes/Routines1.jar”:
SQLJ.INSTALL_JAR('file:~/classes/Routines1.jar', 'routines1_jar', 0)
— ThefirstparameteroftheSQLJ.INSTALL_JARprocedureisacharacterstringspecifyingtheURLof
thegivenJAR.Thisparameterisneverfoldedtouppercase.
— ThesecondparameteroftheSQLJ.INSTALL_JARprocedureisacharacterstringthatwillbeusedas
thenameoftheJARintheSQLsystem.TheJARnameisanSQLqualifiedname,andfollowsSQL
conventionsforqualifiednames.
TheJARnamespecifiedasthesecondparameteroftheSQLJ.INSTALL_JARprocedureidentifiesthe
JARwithintheSQLsystem.Thatis,theJARnamespecifiedisusedonlyinSQL,andhasnothingto
dowiththecontentsoftheJARitself.TheJARnameisusedinthefollowingcontexts,whichare
describedinlaterclauses:
• AsaparameteroftheSQLJ.REMOVE_JARandSQLJ.REPLACE_JARprocedures.
6 ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 14 ----------------------
ISO/IEC19075-4:2021(E)
4.5 InstallingregionandcorrectStatesinSQL
• AsaqualifierofJavaclassnamesinSQLCREATEPROCEDURE/FUNCTIONstatements.
• AsanoperandoftheextendedSQLGRANTandREVOKEstatements.
• AsaqualifierofJavaclassnamesinSQLCREATETYPEstatements.
TheJARnamemayalsobeusedinfollow-onfacilitiesfordownloadingJARsfromtheSQLsystem.
— JARscanalsocontaindeploymentdescriptors,whichspecifyimplicitactionstobetakenbythe
SQLJ.INSTALL_JARandSQLJ.REMOVE_JARprocedures.ThethirdparameteroftheSQLJ.INSTALL_JAR
procedureisanintegerthatspecifieswhetherornot(indicatedbynon-zeroorzerovalues,respec-
tively)theSQLJ.INSTALL_JARprocedureisexpectedtoexecutetheactionsspecifiedbyadeployment
descriptorintheJAR.
ThenameoftheINSTALL_JARprocedureisqualifiedwiththeschemanameSQLJ.Allbuilt-inprocedures
oftheSQL/JRTfacilityaredefinedtobecontainedinthatbuilt-inschema.TheSQLJschemaisassumed
tobepresentineachcatalogofanSQLsystemthatimplementstheSQL/JRTfacility.
ThefirsttwoparametersofSQLJ.INSTALL_JARarecharacterstrings,soiftheyarespecifiedasliterals,
singlequotesareused,notthedoublequotesusedforSQLdelimitedidentifiers.
TheactionsoftheSQLJ.INSTALL_JARprocedureareasfollows:
— ObtaintheJARdesignatedbythefirstparameter.
— ExtracttheclassfilesthatitcontainsandinstallthemintothecurrentSQLschema.
— RetainacopyoftheJARitself,andassociateitwiththevalueofthesecondparameter.
— Ifthethirdparameterisnon-zero,thenperformtheactionsspecifiedbythedeploymentdescriptor
oftheJAR.
AfteraJARhasbeeninstalledwiththeSQLJ.INSTALL_JARprocedure,thestaticmethodsoftheclasses
containedinthatJARcanbereferencedintheCREATEPROCEDURE/FUNCTIONstatement,asdescribed
inthenextSubclause.
4.6 DefiningSQLnamesforregionandcorrectStates
BeforeaJavamethodcanbecalledinSQL,thatmethodmusthaveanSQLname.Thisisdonewithnew
optionsontheSQLCREATEPROCEDURE/FUNCTIONstatement.Forexample:
CREATE PROCEDURE correct_states(old CHARACTER(20), new CHARACTER(20))
MODIFIES SQL DATA
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'routines1_jar:Routines1.correctStates';
CREATE FUNCTION region_of(state CHARACTER(20)) RETURNS INTEGER
NO SQL
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'routines1_jar:Routines1.region';
TheCREATEPROCEDUREandCREATEFUNCTIONstatementsspecifySQLnamesandJavamethodsig-
naturesfortheJavamethodsspecifiedintheEXTERNALNAMEclauses.Theformatofthemethodnames
intheexternalnameclauseconsistsoftheJARnamethatwasspecifiedintheSQLJ.INSTALL_JARprocedure
followedbytheJavamethodname,fullyqualifiedwiththepackagename(s)(ifany)andclassname.
TheCREATEPROCEDUREforcorrect_statesspecifiestheclauseMODIFIESSQLDATA.Thisindicates
thatthespecifiedJavamethodmodifies(viaINSERT,UPDATE,orDELETE)datainSQLtables.TheCREATE
FUNCTIONforregion_ofspecifiesNOSQL.ThisindicatesthatthespecifiedJavamethodperformsno
SQLoperations.
©ISO/IEC2021–Allrightsreserved 7
---------------------- Page: 15 ----------------------
ISO/IEC19075-4:2021(E)
4.6 DefiningSQLnamesforregionandcorrectStates
OtherclausesthatcanbespecifiedareREADSSQLDATA,whichindicatesthatthespecifiedJavamethod
reads(throughSELECT)datainSQLtables,butdoesnotmodifySQLdata,andCONTAINSSQL,which
indicatesthatthespecifiedmethodinvokesSQLoperations,butneitherreadsnormodifiesSQLdata.
ThealternativeCONTAINSSQListhedefault.
TheSQLprocedureandfunctionnamesthataredefinewithsuchCREATEPROCEDURE/FUNCTION
statementsareusedasnormalSQLprocedureandfunctionnames:
SELECT name, region_of(state) AS region
FROM emps
WHERE region_of(state) = 3;
CALL correct_states ('GEO', 'GA');
MultipleSQLnamesforthesameJavamethodcanbedefined:
CREATE PROCEDURE state_correction(old CHARACTER(20), new CHARACTER(20))
MODIFIES SQL DATA
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'routines1_jar:Routines1.correctStates';
CREATE FUNCTION state_region(state CHARACTER(20)) RETURNS INTEGER
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'routines1_jar:Routines1.region';
ThevariousSQLfunctionandprocedurenamesforaJavamethodcanbeusedequivalently:
SELECT name, state_region(state) AS region
FROM emps
WHERE region_of(state) = 2;
CALL state_correction ('ORE', 'OR');
TheSQLnamesarenormal3-partSQLnames,andthefirsttwopartsofthe3-partnamesaredefaulted
asdefinedinSQLforCREATEPROCEDUREandCREATEFUNCTIONstatements.
ThereareotherconsiderationsfortheCREATEPROCEDURE/FUNCTIONstatement,dealingwith
parameterdatatypes,overloadednames,andprivileges,whichisdiscussedinlaterSubclauses.
4.7 AJavamethodwithoutputparameters:bestTwoEmps
TheparametersoftheregionandcorrectStatesmethodsareallinput-onlyparameters.Thisisthe
normalJavaparameterconvention.
SQLproceduresalsosupportparameterswithmodeOUTandINOUT.TheJavalanguagedoesnotdirectly
haveanotionofoutputparameters.SQL/JRTthereforeusesarraystoreturnoutputvaluesforparameters
ofJavamethods.Thatis,ifanIntegerparameteristobeusedtoreturnavaluetothecaller,thetypeof
thatparameterisspecifiedtobeInteger[ ],i.e.,anarrayofInteger.Suchanarraywillcontainonlyone
element:theinputvalueoftheparameteriscontainedinthatelementwhenthemethodiscalled,and
themethodsetsthevalueofthatelementtothedesiredoutputvalue.
Asseeninthefollowingclauses,thisuseofarraysforoutputparametersintheJavamethodsisvisible
onlytotheJavamethod.WhensuchamethodiscalledasanSQLprocedure,normalscalardataitems
aresuppliedasparameters.TheSQLsystemperformsthemappingbetweenthosescalardataitemsand
Javaarraysimplicitly.
ThefollowingJavamethodillustratesthewaythatoutputparametersarecodedinJava.Thismethod,
bestTwoEmps,returnsthename,id,region,andsalesofthetwoemployeesthathavethehighestsales
intheregionswithnumbershigherthanaparametervalue.Thatis,eachofthefirst8parametersisan
OUTparameter,andisthereforedeclaredtobeanarrayofthegiventype.
8 ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 16 ----------------------
ISO/IEC19075-4:2021(E)
4.7 AJavamethodwithoutputparameters:bestTwoEmps
ThefollowingversionofthebestTwoEmpsmethodusesSQL/OLBforstatementsthataccessSQL:
public class Routines2 {
public static void bestTwoEmps (
String[ ] n1, String[ ] id1, int[ ] r1, BigDecimal[ ] s1,
String[ ] n2, String[ ] id2, int[ ] r2, BigDecimal[ ] s2,
int regionParm) throws SQLException {
#sql iterator ByNames (String name, String id, int region, BigDecimal sales);
n1[0]= "****"; n2[0]= "****"; id1[0]= ""; id2[0]= "";
r1[0]=0; r2[0]=0; s1[0]= new BigDecimal(0); s2[0]= new BigDecimal(0);
ByNames r = null;
try {
#sql r = {SELECT name, id, region_of(state) AS region, sales
FROM emp
WHERE region_of(state) > :regionParm
AND sales IS NOT NULL
ORDER BY sales DESC};
if (r.next()) {
n1[0] = r.name();
id1[0] = r.id();
r1[0] = r.region();
s1[0] = r.sales();
}
else return;
if (r.next()) {
n2[0] = r.name();
id2[0] = r.id();
r2[0] = r.region();
s2[0] = r.sales();
}
else return;
} finally {
if (r != null) r.close();
}
}
}
NotethatsincetheaboveJavamethodusesSQL/OLBforSQLoperations,itdoesnothavetoexplicitly
obtainaconnectiontotheSQLsystem.Bydefault,SQL/OLBexecutesanySQLcontainedinaroutinein
thecontextoftheSQLstatementinvokingthatroutine.
Forcomparison,here’saversionofthebestTwoEmpsmethodusingJDBCinsteadofSQL/OLB:
public class Routines2 {
public static void bestTwoEmps (
String[ ] n1, String[ ] id1, int[ ] r1, BigDecimal[ ] s1,
String[ ] n2, String[ ] id2, int[ ] r2, BigDecimal[ ] s2,
int regionParm) throws SQLException {
n1[0]= "****"; n2[0]= "****"; id1[0]= ""; id2[0]= "";
r1[0]=0; r2[0]=0; s1[0]= new BigDecimal(0); s2[0]= new BigDecimal(0);
java.sql.PreparedStatement stmt = null;
try {
Connection conn = DriverManager.getConnection
("jdbc:default:connection");
stmt.conn.prepareStatement
("SELECT name, id, region_of(state) AS region, sales
FROM emp
WHERE region_of(state) > ?
AND sales IS NOT NULL
ORDER BY sales DESC");
stmt.setInt(1, regionParm)
ResultSet r = stmt.executeQuery();
©ISO/IEC2021–Allrightsreserved 9
---------------------- Page: 17 ----------------------
ISO/IEC19075-4:2021(E)
4.7 AJavamethodwithoutputparameters:bestTwoEmps
if (r.next()) {
n1[0] = r.getString("name");
id1[0] = r.getString("id");
r1[0] = r.getInt("region");
s1[0] = r.getBigDecimal("sales");
}
else return;
if (r.next()) {
n2[0] = r.getString("name");
id2[0] = r.getString("id");
r2[0] = r.getInt("region");
s2[0] = r.getBigDecimal("sales");
}
else return;
} finally {
if (stmt != null) stmt.close()
};
}
}
4.8 ACREATEPROCEDUREbest2forbestTwoEmps
AssumethattheSQLJ.INSTALL_JARprocedureiscalledforaJARcontainingtheRoutines2classwiththe
bestTwoEmpsmethod:
SQLJ.INSTALL_JAR ('file:~/classes/Routines2.jar', 'routines2_jar', 0)
Asindicatedpreviously,inordertocallamethodsuchasbestTwoEmpsinSQL,themethodmusthavean
SQLname,definedusingtheCREATEPROCEDUREstatement:
CREATE PROCEDURE best2 (
OUT n1 CHARACTER VARYING(50), OUT id1 CHARACTER VARYING(5), OUT r1 INTEGER,
OUT s1 DECIMAL(6,2),
OUT n2 CHARACTER VARYING(50), OUT id2 CHARACTER VARYING(5), OUT r2 INTEGER,
OUT s2 DECIMAL(6,2), region INTEGER)
READS SQL DATA
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'routines2_jar:Routines2.bestTwoEmps';
ForparametersthatarespecifiedtobeOUTorINOUT,thecorrespondingJavaparametermustbean
arrayofthecorrespondingdatatype.
4.9 Callingthebest2procedure
AftertheRoutines2classhasbeeninstalledinanSQLsystemandtheCREATEPROCEDUREexecutedfor
best2,thebestTwoEmpsmethodcanbecalledasifitwereanSQLstoredprocedure,withnormalconven-
tionsforOUTparameters.SuchacallcouldbewrittenwithembeddedSQL,CLI,ODBC,orJDBC.Thefol-
lowingisanexampleofsuchacallusingJDBC:
java.sql.CallableStatement stmt = conn.prepareCall(
"{call best2(?,?,?,?,?,?,?,?,?)}");
stmt.registerOutParameter(1, java.sql.Types.STRING);
stmt.registerOutParameter(2, java.sql.Types.STRING);
stmt.registerOutParameter(3, java.sql.Types.INTEGER);
stmt.registerOutParameter(4, java.sql.Types.DECIMAL);
10 ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 18 ----------------------
ISO/IEC19075-4:2021(E)
4.9 Callingthebest2procedure
stmt.registerOutParameter(5, java.sql.Types.STRING);
stmt.registerOutParameter(6, java.sql.Types.STRING);
stmt.registerOutParameter(7, java.sql.Types.INTEGER);
stmt.registerOutParameter(8, java.sql.Types.DECIMAL);
stmt.setInt(9, 3);
stmt.executeUpdate();
String n1 = stmt.getString(1);
String id1 = stmt.getString(2);
int r1 = stmt.getInt(3);
BigDecimal s1 = stmt.getBigDecimal(4);
String n2 = stmt.getString(5);
String id2 = stmt.getString(6);
int r2 = stmt.getInt(7);
BigDecimal s2 = stmt.getBigDecimal(8);
4.10 AJavamethodreturningaresultset:orderedEmps
SQLstoredprocedurescangenerateSQLresultsetsastheiroutput.AnSQLresultset(asdefinedinJDBC
andSQL)isanorderedsequenceofSQLrows.SQLresultsetsarenotprocessedasnormalfunctionresult
values,butareinsteadboundtocaller-specifiediteratorsorcursors,whicharesubsequentlyusedto
processtherowsoftheresultset.
ThefollowingJavamethod,orderedEmps,generatesanSQLresultsetandthenreturnsthatresultsetto
theclient
...
INTERNATIONAL ISO/IEC
STANDARD 19075-4
First edition
Information technology — Guidance
for the use of database language
SQL —
Part 4:
Routines and types using the Java™
programming language
PROOF/ÉPREUVE
Reference number
ISO/IEC 19075-4:2021(E)
©
ISO/IEC 2021
---------------------- Page: 1 ----------------------
ISO/IEC 19075-4: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-4:2021(E)
Contents Page
Foreword.v
Introduction.vii
1 Scope.1
2 Normativereferences.2
3 Termsanddefinitions.3
4 Routinestutorial.4
4.1 Contextofroutines.4
4.2 Technical components.4
4.3 Overview.5
4.4 ExampleJavamethods:regionandcorrectStates.5
4.5 InstallingregionandcorrectStatesinSQL.6
4.6 DefiningSQLnamesforregionandcorrectStates.7
4.7 AJavamethodwithoutputparameters:bestTwoEmps.8
4.8 ACREATEPROCEDUREbest2forbestTwoEmps.10
4.9 Callingthebest2procedure.10
4.10 AJavamethodreturningaresultset:orderedEmps.11
4.11 ACREATEPROCEDURErankedEmpsfororderedEmps.12
4.12 CallingtherankedEmpsprocedure.13
4.13 OverloadingJavamethodnamesandSQLnames.14
4.14 Javamainmethods.15
4.15 JavamethodsignaturesintheCREATEstatements.16
4.16 NullargumentvaluesandtheRETURNSNULLclause.17
4.17 Static variables.19
4.18 DroppingSQLnamesofJavamethods.20
4.19 RemovingJavaclassesfromSQL.20
4.20 ReplacingJavaclassesinSQL.21
4.21 Visibility.22
4.22 Exceptions.22
4.23 Deploymentdescriptors.23
4.24 Paths.25
4.25 Privileges.27
4.26 InformationSchema.28
5 Types tutorial.29
5.1 Overview.29
5.2 ExampleJavaclasses.29
5.3 InstallingAddressandAddress2LineinanSQLsystem.31
5.4 CREATETYPEforAddressandAddress2Line.31
5.5 MultipleSQLtypesforasingleJavaclass.33
©ISO/IEC2021–Allrightsreserved iii
---------------------- Page: 3 ----------------------
ISO/IEC19075-4:2021(E)
5.6 Collapsingsubclasses.33
5.7 GRANTandREVOKEstatementsfordatatypes.35
5.8 Deploymentdescriptorsforclasses.35
5.9 UsingJavaclassesasdatatypes.36
5.10 SELECT,INSERT,andUPDATE.37
5.11 ReferencingJavafieldsandmethodsinSQL.38
5.12 Extendedvisibilityrules.38
5.13 LogicalrepresentationofJavainstancesinSQL.39
5.14 Staticmethods.40
5.15 Static fields.41
5.16 Instance-updatemethods.41
5.17 SubtypesinSQL/JRTdata.43
5.18 Referencestofieldsandmethodsofnullinstances.44
5.19 OrderingofSQL/JRTdata.45
Bibliography.48
Index.49
iv ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 4 ----------------------
ISO/IEC19075-4: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-4cancelsandreplacesISO/IECTR19075-4: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-4:2021(E)
AlistofallpartsintheISO/IEC19075seriescanbefoundontheISOandIECwebsites.
Anyfeedbackorquestionsonthisdocumentshouldbedirectedtotheuser’snationalstandardsbody.A
completelistingofthesebodiescanbefoundatwww.iso.org/members.htmlandwww.iec.ch/-
national-committees.
vi ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 6 ----------------------
ISO/IEC19075-4:2021(E)
Introduction
Theorganizationofthisdocumentisasfollows:
1) Clause1,“Scope”,specifiesthescopeofthisdocument.
2) Clause2,“Normativereferences”,identifiesadditionalstandardsthat,throughreferenceinthis
document,constituteprovisionsofthisdocument.
3) Clause3,“Termsanddefinitions”,definesthetermsanddefinitionsusedinthisdocument.
1
4) Clause4,“Routinestutorial”,providesatutorialontheuseofroutineswrittenintheJava™ program-
minglanguagewithinSQLexpressionsandstatements.
5) Clause5,“Typestutorial”,providesatutorialontheuseofuser-definedtypeswrittenintheJava
programminglanguagewithinSQLexpressionsandstatements.
1Java™isthetrademarkofaproductsuppliedbyOracle.Thisinformationisgivenfortheconvenienceofusersofthisdocument
anddoesnotconstituteanendorsementbyISOorIECoftheproductnamed.
©ISO/IEC2021–Allrightsreserved vii
---------------------- Page: 7 ----------------------
ISO/IEC19075-4:2021(E)
viii ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 8 ----------------------
INTERNATIONAL STANDARD ISO/IEC 19075-4:2021(E)
Informationtechnology—GuidancefortheuseofdatabaselanguageSQL—
Part4:
RoutinesandtypesusingtheJava™programminglanguage
1 Scope
ThisdocumentprovidesatutorialofSQLroutinesandtypesusingtheJava™programminglanguage.
ThisdocumentdiscussesthefollowingfeaturesoftheSQLLanguage:
— TheuseofroutineswrittenintheJavaprogramminglanguagewithinSQLexpressionsandstatements.
— Theuseofuser-definedtypeswrittenintheJavaprogramminglanguagewithinSQLexpressions
andstatements.
©ISO/IEC2021–Allrightsreserved 1
---------------------- Page: 9 ----------------------
ISO/IEC19075-4:2021(E)
2 Normativereferences
Thefollowingdocumentsarereferredtointhetextinsuchawaythatsomeoralloftheircontentconsti-
tutesrequirementsofthisdocument.Fordatedreferences,onlytheeditioncitedapplies.Forundated
references,thelatesteditionofthereferenceddocument(includinganyamendments)applies.
ISO/IEC9075-2,Informationtechnology—Databaselanguages—SQL—Part2:Foundation
(SQL/Foundation)
ISO/IEC9075-13,Informationtechnology—Databaselanguages—SQL—Part13:SQLRoutinesand
TypesUsingtheJava™ProgrammingLanguage(SQL/JRT)
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: 10 ----------------------
ISO/IEC19075-4: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: 11 ----------------------
ISO/IEC19075-4:2021(E)
4 Routinestutorial
4.1 Contextofroutines
TherequirementsforthematerialdiscussedinthisdocumentshallbeasspecifiedinISO/IEC9075-1,
ISO/IEC9075-13,Java,andJDBC.
4.2 Technicalcomponents
ISO/IEC9075-13includesthefollowing:
— Newbuilt-inprocedures.
• SQLJ.INSTALL_JAR—toloadasetofJavaclassesinanSQLsystem.
• SQLJ.REPLACE_JAR—tosupersedeasetofJavaclassesinanSQLsystem.
• SQLJ.REMOVE_JAR—todeleteapreviouslyinstalledsetofJavaclasses.
• SQLJ.ALTER_JAVA_PATH—tospecifyapathfornameresolutionwithinJavaclasses.
— Newbuilt-inschema.
Thebuilt-inschemanamedSQLJisassumedtobeinallcatalogsofanSQLsystemthatimplements
theSQL/JRTfacility,andtocontainallofthebuilt-inproceduresoftheSQL/JRTfacility.
— ExtensionsofthefollowingSQLstatements:
• CREATEPROCEDURE/FUNCTION—tospecifyanSQLnameforaJavamethod.
• DROPPROCEDURE/FUNCTION—todeletetheSQLnameofaJavamethod.
• CREATETYPE—tospecifyanSQLnameforaJavaclass.
• DROPTYPE—todeletetheSQLnameofaJavaclass.
• GRANT—togranttheUSAGEprivilegeonJavaJARs.
• REVOKE—torevoketheUSAGEprivilegeonJavaJARs.
— ConventionsforreturningvaluesofOUTandINOUTparameters,andforreturningSQLresultsets.
— Newformsofreference:Qualifiedreferencestothefieldsandmethodsofcolumnswhosedatatypes
aredefinedonJavaclasses.
— AdditionalviewsandcolumnsintheInformationSchema.
4 ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 12 ----------------------
ISO/IEC19075-4:2021(E)
4.3 Overview
4.3 Overview
ThistutorialshowsaseriesofexampleJavaclasses,indicateshowtheycanbeinstalled,andshowshow
theirstatic,publicmethodscanbereferencedwithSQL/JRTfacilitiesinanSQL-environment.
TheexampleJavamethodsassumeanSQLtablenamedEMPS,withthefollowingcolumns:
— NAME—theemployee’sname.
— ID—theemployee’sidentification.
— STATE—thestateinwhichtheemployeeislocated.
— SALES—theamountoftheemployee’ssales.
— JOBCODE—thejobcodeoftheemployee.
Thetabledefinitionis:
CREATE TABLE emps (
name VARCHAR(50),
id CHARACTER(5),
state CHARACTER(20),
sales DECIMAL (6,2),
jobcode INTEGER );
Theexampleclassesandmethodsare:
— Routines1.region—AJavamethodthatmapsaUSstatecodetoaregionnumber.Thismethoddoes
notuseSQLinternally.
— Routines1.correctStates—AJavamethodthatperformsanSQLUPDATEstatementtocorrectthe
spellingofstatecodes.Theoldandnewspellingsarespecifiedbyinput-modeparameters.
— Routines2.bestTwoEmps—AJavamethodthatdeterminesthetoptwoemployeesbytheirsales,and
returnsthecolumnsofthosetwoemployeerowsasoutput-modeparametervalues.Thismethod
createsanSQLresultsetandprocessesitinternally.
— Routines3.orderedEmps—AJavamethodthatcreatesanSQLresultsetconsistingofselected
employeerowsorderedbythesalescolumn,andreturnsthatresultsettotheclient.
— Over1.isOddandOver2.isOdd—ContrivedJavamethodstoillustrateoverloadingrules.
— Routines4.job1andRoutines5.job2—Javamethodsthatreturnastringvaluecorrespondingto
anintegerjobcodevalue.Thesemethodsillustratethetreatmentofnullarguments.
— Routines6.job3—AnotherJavamethodthatreturnsastringvaluecorrespondingtoaninteger
jobcodevalue.ThismethodillustratesthebehaviorofstaticJavavariables.
Unlessotherwisenoted,themethodsthatinvokeSQLuseJDBC.Oneofthemethodsisshowninboth
aversionusingJDBCandaversionusingSQL/OLB.TheotherscouldalsobecodedwithSQL/OLB.
Itisassumedthattheimportstatementsimport java.sql.*;andjava.math.*;havebeenincludedin
allclasses.
4.4 ExampleJavamethods:regionandcorrectStates
ThisclauseshowsanexampleJavaclass,Routines1,withtwosimplemethods.
©ISO/IEC2021–Allrightsreserved 5
---------------------- Page: 13 ----------------------
ISO/IEC19075-4:2021(E)
4.4 ExampleJavamethods:regionandcorrectStates
— Theint-valuedstaticmethodregioncategorizes9statesinto3geographicregions,returningan
integerindicatingtheregionassociatedwithavalidstateorthrowinganexceptionforinvalidstates.
ThismethodwillbecalledasafunctioninSQL.
— ThevoidmethodcorrectStatesupdatestheEMPStabletocorrectspellingerrorsinthestatecolumn.
ThismethodwillbecalledasaprocedureinSQL.
public class Routines1 {
//An int method that will be called as a function
public static int region(String s) throws SQLException {
if (s.equals("MN") || s.equals("VT") || s.equals("NH")) return 1;
else if (s.equals("FL") || s.equals("GA") || s.equals("AL")) return 2;
else if (s.equals("CA") || s.equals("AZ") || s.equals("NV")) return 3;
else throw new SQLException("Invalid state code", "38001");
}
//A void method that will be called as a stored procedure
public static void correctStates (String oldSpelling, String newSpelling)
throws SQLException {
Connection conn = DriverManager.getConnection ("jdbc:default:connection");
PreparedStatement stmt = conn.prepareStatement
("UPDATE emps SET state = ? WHERE state = ?");
stmt.setString(1, newSpelling);
stmt.setString(2, oldSpelling);
stmt.executeUpdate();
stmt.close();
conn.close();
return;
}
}
4.5 InstallingregionandcorrectStatesinSQL
ThesourcecodeforJavaclassessuchasRoutines1willnormallybeinoneormoreJavafiles(i.e.,,files
withfile-type“java”).Whentheyarecompiled(usingthejavaccompilecommand),theresultingcode
willbeinoneormoreclassfiles(i.e.,,fileswithfile-type“class”).Asetofclassfilesisthencollectedinto
aJavaJAR,whichisaZIP-codedcollectionoffiles.
TouseJavaclassesinSQL,aJARcontainingthemisloadedintotheSQLsystembycallingtheSQL
SQLJ.INSTALL_JARprocedure.TheSQLJ.INSTALL_JARprocedureisanewbuilt-inSQLprocedurethat
makesthecollectionofJavaclassescontainedinaspecifiedJARavailableforuseinthecurrentSQLcatalog.
Forexample,assumethattheaboveRoutines1classhasbeenassembledintoaJARwithlocalfilename
“~/classes/Routines1.jar”:
SQLJ.INSTALL_JAR('file:~/classes/Routines1.jar', 'routines1_jar', 0)
— ThefirstparameteroftheSQLJ.INSTALL_JARprocedureisacharacterstringspecifyingtheURLof
thegivenJAR.Thisparameterisneverfoldedtouppercase.
— ThesecondparameteroftheSQLJ.INSTALL_JARprocedureisacharacterstringthatwillbeusedas
thenameoftheJARintheSQLsystem.TheJARnameisanSQLqualifiedname,andfollowsSQL
conventionsforqualifiednames.
TheJARnamespecifiedasthesecondparameteroftheSQLJ.INSTALL_JARprocedureidentifiesthe
JARwithintheSQLsystem.Thatis,theJARnamespecifiedisusedonlyinSQL,andhasnothingto
dowiththecontentsoftheJARitself.TheJARnameisusedinthefollowingcontexts,whichare
describedinlaterclauses:
• AsaparameteroftheSQLJ.REMOVE_JARandSQLJ.REPLACE_JARprocedures.
6 ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 14 ----------------------
ISO/IEC19075-4:2021(E)
4.5 InstallingregionandcorrectStatesinSQL
• AsaqualifierofJavaclassnamesinSQLCREATEPROCEDURE/FUNCTIONstatements.
• AsanoperandoftheextendedSQLGRANTandREVOKEstatements.
• AsaqualifierofJavaclassnamesinSQLCREATETYPEstatements.
TheJARnamemayalsobeusedinfollow-onfacilitiesfordownloadingJARsfromtheSQLsystem.
— JARscanalsocontaindeploymentdescriptors,whichspecifyimplicitactionstobetakenbythe
SQLJ.INSTALL_JARandSQLJ.REMOVE_JARprocedures.ThethirdparameteroftheSQLJ.INSTALL_JAR
procedureisanintegerthatspecifieswhetherornot(indicatedbynon-zeroorzerovalues,respec-
tively)theSQLJ.INSTALL_JARprocedureisexpectedtoexecutetheactionsspecifiedbyadeployment
descriptorintheJAR.
ThenameoftheINSTALL_JARprocedureisqualifiedwiththeschemanameSQLJ.Allbuilt-inprocedures
oftheSQL/JRTfacilityaredefinedtobecontainedinthatbuilt-inschema.TheSQLJschemaisassumed
tobepresentineachcatalogofanSQLsystemthatimplementstheSQL/JRTfacility.
ThefirsttwoparametersofSQLJ.INSTALL_JARarecharacterstrings,soiftheyarespecifiedasliterals,
singlequotesareused,notthedoublequotesusedforSQLdelimitedidentifiers.
TheactionsoftheSQLJ.INSTALL_JARprocedureareasfollows:
— ObtaintheJARdesignatedbythefirstparameter.
— ExtracttheclassfilesthatitcontainsandinstallthemintothecurrentSQLschema.
— RetainacopyoftheJARitself,andassociateitwiththevalueofthesecondparameter.
— Ifthethirdparameterisnon-zero,thenperformtheactionsspecifiedbythedeploymentdescriptor
oftheJAR.
AfteraJARhasbeeninstalledwiththeSQLJ.INSTALL_JARprocedure,thestaticmethodsoftheclasses
containedinthatJARcanbereferencedintheCREATEPROCEDURE/FUNCTIONstatement,asdescribed
inthenextSubclause.
4.6 DefiningSQLnamesforregionandcorrectStates
BeforeaJavamethodcanbecalledinSQL,thatmethodmusthaveanSQLname.Thisisdonewithnew
optionsontheSQLCREATEPROCEDURE/FUNCTIONstatement.Forexample:
CREATE PROCEDURE correct_states(old CHARACTER(20), new CHARACTER(20))
MODIFIES SQL DATA
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'routines1_jar:Routines1.correctStates';
CREATE FUNCTION region_of(state CHARACTER(20)) RETURNS INTEGER
NO SQL
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'routines1_jar:Routines1.region';
TheCREATEPROCEDUREandCREATEFUNCTIONstatementsspecifySQLnamesandJavamethodsig-
naturesfortheJavamethodsspecifiedintheEXTERNALNAMEclauses.Theformatofthemethodnames
intheexternalnameclauseconsistsoftheJARnamethatwasspecifiedintheSQLJ.INSTALL_JARprocedure
followedbytheJavamethodname,fullyqualifiedwiththepackagename(s)(ifany)andclassname.
TheCREATEPROCEDUREforcorrect_statesspecifiestheclauseMODIFIESSQLDATA.Thisindicates
thatthespecifiedJavamethodmodifies(viaINSERT,UPDATE,orDELETE)datainSQLtables.TheCREATE
FUNCTIONforregion_ofspecifiesNOSQL.ThisindicatesthatthespecifiedJavamethodperformsno
SQLoperations.
©ISO/IEC2021–Allrightsreserved 7
---------------------- Page: 15 ----------------------
ISO/IEC19075-4:2021(E)
4.6 DefiningSQLnamesforregionandcorrectStates
OtherclausesthatcanbespecifiedareREADSSQLDATA,whichindicatesthatthespecifiedJavamethod
reads(throughSELECT)datainSQLtables,butdoesnotmodifySQLdata,andCONTAINSSQL,which
indicatesthatthespecifiedmethodinvokesSQLoperations,butneitherreadsnormodifiesSQLdata.
ThealternativeCONTAINSSQListhedefault.
TheSQLprocedureandfunctionnamesthataredefinewithsuchCREATEPROCEDURE/FUNCTION
statementsareusedasnormalSQLprocedureandfunctionnames:
SELECT name, region_of(state) AS region
FROM emps
WHERE region_of(state) = 3;
CALL correct_states ('GEO', 'GA');
MultipleSQLnamesforthesameJavamethodcanbedefined:
CREATE PROCEDURE state_correction(old CHARACTER(20), new CHARACTER(20))
MODIFIES SQL DATA
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'routines1_jar:Routines1.correctStates';
CREATE FUNCTION state_region(state CHARACTER(20)) RETURNS INTEGER
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'routines1_jar:Routines1.region';
ThevariousSQLfunctionandprocedurenamesforaJavamethodcanbeusedequivalently:
SELECT name, state_region(state) AS region
FROM emps
WHERE region_of(state) = 2;
CALL state_correction ('ORE', 'OR');
TheSQLnamesarenormal3-partSQLnames,andthefirsttwopartsofthe3-partnamesaredefaulted
asdefinedinSQLforCREATEPROCEDUREandCREATEFUNCTIONstatements.
ThereareotherconsiderationsfortheCREATEPROCEDURE/FUNCTIONstatement,dealingwith
parameterdatatypes,overloadednames,andprivileges,whichisdiscussedinlaterSubclauses.
4.7 AJavamethodwithoutputparameters:bestTwoEmps
TheparametersoftheregionandcorrectStatesmethodsareallinput-onlyparameters.Thisisthe
normalJavaparameterconvention.
SQLproceduresalsosupportparameterswithmodeOUTandINOUT.TheJavalanguagedoesnotdirectly
haveanotionofoutputparameters.SQL/JRTthereforeusesarraystoreturnoutputvaluesforparameters
ofJavamethods.Thatis,ifanIntegerparameteristobeusedtoreturnavaluetothecaller,thetypeof
thatparameterisspecifiedtobeInteger[ ],i.e.,anarrayofInteger.Suchanarraywillcontainonlyone
element:theinputvalueoftheparameteriscontainedinthatelementwhenthemethodiscalled,and
themethodsetsthevalueofthatelementtothedesiredoutputvalue.
Asseeninthefollowingclauses,thisuseofarraysforoutputparametersintheJavamethodsisvisible
onlytotheJavamethod.WhensuchamethodiscalledasanSQLprocedure,normalscalardataitems
aresuppliedasparameters.TheSQLsystemperformsthemappingbetweenthosescalardataitemsand
Javaarraysimplicitly.
ThefollowingJavamethodillustratesthewaythatoutputparametersarecodedinJava.Thismethod,
bestTwoEmps,returnsthename,id,region,andsalesofthetwoemployeesthathavethehighestsales
intheregionswithnumbershigherthanaparametervalue.Thatis,eachofthefirst8parametersisan
OUTparameter,andisthereforedeclaredtobeanarrayofthegiventype.
8 ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 16 ----------------------
ISO/IEC19075-4:2021(E)
4.7 AJavamethodwithoutputparameters:bestTwoEmps
ThefollowingversionofthebestTwoEmpsmethodusesSQL/OLBforstatementsthataccessSQL:
public class Routines2 {
public static void bestTwoEmps (
String[ ] n1, String[ ] id1, int[ ] r1, BigDecimal[ ] s1,
String[ ] n2, String[ ] id2, int[ ] r2, BigDecimal[ ] s2,
int regionParm) throws SQLException {
#sql iterator ByNames (String name, String id, int region, BigDecimal sales);
n1[0]= "****"; n2[0]= "****"; id1[0]= ""; id2[0]= "";
r1[0]=0; r2[0]=0; s1[0]= new BigDecimal(0); s2[0]= new BigDecimal(0);
ByNames r = null;
try {
#sql r = {SELECT name, id, region_of(state) AS region, sales
FROM emp
WHERE region_of(state) > :regionParm
AND sales IS NOT NULL
ORDER BY sales DESC};
if (r.next()) {
n1[0] = r.name();
id1[0] = r.id();
r1[0] = r.region();
s1[0] = r.sales();
}
else return;
if (r.next()) {
n2[0] = r.name();
id2[0] = r.id();
r2[0] = r.region();
s2[0] = r.sales();
}
else return;
} finally {
if (r != null) r.close();
}
}
}
NotethatsincetheaboveJavamethodusesSQL/OLBforSQLoperations,itdoesnothavetoexplicitly
obtainaconnectiontotheSQLsystem.Bydefault,SQL/OLBexecutesanySQLcontainedinaroutinein
thecontextoftheSQLstatementinvokingthatroutine.
Forcomparison,here’saversionofthebestTwoEmpsmethodusingJDBCinsteadofSQL/OLB:
public class Routines2 {
public static void bestTwoEmps (
String[ ] n1, String[ ] id1, int[ ] r1, BigDecimal[ ] s1,
String[ ] n2, String[ ] id2, int[ ] r2, BigDecimal[ ] s2,
int regionParm) throws SQLException {
n1[0]= "****"; n2[0]= "****"; id1[0]= ""; id2[0]= "";
r1[0]=0; r2[0]=0; s1[0]= new BigDecimal(0); s2[0]= new BigDecimal(0);
java.sql.PreparedStatement stmt = null;
try {
Connection conn = DriverManager.getConnection
("jdbc:default:connection");
stmt.conn.prepareStatement
("SELECT name, id, region_of(state) AS region, sales
FROM emp
WHERE region_of(state) > ?
AND sales IS NOT NULL
ORDER BY sales DESC");
stmt.setInt(1, regionParm)
ResultSet r = stmt.executeQuery();
©ISO/IEC2021–Allrightsreserved 9
---------------------- Page: 17 ----------------------
ISO/IEC19075-4:2021(E)
4.7 AJavamethodwithoutputparameters:bestTwoEmps
if (r.next()) {
n1[0] = r.getString("name");
id1[0] = r.getString("id");
r1[0] = r.getInt("region");
s1[0] = r.getBigDecimal("sales");
}
else return;
if (r.next()) {
n2[0] = r.getString("name");
id2[0] = r.getString("id");
r2[0] = r.getInt("region");
s2[0] = r.getBigDecimal("sales");
}
else return;
} finally {
if (stmt != null) stmt.close()
};
}
}
4.8 ACREATEPROCEDUREbest2forbestTwoEmps
AssumethattheSQLJ.INSTALL_JARprocedureiscalledforaJARcontainingtheRoutines2classwiththe
bestTwoEmpsmethod:
SQLJ.INSTALL_JAR ('file:~/classes/Routines2.jar', 'routines2_jar', 0)
Asindicatedpreviously,inordertocallamethodsuchasbestTwoEmpsinSQL,themethodmusthavean
SQLname,definedusingtheCREATEPROCEDUREstatement:
CREATE PROCEDURE best2 (
OUT n1 CHARACTER VARYING(50), OUT id1 CHARACTER VARYING(5), OUT r1 INTEGER,
OUT s1 DECIMAL(6,2),
OUT n2 CHARACTER VARYING(50), OUT id2 CHARACTER VARYING(5), OUT r2 INTEGER,
OUT s2 DECIMAL(6,2), region INTEGER)
READS SQL DATA
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'routines2_jar:Routines2.bestTwoEmps';
ForparametersthatarespecifiedtobeOUTorINOUT,thecorrespondingJavaparametermustbean
arrayofthecorrespondingdatatype.
4.9 Callingthebest2procedure
AftertheRoutines2classhasbeeninstalledinanSQLsystemandtheCREATEPROCEDUREexecutedfor
best2,thebestTwoEmpsmethodcanbecalledasifitwereanSQLstoredprocedure,withnormalconven-
tionsforOUTparameters.SuchacallcouldbewrittenwithembeddedSQL,CLI,ODBC,orJDBC.Thefol-
lowingisanexampleofsuchacallusingJDBC:
java.sql.CallableStatement stmt = conn.prepareCall(
"{call best2(?,?,?,?,?,?,?,?,?)}");
stmt.registerOutParameter(1, java.sql.Types.STRING);
stmt.registerOutParameter(2, java.sql.Types.STRING);
stmt.registerOutParameter(3, java.sql.Types.INTEGER);
stmt.registerOutParameter(4, java.sql.Types.DECIMAL);
10 ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 18 ----------------------
ISO/IEC19075-4:2021(E)
4.9 Callingthebest2procedure
stmt.registerOutParameter(5, java.sql.Types.STRING);
stmt.registerOutParameter(6, java.sql.Types.STRING);
stmt.registerOutParameter(7, java.sql.Types.INTEGER);
stmt.registerOutParameter(8, java.sql.Types.DECIMAL);
stmt.setInt(9, 3);
stmt.executeUpdate();
String n1 = stmt.getString(1);
String id1 = stmt.getString(2);
int r1 = stmt.getInt(3);
BigDecimal s1 = stmt.getBigDecimal(4);
String n2 = stmt.getString(5);
String id2 = stmt.getString(6);
int r2 = stmt.getInt(7);
BigDecimal s2 = stmt.getBigDecimal(8);
4.10 AJavamethodreturningaresultset:orderedEmps
SQLstoredprocedurescangenerateSQLresultsetsastheiroutput.AnSQLresultset(asdefinedinJDBC
andSQL)isanorderedsequenceofSQLrows.SQLresultsetsarenotprocessedasnormalfunctionresult
values,butareinsteadboundtocaller-specifiediteratorsorcursors,whicharesubsequentlyusedto
processtherowsoftheresultset.
ThefollowingJavamethod,orderedEmps,generatesanSQLresultsetandthenreturnsthatresultsetto
theclient.NotethattheorderedEmpsmethodinternallygeneratestheresultsetinthesamewayasthe
bestTwoEmpsmethod.However,thebestTwoEmpsmethodprocessestheresultsetwithinthebestTwoEmps
method
...
Questions, Comments and Discussion
Ask us and Technical Secretary will try to provide an answer. You can facilitate discussion about the standard in here.