ISO/IEC 19075-4:2021

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

• Status: Published
• Publication Date: 31-Aug-2021
• 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
• Current Stage: 6060 - International Standard published
• Start Date: 01-Sep-2021
• Due Date: 11-Feb-2024
• Completion Date: 31-Aug-2021

Relations

• Revises: ISO/IEC TR 19075-4:2015 - Information technology - Database languages - SQL Technical Reports - Part 4: SQL with Routines and types using the JavaTM programming language
• Effective Date: 23-Apr-2020

Overview

ISO/IEC 19075-4:2021 is part of the ISO/IEC 19075 series that provides a tutorial-style, standards-based guide for using SQL routines and user-defined types implemented in the Java™ programming language. It explains how Java methods and classes can be installed, referenced and invoked from SQL environments (the SQL/JRT facility), and shows practical examples for integrating Java logic with SQL statements and expressions.

Key topics and technical requirements

• Scope: Tutorial guidance for using Java-written routines (functions/procedures) and user-defined types in SQL expressions and statements.
• SQL/JRT components:
  • Built-in schema SQLJ and built-in procedures such as SQLJ.INSTALL_JAR, SQLJ.REPLACE_JAR, SQLJ.REMOVE_JAR, and SQLJ.ALTER_JAVA_PATH for loading and managing Java JARs in the SQL catalog.
  • Extensions to SQL DDL/DML: CREATE PROCEDURE/FUNCTION, DROP PROCEDURE/FUNCTION, CREATE TYPE, DROP TYPE, and extended GRANT/REVOKE semantics for Java JAR usage.
  • Conventions for returning OUT/INOUT parameters and SQL result sets created by Java routines.
  • Qualified referencing of Java class fields and methods from SQL and additional Information Schema views/columns to expose metadata.
• Installation & deployment: Guidance on packaging Java class files into JARs, installing them into the SQL system with SQLJ.INSTALL_JAR (including a JAR name used within SQL), and handling deployment descriptors and path resolution.
• Examples and behavior: Sample Java classes and methods (e.g., Routines1.region, Routines1.correctStates) illustrate mapping between Java methods and SQL functions/procedures, JDBC usage, null argument handling, overloading, static variables, and visibility/privilege considerations.

Applications and who should use this standard

• Database implementers and DBMS vendors adding or supporting the SQL/JRT facility (Java-based routines and types).
• Application developers and architects who need to implement stored procedures, UDFs or custom data types in Java and expose them to SQL clients.
• DBAs responsible for deploying Java routines and managing JARs, privileges, and Information Schema metadata.
• Teams integrating Java business logic with SQL queries for advanced processing, result-set generation, or custom type mapping.

Practical use cases include Java-based stored procedures that perform complex computation or JDBC access, Java-backed SQL user-defined types for rich data models, and migration patterns where Java logic replaces or complements server-side SQL logic.

Related standards

Intended for use with the SQL/ISO family, for example:

• ISO/IEC 9075-1, -2, -3, -4, -9, -10, -11, -13, -14, -15, -16 (SQL/Foundation and related parts).

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 2021
© 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

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

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

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

ISO/IEC19075-4:2021(E)
AlistofallpartsintheISO/IEC19075seriescanbefoundontheISOandIECwebsites.
Anyfeedbackorquestionsonthisdocumentshouldbedirectedtotheuser’snationalstandardsbody.A
completelistingofthesebodiescanbefoundatwww.iso.org/members.htmlandwww.iec.ch/-
national-committees.
vi ©ISO/IEC2021–Allrightsreserved

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

