ISO/IEC PRF 19075-4

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

Technologies de l'information -- Recommandations pour l'utilisation du langage de base de données SQL

General Information

• Status: Published
• ICS: 35.060 - Languages used in information technology
• Technical Committee: ISO/IEC JTC 1/SC 32 - Data management and interchange
• Drafting Committee: ISO/IEC JTC 1/SC 32/WG 3 - Database language

RELATIONS

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

Standards Content (sample)

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: [phone]
Email: [e-mail]
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.

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
...