339 lines
19 KiB
HTML
339 lines
19 KiB
HTML
<?xml version="1.0" encoding="UTF-8"?>
|
|
<!DOCTYPE html
|
|
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
|
<html lang="en-us" xml:lang="en-us">
|
|
<head>
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
|
<meta name="security" content="public" />
|
|
<meta name="Robots" content="index,follow" />
|
|
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
|
|
<meta name="DC.Type" content="concept" />
|
|
<meta name="DC.Title" content="Java user-defined scalar functions" />
|
|
<meta name="abstract" content="A Java scalar function returns one value from a Java program to the database. For example, a scalar function could be created that returns the sum of two numbers." />
|
|
<meta name="description" content="A Java scalar function returns one value from a Java program to the database. For example, a scalar function could be created that returns the sum of two numbers." />
|
|
<meta name="DC.Relation" scheme="URI" content="jsqlrout.htm" />
|
|
<meta name="DC.Relation" scheme="URI" content="jsqlover.htm" />
|
|
<meta name="DC.Relation" scheme="URI" content="javaproc.htm" />
|
|
<meta name="DC.Relation" scheme="URI" content="sqljjar.htm" />
|
|
<meta name="DC.Relation" scheme="URI" content="udfparam.htm" />
|
|
<meta name="DC.Relation" scheme="URI" content="udfrestr.htm" />
|
|
<meta name="DC.Relation" scheme="URI" content="udftable.htm" />
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 2006" />
|
|
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 2006" />
|
|
<meta name="DC.Format" content="XHTML" />
|
|
<meta name="DC.Identifier" content="writeudf" />
|
|
<meta name="DC.Language" content="en-us" />
|
|
<!-- All rights reserved. Licensed Materials Property of IBM -->
|
|
<!-- US Government Users Restricted Rights -->
|
|
<!-- Use, duplication or disclosure restricted by -->
|
|
<!-- GSA ADP Schedule Contract with IBM Corp. -->
|
|
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
|
|
<link rel="stylesheet" type="text/css" href="./ic.css" />
|
|
<title>Java user-defined scalar functions</title>
|
|
</head>
|
|
<body id="writeudf"><a name="writeudf"><!-- --></a>
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
|
<h1 class="topictitle1">Java user-defined scalar functions</h1>
|
|
<div><p>A Java™ scalar function returns one value from a Java program
|
|
to the database. For example, a scalar function could be created that returns
|
|
the sum of two numbers.</p>
|
|
<p> Like Java stored procedures, Java scalar functions use one of two parameter
|
|
styles, Java and DB2GENERAL. When coding a Java user-defined
|
|
function (UDF), you must be aware of restrictions placed on creating Java scalar
|
|
functions.</p>
|
|
<div class="section"><h4 class="sectiontitle">Parameter style Java</h4><p>The Java parameter
|
|
style is the style specified by the <em>SQLJ Part 1: SQL Routines</em> standard.
|
|
When coding a Java UDF, use the following conventions.</p>
|
|
<ul><li>The Java method must be a public static method.</li>
|
|
<li>The Java method must return an SQL compatible type. The
|
|
return value is the result of the method.</li>
|
|
<li>The parameters of the Java method must be SQL compatible types.</li>
|
|
<li>The Java method may test for a SQL NULL for Java types
|
|
that permit the null value.</li>
|
|
</ul>
|
|
<p>For example, given a UDF called sample!test3 that returns INTEGER
|
|
and takes arguments of type CHAR(5), BLOB(10K), and DATE, DB2<sup>®</sup> expects the Java implementation
|
|
of the UDF to have the following signature:</p>
|
|
<blockquote><pre>import com.ibm.db2.app.*;
|
|
public class sample {
|
|
public static int test3(String arg1, Blob arg2, Date arg3) { ... }
|
|
}</pre>
|
|
</blockquote>
|
|
<p>The parameters of a Java method must be SQL compatible types.
|
|
For example, if a UDF is declared as taking arguments of SQL types t1, t2,
|
|
and t3, and returning type t4, it is called as a Java method with the expected Java signature:</p>
|
|
<pre> public static T4 <em>name</em> (T1 <em>a</em>, T2 <em>b</em>, T3 <em>c</em>) { .....}
|
|
</pre>
|
|
<p>where:</p>
|
|
<ul><li><em>name</em> is the method name</li>
|
|
<li>T1 through T4 are the Java types that correspond to SQL types
|
|
t1 through t4.</li>
|
|
<li><em>a</em>, <em>b</em>, and <em>c</em> are arbitrary variable names for the
|
|
input arguments.</li>
|
|
</ul>
|
|
<p>The correlation between SQL types and Java types is found in <a href="udfparam.htm">Parameter
|
|
passing conventions for stored procedures and UDFs</a>.</p>
|
|
<p>SQL NULL
|
|
values are represented by Java variables that are not initialized.
|
|
These variables have a Java null value if they are object types.
|
|
If an SQL NULL is passed to a Java scalar data type, such as int, then
|
|
an exception condition is raised.</p>
|
|
<p>To return a result from a Java UDF
|
|
when using the JAVA parameter style, simply return the result from the method.</p>
|
|
<blockquote><pre>{ ....
|
|
return value;
|
|
} </pre>
|
|
</blockquote>
|
|
<p>Like C modules used in UDFs and stored procedures, you
|
|
cannot use the Java standard I/O streams (System.in, System.out, and
|
|
System.err) in Java UDFs.</p>
|
|
</div>
|
|
<div class="section"><h4 class="sectiontitle">Parameter style DB2GENERAL</h4><p>Parameter style DB2GENERAL
|
|
is used by Java UDFs. In this parameter style, the return value
|
|
is passed as the last parameter of the function and must be set using a <em>set</em> method
|
|
of the com.ibm.db2.app.UDF class.</p>
|
|
<p>When coding a Java UDF,
|
|
the following conventions must be followed:</p>
|
|
<ul><li>The class, which includes the Java UDF, must <em>extend</em>, or be a subclass
|
|
of, the Java com.ibm.db2.app.UDF class.</li>
|
|
<li>For the DB2GENERAL parameter style, the Java method must be a public void instance
|
|
method.</li>
|
|
<li>The parameters of the Java method must be SQL-compatible types.</li>
|
|
<li>The Java method may test for an SQL NULL value using the
|
|
isNull method.</li>
|
|
<li>For the DB2GENERAL parameter style, the Java method must explicitly set the return
|
|
parameter using the set() method.</li>
|
|
</ul>
|
|
<p>A class that includes a Java UDF must extend the Java class,
|
|
com.ibm.db2.app.UDF. A Java UDF that uses the DB2GENERAL parameter
|
|
style must be a void instance method of the Java class. For example, for a UDF called
|
|
sample!test3 that returns INTEGER and takes arguments of type CHAR(5), BLOB(10K),
|
|
and DATE, DB2 expects
|
|
the Java implementation
|
|
of the UDF to have the following signature:</p>
|
|
<blockquote><pre>import com.ibm.db2.app.*;
|
|
public class sample extends UDF {
|
|
public void test3(String arg1, Blob arg2, String arg3, int result) { ... }
|
|
}</pre>
|
|
</blockquote>
|
|
<p>The parameters of a Java method must be SQL types. For example,
|
|
if a UDF is declared as taking arguments of SQL types t1, t2, and t3, returning
|
|
type t4, it is called as a Java method with the expected Java signature:</p>
|
|
<blockquote><pre>public void <em>name</em> (T1 <em>a</em>, T2 <em>b</em>, T3 <em>c</em>, T4 <em>d</em>) { .....}</pre>
|
|
</blockquote>
|
|
<p>where:</p>
|
|
<ul><li><em>name</em> is the method name</li>
|
|
<li>T1 through T4 are the Java types that correspond to SQL types
|
|
t1 through t4.</li>
|
|
<li><em>a</em>, <em>b</em>, and <em>c</em> are arbitrary variable names for the
|
|
input arguments.</li>
|
|
<li><em>d</em> is an arbitrary variable name that represents the UDF result
|
|
being computed.</li>
|
|
</ul>
|
|
<p>The correlation between SQL types and Java types is given in the section, <a href="udfparam.htm">Parameter passing conventions for stored procedures and
|
|
UDFs</a>.</p>
|
|
<p>SQL NULL values are represented by Java variables
|
|
that are not initialized. These variables have a value of zero if they are
|
|
primitive types, and Java null if they are object types, in accordance
|
|
with Java rules.
|
|
To tell an SQL NULL apart from an ordinary zero, the isNull method can be
|
|
called for any input argument:</p>
|
|
<pre> { ....
|
|
if (isNull(1)) { /* argument #1 was a SQL NULL */ }
|
|
else { /* not NULL */ }
|
|
}</pre>
|
|
<p>In the previous example, the argument numbers start at
|
|
one. The isNull() function, like the other functions that follow, are inherited
|
|
from the com.ibm.db2.app.UDF class. To return a result from a Java UDF
|
|
when using the DB2GENERAL parameter style, use the set() method in the UDF,
|
|
as follows:</p>
|
|
<pre> { ....
|
|
set(2, <em>value</em>);
|
|
}</pre>
|
|
<p>Where 2 is the index of an output argument, and <em>value</em> is
|
|
a literal or variable of a compatible type. The argument number is the index
|
|
in the argument list of the selected output. In the first example in this
|
|
section, the int result variable has an index of 4. An output argument that
|
|
is not set before the UDF returns has a NULL value.</p>
|
|
<p>Like C modules used
|
|
in UDFs and stored procedures, you cannot use the Java standard I/O streams (System.in, System.out,
|
|
and System.err) in Java UDFs.</p>
|
|
<p>Typically, DB2 calls a UDF
|
|
many times, once for each row of an input or result set in a query. If SCRATCHPAD
|
|
is specified in the CREATE FUNCTION statement of the UDF, DB2 recognizes
|
|
that some "continuity" is needed between successive invocations of the UDF,
|
|
and therefore, for DB2GENERAL parameter style functions, the implementing Java class
|
|
is not instantiated for each call, but generally speaking once per UDF reference
|
|
per statement. If, however, NO SCRATCHPAD is specified for a UDF, then a clean
|
|
instance is instantiated for each call to the UDF, by means of a call to the
|
|
class constructor.</p>
|
|
<p>A scratchpad may be useful for saving information
|
|
across calls to a UDF. Java UDFs can either use instance variables
|
|
or set the scratchpad to achieve continuity between calls. Java UDFs
|
|
access the scratchpad with the getScratchPad and setScratchPad methods available
|
|
in com.ibm.db2.app.UDF. At the end of a query, if you specify the FINAL CALL
|
|
option on the CREATE FUNCTION statement, the object's public void close()
|
|
method is called (for DB2GENERAL parameter style functions). If you do not
|
|
define this method, a stub function takes over and the event is ignored. The
|
|
com.ibm.db2.app.UDF class contains useful variables and methods that you can
|
|
use within a DB2GENERAL parameter style UDF. These variables and methods are
|
|
explained in the following table.</p>
|
|
|
|
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" width="100%" frame="border" border="1" rules="all"><thead align="left"><tr><th valign="top" id="d0e365">Variables and Methods</th>
|
|
<th valign="top" id="d0e367">Description</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody><tr><td valign="top" headers="d0e365 "> <ul><li>public static final int SQLUDF_FIRST_CALL = -1;</li>
|
|
<li>public static final int SQLUDF_NORMAL_CALL = 0;</li>
|
|
<li>public static final int SQLUDF_TF_FIRST = -2;</li>
|
|
<li>public static final int SQLUDF_TF_OPEN = -1;</li>
|
|
<li>public static final int SQLUDF_TF_FETCH = 0;</li>
|
|
<li>public static final int SQLUDF_TF_CLOSE = 1;</li>
|
|
<li>public static final int SQLUDF_TF_FINAL = 2;</li>
|
|
</ul>
|
|
</td>
|
|
<td valign="top" headers="d0e367 ">For scalar UDFs, these are constants to determine if the call is a
|
|
first call or a normal call. For table UDFs, these are constants to determine
|
|
if the call is a first call, open call, fetch call, close call, or final call.</td>
|
|
</tr>
|
|
<tr><td valign="top" headers="d0e365 "> public Connection getConnection(); </td>
|
|
<td valign="top" headers="d0e367 ">The method obtains the JDBC connection handle for this stored procedure
|
|
call and returns a JDBC object that represents the calling application's connection
|
|
to the database. It is analogous to the result of a null SQLConnect() call
|
|
in a C stored procedure.</td>
|
|
</tr>
|
|
<tr><td valign="top" headers="d0e365 "> public void close(); </td>
|
|
<td valign="top" headers="d0e367 ">This method is called by the database at the end of a UDF evaluation,
|
|
if the UDF was created with the FINAL CALL option. It is analogous to the
|
|
final call for a C UDF. If a Java UDF class does not implement this method,
|
|
this event is ignored.</td>
|
|
</tr>
|
|
<tr><td valign="top" headers="d0e365 "> public boolean isNull(int i) </td>
|
|
<td valign="top" headers="d0e367 ">This method tests whether an input argument with the given index is
|
|
an SQL NULL.</td>
|
|
</tr>
|
|
<tr><td valign="top" headers="d0e365 "> <ul><li>public void set(int i, short s);</li>
|
|
<li>public void set(int i, int j);</li>
|
|
<li>public void set(int i, long j);</li>
|
|
<li>public void set(int i, double d);</li>
|
|
<li>public void set(int i, float f);</li>
|
|
<li>public void set(int i, BigDecimal bigDecimal);</li>
|
|
<li>public void set(int i, String string);</li>
|
|
<li>public void set(int i, Blob blob);</li>
|
|
<li>public void set(int i, Clob clob);</li>
|
|
<li>public boolean needToSet(int i);</li>
|
|
</ul>
|
|
</td>
|
|
<td valign="top" headers="d0e367 ">These methods set an output argument to the given value. An exception
|
|
is thrown if anything goes wrong, including the following: <ul><li>UDF call is not in progress</li>
|
|
<li>Index does not refer to valid output argument</li>
|
|
<li>Data type does not match</li>
|
|
<li>Data length does not match</li>
|
|
<li>Code page conversion error occurs</li>
|
|
</ul>
|
|
</td>
|
|
</tr>
|
|
<tr><td valign="top" headers="d0e365 "> public void setSQLstate(String string); </td>
|
|
<td valign="top" headers="d0e367 ">This method may be called from a UDF to set the SQLSTATE to be returned
|
|
from this call. If the string is not acceptable as an SQLSTATE, an exception
|
|
is thrown. The user may set the SQLSTATE in the external program to return
|
|
an error or warning from the function. In this case, the SQLSTATE must contain
|
|
one of the following: <ul><li>'00000' to indicate success</li>
|
|
<li>'01Hxx', where xx is any two digits or uppercase letters, to indicate
|
|
a warning</li>
|
|
<li>'38yxx', where y is an uppercase letter between 'I' and 'Z' and xx is
|
|
any two digits or uppercase letters, to indicate an error</li>
|
|
</ul>
|
|
</td>
|
|
</tr>
|
|
<tr><td valign="top" headers="d0e365 ">public void setSQLmessage(String string);</td>
|
|
<td valign="top" headers="d0e367 ">This method is similar to the setSQLstate method. It sets the SQL message
|
|
result. If the string is not acceptable (for example, longer than 70 characters),
|
|
an exception is thrown.</td>
|
|
</tr>
|
|
<tr><td valign="top" headers="d0e365 ">public String getFunctionName();</td>
|
|
<td valign="top" headers="d0e367 ">This method returns the name of the processing UDF.</td>
|
|
</tr>
|
|
<tr><td valign="top" headers="d0e365 ">public String getSpecificName();</td>
|
|
<td valign="top" headers="d0e367 ">This method returns the specific name of the processing UDF.</td>
|
|
</tr>
|
|
<tr><td valign="top" headers="d0e365 ">public byte[] getDBinfo();</td>
|
|
<td valign="top" headers="d0e367 ">This method returns a raw, unprocessed DBINFO structure for the processing
|
|
UDF, as a byte array. The UDF must have been registered (using CREATE FUNCTION)
|
|
with the DBINFO option.</td>
|
|
</tr>
|
|
<tr><td valign="top" headers="d0e365 "> <ul><li>public String getDBname();</li>
|
|
<li>public String getDBauthid();</li>
|
|
<li>public String getDBver_rel();</li>
|
|
<li>public String getDBplatform();</li>
|
|
<li>public String getDBapplid();</li>
|
|
<li>public String getDBapplid();</li>
|
|
<li>public String getDBtbschema();</li>
|
|
<li>public String getDBtbname();</li>
|
|
<li>public String getDBcolname();</li>
|
|
</ul>
|
|
</td>
|
|
<td valign="top" headers="d0e367 ">These methods return the value of the appropriate field from the DBINFO
|
|
structure of the processing UDF. The UDF must have been registered (using
|
|
CREATE FUNCTION) with the DBINFO option. The getDBtbschema(), getDBtbname(),
|
|
and getDBcolname() methods only return meaningful information if a user-defined
|
|
function is specified on the right side of a SET™ clause in an UPDATE statement.</td>
|
|
</tr>
|
|
<tr><td valign="top" headers="d0e365 "> public int getCCSID();</td>
|
|
<td valign="top" headers="d0e367 ">This method returns the CCSID of the job.</td>
|
|
</tr>
|
|
<tr><td valign="top" headers="d0e365 "> public byte[] getScratchpad(); </td>
|
|
<td valign="top" headers="d0e367 ">This method returns a copy of the scratchpad of the currently processing
|
|
UDF. You must first declare the UDF with the SCRATCHPAD option.</td>
|
|
</tr>
|
|
<tr><td valign="top" headers="d0e365 "> public void setScratchpad(byte ab[]); </td>
|
|
<td valign="top" headers="d0e367 ">This method overwrites the scratchpad of the currently processing UDF
|
|
with the contents of the given byte array. You must first declare the UDF
|
|
with the SCRATCHPAD option. The byte array must have the same size as getScratchpad()
|
|
returns.</td>
|
|
</tr>
|
|
<tr><td valign="top" headers="d0e365 ">public int getCallType();</td>
|
|
<td valign="top" headers="d0e367 ">This method returns the type of call that is currently being made.
|
|
These values correspond to the C values defined in sqludf.h. Possible return
|
|
values include the following: <ul><li>SQLUDF_FIRST_CALL</li>
|
|
<li>SQLUDF_NORMAL_CALL</li>
|
|
<li>SQLUDF_TF_FIRST</li>
|
|
<li>SQLUDF_TF_OPEN</li>
|
|
<li>SQLUDF_TF_FETCH</li>
|
|
<li>SQLUDF_TF_CLOSE</li>
|
|
<li>SQLUDF_TF_FINAL</li>
|
|
</ul>
|
|
</td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
<div>
|
|
<ul class="ullinks">
|
|
<li class="ulchildlink"><strong><a href="udfrestr.htm">Restrictions on Java user-defined functions</a></strong><br />
|
|
These restrictions apply to Java user-defined functions (UDFs).</li>
|
|
<li class="ulchildlink"><strong><a href="udftable.htm">Java user-defined table functions</a></strong><br />
|
|
DB2 provides
|
|
the ability for a function to return a table. This is useful for exposing
|
|
information from outside the database to the database in table form. For example,
|
|
a table can be created that exposes the properties set in the Java virtual
|
|
machine (JVM) used for Java stored procedures and Java UDFs
|
|
(both table and scalar).</li>
|
|
</ul>
|
|
|
|
<div class="familylinks">
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="jsqlrout.htm" title="Your iSeries server provides the ability to access Java programs from SQL statements and programs. This can be done using Java stored procedures and Java user-defined functions (UDFs). The iSeries server supports both the DB2 and SQLJ conventions for calling Java stored procedures and Java UDFs. Both Java stored procedures and Java UDFs can use Java classes that are stored in JAR files. The iSeries server uses stored procedures defined by the SQLJ Part 1 standard to register JAR files with the database.">Java SQL routines</a></div>
|
|
</div>
|
|
<div class="relconcepts"><strong>Related concepts</strong><br />
|
|
<div><a href="javaproc.htm" title="When using Java to write stored procedures, you can use two possible parameter passing styles.">Java stored procedures</a></div>
|
|
<div><a href="sqljjar.htm" title="Both Java stored procedures and Java UDFs can use Java classes that are stored in Java JAR files.">SQLJ procedures that manipulate JAR files</a></div>
|
|
<div><a href="udfparam.htm" title="The following table lists how SQL data types are represented in Java stored procedures and UDFs.">Parameter passing conventions for Java stored procedures and UDFs</a></div>
|
|
</div>
|
|
<div class="reltasks"><strong>Related tasks</strong><br />
|
|
<div><a href="jsqlover.htm" title="You can access Java programs from SQL statements and programs. This can be done using Java stored procedures and Java user-defined functions (UDFs).">Use Java SQL routines</a></div>
|
|
</div>
|
|
</div>
|
|
</body>
|
|
</html> |