ISO/IEC19075-4:2021(E)
viii ©ISO/IEC2021–Allrightsreserved

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

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

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

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

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

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

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

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

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

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

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
methoditself,whereasthisorderedEmps methodreturnstheresultsettotheclientasanSQLresultset.
TowriteaJavamethodthatreturnsaresultsettotheclient,themethodisspecifiedtohaveanadditional
parameterthatisasingle-elementarrayofeithertheJavaResultSetclassoraclassgeneratedbyan
SQL/OLBiteratordeclaration(“#sql iterator.”).
ThefollowingversionoftheorderedEmpsprocedureusesSQL/OLBtoaccesstheSQLserver,andreturns
theresultsetasanSQL/OLBiterator,SalesReport:
// #sql public iterator SalesReport (String name, int region, BigDecimal sales);
public class Routines3 {
public static void orderedEmps (int regionParm, SalesReport[ ] rs)
throws SQLException {
#sql rs[0] = { SELECT name, region_of(state) AS region, sales
FROM emp
WHERE region_of(state) > :regionParm
AND sales IS NOT NULL
ORDER BY sales DESC };
return;
}
}
TheSalesReportiteratorclasscouldbeapublicstaticinnerclassofRoutines3.However,theabove
examplepresumesexistenceofan“*.sqlj”file,namedSalesReport.sqlj,inthesamepackageasRou-
tines3,containingthepublicdefinitionoftheSalesReportiterator.Thatis,SalesReport.sqljcontains:
#sql public iterator SalesReport (String name, int region, BigDecimal sales);
Assume,forthisexample,thatbothclassRoutines3andtheiteratorSalesReportaredefinedinapackage
namedclasses.
Forcomparison,thefollowingshowsorderedEmpswrittenusingJDBCinsteadofSQL/OLB.
©ISO/IEC2021–Allrightsreserved 11

ISO/IEC19075-4:2021(E)
4.10 AJavamethodreturningaresultset:orderedEmps
public class Routines3 {
public static void orderedEmps(int regionParm, ResultSet[ ] rs)
throws SQLException {
Connection conn = DriverManager.getConnection ("jdbc:default:connection");
java.sql.PreparedStatement stmt = conn.prepareStatement
("SELECT name, 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);
rs[0] = stmt.executeQuery();
return;
}
}
ThemethodsetsthefirstelementoftheResultSet[]parametertoreferencetheJavaResultSetcontaining
theSQLresultsettobereturned.ThemethoddoesnotcloseeitherthereturnedResultSetobjectorthe
Javastatementobjectthatgeneratedtheresultset.TheSQLsystemwillimplicitlyclosebothofthose
objects.
AmethodsuchasorderedEmpscanbecalledinJavainthenormalmanner,supplyingexplicitarguments
forbothparameters.ItcanalsobecalledinSQL,asastoredprocedurethatgeneratesaresultsettobe
processedintheSQLmanner.Thisisillustratedinthefollowingtwoclauses.
EachoftheaboveorderedEmpsexampleshasasingleresultsetparameter,rs,inwhichonlyasingle
resultsetcanbereturned.Multipleresultsetparameterscanalsobespecified.
Notethat,incomparisontothepriorexamplesofbestTwoEmps,thereisnotry.finallyblocktoclose
theSQL/OLBiteratororResultSet,rs[0],ortheJDBCPreparedStatement,stmt.Foraresultsettobe
returnedfromastoredprocedureitmustnotbeexplicitlyclosed,whichmeans,inthecaseofJDBC,that
thestatementexecutedtogeneratetheresultsetalsomustnotbeexplicitlyclosed.
4.11 ACREATEPROCEDURErankedEmpsfororderedEmps
AssumethattheSQLJ.INSTALL_JARprocedureiscalledforaJARcontainingtheRoutines3classwiththe
orderedEmpsmethod:
SQLJ.INSTALL_JAR( 'file:~/classes/Routines3.jar', 'routines3_jar', 0)
Aswithpreviousmethods,anSQLnamefortheorderedEmpsmethodmustbedefinedbeforeitcanbe
calledasanSQLprocedure.Asabove,thisisdonewithaCREATEPROCEDUREstatementthatspecifies
anEXTERNAL.LANGUAGEJAVAclausetoreferencetheorderedEmpsmethod.Thefollowingisan
exampleCREATEPROCEDURE.DYNAMICRESULTSETSfortheaboveorderedEmpsmethod:
CREATE PROCEDURE rankedEmps (region INTEGER)
READS SQL DATA
DYNAMIC RESULT SETS 1
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'routines3_jar:classes.Routines3.orderedEmps';
ACREATEPROCEDUREstatementforaJavamethodthatgeneratesSQLresultsetshasthefollowing
characteristics:
— TheDYNAMICRESULTSETSclauseindicatesthattheproceduregeneratesoneormoreresultsets.
TheintegerspecifiedintheDYNAMICRESULTSETSclauseisthemaximumnumberofresultsets
thattheprocedurewillgenerate.Ifanexecutiongeneratesmorethanthisnumberofresultsets,a
warningwillbeissued,andonlythespecifiednumberofresultsetswillbereturned.
12 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-4:2021(E)
4.11 ACREATEPROCEDURErankedEmpsfororderedEmps
— TheSQLsignaturespecifiesonlytheparametersthatthecallerexplicitlysupplies.
— ThespecifiedJavamethodactuallyhasoneormoreadditional,trailingparameters,whosedatatypes
areaJavaarrayofeitherjava.sql.ResultSetoranimplementationofsqlj.runtime.ResultSetIt-
erator.
TheCREATEPROCEDUREstatementshownearlierinthisSubclausecouldbeusedtoreferenceeither
anSQL/OLB-basedorJDBC-basedversionofRoutines3.orderedEmps.Whenitisnecessarytochoosea
particularimplementation,theJavamethodsignatureofthedesiredJavamethodisexplicitlystated.For
theSQL/OLB-basedorderedEmps:
CREATE PROCEDURE rankedEmps (region INTEGER)
READS SQL DATA
DYNAMIC RESULT SETS 1
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME
'routines3_jar:classes.Routines3.orderedEmps(int, classes.SalesReport[])';
And,fortheJDBC-basedorderedEmps:
CREATE PROCEDURE rankedEmps (region INTEGER)
READS SQL DATA
DYNAMIC RESULT SETS 1
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME
'routines3_jar:classes.Routines3.orderedEmps(int, java.sql.ResultSet[])';
TheonlydifferenceintheaboveCREATEPROCEDURErankedEmpsstatementsisintheJavamethodsig-
nature’sdescriptionofthedynamicresultsetreturned.Inbothcases,afullyqualifiedclassnameispro-
videdfor,respectively,theSQL/OLBiterator(rememberthatSalesReportisinthepackagenamed
classes)andtheJDBCresultset.
It’sworthobservingthatthetwoCREATEPROCEDURErankedEmpsstatementsabovewouldnotbe
allowedbySQL,becausethenamesandsignaturesareidenticalandSQLwouldnotbeabletodetermine
whichonetoinvokewhenrequestedbyanapplication.Theycouldbepermittediftheywerecreatedin
differentschemas,however.
Thenextclausewillshowanexampleinvocationofthisprocedure.
4.12 CallingtherankedEmpsprocedure
AftertheRoutines3classhasbeeninstalledinanSQLsystemandtheCREATEPROCEDUREforrankedEmps
hasbeenexecuted,therankedEmpsprocedurecanbecalledasifitwereanSQLstoredprocedure.Such
acallcouldbewrittenwithanyfacilitythatdefinesmechanismsforprocessingSQLresultsets—that
is,SQL/CLI,JDBC,andSQL/OLB.ThefollowingisanexampleofsuchacallusingJDBC:
java.sql.CallableStatement stmt = conn.prepareCall( "{call rankedEmps(?)}");
stmt.setInt(1, 3);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
String name = rs.getString(1);
int region = rs.getInt(2);
BigDecimal sales = rs.getBigDecimal(3);
System.out.print("Name = " + name);
System.out.print("Region = " + region);
System.out.print(S"ales = " + sales);
System.out.println();
}
©ISO/IEC2021–Allrightsreserved 13

