blob: 1328e8695a572c4eb25176d71a75bd0db2d0a348 [file] [log] [blame]
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<!--[if IE]><meta http-equiv="X-UA-Compatible" content="IE=edge"><![endif]-->
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="generator" content="Asciidoctor 1.5.4">
<title>Stored Procedures in Java (SPJs) Guide</title>
<style>
/**
* @@@ START COPYRIGHT @@@
*
* Licensed to the Apache Software Foundation (ASF) under one
* or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information
* regarding copyright ownership. The ASF licenses this file
* to you under the Apache License, Version 2.0 (the
* "License"); you may not use this file except in compliance
* with the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing,
* software distributed under the License is distributed on an
* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
* KIND, either express or implied. See the License for the
* specific language governing permissions and limitations
* under the License.
*
* @@@ END COPYRIGHT @@@
*/
/* Asciidoctor default stylesheet | MIT License | http://asciidoctor.org */
/* Remove the comments around the @import statement below when using this as a custom stylesheet */
/*@import "https://fonts.googleapis.com/css?family=Open+Sans:300,300italic,400,400italic,600,600italic%7CNoto+Serif:400,400italic,700,700italic%7CDroid+Sans+Mono:400";*/
article,aside,details,figcaption,figure,footer,header,hgroup,main,nav,section,summary{display:block}
audio,canvas,video{display:inline-block}
audio:not([controls]){display:none;height:0}
[hidden],template{display:none}
script{display:none!important}
html{font-family:sans-serif;-ms-text-size-adjust:100%;-webkit-text-size-adjust:100%}
body{margin:0}
a{background:transparent}
a:focus{outline:thin dotted}
a:active,a:hover{outline:0}
h1{font-size:2em;margin:.67em 0}
abbr[title]{border-bottom:1px dotted}
b,strong{font-weight:bold}
dfn{font-style:italic}
hr{-moz-box-sizing:content-box;box-sizing:content-box;height:0}
mark{background:#ff0;color:#000}
code,kbd,pre,samp{font-family:monospace;font-size:1em}
pre{white-space:pre-wrap}
q{quotes:"\201C" "\201D" "\2018" "\2019"}
small{font-size:80%}
sub,sup{font-size:75%;line-height:0;position:relative;vertical-align:baseline}
sup{top:-.5em}
sub{bottom:-.25em}
img{border:0}
svg:not(:root){overflow:hidden}
figure{margin:0}
fieldset{border:1px solid silver;margin:0 2px;padding:.35em .625em .75em}
legend{border:0;padding:0}
button,input,select,textarea{font-family:inherit;font-size:100%;margin:0}
button,input{line-height:normal}
button,select{text-transform:none}
button,html input[type="button"],input[type="reset"],input[type="submit"]{-webkit-appearance:button;cursor:pointer}
button[disabled],html input[disabled]{cursor:default}
input[type="checkbox"],input[type="radio"]{box-sizing:border-box;padding:0}
input[type="search"]{-webkit-appearance:textfield;-moz-box-sizing:content-box;-webkit-box-sizing:content-box;box-sizing:content-box}
input[type="search"]::-webkit-search-cancel-button,input[type="search"]::-webkit-search-decoration{-webkit-appearance:none}
button::-moz-focus-inner,input::-moz-focus-inner{border:0;padding:0}
textarea{overflow:auto;vertical-align:top}
table{border-collapse:collapse;border-spacing:0}
*,*:before,*:after{-moz-box-sizing:border-box;-webkit-box-sizing:border-box;box-sizing:border-box}
html,body{font-size:100%}
body{background:#fff;color:rgba(0,0,0,.8);padding:0;margin:0;font-family:"Helvetica Neue",Helvetica,Arial,sans-serif,serif;font-weight:400;font-style:normal;line-height:1;position:relative;cursor:auto}
a:hover{cursor:pointer}
img,object,embed{max-width:100%;height:auto}
object,embed{height:100%}
img{-ms-interpolation-mode:bicubic}
#map_canvas img,#map_canvas embed,#map_canvas object,.map_canvas img,.map_canvas embed,.map_canvas object{max-width:none!important}
.left{float:left!important}
.right{float:right!important}
.text-left{text-align:left!important}
.text-right{text-align:right!important}
.text-center{text-align:center!important}
.text-justify{text-align:justify!important}
.hide{display:none}
.antialiased,body{-webkit-font-smoothing:antialiased}
img{display:inline-block;vertical-align:middle}
textarea{height:auto;min-height:50px}
select{width:100%}
p.lead,.paragraph.lead>p,#preamble>.sectionbody>.paragraph:first-of-type p{font-size:1.21875em;line-height:1.6}
.subheader,.admonitionblock td.content>.title,.audioblock>.title,.exampleblock>.title,.imageblock>.title,.listingblock>.title,.literalblock>.title,.stemblock>.title,.openblock>.title,.paragraph>.title,.quoteblock>.title,table.tableblock>.title,.verseblock>.title,.videoblock>.title,.dlist>.title,.olist>.title,.ulist>.title,.qlist>.title,.hdlist>.title{line-height:1.45;color:#3188ac;font-weight:400;margin-top:0;margin-bottom:.25em}
div,dl,dt,dd,ul,ol,li,h1,h2,h3,#toctitle,.sidebarblock>.content>.title,h4,h5,h6,pre,form,p,blockquote,th,td{margin:0;padding:0;direction:ltr}
a{color:#2156a5;text-decoration:underline;line-height:inherit}
a:hover,a:focus{color:#1d4b8f}
a img{border:none}
p{font-family:inherit;font-weight:400;font-size:1em;line-height:1.6;margin-bottom:1.25em;text-rendering:optimizeLegibility}
p aside{font-size:.875em;line-height:1.35;font-style:italic}
/* Defines headings */
h1,h2,h3,#toctitle,.sidebarblock>.content>.title,h4,h5,h6{font-family:"Helvetica Neue",Helvetica,Arial,sans-serif;font-weight:bold;font-style:normal;color:#3188ac;text-rendering:optimizeLegibility;margin-top:1em;margin-bottom:.5em;line-height:1.0125em}
h1 small,h2 small,h3 small,#toctitle small,.sidebarblock>.content>.title small,h4 small,h5 small,h6 small{font-size:60%;color:#e99b8f;line-height:0}
h1{font-size:2.125em}
h2{font-size:1.6875em}
h3,#toctitle,.sidebarblock>.content>.title{font-size:1.375em}
h4,h5{font-size:1.125em}
h6{font-size:1em}
hr{border:solid #ddddd8;border-width:1px 0 0;clear:both;margin:1.25em 0 1.1875em;height:0}
em,i{font-style:italic;line-height:inherit}
strong,b{font-weight:bold;line-height:inherit}
small{font-size:60%;line-height:inherit}
/* Defines the `text` (passthru) format */
code{font-family:"Droid Sans Mono","DejaVu Sans Mono",monospace;font-weight:bold;color:#3188ac}
ul,ol,dl{font-size:1em;line-height:1.6;margin-bottom:1.25em;list-style-position:outside;font-family:inherit}
ul,ol,ul.no-bullet,ol.no-bullet{margin-left:1.5em}
ul li ul,ul li ol{margin-left:1.25em;margin-bottom:0;font-size:1em}
ul.square li ul,ul.circle li ul,ul.disc li ul{list-style:inherit}
ul.square{list-style-type:square}
ul.circle{list-style-type:circle}
ul.disc{list-style-type:disc}
ul.no-bullet{list-style:none}
ol li ul,ol li ol{margin-left:1.25em;margin-bottom:0}
dl dt{margin-bottom:.3125em;font-weight:bold}
dl dd{margin-bottom:1.25em}
abbr,acronym{text-transform:uppercase;font-size:90%;color:rgba(0,0,0,.8);border-bottom:1px dotted #ddd;cursor:help}
abbr{text-transform:none}
blockquote{margin:0 0 1.25em;padding:.5625em 1.25em 0 1.1875em;border-left:1px solid #ddd}
blockquote cite{display:block;font-size:.9375em;color:rgba(0,0,0,.6)}
blockquote cite:before{content:"\2014 \0020"}
blockquote cite a,blockquote cite a:visited{color:rgba(0,0,0,.6)}
blockquote,blockquote p{line-height:1.6;color:rgba(0,0,0,.85)}
@media only screen and (min-width:768px){h1,h2,h3,#toctitle,.sidebarblock>.content>.title,h4,h5,h6{line-height:1.2}
h1{font-size:2.75em}
h2{font-size:2.3125em}
h3,#toctitle,.sidebarblock>.content>.title{font-size:1.6875em}
h4{font-size:1.4375em}}table{background:#fff;margin-bottom:1.25em;border:solid 1px #dedede}
table thead,table tfoot{background:#f7f8f7;font-weight:bold}
table thead tr th,table thead tr td,table tfoot tr th,table tfoot tr td{padding:.5em .625em .625em;font-size:inherit;color:rgba(0,0,0,.8);text-align:left}
table tr th,table tr td{padding:.5625em .625em;font-size:inherit;color:rgba(0,0,0,.8)}
table tr.even,table tr.alt,table tr:nth-of-type(even){background:#f8f8f7}
table thead tr th,table tfoot tr th,table tbody tr td,table tr td,table tfoot tr td{display:table-cell;line-height:1.6}
h1,h2,h3,#toctitle,.sidebarblock>.content>.title,h4,h5,h6{line-height:1.2;word-spacing:-.05em}
h1 strong,h2 strong,h3 strong,#toctitle strong,.sidebarblock>.content>.title strong,h4 strong,h5 strong,h6 strong{font-weight:400}
.clearfix:before,.clearfix:after,.float-group:before,.float-group:after{content:" ";display:table}
.clearfix:after,.float-group:after{clear:both}
*:not(pre)>code{font-size:1.0em;font-style:normal!important;letter-spacing:0;padding:.1em .5ex;word-spacing:-.15em;-webkit-border-radius:4px;border-radius:4px;line-height:1.45;text-rendering:optimizeSpeed}
/**:not(pre)>code{font-size:.9375em;font-style:normal!important;letter-spacing:0;padding:.1em .5ex;word-spacing:-.15em;background-color:#f7f7f8;-webkit-border-radius:4px;border-radius:4px;line-height:1.45;text-rendering:optimizeSpeed}*/
pre,pre>code{line-height:1.45;color:rgba(0,0,0,.9);font-family:"Droid Sans Mono","DejaVu Sans Mono",monospace;font-weight:400;text-rendering:optimizeSpeed}
.keyseq{color:rgba(51,51,51,.8)}
kbd{display:inline-block;color:rgba(0,0,0,.8);font-size:.75em;line-height:1.4;background-color:#f7f7f7;border:1px solid #ccc;-webkit-border-radius:3px;border-radius:3px;-webkit-box-shadow:0 1px 0 rgba(0,0,0,.2),0 0 0 .1em white inset;box-shadow:0 1px 0 rgba(0,0,0,.2),0 0 0 .1em #fff inset;margin:-.15em .15em 0 .15em;padding:.2em .6em .2em .5em;vertical-align:middle;white-space:nowrap}
.keyseq kbd:first-child{margin-left:0}
.keyseq kbd:last-child{margin-right:0}
.menuseq,.menu{color:rgba(0,0,0,.8)}
b.button:before,b.button:after{position:relative;top:-1px;font-weight:400}
b.button:before{content:"[";padding:0 3px 0 2px}
b.button:after{content:"]";padding:0 2px 0 3px}
p a>code:hover{color:rgba(0,0,0,.9)}
#header,#content,#footnotes,#footer{width:100%;margin-left:auto;margin-right:auto;margin-top:0;margin-bottom:0;max-width:62.5em;*zoom:1;position:relative;padding-left:.9375em;padding-right:.9375em}
#header:before,#header:after,#content:before,#content:after,#footnotes:before,#footnotes:after,#footer:before,#footer:after{content:" ";display:table}
#header:after,#content:after,#footnotes:after,#footer:after{clear:both}
#content{margin-top:1.25em}
#content:before{content:none}
/* #header>h1:first-child{color:rgba(0,0,0,.85);margin-top:2.25rem;margin-bottom:0} */
#header>h1:first-child{color:#3188ac;margin-top:2.25rem;margin-bottom:0}
#header>h1:first-child+#toc{margin-top:8px;border-top:1px solid #ddddd8}
#header>h1:only-child,body.toc2 #header>h1:nth-last-child(2){border-bottom:1px solid #ddddd8;padding-bottom:8px}
#header .details{border-bottom:1px solid #ddddd8;line-height:1.45;padding-top:.25em;padding-bottom:.25em;padding-left:.25em;color:rgba(0,0,0,.6);display:-ms-flexbox;display:-webkit-flex;display:flex;-ms-flex-flow:row wrap;-webkit-flex-flow:row wrap;flex-flow:row wrap}
#header .details span:first-child{margin-left:-.125em}
#header .details span.email a{color:rgba(0,0,0,.85)}
#header .details br{display:none}
#header .details br+span:before{content:"\00a0\2013\00a0"}
#header .details br+span.author:before{content:"\00a0\22c5\00a0";color:rgba(0,0,0,.85)}
#header .details br+span#revremark:before{content:"\00a0|\00a0"}
#header #revnumber{text-transform:capitalize}
#header #revnumber:after{content:"\00a0"}
#content>h1:first-child:not([class]){color:rgba(0,0,0,.85);border-bottom:1px solid #ddddd8;padding-bottom:8px;margin-top:0;padding-top:1rem;margin-bottom:1.25rem}
#toc{border-bottom:1px solid #efefed;padding-bottom:.5em}
#toc>ul{margin-left:.125em}
#toc ul.sectlevel0>li>a{font-style:italic}
#toc ul.sectlevel0 ul.sectlevel1{margin:.5em 0}
#toc ul{font-family:"Open Sans","DejaVu Sans",sans-serif;list-style-type:none}
#toc a{text-decoration:none}
#toc a:active{text-decoration:underline}
#toctitle{color:#3188ac;font-size:1.2em}
@media only screen and (min-width:768px){#toctitle{font-size:1.375em}
body.toc2{padding-left:15em;padding-right:0}
#toc.toc2{margin-top:0!important;background-color:#f8f8f7;position:fixed;width:15em;left:0;top:0;border-right:1px solid #efefed;border-top-width:0!important;border-bottom-width:0!important;z-index:1000;padding:1.25em 1em;height:100%;overflow:auto}
#toc.toc2 #toctitle{margin-top:0;font-size:1.2em}
#toc.toc2>ul{font-size:.9em;margin-bottom:0}
#toc.toc2 ul ul{margin-left:0;padding-left:1em}
#toc.toc2 ul.sectlevel0 ul.sectlevel1{padding-left:0;margin-top:.5em;margin-bottom:.5em}
body.toc2.toc-right{padding-left:0;padding-right:15em}
body.toc2.toc-right #toc.toc2{border-right-width:0;border-left:1px solid #efefed;left:auto;right:0}}@media only screen and (min-width:1280px){body.toc2{padding-left:20em;padding-right:0}
/* Controls width of panel */
#toc.toc2{width:20em}
#toc.toc2 #toctitle{font-size:1.375em}
#toc.toc2>ul{font-size:.95em}
#toc.toc2 ul ul{padding-left:1.25em}
body.toc2.toc-right{padding-left:0;padding-right:20em}}#content #toc{border-style:solid;border-width:1px;border-color:#e0e0dc;margin-bottom:1.25em;padding:1.25em;background:#f8f8f7;-webkit-border-radius:4px;border-radius:4px}
#content #toc>:first-child{margin-top:0}
#content #toc>:last-child{margin-bottom:0}
#footer{max-width:100%;background-color:rgba(0,0,0,.8);padding:1.25em}
#footer-text,#footer_nav{color:rgba(255,255,255,.8);line-height:1.44}
#footer a{color: #990000}
.sect1{padding-bottom:.625em}
@media only screen and (min-width:768px){.sect1{padding-bottom:1.25em}}.sect1+.sect1{border-top:1px solid #efefed}
#content h1>a.anchor,h2>a.anchor,h3>a.anchor,#toctitle>a.anchor,.sidebarblock>.content>.title>a.anchor,h4>a.anchor,h5>a.anchor,h6>a.anchor{position:absolute;z-index:1001;width:1.5ex;margin-left:-1.5ex;display:block;text-decoration:none!important;visibility:hidden;text-align:center;font-weight:400}
#content h1>a.anchor:before,h2>a.anchor:before,h3>a.anchor:before,#toctitle>a.anchor:before,.sidebarblock>.content>.title>a.anchor:before,h4>a.anchor:before,h5>a.anchor:before,h6>a.anchor:before{content:"\00A7";font-size:.85em;display:block;padding-top:.1em}
#content h1:hover>a.anchor,#content h1>a.anchor:hover,h2:hover>a.anchor,h2>a.anchor:hover,h3:hover>a.anchor,#toctitle:hover>a.anchor,.sidebarblock>.content>.title:hover>a.anchor,h3>a.anchor:hover,#toctitle>a.anchor:hover,.sidebarblock>.content>.title>a.anchor:hover,h4:hover>a.anchor,h4>a.anchor:hover,h5:hover>a.anchor,h5>a.anchor:hover,h6:hover>a.anchor,h6>a.anchor:hover{visibility:visible}
#content h1>a.link,h2>a.link,h3>a.link,#toctitle>a.link,.sidebarblock>.content>.title>a.link,h4>a.link,h5>a.link,h6>a.link{color:#990000;text-decoration:none}
#content h1>a.link:hover,h2>a.link:hover,h3>a.link:hover,#toctitle>a.link:hover,.sidebarblock>.content>.title>a.link:hover,h4>a.link:hover,h5>a.link:hover,h6>a.link:hover{color:#a53221}
.audioblock,.imageblock,.literalblock,.listingblock,.stemblock,.videoblock{margin-bottom:1.25em}
.admonitionblock td.content>.title,.audioblock>.title,.exampleblock>.title,.imageblock>.title,.listingblock>.title,.literalblock>.title,.stemblock>.title,.openblock>.title,.paragraph>.title,.quoteblock>.title,table.tableblock>.title,.verseblock>.title,.videoblock>.title,.dlist>.title,.olist>.title,.ulist>.title,.qlist>.title,.hdlist>.title{text-rendering:optimizeLegibility;text-align:left;font-family:"Helvetica Neue",Helvetica,Arial,sans-serif,serif;font-size:1rem;font-weight:bold}
/* Here */
table.tableblock>caption.title{white-space:nowrap;overflow:visible;max-width:0}
.paragraph.lead>p,#preamble>.sectionbody>.paragraph:first-of-type p{color:rgba(0,0,0,.85)}
table.tableblock #preamble>.sectionbody>.paragraph:first-of-type p{font-size:inherit}
.admonitionblock>table{border-collapse:separate;border:0;background:none;width:100%}
.admonitionblock>table td.icon{text-align:center;width:80px}
.admonitionblock>table td.icon img{max-width:none}
.admonitionblock>table td.icon .title{font-weight:bold;font-family:"Open Sans","DejaVu Sans",sans-serif;text-transform:uppercase}
.admonitionblock>table td.content{padding-left:1.125em;padding-right:1.25em;border-left:1px solid #ddddd8;color:rgba(0,0,0,.6)}
.admonitionblock>table td.content>:last-child>:last-child{margin-bottom:0}
.exampleblock>.content{border-style:solid;border-width:1px;border-color:#e6e6e6;margin-bottom:1.25em;padding:1.25em;background:#fff;-webkit-border-radius:4px;border-radius:4px}
.exampleblock>.content>:first-child{margin-top:0}
.exampleblock>.content>:last-child{margin-bottom:0}
.sidebarblock{border-style:solid;border-width:1px;border-color:#e0e0dc;margin-bottom:1.25em;padding:1.25em;background:#f8f8f7;-webkit-border-radius:4px;border-radius:4px}
.sidebarblock>:first-child{margin-top:0}
.sidebarblock>:last-child{margin-bottom:0}
.sidebarblock>.content>.title{color:#7a2518;margin-top:0;text-align:center}
.exampleblock>.content>:last-child>:last-child,.exampleblock>.content .olist>ol>li:last-child>:last-child,.exampleblock>.content .ulist>ul>li:last-child>:last-child,.exampleblock>.content .qlist>ol>li:last-child>:last-child,.sidebarblock>.content>:last-child>:last-child,.sidebarblock>.content .olist>ol>li:last-child>:last-child,.sidebarblock>.content .ulist>ul>li:last-child>:last-child,.sidebarblock>.content .qlist>ol>li:last-child>:last-child{margin-bottom:0}
.literalblock pre,.listingblock pre:not(.highlight),.listingblock pre[class="highlight"],.listingblock pre[class^="highlight "],.listingblock pre.CodeRay,.listingblock pre.prettyprint{background:#f7f7f8}
.sidebarblock .literalblock pre,.sidebarblock .listingblock pre:not(.highlight),.sidebarblock .listingblock pre[class="highlight"],.sidebarblock .listingblock pre[class^="highlight "],.sidebarblock .listingblock pre.CodeRay,.sidebarblock .listingblock pre.prettyprint{background:#f2f1f1}
.literalblock pre,.literalblock pre[class],.listingblock pre,.listingblock pre[class]{-webkit-border-radius:4px;border-radius:4px;word-wrap:break-word;padding:1em;font-size:.8125em}
.literalblock pre.nowrap,.literalblock pre[class].nowrap,.listingblock pre.nowrap,.listingblock pre[class].nowrap{overflow-x:auto;white-space:pre;word-wrap:normal}
@media only screen and (min-width:768px){.literalblock pre,.literalblock pre[class],.listingblock pre,.listingblock pre[class]{font-size:.90625em}}@media only screen and (min-width:1280px){.literalblock pre,.literalblock pre[class],.listingblock pre,.listingblock pre[class]{font-size:1em}}.literalblock.output pre{color:#f7f7f8;background-color:rgba(0,0,0,.9)}
.listingblock pre.highlightjs{padding:0}
.listingblock pre.highlightjs>code{padding:1em;-webkit-border-radius:4px;border-radius:4px}
.listingblock pre.prettyprint{border-width:0}
.listingblock>.content{position:relative}
.listingblock code[data-lang]:before{display:none;content:attr(data-lang);position:absolute;font-size:.75em;top:.425rem;right:.5rem;line-height:1;text-transform:uppercase;color:#999}
.listingblock:hover code[data-lang]:before{display:block}
.listingblock.terminal pre .command:before{content:attr(data-prompt);padding-right:.5em;color:#999}
.listingblock.terminal pre .command:not([data-prompt]):before{content:"$"}
table.pyhltable{border-collapse:separate;border:0;margin-bottom:0;background:none}
table.pyhltable td{vertical-align:top;padding-top:0;padding-bottom:0}
table.pyhltable td.code{padding-left:.75em;padding-right:0}
pre.pygments .lineno,table.pyhltable td:not(.code){color:#999;padding-left:0;padding-right:.5em;border-right:1px solid #ddddd8}
pre.pygments .lineno{display:inline-block;margin-right:.25em}
table.pyhltable .linenodiv{background:none!important;padding-right:0!important}
.quoteblock{margin:0 1em 1.25em 1.5em;display:table}
.quoteblock>.title{margin-left:-1.5em;margin-bottom:.75em}
.quoteblock blockquote,.quoteblock blockquote p{color:rgba(0,0,0,.85);font-size:1.15rem;line-height:1.75;word-spacing:.1em;letter-spacing:0;font-style:italic;text-align:justify}
.quoteblock blockquote{margin:0;padding:0;border:0}
.quoteblock blockquote:before{content:"\201c";float:left;font-size:2.75em;font-weight:bold;line-height:.6em;margin-left:-.6em;color:#7a2518;text-shadow:0 1px 2px rgba(0,0,0,.1)}
.quoteblock blockquote>.paragraph:last-child p{margin-bottom:0}
.quoteblock .attribution{margin-top:.5em;margin-right:.5ex;text-align:right}
.quoteblock .quoteblock{margin-left:0;margin-right:0;padding:.5em 0;border-left:3px solid rgba(0,0,0,.6)}
.quoteblock .quoteblock blockquote{padding:0 0 0 .75em}
.quoteblock .quoteblock blockquote:before{display:none}
.verseblock{margin:0 1em 1.25em 1em}
.verseblock pre{font-family:"Open Sans","DejaVu Sans",sans;font-size:1.15rem;color:rgba(0,0,0,.85);font-weight:300;text-rendering:optimizeLegibility}
.verseblock pre strong{font-weight:400}
.verseblock .attribution{margin-top:1.25rem;margin-left:.5ex}
.quoteblock .attribution,.verseblock .attribution{font-size:.9375em;line-height:1.45;font-style:italic}
.quoteblock .attribution br,.verseblock .attribution br{display:none}
.quoteblock .attribution cite,.verseblock .attribution cite{display:block;letter-spacing:-.05em;color:rgba(0,0,0,.6)}
.quoteblock.abstract{margin:0 0 1.25em 0;display:block}
.quoteblock.abstract blockquote,.quoteblock.abstract blockquote p{text-align:left;word-spacing:0}
.quoteblock.abstract blockquote:before,.quoteblock.abstract blockquote p:first-of-type:before{display:none}
table.tableblock{max-width:100%;border-collapse:separate}
table.tableblock td>.paragraph:last-child p>p:last-child,table.tableblock th>p:last-child,table.tableblock td>p:last-child{margin-bottom:0}
table.spread{width:100%}
table.tableblock,th.tableblock,td.tableblock{border:0 solid #dedede}
table.grid-all th.tableblock,table.grid-all td.tableblock{border-width:0 1px 1px 0}
table.grid-all tfoot>tr>th.tableblock,table.grid-all tfoot>tr>td.tableblock{border-width:1px 1px 0 0}
table.grid-cols th.tableblock,table.grid-cols td.tableblock{border-width:0 1px 0 0}
table.grid-all *>tr>.tableblock:last-child,table.grid-cols *>tr>.tableblock:last-child{border-right-width:0}
table.grid-rows th.tableblock,table.grid-rows td.tableblock{border-width:0 0 1px 0}
table.grid-all tbody>tr:last-child>th.tableblock,table.grid-all tbody>tr:last-child>td.tableblock,table.grid-all thead:last-child>tr>th.tableblock,table.grid-rows tbody>tr:last-child>th.tableblock,table.grid-rows tbody>tr:last-child>td.tableblock,table.grid-rows thead:last-child>tr>th.tableblock{border-bottom-width:0}
table.grid-rows tfoot>tr>th.tableblock,table.grid-rows tfoot>tr>td.tableblock{border-width:1px 0 0 0}
table.frame-all{border-width:1px}
table.frame-sides{border-width:0 1px}
table.frame-topbot{border-width:1px 0}
th.halign-left,td.halign-left{text-align:left}
th.halign-right,td.halign-right{text-align:right}
th.halign-center,td.halign-center{text-align:center}
th.valign-top,td.valign-top{vertical-align:top}
th.valign-bottom,td.valign-bottom{vertical-align:bottom}
th.valign-middle,td.valign-middle{vertical-align:middle}
table thead th,table tfoot th{font-weight:bold}
tbody tr th{display:table-cell;line-height:1.6;background:#f7f8f7}
tbody tr th,tbody tr th p,tfoot tr th,tfoot tr th p{color:rgba(0,0,0,.8);font-weight:bold}
p.tableblock>code:only-child{background:none;padding:0}
p.tableblock{font-size:1em}
td>div.verse{white-space:pre}
ol{margin-left:1.75em}
ul li ol{margin-left:1.5em}
dl dd{margin-left:1.125em}
dl dd:last-child,dl dd:last-child>:last-child{margin-bottom:0}
ol>li p,ul>li p,ul dd,ol dd,.olist .olist,.ulist .ulist,.ulist .olist,.olist .ulist{margin-bottom:.625em}
ul.unstyled,ol.unnumbered,ul.checklist,ul.none{list-style-type:none}
ul.unstyled,ol.unnumbered,ul.checklist{margin-left:.625em}
ul.checklist li>p:first-child>.fa-square-o:first-child,ul.checklist li>p:first-child>.fa-check-square-o:first-child{width:1em;font-size:.85em}
ul.checklist li>p:first-child>input[type="checkbox"]:first-child{width:1em;position:relative;top:1px}
ul.inline{margin:0 auto .625em auto;margin-left:-1.375em;margin-right:0;padding:0;list-style:none;overflow:hidden}
ul.inline>li{list-style:none;float:left;margin-left:1.375em;display:block}
ul.inline>li>*{display:block}
.unstyled dl dt{font-weight:400;font-style:normal}
ol.arabic{list-style-type:decimal}
ol.decimal{list-style-type:decimal-leading-zero}
ol.loweralpha{list-style-type:lower-alpha}
ol.upperalpha{list-style-type:upper-alpha}
ol.lowerroman{list-style-type:lower-roman}
ol.upperroman{list-style-type:upper-roman}
ol.lowergreek{list-style-type:lower-greek}
.hdlist>table,.colist>table{border:0;background:none}
.hdlist>table>tbody>tr,.colist>table>tbody>tr{background:none}
td.hdlist1{padding-right:.75em;font-weight:bold}
td.hdlist1,td.hdlist2{vertical-align:top}
.literalblock+.colist,.listingblock+.colist{margin-top:-.5em}
.colist>table tr>td:first-of-type{padding:0 .75em;line-height:1}
.colist>table tr>td:last-of-type{padding:.25em 0}
.thumb,.th{line-height:0;display:inline-block;border:solid 4px #fff;-webkit-box-shadow:0 0 0 1px #ddd;box-shadow:0 0 0 1px #ddd}
.imageblock.left,.imageblock[style*="float: left"]{margin:.25em .625em 1.25em 0}
.imageblock.right,.imageblock[style*="float: right"]{margin:.25em 0 1.25em .625em}
.imageblock>.title{margin-bottom:0}
.imageblock.thumb,.imageblock.th{border-width:6px}
.imageblock.thumb>.title,.imageblock.th>.title{padding:0 .125em}
.image.left,.image.right{margin-top:.25em;margin-bottom:.25em;display:inline-block;line-height:0}
.image.left{margin-right:.625em}
.image.right{margin-left:.625em}
a.image{text-decoration:none}
span.footnote,span.footnoteref{vertical-align:super;font-size:.875em}
span.footnote a,span.footnoteref a{text-decoration:none}
span.footnote a:active,span.footnoteref a:active{text-decoration:underline}
#footnotes{padding-top:.75em;padding-bottom:.75em;margin-bottom:.625em}
#footnotes hr{width:20%;min-width:6.25em;margin:-.25em 0 .75em 0;border-width:1px 0 0 0}
#footnotes .footnote{padding:0 .375em;line-height:1.3;font-size:.875em;margin-left:1.2em;text-indent:-1.2em;margin-bottom:.2em}
#footnotes .footnote a:first-of-type{font-weight:bold;text-decoration:none}
#footnotes .footnote:last-of-type{margin-bottom:0}
#content #footnotes{margin-top:-.625em;margin-bottom:0;padding:.75em 0}
.gist .file-data>table{border:0;background:#fff;width:100%;margin-bottom:0}
.gist .file-data>table td.line-data{width:99%}
div.unbreakable{page-break-inside:avoid}
.big{font-size:larger}
.small{font-size:smaller}
.underline{text-decoration:underline}
.overline{text-decoration:overline}
.line-through{text-decoration:line-through}
.aqua{color:#00bfbf}
.aqua-background{background-color:#00fafa}
.black{color:#000}
.black-background{background-color:#000}
.blue{color:#0000bf}
.blue-background{background-color:#0000fa}
.fuchsia{color:#bf00bf}
.fuchsia-background{background-color:#fa00fa}
.gray{color:#606060}
.gray-background{background-color:#7d7d7d}
.green{color:#006000}
.green-background{background-color:#007d00}
.lime{color:#00bf00}
.lime-background{background-color:#00fa00}
.maroon{color:#600000}
.maroon-background{background-color:#7d0000}
.navy{color:#000060}
.navy-background{background-color:#00007d}
.olive{color:#606000}
.olive-background{background-color:#7d7d00}
.purple{color:#600060}
.purple-background{background-color:#7d007d}
.red{color:#bf0000}
.red-background{background-color:#fa0000}
.silver{color:#909090}
.silver-background{background-color:#bcbcbc}
.teal{color:#006060}
.teal-background{background-color:#007d7d}
.white{color:#bfbfbf}
.white-background{background-color:#fafafa}
.yellow{color:#bfbf00}
.yellow-background{background-color:#fafa00}
span.icon>.fa{cursor:default}
.admonitionblock td.icon [class^="fa icon-"]{font-size:2.5em;text-shadow:1px 1px 2px rgba(0,0,0,.5);cursor:default}
.admonitionblock td.icon .icon-note:before{content:"\f05a";color:#19407c}
.admonitionblock td.icon .icon-tip:before{content:"\f0eb";text-shadow:1px 1px 2px rgba(155,155,0,.8);color:#111}
.admonitionblock td.icon .icon-warning:before{content:"\f071";color:#bf6900}
.admonitionblock td.icon .icon-caution:before{content:"\f06d";color:#bf3400}
.admonitionblock td.icon .icon-important:before{content:"\f06a";color:#bf0000}
.conum[data-value]{display:inline-block;color:#fff!important;background-color:rgba(0,0,0,.8);-webkit-border-radius:100px;border-radius:100px;text-align:center;font-size:.75em;width:1.67em;height:1.67em;line-height:1.67em;font-family:"Open Sans","DejaVu Sans",sans-serif;font-style:normal;font-weight:bold}
.conum[data-value] *{color:#fff!important}
.conum[data-value]+b{display:none}
.conum[data-value]:after{content:attr(data-value)}
pre .conum[data-value]{position:relative;top:-.125em}
b.conum *{color:inherit!important}
.conum:not([data-value]):empty{display:none}
h1,h2{letter-spacing:-.01em}
dt,th.tableblock,td.content{text-rendering:optimizeLegibility}
p,td.content{letter-spacing:-.01em}
p strong,td.content strong{letter-spacing:-.005em}
p,blockquote,dt,td.content{font-size:1.0625rem}
p{margin-bottom:1.25rem}
.sidebarblock p,.sidebarblock dt,.sidebarblock td.content,p.tableblock{font-size:1em}
.exampleblock>.content{background-color:#fffef7;border-color:#e0e0dc;-webkit-box-shadow:0 1px 4px #e0e0dc;box-shadow:0 1px 4px #e0e0dc}
.print-only{display:none!important}
@media print{@page{margin:1.25cm .75cm}
*{-webkit-box-shadow:none!important;box-shadow:none!important;text-shadow:none!important}
a{color:inherit!important;text-decoration:underline!important}
a.bare,a[href^="#"],a[href^="mailto:"]{text-decoration:none!important}
a[href^="http:"]:not(.bare):after,a[href^="https:"]:not(.bare):after{content:"(" attr(href) ")";display:inline-block;font-size:.875em;padding-left:.25em}
abbr[title]:after{content:" (" attr(title) ")"}
pre,blockquote,tr,img{page-break-inside:avoid}
thead{display:table-header-group}
img{max-width:100%!important}
p,blockquote,dt,td.content{font-size:1em;orphans:3;widows:3}
h2,h3,#toctitle,.sidebarblock>.content>.title{page-break-after:avoid}
#toc,.sidebarblock,.exampleblock>.content{background:none!important}
#toc{border-bottom:1px solid #ddddd8!important;padding-bottom:0!important}
.sect1{padding-bottom:0!important}
.sect1+.sect1{border:0!important}
#header>h1:first-child{margin-top:1.25rem}
body.book #header{text-align:center}
body.book #header>h1:first-child{border:0!important;margin:2.5em 0 1em 0}
body.book #header .details{border:0!important;display:block;padding:0!important}
body.book #header .details span:first-child{margin-left:0!important}
body.book #header .details br{display:block}
body.book #header .details br+span:before{content:none!important}
body.book #toc{border:0!important;text-align:left!important;padding:0!important;margin:0!important}
body.book #toc,body.book #preamble,body.book h1.sect0,body.book .sect1>h2{page-break-before:always}
.listingblock code[data-lang]:before{display:block}
#footer{background:none!important;padding:0 .9375em}
#footer-text{color:rgba(0,0,0,.6)!important;font-size:.9em}
.hide-on-print{display:none!important}
.print-only{display:block!important}
.hide-for-print{display:none!important}
.show-for-print{display:inherit!important}}
div.paragraph.indented p {padding-left: 3em;}
div.paragraph.indented2 p {padding-left: 6em;}
div.paragraph.indented3 p {padding-left: 9em;}
</style>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.5.0/css/font-awesome.min.css">
<style>
/* Stylesheet for CodeRay to match GitHub theme | MIT License | http://foundation.zurb.com */
/*pre.CodeRay {background-color:#f7f7f8;}*/
.CodeRay .line-numbers{border-right:1px solid #d8d8d8;padding:0 0.5em 0 .25em}
.CodeRay span.line-numbers{display:inline-block;margin-right:.5em;color:rgba(0,0,0,.3)}
.CodeRay .line-numbers strong{color:rgba(0,0,0,.4)}
table.CodeRay{border-collapse:separate;border-spacing:0;margin-bottom:0;border:0;background:none}
table.CodeRay td{vertical-align: top;line-height:1.45}
table.CodeRay td.line-numbers{text-align:right}
table.CodeRay td.line-numbers>pre{padding:0;color:rgba(0,0,0,.3)}
table.CodeRay td.code{padding:0 0 0 .5em}
table.CodeRay td.code>pre{padding:0}
.CodeRay .debug{color:#fff !important;background:#000080 !important}
.CodeRay .annotation{color:#007}
.CodeRay .attribute-name{color:#000080}
.CodeRay .attribute-value{color:#700}
.CodeRay .binary{color:#509}
.CodeRay .comment{color:#998;font-style:italic}
.CodeRay .char{color:#04d}
.CodeRay .char .content{color:#04d}
.CodeRay .char .delimiter{color:#039}
.CodeRay .class{color:#458;font-weight:bold}
.CodeRay .complex{color:#a08}
.CodeRay .constant,.CodeRay .predefined-constant{color:#008080}
.CodeRay .color{color:#099}
.CodeRay .class-variable{color:#369}
.CodeRay .decorator{color:#b0b}
.CodeRay .definition{color:#099}
.CodeRay .delimiter{color:#000}
.CodeRay .doc{color:#970}
.CodeRay .doctype{color:#34b}
.CodeRay .doc-string{color:#d42}
.CodeRay .escape{color:#666}
.CodeRay .entity{color:#800}
.CodeRay .error{color:#808}
.CodeRay .exception{color:inherit}
.CodeRay .filename{color:#099}
.CodeRay .function{color:#900;font-weight:bold}
.CodeRay .global-variable{color:#008080}
.CodeRay .hex{color:#058}
.CodeRay .integer,.CodeRay .float{color:#099}
.CodeRay .include{color:#555}
.CodeRay .inline{color:#000}
.CodeRay .inline .inline{background:#ccc}
.CodeRay .inline .inline .inline{background:#bbb}
.CodeRay .inline .inline-delimiter{color:#d14}
.CodeRay .inline-delimiter{color:#d14}
.CodeRay .important{color:#555;font-weight:bold}
.CodeRay .interpreted{color:#b2b}
.CodeRay .instance-variable{color:#008080}
.CodeRay .label{color:#970}
.CodeRay .local-variable{color:#963}
.CodeRay .octal{color:#40e}
.CodeRay .predefined{color:#369}
.CodeRay .preprocessor{color:#579}
.CodeRay .pseudo-class{color:#555}
.CodeRay .directive{font-weight:bold}
.CodeRay .type{font-weight:bold}
.CodeRay .predefined-type{color:inherit}
.CodeRay .reserved,.CodeRay .keyword {color:#000;font-weight:bold}
.CodeRay .key{color:#808}
.CodeRay .key .delimiter{color:#606}
.CodeRay .key .char{color:#80f}
.CodeRay .value{color:#088}
.CodeRay .regexp .delimiter{color:#808}
.CodeRay .regexp .content{color:#808}
.CodeRay .regexp .modifier{color:#808}
.CodeRay .regexp .char{color:#d14}
.CodeRay .regexp .function{color:#404;font-weight:bold}
.CodeRay .string{color:#d20}
.CodeRay .string .string .string{background:#ffd0d0}
.CodeRay .string .content{color:#d14}
.CodeRay .string .char{color:#d14}
.CodeRay .string .delimiter{color:#d14}
.CodeRay .shell{color:#d14}
.CodeRay .shell .delimiter{color:#d14}
.CodeRay .symbol{color:#990073}
.CodeRay .symbol .content{color:#a60}
.CodeRay .symbol .delimiter{color:#630}
.CodeRay .tag{color:#008080}
.CodeRay .tag-special{color:#d70}
.CodeRay .variable{color:#036}
.CodeRay .insert{background:#afa}
.CodeRay .delete{background:#faa}
.CodeRay .change{color:#aaf;background:#007}
.CodeRay .head{color:#f8f;background:#505}
.CodeRay .insert .insert{color:#080}
.CodeRay .delete .delete{color:#800}
.CodeRay .change .change{color:#66f}
.CodeRay .head .head{color:#f4f}
</style>
</head>
<body class="book toc2 toc-left">
<div id="header">
<h1>Stored Procedures in Java (SPJs) Guide</h1>
<div class="details">
<span id="revnumber">version 2.4.0</span>
</div>
<div id="toc" class="toc2">
<div id="toctitle">Table of Contents</div>
<ul class="sectlevel1">
<li><a href="#_about_this_document">1. About This Document</a>
<ul class="sectlevel2">
<li><a href="#_intended_audience">1.1. Intended Audience</a></li>
<li><a href="#_document_organization">1.2. Document Organization</a></li>
<li><a href="#_new_and_changed_information">1.3. New and Changed Information</a></li>
<li><a href="#_notation_conventions">1.4. Notation Conventions</a></li>
<li><a href="#_comments_encouraged">1.5. Comments Encouraged</a></li>
</ul>
</li>
<li><a href="#introduction">2. Introduction</a>
<ul class="sectlevel2">
<li><a href="#what-is-an-spj">2.1. What Is an SPJ?</a></li>
<li><a href="#benefits-of-spjs">2.2. Benefits of SPJs</a>
<ul class="sectlevel3">
<li><a href="#java-methods-callable-from-sql">2.2.1. Java Methods Callable From SQL</a></li>
<li><a href="#common-packaging-technique">2.2.2. Common Packaging Technique</a></li>
<li><a href="#security">2.2.3. Security</a></li>
<li><a href="#increased-productivity">2.2.4. Increased Productivity</a></li>
<li><a href="#portability">2.2.5. Portability</a></li>
</ul>
</li>
<li><a href="#use-spjs">2.3. Use SPJs</a></li>
</ul>
</li>
<li><a href="#get-started">3. Get Started</a>
<ul class="sectlevel2">
<li><a href="#required-client-software">3.1. Required Client Software</a>
<ul class="sectlevel3">
<li><a href="#java-development-kit">3.1.1. Java Development Kit</a></li>
</ul>
</li>
<li><a href="#recommended-client-software">3.2. Recommended Client Software</a>
<ul class="sectlevel3">
<li><a href="#trafodion-command-interface-trafci">3.2.1. Trafodion Command Interface (trafci)</a></li>
<li><a href="#hp-jdbc-type-4-driver">3.2.2. Trafodion JDBC Type 4 Driver</a></li>
</ul>
</li>
</ul>
</li>
<li><a href="#develop-spj-methods">4. Develop SPJ Methods</a>
<ul class="sectlevel2">
<li><a href="#guidelines-for-writing-spj-methods">4.1. Guidelines for Writing SPJ Methods</a>
<ul class="sectlevel3">
<li><a href="#signature-of-the-java-method">4.1.1. Signature of the Java Method</a></li>
<li><a href="#returning-output-values-from-the-java-method">4.1.2. Returning Output Values From the Java Method</a></li>
<li><a href="#returning-stored-procedure-result-sets">4.1.3. Returning Stored Procedure Result Sets</a></li>
<li><a href="#using-the-main-method">4.1.4. Using the main() Method</a></li>
<li><a href="#null-input-and-output">4.1.5. Null Input and Output</a></li>
<li><a href="#static-java-variables">4.1.6. Static Java Variables</a></li>
<li><a href="#nested-java-method-invocations">4.1.7. Nested Java Method Invocations</a></li>
</ul>
</li>
<li><a href="#accessing-a-trafodion-database">4.2. Accessing Trafodion</a>
<ul class="sectlevel3">
<li><a href="#use-of-java.sql.connection-objects">4.2.1. Use of java.sql.Connection Objects</a></li>
<li><a href="#using-jdbc-method-calls">4.2.2. Using JDBC Method Calls</a></li>
<li><a href="#referring-to-database-objects-in-an-spj-method">4.2.3. Referring to Database Objects in an SPJ Method</a></li>
<li><a href="#using-the-session_user-or-current_user-function-in-an-spj-method">4.2.4. Using the SESSION_USER or CURRENT_USER Function in an SPJ Method</a></li>
<li><a href="#exception-handling">4.2.5. Exception Handling</a></li>
</ul>
</li>
<li><a href="#handling-java-exceptions">4.3. Handling Java Exceptions</a>
<ul class="sectlevel3">
<li><a href="#user-defined-exceptions">4.3.1. User-Defined Exceptions</a></li>
</ul>
</li>
<li><a href="#compiling-and-packaging-java-classes">4.4. Compiling and Packaging Java Classes</a></li>
</ul>
</li>
<li><a href="#deploy-spj-jar-files">5. Deploy SPJ JAR Files</a>
<ul class="sectlevel2">
<li><a href="#create-a-library">5.1. Create a Library</a></li>
<li><a href="#drop-a-library">5.2. Drop a Library</a></li>
<li><a href="#display-libraries">5.3. Display Libraries</a></li>
</ul>
</li>
<li><a href="#create-spjs">6. Create SPJs</a>
<ul class="sectlevel2">
<li><a href="#create-a-procedure">6.1. Create a Procedure</a>
<ul class="sectlevel3">
<li><a href="#create-procedure-settings">6.1.1. Create Procedure Settings</a></li>
</ul>
</li>
<li><a href="#understand-external-security">6.2. Understand External Security</a></li>
<li><a href="#drop-a-procedure">6.3. Drop a Procedure</a></li>
<li><a href="#display-procedures-and-their-properties">6.4. Display Procedures and Their Properties</a></li>
</ul>
</li>
<li><a href="#grant-privileges">7. Grant Privileges</a>
<ul class="sectlevel2">
<li><a href="#granting-execute-privileges-on-an-spj">7.1. Granting Execute Privileges on an SPJ</a></li>
<li><a href="#granting-privileges-on-referenced-database-objects">7.2. Granting Privileges on Referenced Database Objects</a></li>
<li><a href="#revoking-execute-privileges-on-an-spj">7.3. Revoking Execute Privileges on an SPJ</a></li>
<li><a href="#_using_script_files_to_grant_and_revoke_privileges">7.4. Using Script Files to Grant and Revoke Privileges</a>
<ul class="sectlevel3">
<li><a href="#_script_file_for_granting_privileges">7.4.1. Script File for Granting Privileges</a></li>
<li><a href="#_script_file_for_revoking_privileges">7.4.2. Script File for Revoking Privileges</a></li>
</ul>
</li>
</ul>
</li>
<li><a href="#execute-spjs">8. Execute SPJs</a>
<ul class="sectlevel2">
<li><a href="#required-privileges-for-calling-an-spj">8.1. Required Privileges for Calling an SPJ</a></li>
<li><a href="#transaction-behavior">8.2. Transaction Behavior</a>
<ul class="sectlevel3">
<li><a href="#transaction-required">8.2.1. Transaction Required</a></li>
<li><a href="#no-transaction-required">8.2.2. No Transaction Required</a></li>
</ul>
</li>
<li><a href="#multithreading">8.3. Multithreading</a></li>
<li><a href="#using-the-call-statement">8.4. Using the CALL Statement</a>
<ul class="sectlevel3">
<li><a href="#specifying-the-name-of-the-spj">8.4.1. Specifying the Name of the SPJ</a></li>
<li><a href="#listing-the-parameter-arguments-of-the-spj">8.4.2. Listing the Parameter Arguments of the SPJ</a></li>
</ul>
</li>
<li><a href="#calling-spjs-in-trafci">8.5. Calling SPJs in trafci</a>
<ul class="sectlevel3">
<li><a href="#using-named-parameters">8.5.1. Using Named Parameters</a></li>
<li><a href="#using-unnamed-parameters">8.5.2. Using Unnamed Parameters</a></li>
<li><a href="#returning-result-sets-in-trafci">8.5.3. Returning Result Sets in trafci</a></li>
</ul>
</li>
<li><a href="#calling-spjs-from-an-odbc-client-application">8.6. Calling SPJs From an ODBC Client Application</a>
<ul class="sectlevel3">
<li><a href="#returning-result-sets-in-an-odbc-client-application">8.6.1. Returning Result Sets in an ODBC Client Application</a></li>
</ul>
</li>
<li><a href="#calling-spjs-from-a-jdbc-client-application">8.7. Calling SPJs From a JDBC Client Application</a>
<ul class="sectlevel3">
<li><a href="#returning-result-sets-in-a-jdbc-client-application">8.7.1. Returning Result Sets in a JDBC Client Application</a></li>
</ul>
</li>
</ul>
</li>
<li><a href="#performance-and-troubleshooting">9. Performance and Troubleshooting</a>
<ul class="sectlevel2">
<li><a href="#troubleshooting-common-problems">9.1. Troubleshooting Common Problems</a></li>
<li><a href="#performance-tip">9.2. Performance Tip</a></li>
<li><a href="#displaying-an-execution-plan-of-a-call-statement">9.3. Displaying an Execution Plan of a CALL Statement</a>
<ul class="sectlevel3">
<li><a href="#using-the-explain-statement">9.3.1. Using the EXPLAIN Statement</a></li>
<li><a href="#using-the-explain-function">9.3.2. Using the EXPLAIN Function</a></li>
</ul>
</li>
</ul>
</li>
<li><a href="#a-sample-spjs">10. A Sample SPJs</a>
<ul class="sectlevel2">
<li><a href="#procedures-in-the-sales-schema">10.1. Procedures in the SALES Schema</a>
<ul class="sectlevel3">
<li><a href="#lowerprice-procedure">10.1.1. LOWERPRICE Procedure</a></li>
<li><a href="#dailyorders-procedure">10.1.2. DAILYORDERS Procedure</a></li>
<li><a href="#monthlyorders-procedure">10.1.3. MONTHLYORDERS Procedure</a></li>
<li><a href="#totalprice-procedure">10.1.4. TOTALPRICE Procedure</a></li>
<li><a href="#partdata-procedure">10.1.5. PARTDATA Procedure</a></li>
<li><a href="#ordersummary-procedure">10.1.6. ORDERSUMMARY Procedure</a></li>
</ul>
</li>
<li><a href="#procedures-in-the-persnl-schema">10.2. Procedures in the PERSNL Schema</a>
<ul class="sectlevel3">
<li><a href="#adjustsalary-procedure">10.2.1. ADJUSTSALARY Procedure</a></li>
<li><a href="#employeejob-procedure">10.2.2. EMPLOYEEJOB Procedure</a></li>
<li><a href="#projectteam-procedure">10.2.3. PROJECTTEAM Procedure</a></li>
<li><a href="#topsalesreps-procedure">10.2.4. TOPSALESREPS Procedure</a></li>
</ul>
</li>
<li><a href="#procedures-in-the-invent-schema">10.3. Procedures in the INVENT Schema</a>
<ul class="sectlevel3">
<li><a href="#supplierinfo-procedure">10.3.1. SUPPLIERINFO Procedure</a></li>
<li><a href="#supplyquantities-procedure">10.3.2. SUPPLYQUANTITIES Procedure</a></li>
<li><a href="#partlocations-procedure">10.3.3. PARTLOCATIONS Procedure</a></li>
</ul>
</li>
</ul>
</li>
<li><a href="#b-sample-database">11. B Sample Database</a>
<ul class="sectlevel2">
<li><a href="#persnl-schema">11.1. PERSNL Schema</a>
<ul class="sectlevel3">
<li><a href="#job-table">11.1.1. JOB Table</a></li>
<li><a href="#employee-table">11.1.2. EMPLOYEE Table</a></li>
<li><a href="#dept-table">11.1.3. DEPT Table</a></li>
<li><a href="#project-table">11.1.4. PROJECT Table</a></li>
</ul>
</li>
<li><a href="#sales-schema">11.2. SALES Schema</a>
<ul class="sectlevel3">
<li><a href="#customer-table">11.2.1. CUSTOMER Table</a></li>
<li><a href="#orders-table">11.2.2. ORDERS Table</a></li>
<li><a href="#odetail-table">11.2.3. ODETAIL Table</a></li>
<li><a href="#parts-table">11.2.4. PARTS Table</a></li>
<li><a href="#invent-schema">11.2.5. INVENT Schema</a></li>
<li><a href="#supplier-table">11.2.6. SUPPLIER Table</a></li>
<li><a href="#partsupp-table">11.2.7. PARTSUPP Table</a></li>
<li><a href="#partloc-table">11.2.8. PARTLOC Table</a></li>
</ul>
</li>
</ul>
</li>
</ul>
</div>
</div>
<div id="content">
<div id="preamble">
<div class="sectionbody">
<div class="paragraph">
<p><strong>License Statement</strong></p>
</div>
<div class="paragraph">
<p>Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file
distributed with this work for additional information regarding copyright ownership. The ASF licenses this file
to you under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at <a href="http://www.apache.org/licenses/LICENSE-2.0" class="bare">http://www.apache.org/licenses/LICENSE-2.0</a></p>
</div>
<div class="paragraph">
<p>Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the
specific language governing permissions and limitations under the License.</p>
</div>
<div style="page-break-after: always;"></div>
<div class="paragraph">
<p><strong>Acknowledgements</strong></p>
</div>
<div class="paragraph">
<p>Microsoft®, Windows®, Windows NT®, Windows® XP, and Windows Vista® are
U.S. registered trademarks of Microsoft Corporation. Intel® and Intel®
Itanium® are trademarks of Intel Corporation in the U.S. and other
countries. Java® is a registered trademark of Oracle and/or its
affiliates. Motif, OSF/1, UNIX®, X/Open®, and the X device is a
trademark of X/Open Company Ltd. in the UK and other countries.</p>
</div>
<div class="paragraph">
<p>OSF, OSF/1, OSF/Motif, Motif, and Open Software Foundation are trademarks of
the Open Software Foundation in the U.S. and other countries.
© 1990, 1991, 1992, 1993 Open Software Foundation, Inc.</p>
</div>
<div class="paragraph">
<p>The OSF documentation and the OSF software to which it relates are derived in
part from materials supplied by the following: © 1987, 1988, 1989
Carnegie-Mellon University. © 1989, 1990, 1991 Digital Equipment
Corporation. © 1985, 1988, 1989, 1990 Encore Computer Corporation. © 1988 Free
Software Foundation, Inc. © 1987, 1988, 1989, 1990, 1991 Hewlett-Packard
Company. © 1985, 1987, 1988, 1989, 1990, 1991, 1992 International
Business Machines Corporation. © 1988, 1989 Massachusetts Institute of
Technology. © 1988, 1989, 1990 Mentat Inc. © 1988 Microsoft Corporation.
© 1987, 1988, 1989, 1990, 1991,
1992 SecureWare, Inc. © 1990, 1991 Siemens Nixdorf Informations systeme
AG. © 1986, 1989, 1996, 1997 Sun Microsystems, Inc. © 1989, 1990, 1991
Transarc Corporation.</p>
</div>
<div class="paragraph">
<p>OSF software and documentation are based in part
on the Fourth Berkeley Software Distribution under license from The
Regents of the University of California. OSF acknowledges the following
individuals and institutions for their role in its development: Kenneth
C.R.C. Arnold, Gregory S. Couch, Conrad C. Huang, Ed James, Symmetric
Computer Systems, Robert Elz. © 1980, 1981, 1982, 1983, 1985, 1986,
1987, 1988, 1989 Regents of the University of California. OSF MAKES NO
WARRANTY OF ANY KIND WITH REGARD TO THE OSF MATERIAL PROVIDED HEREIN,
INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
AND FITNESS FOR A PARTICULAR PURPOSE. OSF shall not be liable for errors
contained herein or for incidental consequential damages in connection
with the furnishing, performance, or use of this material.</p>
</div>
<div style="page-break-after: always;"></div>
<div class="paragraph">
<p><strong>Revision History</strong></p>
</div>
<table class="tableblock frame-all grid-all spread">
<colgroup>
<col style="width: 50%;">
<col style="width: 50%;">
</colgroup>
<thead>
<tr>
<th class="tableblock halign-left valign-top">Version</th>
<th class="tableblock halign-left valign-top">Date</th>
</tr>
</thead>
<tbody>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">2.2.0</p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">TBD</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">2.1.0</p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">May 1, 2017</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">2.0.1</p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">July 7, 2016</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">2.0.0</p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">June 6, 2016</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">1.3.0</p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">January, 2016</p></td>
</tr>
</tbody>
</table>
</div>
</div>
<div class="sect1">
<h2 id="_about_this_document">1. About This Document</h2>
<div class="sectionbody">
<div class="paragraph">
<p>This guide describes how to develop, deploy, and manage Stored Procedures in Java (SPJs) on Trafodion.</p>
</div>
<div class="sect2">
<h3 id="_intended_audience">1.1. Intended Audience</h3>
<div class="paragraph">
<p>This manual is intended for application programmers who are writing and compiling Java code for stored procedures
and for database administrators who are deploying and managing Stored Procedures in Java (SPJs) on Trafodion.
The reader should know:</p>
</div>
<div class="ulist">
<ul>
<li>
<p>The Java programming language.</p>
</li>
<li>
<p>JDBC and the Trafodion JDBC Type-4 Driver.</p>
</li>
<li>
<p>Structured Query Language (SQL) and database terms and concepts.</p>
</li>
</ul>
</div>
<div class="paragraph">
<p>Although not required, it helps to be familiar with the part of the ANSI SQL/Foundation standard called
SQL/JRT (Java Routines and Types) on which this implementation of stored procedures is based.</p>
</div>
</div>
<div class="sect2">
<h3 id="_document_organization">1.2. Document Organization</h3>
<div class="paragraph">
<p>This document is organized as follows:</p>
</div>
<table class="tableblock frame-all grid-all spread">
<colgroup>
<col style="width: 30%;">
<col style="width: 70%;">
</colgroup>
<thead>
<tr>
<th class="tableblock halign-left valign-top">Chapter</th>
<th class="tableblock halign-left valign-top">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><strong><a href="#introduction">Introduction</a></strong></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Defines what an SPJ is, describes the benefits of using SPJs on the database,
and lists steps for developing and deploying SPJs on Trafodion.</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><strong><a href="#get-started">Get Started</a></strong></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Describes the software requirements for using SPJs on Trafodion.</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><strong><a href="#develop-spj-methods">Develop SPJ Methods</a></strong></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Provides guidelines for writing and compiling a Java method to be used as the
body of a stored procedure.</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><strong><a href="#deploy-spj-jar-files">Deploy SPJ JAR Files</a></strong></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Explains how to deploy SPJ JAR files on Trafodion.</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><strong><a href="#create-spjs">Create SPJs</a></strong></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Explains how to create, drop, and alter an SPJ on Trafodion.</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><strong><a href="#grant-privileges">Grant Privileges</a></strong></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Explains how to grant and revoke privileges for executing SPJs on the
Trafodion database.</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><strong><a href="#execute-spjs">Execute SPJs</a></strong></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Explains how to execute an SPJ on Trafodion by using the CALL statement.</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><strong><a href="#performance-and-troubleshooting">Performance and Troubleshooting</a></strong></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Describes how to improve and monitor the performance of SPJs and provides guidelines for troubleshooting common problems.</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><strong><a href="#sample-spjs">Appendix A: Sample SPJs</a></strong></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Provides examples of SPJ methods that demonstrate business logic on Trafodion.</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><strong><a href="#sample-database">Appendix B: Sample Database</a></strong></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Describes the sample database on which the Sample SPJs operate.</p></td>
</tr>
</tbody>
</table>
</div>
<div class="sect2">
<h3 id="_new_and_changed_information">1.3. New and Changed Information</h3>
<div class="paragraph">
<p>This is a new manual.</p>
</div>
</div>
<div class="sect2">
<h3 id="_notation_conventions">1.4. Notation Conventions</h3>
<div class="paragraph">
<p>This list summarizes the notation conventions for syntax presentation in this manual.</p>
</div>
<div class="ulist">
<ul>
<li>
<p>UPPERCASE LETTERS</p>
<div class="paragraph">
<p>Uppercase letters indicate keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required.</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="text">SELECT</code></pre>
</div>
</div>
</li>
<li>
<p>lowercase letters</p>
<div class="paragraph">
<p>Lowercase letters, regardless of font, indicate variable items that you supply. Items not enclosed in brackets are required.</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="text">file-name</code></pre>
</div>
</div>
</li>
<li>
<p>&#91; &#93; Brackets</p>
<div class="paragraph">
<p>Brackets enclose optional syntax items.</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="text">DATETIME [start-field TO] end-field</code></pre>
</div>
</div>
<div class="paragraph">
<p>A group of items enclosed in brackets is a list from which you can choose one item or none.</p>
</div>
<div class="paragraph">
<p>The items in the list can be arranged either vertically, with aligned brackets on each side of the list, or horizontally, enclosed in a pair of brackets and separated by vertical lines.</p>
</div>
<div class="paragraph">
<p>For example:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="text">DROP SCHEMA schema [CASCADE]
DROP SCHEMA schema [ CASCADE | RESTRICT ]</code></pre>
</div>
</div>
</li>
<li>
<p>{ } Braces</p>
<div class="paragraph">
<p>Braces enclose required syntax items.</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="text">FROM { grantee [, grantee ] ... }</code></pre>
</div>
</div>
<div class="paragraph">
<p>A group of items enclosed in braces is a list from which you are required to choose one item.</p>
</div>
<div class="paragraph">
<p>The items in the list can be arranged either vertically, with aligned braces on each side of the list, or horizontally, enclosed in a pair of braces and separated by vertical lines.</p>
</div>
<div class="paragraph">
<p>For example:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="text">INTERVAL { start-field TO end-field }
{ single-field }
INTERVAL { start-field TO end-field | single-field }</code></pre>
</div>
</div>
</li>
<li>
<p>| Vertical Line</p>
<div class="paragraph">
<p>A vertical line separates alternatives in a horizontal list that is enclosed in brackets or braces.</p>
</div>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="text">{expression | NULL}</code></pre>
</div>
</div>
<div class="ulist">
<ul>
<li>
<p>&#8230; Ellipsis</p>
<div class="paragraph">
<p>An ellipsis immediately following a pair of brackets or braces indicates that you can repeat the enclosed sequence of syntax items any number of times.</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="text">ATTRIBUTE[S] attribute [, attribute] ...
{, sql-expression } ...</code></pre>
</div>
</div>
<div class="paragraph">
<p>An ellipsis immediately following a single syntax item indicates that you can repeat that syntax item any number of times.</p>
</div>
<div class="paragraph">
<p>For example:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="text">expression-n ...</code></pre>
</div>
</div>
</li>
</ul>
</div>
<div style="page-break-after: always;"></div>
<div class="ulist">
<ul>
<li>
<p>Punctuation</p>
<div class="paragraph">
<p>Parentheses, commas, semicolons, and other symbols not previously described must be typed as shown.</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="text">DAY (datetime-expression)
@script-file</code></pre>
</div>
</div>
<div class="paragraph">
<p>Quotation marks around a symbol such as a bracket or brace indicate the symbol is a required character that you must type as shown.</p>
</div>
<div class="paragraph">
<p>For example:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="text">&quot;{&quot; module-name [, module-name] ... &quot;}&quot;</code></pre>
</div>
</div>
</li>
<li>
<p>Item Spacing</p>
<div class="paragraph">
<p>Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma.</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="text">DAY (datetime-expression) DAY(datetime-expression)</code></pre>
</div>
</div>
<div class="paragraph">
<p>If there is no space between two items, spaces are not permitted. In this example, no spaces are permitted between the period and any other items:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="text">myfile.sh</code></pre>
</div>
</div>
</li>
<li>
<p>Line Spacing</p>
<div class="paragraph">
<p>If the syntax of a command is too long to fit on a single line, each continuation line is indented three spaces and is separated from the preceding line by a blank line.</p>
</div>
<div class="paragraph">
<p>This spacing distinguishes items in a continuation line from items in a vertical list of selections.</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="text">match-value [NOT] LIKE _pattern
[ESCAPE esc-char-expression]</code></pre>
</div>
</div>
</li>
</ul>
</div>
<div style="page-break-after: always;"></div>
</div>
<div class="sect2">
<h3 id="_comments_encouraged">1.5. Comments Encouraged</h3>
<div class="paragraph">
<p>We encourage your comments concerning this document. We are committed to providing documentation that meets your
needs. Send any errors found, suggestions for improvement, or compliments to <a href="mailto:user@trafodion.apache.org">user@trafodion.apache.org</a>.</p>
</div>
<div class="paragraph">
<p>Include the document title and any comment, error found, or suggestion for improvement you have concerning this document.</p>
</div>
</div>
</div>
</div>
<div class="sect1">
<h2 id="introduction">2. Introduction</h2>
<div class="sectionbody">
<div class="paragraph">
<p>This chapter introduces stored procedures in Java (SPJs) in Trafodion
and covers these topics:</p>
</div>
<div class="sect2">
<h3 id="what-is-an-spj">2.1. What Is an SPJ?</h3>
<div class="paragraph">
<p>A stored procedure is a type of user-defined routine (UDR) that operates
within a database server and typically performs SQL operations on a
database. The database server contains information about the stored
procedure and controls its execution. A client application executes a
stored procedure by issuing an SQL CALL statement. Unlike a user-defined
function, which returns a value directly to the calling application, a
stored procedure returns each output value to a dynamic parameter in its
parameter list or returns a set of values to a result set array.</p>
</div>
<div class="paragraph">
<p>Trafodion supports stored procedures written in the Java
programming language. The Trafodion implementation of stored procedures
complies mostly, unless otherwise specified, with SQL/JRT (Java Routines
and Types), which extends the ANSI SQL/Foundation standard. A stored
procedure in Java (SPJ) is a Java method contained in a Java archive
(JAR) file on Trafodion, registered in the database, and
executed by the database engine when a client application issues a CALL
statement.</p>
</div>
<div class="paragraph">
<p>The body of a stored procedure consists of a public, static Java method
that returns void. These Java methods, called <em>SPJ methods</em>, are
contained in classes within JAR files on the cluster hosting Trafodion.</p>
</div>
<div class="paragraph">
<p>An SPJ method must be registered as a stored procedure in the database before a
client application can execute it with a CALL statement. You upload the SPJ to
the cluster where Trafodion is running
and then you register the SPJ as a library object using the
<a href="http://trafodion.apache.org/docs/sql_reference/index.html#create_library_statement">CREATE LIBRARY</a>
statement. Next, you register the library object using the
<a href="http://trafodion.apache.org/docs/sql_reference/index.html#create_procedure_statement">CREATE PROCEDURE</a>
statement.</p>
</div>
</div>
<div class="sect2">
<h3 id="benefits-of-spjs">2.2. Benefits of SPJs</h3>
<div class="paragraph">
<p>SPJs provide an efficient and secure way to implement business logic in
the database. SPJs offer these advantages:</p>
</div>
<div class="ulist">
<ul>
<li>
<p><a href="#java-methods-callable-from-sql">Java Methods Callable From SQL</a></p>
</li>
<li>
<p><a href="#common-packaging-technique">Common Packaging Technique</a></p>
</li>
<li>
<p><a href="#security">Security</a></p>
</li>
<li>
<p><a href="#increased-productivity">Increased Productivity</a></p>
</li>
<li>
<p><a href="#portability">Portability</a></p>
</li>
</ul>
</div>
<div class="sect3">
<h4 id="java-methods-callable-from-sql">2.2.1. Java Methods Callable From SQL</h4>
<div class="paragraph">
<p>With support for SPJs, Java methods are callable from any client
application that connects to Trafodion. For example, you can
invoke the same SPJ method from JDBC client applications and ODBC client
applications. By using the database engine to invoke Java methods, you
can extend the functionality of the database and share business logic
among different applications.</p>
</div>
<div class="paragraph">
<p>For more information, see <a href="#execute-spjs">Execute SPJs</a>.</p>
</div>
</div>
<div class="sect3">
<h4 id="common-packaging-technique">2.2.2. Common Packaging Technique</h4>
<div class="paragraph">
<p>Different applications can invoke the same SPJ to perform a common
business function. By encapsulating business logic in an SPJ, you can
maintain consistent database operations and avoid duplicating code in
applications.</p>
</div>
<div class="paragraph">
<p>Applications that call SPJs are not required to know the structure of
the database tables that the SPJ methods access. The application does
not need to use any table or column names; it needs only the name of the
stored procedure in the CALL statement. If the table structure changes,
you might need to change the SPJ methods but not necessarily the CALL
statements within each application.</p>
</div>
</div>
<div class="sect3">
<h4 id="security">2.2.3. Security</h4>
<div class="paragraph">
<p>By using SPJs, you can conceal sensitive business logic inside SPJ
methods instead of exposing it in client applications. You can also
grant privileges to execute an SPJ to specific users and restrict the
privileges of other users. For more information, see
<a href="#grant-privileges">Grant Privileges</a></p>
</div>
</div>
<div class="sect3">
<h4 id="increased-productivity">2.2.4. Increased Productivity</h4>
<div class="paragraph">
<p>Use SPJs to reduce the time and cost of developing and maintaining
client applications. By having several applications call the same SPJ,
you need only change the SPJ method once when business rules or table
structures change instead of changing every application that calls the
SPJ.</p>
</div>
<div class="paragraph">
<p>Using the Java language to implement stored procedures increases
productivity. Given the popularity of the Java language, you can
leverage the existing skill set of Java programmers to develop SPJs.</p>
</div>
<div class="paragraph">
<p>The portability of the Java language enables you to write and compile
Java class files for SPJs once and deploy them anywhere.</p>
</div>
</div>
<div class="sect3">
<h4 id="portability">2.2.5. Portability</h4>
<div class="paragraph">
<p>Because SPJ methods are written in Java, and SPJs conform to the ANSI
SQL standard, SPJs are portable across different database servers. With
minimal changes to SPJ methods, you can port existing SPJ JAR files from
another database server to Trafodion and register the methods
as stored procedures in a Trafodion database. You can also port client
applications that call SPJs in other databases to Trafodion SQL with
minimal changes to the CALL statements in the application.</p>
</div>
<div style="page-break-after: always;"></div>
</div>
</div>
<div class="sect2">
<h3 id="use-spjs">2.3. Use SPJs</h3>
<div class="paragraph">
<p>To use SPJs in Trafodion:</p>
</div>
<div class="olist arabic">
<ol class="arabic">
<li>
<p>Verify that you have the required software installed on the client
workstation. See <a href="#get-started">Get Started</a>.</p>
</li>
<li>
<p>Develop a Java method to be used as an SPJ:</p>
<div class="olist loweralpha">
<ol class="loweralpha" type="a">
<li>
<p>Write a static Java method:</p>
<div class="paragraph">
<p>Create a file named <code>Payroll.java</code> with the following content:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="java"><span class="keyword">import</span> <span class="include">java.sql</span>.* ;
<span class="keyword">import</span> <span class="include">java.math</span>.* ;
<span class="directive">public</span> <span class="type">class</span> <span class="class">Payroll</span>
{
<span class="directive">public</span> <span class="directive">static</span> <span class="type">void</span> adjustSalary( <span class="predefined-type">BigDecimal</span> empNum
, <span class="type">double</span> percent, <span class="predefined-type">BigDecimal</span><span class="type">[]</span> newSalary
) <span class="directive">throws</span> <span class="exception">SQLException</span>
{
<span class="predefined-type">Connection</span> conn =
<span class="predefined-type">DriverManager</span>.getConnection( <span class="string"><span class="delimiter">&quot;</span><span class="content">jdbc:default:connection</span><span class="delimiter">&quot;</span></span> ) ;
<span class="predefined-type">PreparedStatement</span> setSalary =
conn.prepareStatement( <span class="string"><span class="delimiter">&quot;</span><span class="content">UPDATE trafodion.persnl.employee </span><span class="delimiter">&quot;</span></span>
+ <span class="string"><span class="delimiter">&quot;</span><span class="content">SET salary = salary * (1 + (? / 100)) </span><span class="delimiter">&quot;</span></span>
+ <span class="string"><span class="delimiter">&quot;</span><span class="content">WHERE empnum = ?</span><span class="delimiter">&quot;</span></span>
) ;
<span class="predefined-type">PreparedStatement</span> getSalary =
conn.prepareStatement( <span class="string"><span class="delimiter">&quot;</span><span class="content">SELECT salary </span><span class="delimiter">&quot;</span></span>
+ <span class="string"><span class="delimiter">&quot;</span><span class="content">FROM trafodion.persnl.employee </span><span class="delimiter">&quot;</span></span>
+ <span class="string"><span class="delimiter">&quot;</span><span class="content">WHERE empnum = ?</span><span class="delimiter">&quot;</span></span>
) ;
setSalary.setDouble( <span class="integer">1</span>, percent ) ;
setSalary.setBigDecimal( <span class="integer">2</span>, empNum ) ;
setSalary.executeUpdate() ;
getSalary.setBigDecimal( <span class="integer">1</span>, empNum ) ;
<span class="predefined-type">ResultSet</span> rs = getSalary.executeQuery() ;
rs.next() ;
newSalary[<span class="integer">0</span>] = rs.getBigDecimal( <span class="integer">1</span> ) ;
rs.close() ;
conn.close() ;
}
}</code></pre>
</div>
</div>
</li>
<li>
<p>Compile the Java source file to produce a class file:</p>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="text">$ javac Payroll.java
$</code></pre>
</div>
</div>
</li>
<li>
<p>Package the SPJ class file in a JAR file:</p>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="text">jar cvf Payroll.jar Payroll.class</code></pre>
</div>
</div>
<div class="paragraph">
<p>If the SPJ class refers to other classes, package the other classes in
the same JAR file as the SPJ class:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="text">$ jar cvf Payroll.jar Payroll.class other.class
added manifest
adding: Payroll.class(in = 1213) (out= 711)(deflated 41%)
$</code></pre>
</div>
</div>
<div class="paragraph">
<p>For details, see <a href="#develop-spj-methods">Develop SPJ Methods</a>.</p>
</div>
</li>
</ol>
</div>
</li>
<li>
<p>Deploy the SPJ JAR file on Trafodion by creating a
library object for the JAR file in one of the database schemas. For
details, see <a href="#Deploy-spj-jar-files">Deploy SPJ JAR Files</a>.</p>
</li>
<li>
<p>As the schema owner, create the SPJ in the database. For details,
see <a href="#create-spjs">Create SPJs</a>.</p>
</li>
<li>
<p>Grant privileges to database users for executing the SPJ and for
operating on the referenced database objects. For example, you can issue
GRANT statements in an trafci session, as shown below:</p>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="sql"><span class="class">GRANT</span> EXECUTE
<span class="keyword">ON</span> <span class="type">PROCEDURE</span> trafodion.persnl.adjustsalary
<span class="keyword">TO</span> <span class="string"><span class="delimiter">&quot;</span><span class="content">payrolldir1</span><span class="delimiter">&quot;</span></span>, <span class="string"><span class="delimiter">&quot;</span><span class="content">payrolldir2</span><span class="delimiter">&quot;</span></span>
WITH <span class="class">GRANT</span> OPTION ;
<span class="class">GRANT</span> <span class="class">SELECT</span>, <span class="class">UPDATE</span> (salary)
<span class="keyword">ON</span> <span class="type">TABLE</span> trafodion.persnl.employee
<span class="keyword">TO</span> <span class="string"><span class="delimiter">&quot;</span><span class="content">payrolldir1</span><span class="delimiter">&quot;</span></span>, <span class="string"><span class="delimiter">&quot;</span><span class="content">payrolldir2</span><span class="delimiter">&quot;</span></span>
WITH <span class="class">GRANT</span> OPTION ;</code></pre>
</div>
</div>
<div class="paragraph">
<p>For details, see <a href="#grant-privileges">Grant Privileges</a>.</p>
</div>
<div style="page-break-after: always;"></div>
</li>
<li>
<p>Execute an SPJ by using a CALL statement in a client application.
For example, you can issue a CALL statement in an trafci session, as
shown below, or in a JDBC or ODBC client application:</p>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="text">SQL&gt; CALL trafodion.persnl.adjustsalary( 29, 2.5, ? ) ;
NEWSALARY
------------
139400.00
--- SQL operation complete.</code></pre>
</div>
</div>
<div class="paragraph">
<p>For details, see <a href="#execute-spjs">Execute SPJs</a>.</p>
</div>
</li>
<li>
<p>Monitor the performance of SPJs and resolve common problems with
SPJs in the database. See <a href="#performance-and-troubleshooting">Performance and Troubleshooting</a>.</p>
</li>
</ol>
</div>
</div>
</div>
</div>
<div class="sect1">
<h2 id="get-started">3. Get Started</h2>
<div class="sectionbody">
<div class="paragraph">
<p>Before you can start using SPJs on Trafodion, verify that
you have the required software installed on the client workstation.
Trafodion is delivered to you ready to use and pre-configured
with the software required to support SPJs.</p>
</div>
<div class="sect2">
<h3 id="required-client-software">3.1. Required Client Software</h3>
<div class="sect3">
<h4 id="java-development-kit">3.1.1. Java Development Kit</h4>
<div class="paragraph">
<p>To develop Java methods to be used as SPJs, you must have a Java
Development Kit (JDK) installed on the client workstation. To download a
JDK, go to
<a href="http://www.oracle.com/technetwork/java/index.html" class="bare">http://www.oracle.com/technetwork/java/index.html</a></p>
</div>
<div class="paragraph">
<p>The version of the JDK that you download and use on the client
workstation should be the same as or lower than the Java version running
on Trafodion. To check the Java version that is running in
Trafodion, use this approach:</p>
</div>
<div class="ulist">
<ul>
<li>
<p>Launch <code>trafci</code> on Trafodion, and run the <code>LOCALHOST</code> or <code>lh java -version</code> command.
(To use the on-platform trafci client, see the
<a href="http://trafodion.apache.org/docs/command_interface/index.html">Trafodion Command Interface Guide</a>.</p>
<div class="paragraph">
<p>For example:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="text">SQL&gt; lh java -version
java version &quot;1.6.0_06&quot;
Java(TM) SE Runtime Environment (build 1.6.0_06-b02)
Java HotSpot(TM) Client VM (build 10.0-b22, mixed mode)
SQL&gt;</code></pre>
</div>
</div>
<div class="paragraph">
<p>In this example, the returned Java version indicates that the Trafodion
instance supports Java SE 6 or JDK 6 (1.6.0), or earlier versions of the
JDK.</p>
</div>
</li>
</ul>
</div>
<div class="admonitionblock note">
<table>
<tr>
<td class="icon">
<i class="fa icon-note" title="Note"></i>
</td>
<td class="content">
If you plan to install the Trafodion JDBC Type-4 Driver on the client
workstation, you must have JDK 6 (1.6.0) or higher installed on the
client workstation.
</td>
</tr>
</table>
</div>
<div style="page-break-after: always;"></div>
</div>
</div>
<div class="sect2">
<h3 id="recommended-client-software">3.2. Recommended Client Software</h3>
<div class="sect3">
<h4 id="trafodion-command-interface-trafci">3.2.1. Trafodion Command Interface (trafci)</h4>
<div class="paragraph">
<p><code>trafci</code> is a command-line interface in which you can run SQL statements,
such as GRANT PROCEDURE and CALL statements, interactively or from
script files. To install <code>trafci</code> on a client workstation, see the
<a href="http://trafodion.apache.org/docs/client_install/index.html">Trafodion Client Installation Guide</a>.</p>
</div>
</div>
<div class="sect3">
<h4 id="hp-jdbc-type-4-driver">3.2.2. Trafodion JDBC Type 4 Driver</h4>
<div class="paragraph">
<p>If you plan to use <code>trafci</code>, you must have a compatible version of the Trafodion
JDBC Type-4 Driver installed on the client workstation.</p>
</div>
<div class="paragraph">
<p>To install the JDBC Type-4 driver on the client workstation, see the
<a href="http://trafodion.apache.org/docs/client_install/index.html">Trafodion Client Installation Guide</a>.</p>
</div>
</div>
</div>
</div>
</div>
<div class="sect1">
<h2 id="develop-spj-methods">4. Develop SPJ Methods</h2>
<div class="sectionbody">
<div class="paragraph">
<p>Before creating, or registering, an SPJ in the database, you must write
and compile the Java method to be used as the body of the SPJ. The
manual refers to those Java methods as <em>SPJ methods</em>.</p>
</div>
<div class="paragraph">
<p>This chapter requires a familiarity with writing and compiling Java programs.</p>
</div>
<div class="sect2">
<h3 id="guidelines-for-writing-spj-methods">4.1. Guidelines for Writing SPJ Methods</h3>
<div class="paragraph">
<p>Follow the guidelines for these topics when you write SPJ methods to be
used as SPJs in the database:</p>
</div>
<div class="sect3">
<h4 id="signature-of-the-java-method">4.1.1. Signature of the Java Method</h4>
<div class="paragraph">
<p>A Java method that you use as an SPJ must have this general signature:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="java"><span class="directive">public</span> <span class="directive">static</span> <span class="type">void</span> myMethodName ( java-parameter-list )</code></pre>
</div>
</div>
<div class="sect4">
<h5 id="public-access-and-static-modifiers">Public Access and Static Modifiers</h5>
<div class="paragraph">
<p>The Java method must be defined as <code>public</code> and <code>static</code>. If a method is
<code>private</code> or <code>protected</code>, then Trafodion is unable to find the Java
method when you try to register the SPJ and returns an error. The Java
method must be defined as <code>static</code> so that the method can be invoked
without having to instantiate its class.</p>
</div>
</div>
<div class="sect4">
<h5 id="void-return-type">Void Return Type</h5>
<div class="paragraph">
<p>The return type of the Java method must be <code>void</code>. The method must not
return a value directly to the caller.</p>
</div>
</div>
<div class="sect4">
<h5 id="java-parameters">Java Parameters</h5>
<div class="paragraph">
<p>Except for result sets, which are described in
<a href="#returning-stored-procedure-result-sets">Returning Stored Procedure Result Sets</a>,
the parameter types in the Java signature must correspond to the SQL
parameters of the stored procedure that you are planning to create. For
type mappings, see the table below.</p>
</div>
<div style="page-break-after: always;"></div>
<table id="table-1" class="tableblock frame-all grid-all spread">
<caption class="title">Table 1. Mapping of Java Data Types to SQL Data Types</caption>
<colgroup>
<col style="width: 60%;">
<col style="width: 40%;">
</colgroup>
<thead>
<tr>
<th class="tableblock halign-left valign-top">SQL Data Type</th>
<th class="tableblock halign-left valign-top">Maps to Java Data Type&#8230;</th>
</tr>
</thead>
<tbody>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">CHAR[ACTER]<br>
CHAR[ACTER] VARYING<br>
VARCHAR<br>
PIC[TURE] X<sup>1</sup><br>
NCHAR<br>
NCHAR VARYING<br>
NATIONAL CHAR[ACTER]<br>
NATIONAL CHAR[ACTER] VARYING</p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">java.lang.String</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">DATE</p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">java.sql.Date</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">TIME</p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">java.sql.Time</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">TIMESTAMP</p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">java.sql.Timestamp</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">DEC<sup class="IMAL">2</sup><br>
PIC[TURE] S9<sup>3</sup><br>
NUMERIC (including numeric with a precision greater than eighteen)<sup>2</sup></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">java.math.BigDecimal</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">SMALLINT<sup>2</sup></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">short</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">INT<sup class="EGER">2</sup></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">int or java.lang.Integer<sup>4</sup></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">LARGEINT<sup>2</sup></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">long or java.lang.Long<sup>4</sup></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">FLOAT</p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">double or java.lang.Double<sup>4</sup></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">REAL</p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">float or java.lang.Float<sup>4</sup></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">DOUBLE PRECISION</p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">double or java.lang.Double<sup>4</sup></p></td>
</tr>
</tbody>
</table>
<div class="olist arabic">
<ol class="arabic">
<li>
<p>Trafodion stores PIC X as a CHAR data type.</p>
</li>
<li>
<p>Numeric data types of SQL parameters must be signed, which is the default in Trafodion.</p>
</li>
<li>
<p>Trafodion stores PIC S9 as a DECIMAL or NUMERIC data type.</p>
</li>
<li>
<p>By default, the SQL data type maps to a Java primitive type. The SQL data type maps to a Java wrapper class
only if you specify the wrapper class in the Java signature of the external name clause.</p>
</li>
</ol>
</div>
<div class="paragraph">
<p>Output parameters in the Java signature must be arrays (for example,
<code>int[]</code> or <code>String[]</code>) that accept only one value in the first element of
the array at index 0. For more information, see
<a href="#returning-output-values-from-the-java-method">Returning Output Values From the Java Method</a>.</p>
</div>
<div style="page-break-after: always;"></div>
</div>
</div>
<div class="sect3">
<h4 id="returning-output-values-from-the-java-method">4.1.2. Returning Output Values From the Java Method</h4>
<div class="paragraph">
<p>The Java method can return data to the calling application in the form
of output parameters or result sets.</p>
</div>
<div class="paragraph">
<p><em>Output parameters</em> in the Java signature are parameter arrays that
accept only one value in the first element of the array at index 0. The
array objects have a fixed size of one element.</p>
</div>
<div class="admonitionblock important">
<table>
<tr>
<td class="icon">
<i class="fa icon-important" title="Important"></i>
</td>
<td class="content">
You cannot return more than one value to an output parameter of an SPJ.
Any attempt to return more than one value to an output parameter results in a Java exception,
<code>ArrayIndexOutOfBounds</code>.
</td>
</tr>
</table>
</div>
<div class="paragraph">
<p><em>Result sets</em> in the Java signature are one-element <code>java.sql.ResultSet[]</code>
arrays that contain ResultSet objects that have multiple rows of data.
For more information about result sets, see
<a href="#returning-stored-procedure-result-sets">Returning Stored Procedure Result Sets</a></p>
</div>
<div class="paragraph">
<p>This subsection covers these topics related to output parameters:</p>
</div>
<div class="ulist">
<ul>
<li>
<p><a href="#using-arrays-for-output-parameters">Using Arrays for Output Parameters</a></p>
</li>
<li>
<p><a href="#type-mapping-of-output-parameters">Type Mapping of Output Parameters</a></p>
</li>
</ul>
</div>
<div class="sect4">
<h5 id="using-arrays-for-output-parameters">Using Arrays for Output Parameters</h5>
<div class="paragraph">
<p>You must use arrays for the output parameters of a Java method because
of how Java handles the arguments of a method. Java supports arguments
that are passed by value to a method and does not support arguments that
are passed by reference. As a result, Java primitive types can be passed
only to a method, not out of a method. Because a Java array is an
object, its reference is passed by value to a method, and changes to the
array are visible to the caller of the method. Therefore, arrays must be
used for output parameters in a Java method.</p>
</div>
<div class="admonitionblock important">
<table>
<tr>
<td class="icon">
<i class="fa icon-important" title="Important"></i>
</td>
<td class="content">
An output parameter accepts only one value in the first element
of the array at index 0. Any attempt to return more than one value to an
output parameter results in a Java exception, <code>ArrayIndexOutOfBounds</code>.
</td>
</tr>
</table>
</div>
<div class="paragraph">
<p>For each output parameter, specify the Java type followed by empty
square brackets (<code>[]</code>) to indicate that the type is an array. For example,
specify an int type as <code>int[]</code> for an output parameter in the Java
signature.</p>
</div>
<div class="paragraph">
<p>To return multiple values from a Java method, use an output parameter
for each returned value. For example, the <code>supplierInfo()</code> method returns
a supplier&#8217;s name, address, city, state, and post code, each as a single
string in an output parameter:</p>
</div>
<div style="page-break-after: always;"></div>
<div class="paragraph">
<p>The <code>supplyQuantities()</code> method returns an average quantity, a minimum
quantity, and a maximum quantity to separate output parameters of the
integer type:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="java"><span class="directive">public</span> <span class="directive">static</span> <span class="type">void</span> supplyQuantities( <span class="type">int</span><span class="type">[]</span> avgQty
, <span class="type">int</span><span class="type">[]</span> minQty
, <span class="type">int</span><span class="type">[]</span> maxQty
) <span class="directive">throws</span> <span class="exception">SQLException</span>
{
...
}</code></pre>
</div>
</div>
<div class="paragraph">
<p>For more information about the SPJ examples, see
<a href="#sample-spjs">Appendix A: Sample SPJs</a>.</p>
</div>
</div>
<div class="sect4">
<h5 id="type-mapping-of-output-parameters">Type Mapping of Output Parameters</h5>
<div class="paragraph">
<p>When writing an SPJ method, consider how the output of the SPJ is
used in the calling application. For output parameters, the Java data
type of the SPJ method must map to an SQL data type. See
<a href="#table-1">Table 1</a>.</p>
</div>
<div class="paragraph">
<p>The SQL data type must then map to a compatible data type in the calling
application. For the client application programming interfaces (APIs) that
support SPJs and for cross-references to the appropriate manuals for type
mappings between Trafodion SQL and each API, see
<a href="#execute-spjs">Execute SPJs</a> below.</p>
</div>
</div>
</div>
<div class="sect3">
<h4 id="returning-stored-procedure-result-sets">4.1.3. Returning Stored Procedure Result Sets</h4>
<div class="paragraph">
<p>Trafodion supports SPJs that return stored procedure
result sets. A stored procedure result set is a cursor that is left open
after the SPJ method executes (that is, after the CALL statement
executes successfully). After the CALL statement executes successfully,
the calling application can issue requests to open and then retrieve
multiple rows of data from the returned result sets.</p>
</div>
<div class="paragraph">
<p>An SPJ method returns an ordered collection of result sets to the
calling application by executing SELECT statements and placing each
returned ResultSet object into a one-element Java array of type
<code>java.sql.ResultSet[]</code>. The <code>java.sql.ResultSet[]</code> array is part of the Java
method&#8217;s signature and is recognized by the database engine as a
container for a single stored procedure result set.</p>
</div>
<div class="paragraph">
<p>Place the <code>java.sql.ResultSet[]</code> parameters after the other Java
parameters, if any, in the Java signature. If you do not place the
<code>java.sql.ResultSet[]</code> parameters after the other
parameters in the signature, the database engine prevents you from
creating an SPJ using that Java method. This example shows the
declaration of an SPJ method, <code>orderSummary()</code>, which returns a maximum of
two result sets:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="java"><span class="directive">public</span> <span class="directive">static</span> <span class="type">void</span> orderSummary( java.lang.String onOrAfter
, <span class="type">long</span><span class="type">[]</span> numOrders
, java.sql.ResultSet<span class="type">[]</span> orders
, java.sql.ResultSet<span class="type">[]</span> detail
)</code></pre>
</div>
</div>
<div class="paragraph">
<p>This code fragment shows how the <code>orderSummary()</code> method returns one of
its result sets by executing a SELECT statement and assigning the
<code>java.sql.ResultSet</code> object to a <code>java.sql.ResultSet[]</code> output array:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="java"><span class="comment">// Open a result set for order num, order info rows</span>
java.lang.String s =
<span class="string"><span class="delimiter">&quot;</span><span class="content">SELECT amounts.*, orders.order_date, emps.last_name </span><span class="delimiter">&quot;</span></span>
+ <span class="string"><span class="delimiter">&quot;</span><span class="content">FROM ( SELECT o.ordernum, COUNT( d.partnum ) AS num_parts, </span><span class="delimiter">&quot;</span></span>
+ <span class="string"><span class="delimiter">&quot;</span><span class="content"> SUM( d.unit_price * d.qty_ordered ) AS amount </span><span class="delimiter">&quot;</span></span>
+ <span class="string"><span class="delimiter">&quot;</span><span class="content"> FROM trafodion.sales.orders o, trafodion.sales.odetail d </span><span class="delimiter">&quot;</span></span>
+ <span class="string"><span class="delimiter">&quot;</span><span class="content"> WHERE o.ordernum = d.ordernum </span><span class="delimiter">&quot;</span></span>
+ <span class="string"><span class="delimiter">&quot;</span><span class="content"> AND o.order_date &gt;= CAST(? AS DATE) </span><span class="delimiter">&quot;</span></span>
+ <span class="string"><span class="delimiter">&quot;</span><span class="content"> GROUP BY o.ordernum ) amounts, </span><span class="delimiter">&quot;</span></span>
+ <span class="string"><span class="delimiter">&quot;</span><span class="content"> trafodion.sales.orders orders, trafodion.persnl.employee emps </span><span class="delimiter">&quot;</span></span>
+ <span class="string"><span class="delimiter">&quot;</span><span class="content">WHERE amounts.ordernum = orders.ordernum </span><span class="delimiter">&quot;</span></span>
+ <span class="string"><span class="delimiter">&quot;</span><span class="content"> AND orders.salesrep = emps.empnum </span><span class="delimiter">&quot;</span></span>
+ <span class="string"><span class="delimiter">&quot;</span><span class="content">ORDER BY orders.ordernum </span><span class="delimiter">&quot;</span></span>
;
java.sql.PreparedStatement ps2 = conn.prepareStatement(s) ;
ps2.setString( <span class="integer">1</span>, onOrAfter ) ;
<span class="comment">// Assign the returned result set object to the first element of a</span>
<span class="comment">// java.sql.ResultSet[] output array</span>
orders[<span class="integer">0</span>] = ps2.executeQuery() ;</code></pre>
</div>
</div>
<div class="paragraph">
<p>For the entire example, see
<a href="#ordersummary-procedure">ORDERSUMMARY Procedure</a>.</p>
</div>
<div class="admonitionblock important">
<table>
<tr>
<td class="icon">
<i class="fa icon-important" title="Important"></i>
</td>
<td class="content">
In an SPJ method that returns result sets, do not explicitly close
the default connection or the statement object. The database engine closes the
connection used to return result sets after it finishes processing the result
sets. If you close the connection on which the result sets are being returned,
those result sets will be lost, and the calling application will not be able
to process them.
</td>
</tr>
</table>
</div>
<div class="paragraph">
<p>An SPJ method can return result sets that contain any data types, except
large object (LOB) data. An SPJ method can return a holdable or
updatable cursor as a result set. However, Trafodion SQL does not expose
those attributes in the calling application. An SPJ method can return a
ResultSet object that is a stored procedure result set acquired from a
nested CALL statement executed by the SPJ method. However, you are
discouraged from nesting CALL statements in SPJ methods. For more
information, see <a href="#nested-java-method-invocations">Nested Java Method Invocations</a>.</p>
</div>
<div style="page-break-after: always;"></div>
<div class="paragraph">
<p>If an SPJ method returns multiple ResultSet objects, the database engine
sorts the collection of valid result sets in chronological order
according to when the underlying SQL statements were executed. If the
number of result sets exceeds the declared maximum for the SPJ, only the
first set of result sets up to the maximum number are returned. The
database engine discards the other result sets and returns a warning to
the calling application.</p>
</div>
<div class="paragraph">
<p>When an SPJ method returns a ResultSet object through a
<code>java.sql.ResultSet[]</code> parameter, Trafodion SQL exposes the underlying rows
of data as an SQL cursor in the calling application.</p>
</div>
<div class="paragraph">
<p>If a returned result set is a scrollable cursor, all underlying rows are
included in the result set and are available to the calling application.
If a returned result set is not scrollable, only those rows not
processed by the SPJ method are included in the result set and are
available to the calling application. If an SPJ method returns multiple
occurrences of the same ResultSet object, the database engine ignores
all but one occurrence and makes the underlying rows available to the
calling application as a single result set.</p>
</div>
<div class="paragraph">
<p>For information about processing result sets in different calling
applications, see:</p>
</div>
<div class="ulist">
<ul>
<li>
<p><a href="#returning-result-sets-in-trafci">Returning Result Sets in trafci</a></p>
</li>
<li>
<p><a href="#returning-result-sets-in-an-odbc-client-application">Returning Result Sets in an ODBC Client Application</a></p>
</li>
<li>
<p><a href="#returning-result-sets-in-a-jdbc-client-application">Returning Result Sets in a JDBC Client Application</a></p>
</li>
</ul>
</div>
</div>
<div class="sect3">
<h4 id="using-the-main-method">4.1.4. Using the main() Method</h4>
<div class="paragraph">
<p>You can use the <code>main()</code> method of a Java class file as an SPJ method. The
<code>main()</code> method is different from other Java methods because it accepts
input values in an array of <code>java.lang.String</code> objects and does not return
any values in its array parameter.</p>
</div>
<div class="paragraph">
<p>For example, you can register this main() method as an SPJ:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="java"><span class="directive">public</span> <span class="directive">static</span> <span class="type">void</span> main (java.lang.String <span class="type">[]</span> args)
{
...
}</code></pre>
</div>
</div>
<div class="paragraph">
<p>When you register a <code>main()</code> method as an SPJ, you can specify zero or
more SQL parameters, even though the underlying <code>main()</code> method has only
one array parameter. All the SQL parameters of the SPJ must have the
character string data type, CHAR or VARCHAR, and be declared with the IN
mode.</p>
</div>
<div class="paragraph">
<p>If you specify the optional Java signature, the signature must be
(<code>java.lang.String []</code>). For more information about registering an SPJ,
see <a href="#create-spjs">Create SPJs</a>.</p>
</div>
</div>
<div class="sect3">
<h4 id="null-input-and-output">4.1.5. Null Input and Output</h4>
<div class="paragraph">
<p>You can pass a <code>null</code> value as input to or output from an SPJ method,
provided that the Java data type of the parameter supports nulls. Java
primitive data types do not support nulls. However, Java wrapper classes
that correspond to primitive data types do support nulls. If a null is
input or output for a parameter that does not support nulls, the
database engine raises an error condition.</p>
</div>
<div class="paragraph">
<p>To anticipate null input or output for your SPJ, use Java wrapper
classes instead of primitive data types in the method signature.</p>
</div>
<div class="paragraph">
<p>For example, this Java method uses a Java primitive data type in its
signature where no null values are expected:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="java"><span class="directive">public</span> <span class="directive">static</span> <span class="type">void</span> employeeJob( <span class="type">int</span> empNum, <span class="predefined-type">Integer</span><span class="type">[]</span> jobCode )</code></pre>
</div>
</div>
<div class="paragraph">
<p>This Java method also uses a Java wrapper class in its signature to
anticipate a possible returned null value:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="java"><span class="directive">public</span> <span class="directive">static</span> <span class="type">void</span> employeeJob( <span class="type">int</span> empNum, <span class="predefined-type">Integer</span><span class="type">[]</span> jobCode )</code></pre>
</div>
</div>
</div>
<div class="sect3">
<h4 id="static-java-variables">4.1.6. Static Java Variables</h4>
<div class="paragraph">
<p>To ensure that your SPJ method is portable, you should avoid using
static variables in the method. The database engine does not ensure the
scope and persistence of static Java variables.</p>
</div>
</div>
<div class="sect3">
<h4 id="nested-java-method-invocations">4.1.7. Nested Java Method Invocations</h4>
<div class="paragraph">
<p>An SPJ that invokes another SPJ by issuing a CALL statement causes
additional system resources to be used. If you want an SPJ method to
call another SPJ method, consider invoking the other Java method
directly through Java instead of using a CALL statement. The other Java
method should be packaged in the same JAR file as the SPJ method. For
more information, see
<a href="#compiling-and-packaging-java-classes">Compiling and Packaging Java Classes</a>.</p>
</div>
<div style="page-break-after: always;"></div>
</div>
</div>
<div class="sect2">
<h3 id="accessing-a-trafodion-database">4.2. Accessing Trafodion</h3>
<div class="paragraph">
<p>SPJ methods that access Trafodion must be from a Java class
that uses JDBC method calls. Follow these guidelines when writing an SPJ
method that accesses Trafodion:</p>
</div>
<div class="sect3">
<h4 id="use-of-java.sql.connection-objects">4.2.1. Use of java.sql.Connection Objects</h4>
<div class="paragraph">
<p>Trafodion supports a default connection in an SPJ
execution environment, which has a data source URL of
<code>"jdbc:default:connection"</code>. For example:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="java"><span class="predefined-type">Connection</span> conn =
<span class="predefined-type">DriverManager</span>.getConnection( <span class="string"><span class="delimiter">&quot;</span><span class="content">jdbc:default:connection</span><span class="delimiter">&quot;</span></span> ) ;</code></pre>
</div>
</div>
<div class="paragraph">
<p><code>java.sql.Connection</code> objects that use the <code>"jdbc:default:connection"</code> URL
are portable to Trafodion from other database management
systems (DBMSs).</p>
</div>
<div class="sect4">
<h5 id="closing-default-connections">Closing Default Connections</h5>
<div class="paragraph">
<p>Trafodion controls default connections in the SPJ
environment and closes default connections when they are no longer
needed. Therefore, you do not need to use the <code>close()</code> method in an SPJ
method to explicitly close a default connection when the connection is
no longer needed.</p>
</div>
<div class="admonitionblock important">
<table>
<tr>
<td class="icon">
<i class="fa icon-important" title="Important"></i>
</td>
<td class="content">
If an SPJ method returns result sets, you should not explicitly
close the default connection. The database engine closes the connection used
to return result sets after it finishes processing the result sets. If an SPJ
method closes the connection on which the result sets are being returned, those
result sets will be lost, and the calling application will not be able to
process them. The JVM does not return an error or warning when the
connection is closed.
</td>
</tr>
</table>
</div>
<div class="paragraph">
<p>A default connection that is acquired when an SPJ method executes does
not necessarily remain open for future invocations of the SPJ method.
Therefore, do not store default connections in static variables for
future use.</p>
</div>
</div>
<div class="sect4">
<h5 id="default-connection-url">Default Connection URL</h5>
<div class="paragraph">
<p>The default connection URL, <code>"jdbc:default:connection"</code>, is invalid when
the Java method is invoked outside the DBMS, such as when you execute
the Java method in a client application. To write an SPJ method that
operates in a DBMS, in a client application, or both, without having to
change and recompile the code, use the <code>sqlj.defaultconnection</code> system
property:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="java"><span class="predefined-type">String</span> s = <span class="predefined-type">System</span>.property( <span class="string"><span class="delimiter">&quot;</span><span class="content">sqlj.defaultconnection</span><span class="delimiter">&quot;</span></span> ) ;
<span class="keyword">if</span> ( s == <span class="predefined-constant">null</span> )
{
s = other-url ;
}
<span class="predefined-type">Connection</span> c = <span class="predefined-type">DriverManager</span>.getConnection( s ) ;</code></pre>
</div>
</div>
<div class="paragraph">
<p>The value of <code>sqlj.defaultconnection</code> is <code>"jdbc:default:connection"</code> in a
DBMS and <code>null</code> outside a DBMS.</p>
</div>
</div>
<div class="sect4">
<h5 id="connection-pooling">Connection Pooling</h5>
<div class="paragraph">
<p>Connection pooling, where a cache of database connections is assigned to
a client session and reused, is enabled by default in the SPJ
environment. The SPJ environment sets the initial connection pool size
to <code>1</code>, but it does not limit the number of connections an SPJ method can
make.</p>
</div>
<div class="paragraph">
<p>The SPJ environment also sets the minimum connection pool size to 1 so that
there is always at least one connection available in the pool. The
default settings in the SPJ environment are:</p>
</div>
<div class="ulist">
<ul>
<li>
<p><code>maxPoolSize=0</code></p>
</li>
<li>
<p><code>minPoolSize=1</code></p>
</li>
<li>
<p><code>initialPoolSize=1</code></p>
</li>
</ul>
</div>
<div class="paragraph">
<p>To change these settings, use the properties parameter of the
<code>DriverManager.getConnection()</code> method as shown below:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="java">java.util.Properties props = <span class="keyword">new</span> <span class="predefined-type">Properties</span>() ;
props.setProperty( <span class="string"><span class="delimiter">&quot;</span><span class="content">maxPoolSize</span><span class="delimiter">&quot;</span></span>, <span class="string"><span class="delimiter">&quot;</span><span class="content">10</span><span class="delimiter">&quot;</span></span> ) ;
props.setProperty( <span class="string"><span class="delimiter">&quot;</span><span class="content">minPoolSize</span><span class="delimiter">&quot;</span></span>, <span class="string"><span class="delimiter">&quot;</span><span class="content">5</span><span class="delimiter">&quot;</span></span> ) ;
props.setProperty( <span class="string"><span class="delimiter">&quot;</span><span class="content">initialPoolSize</span><span class="delimiter">&quot;</span></span>, <span class="string"><span class="delimiter">&quot;</span><span class="content">5</span><span class="delimiter">&quot;</span></span> ) ;
<span class="predefined-type">Connection</span> conn =
<span class="predefined-type">DriverManager</span>.getConnection( <span class="string"><span class="delimiter">&quot;</span><span class="content">jdbc:default:connection</span><span class="delimiter">&quot;</span></span>, props ) ;</code></pre>
</div>
</div>
<div style="page-break-after: always;"></div>
</div>
</div>
<div class="sect3">
<h4 id="using-jdbc-method-calls">4.2.2. Using JDBC Method Calls</h4>
<div class="paragraph">
<p>Trafodion uses a JDBC Type-4 driver internally to execute
the SQL statements inside an SPJ method. To enable an SPJ to perform SQL
operations on a Trafodion database, use JDBC method calls in the SPJ
method. The JDBC method calls must be supported by the JDBC Type-4
driver on Trafodion.</p>
</div>
<div class="paragraph">
<p>For example, if you want the SPJ method to operate on a Trafodion database,
use the JDBC API that is supported by Trafodion.</p>
</div>
<div class="admonitionblock note">
<table>
<tr>
<td class="icon">
<i class="fa icon-note" title="Note"></i>
</td>
<td class="content">
You do not have to explicitly load the JDBC driver before
establishing a connection to Trafodion. The database engine
automatically loads the JDBC driver when the SPJ is called.
</td>
</tr>
</table>
</div>
<div class="paragraph">
<p>Here is an example of an SPJ method, <code>adjustSalary()</code>, that uses JDBC
method calls to adjust an employee&#8217;s salary in the EMPLOYEE table:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="java"><span class="directive">public</span> <span class="type">class</span> <span class="class">Payroll</span>