ISO/IEC19075-4:2021(E)
4.12 CallingtherankedEmpsprocedure
NotethatthecalloftherankedEmpsproceduresuppliesonlythesingleparameterthatwasdeclaredin
theCREATEPROCEDUREstatement.TheSQLsystemthenimplicitlysupplies,asapplicable,aparameter
thatisanemptyarrayofResultSetoranemptyarrayofclasses.SalesReport,andcallstheJavamethod.
ThatJavamethodassignstheoutputresultsetoriteratortothearrayparameter.And,whentheJava
methodcompletes,theSQLsystemreturnstheresultsetoriteratorinthatoutputarrayelementasan
SQLresultset.
4.13 OverloadingJavamethodnamesandSQLnames
WhenCREATEPROCEDURE/FUNCTIONstatementsareusedtospecifySQLnamesforJavamethods,the
SQLnamescanbeoverloaded.Thatis,thesameSQLnamecanbespecifiedinmultipleCREATEPROCE-
DURE/FUNCTIONstatements.NotethatsupportforsuchSQLoverloadingisanoptionalfeature.
ConsiderthefollowingJavaclassesandmethods.Thesearecontrivedroutinesintendedonlytoillustrate
overloading;theroutinebodiesarenotshownhere.
public class Over1 {
public static int isOdd (int i) {.};
public static int isOdd (float f) {.};
public static int testOdd (double d) {.};
}
public class Over2 {
public static int isOdd (java.sql.Timestamp t) {.};
public static int oddDateTime (java.sql.Date d) {.};
public static int oddDateTime (java.sql.Time t) {.};
}
NotethattheisOddmethodnameisoverloadedintheOver1class,andtheoddDateTimemethodname
isoverloadedintheOver2class.
AssumethattheaboveclassesareinaJAR~/classes/Over.jar,whichisinstalled:
SQLJ.INSTALL_JAR ('file:~/classes/Over.jar', 'over_jar', 0)
ToreferencethesemethodsinSQL,SQLnamesmustbespecifiedforthemwithCREATEFUNCTION
statements.TheseCREATEFUNCTIONstatementscanspecifySQLnamesthatareoverloaded.The
overloadingoftheSQLnamesiscompletelyseparatefromtheoverloadingintheJavanames.Thisis
illustratedinthefollowing.
RecallthatthesameJavamethodcanbespecifiedinmultipleCREATEPROCEDURE/FUNCTIONstatements.
CREATE FUNCTION odd (INTEGER) RETURNS INTEGER
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'over_jar:Over1.isOdd';
CREATE FUNCTION odd (REAL) RETURNS INTEGER
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'over_jar:Over1.isOdd';
CREATE FUNCTION odd (DOUBLE PRECISION) RETURNS INTEGER
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'over_jar:Over1.testOdd';
CREATE FUNCTION odd (TIMESTAMP) RETURNS INTEGER
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'over_jar:Over2.isOdd';
CREATE FUNCTION odd (DATE) RETURNS INTEGER
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'over_jar:Over2.oddDateTime';
CREATE FUNCTION odd (TIME) RETURNS INTEGER
LANGUAGE JAVA PARAMETER STYLE JAVA
14 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-4:2021(E)
4.13 OverloadingJavamethodnamesandSQLnames
EXTERNAL NAME 'over_jar:Over2.oddDateTime';
CREATE FUNCTION is_odd (INTEGER) RETURNS INTEGER
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'over_jar:Over1.isOdd';
CREATE FUNCTION test_odd (REAL) RETURNS INTEGER
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'over_jar:Over1.isOdd';
NotethefollowingcharacteristicsoftheseCREATEFUNCTIONstatements:
— TheSQLnameoddisdefinedonthetwoisOddmethodsandthetestOddmethodofOver1,andalso
theisOddmethodandtwooddDateTimemethodsofOver2.Thatis,theSQLnameoddspansboth
overloadedandnon-overloadedJavanames.
— TheSQLnamesis_oddandtest_oddaredefinedonthetwoisOddmethodsofOver1.Thatis,those
twodifferentSQLnamesaredefinedonthesameJavaname.
TherulesgoverningoverloadingarethoseoftheSQLlanguageasdefinedinISO/IEC9075-2intheSub-
clausesdefiningsands.Thisincludes:
— Rulesgoverningwhatparametercombinationscanbeoverloaded.Thatis,thelegality(ornot)of
thefollowingCREATEstatementsisdeterminedbySQLlanguagerules:
CREATE FUNCTION is_odd (INTEGER) RETURNS INTEGER.
CREATE FUNCTION is_odd (SMALLINT) RETURNS INTEGER.
CREATE PROCEDURE is_odd (SMALLINT) .
— RulesgoverningtheresolutionofcallsusingoverloadedSQLnames.Thatis,thedeterminationof
whichJavamethodiscalledby“odd(x)”forsomedataitem“x”isdeterminedbySQLlanguagerules.
TheEXTERNALNAMEclausesoftheaboveCREATEFUNCTIONstatementsspecifyonlytheJARname
andmethodnameoftheJavamethod.Forexample:
CREATE FUNCTION odd (INTEGER) RETURNS INTEGER
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'over_jar:Over1.isOdd';
TheJavamethodsignature(i.e.,,alistoftheparameterdatatypes)ofamethodcanalsobespecifiedin
theEXTERNALNAMEclause.Forexample:
CREATE FUNCTION odd (INTEGER) RETURNS INTEGER
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'over_jar:Over1.isOdd (int)';
ThegroupofeightexampleCREATEFUNCTIONstatements,shownearlierinthisclause,donotrequire
Javamethodsignatures,butcanbeincludedforclarity.Subclause4.15,“Javamethodsignaturesinthe
CREATEstatements”,describescaseswheretheJavamethodsignatureisrequired.
4.14 Javamainmethods
Javaplacesspecialnorequirementsonanymethodnamedmain.However,aJVMrecognizesamethod
namedmain,withthefollowingJavamethodsignature,asthemethodtoinvokewhenonlyaclassname
isprovided:
public static void main (String[ ]);
©ISO/IEC2021–Allrightsreserved 15

ISO/IEC19075-4:2021(E)
4.14 Javamainmethods
IfaJavamethodnamedmainisspecifiedinanSQLCREATEPROCEDURE.EXTERNALstatement,then
thatJavamethodmusthavetheaboveJavamethodsignature.ThesignatureoftheSQLprocedurecan
eitherbe:
— AsingleparameterthatisanarrayofCHARACTERorCHARACTERVARYING.Thatarrayispassed
totheJavamethodastheStringarrayparameter.Note:ThisSQLmethodsignaturecanonlybeused
inSQLsystemsthatsupportarraydatatypesinSQL.
— Zeroormoreparameters,eachofwhichisCHARACTERorCHARACTERVARYING.ThoseNparameters
arepassedtotheJavamethodasasingleNelementarrayofString.
4.15 JavamethodsignaturesintheCREATEstatements
Considerthefollowingmethod,job1,whichhasanintegerparameterandreturnsaStringwiththejob
correspondingwithajobcodevalue:
public class Routines4 {
//A String method that will be called as a function
public static String job1 (Integer jc) throws SQLException {
if (jc == 1) return "Admin";
else if (jc == 2) return "Sales";
else if (jc == 3) return "Clerk";
else if (jc == null) return null;
else return "unknown jobcode";
}
}
Notethatthemethodnamehasbeensuffixedwitha“1”inanticipationofsubsequentvariantsofthe
method.
AssumethatthisclasshasbeeninstalledinSQL:
SQLJ.INSTALL_JAR ('file:~/classes/Routines4.jar', 'routines4_jar', 0)
AnSQLfunctionjob_of1definedonthejob1methodcanalsobespecified:
CREATE FUNCTION job_of1(jc INTEGER) RETURNS CHARACTER VARYING(20)
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'routines4_jar:Routines4.job1';
However,aswrittenabove,thisCREATEstatementisnotvalid.Notethatthedatatypeoftheparameter
oftheJavamethodjob1isJavaInteger(whichisshortforjava.lang.Integer),andtheSQLdatatype
INTEGERhasbeenspecifiedforthecorrespondingparameteroftheSQLjob_of1function.However,the
detailedrulesfortheexternalJavaformoftheSQLCREATEPROCEDURE/FUNCTIONstatementspecifies
thatthedefaultJavaparameterdatatypeforanSQLINTEGERparameteristheJavaintdatatype,not
theJavaIntegerdatatype.Subclause4.16,“NullargumentvaluesandtheRETURNSNULLclause”,
describessomereasonswhythespecificationofJavaIntegerratherthanJavaintmightbedesirable.
IfspecificationofanSQLCREATEPROCEDURE/FUNCTIONstatementforaJavamethodwhoseparameter
datatypesincludeJavatypesdifferingfromtheirdefaultJavatypesisdesired,thenthosedatatypesare
specifiedinaJavamethodsignatureintheCREATEstatement.ThisJavamethodsignatureiswritten
aftertheJavamethodnameintheEXTERNALNAMEclause.Forexample,theaboveCREATEstatement
forthejob1methodwouldbewrittenas:
CREATE FUNCTION job_of1(jc INTEGER) RETURNS CHARACTER VARYING(20)
LANGUAGE JAVA PARAMETER STYLE JAVA
EXTERNAL NAME 'routines4_jar:Routines4.job1(java.lang.Integer)';
16 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-4:2021(E)
4.15 JavamethodsignaturesintheCREATEstatements
IfdatatypesarespecifiedintheJavamethodsignatureofaCREATEstatementthatspecifiesDYNAMIC
RESULTSETS,thentheimplicittrailingresultsetoriteratorparametersinthatJavamethodsignature
arerequire
...