// Source File Name: DB2Udf.java 1.5
//
// Licensed Materials -- Property of IBM
//
// (c) Copyright International Business Machines Corporation, 1996, 1999.
// All Rights Reserved.
//
// US Government Users Restricted Rights -
// Use, duplication or disclosure restricted by
// GSA ADP Schedule Contract with IBM Corp.
// Sample program - Java User-defined Functions
// Steps to set up the server side:
// (1) create and populate the SAMPLE database (db2sampl)
// (2) compile this Java file (javac DB2Udf.java)
// (3) copy the resulting DB2Udf.class file into sqllib/function
// NOTES: (1) The jdk11_path database manager configuration parameter must
// be set
// (2) The CLASSPATH and shared library path environment variables
// must be set, as for any JDBC application.
// (3) Visit http://www.software.ibm.com/data/db2/java
// for current DB2 Java information
// Class DB2UdCli implements the sample client (configuration and
// execution using JDBC). Class DB2Udf implements the UDF
// method bodies.
// DB2Udf uses the LOB support provided by the COM.ibm.db2.app.* package.
// Since JDK1.2 also provides LOB support, references to objects of type
// Lob, Blob, and Clob must be explicitly type-decorated to compile on a
// system with JDK1.2 support. This sample uses the COM.ibm.db2.app.* LOB
// support to provide compatibility for both JDK1.1 and JDK1.2 systems.
// For more information about this sample, refer to the README file.
// For more information on Programming in Java, refer to the
// "Programming in Java" section of the Application Development Guide.
// For more information on creating and using user-defined functions,
// refer to the "Object-Relational Programming" section of the
// Application Development Guide.
// For more information on building and running Java programs for DB2,
// refer to the "Building Java Applets and Applications" section of the
// Application Building Guide.
// For more information on the SQL language, refer to the SQL Reference.
import COM.ibm.db2.app.*;
import COM.ibm.db2.jdbc.app.*;
import java.sql.*;
import java.io.*;
import java.net.*;
// /////
// Java user-defined functions are in this class
// /////
class DB2Udf extends UDF
{
// /////
// Translations of C UDFs
// /////
// Integer division example.
// Returns NULL if divisor (b) is zero, otherwise returns plain quotient.
public void
divid (int a, int b, int result) throws Exception
{
if (b == 0)
return;
else
set (3, a / b);
}
// CLOB folding example.
// fold() moves n chars from the beginning of a string to its end.
// Since there is no direct access to characters in a CLOB,
// the JDK 1.1 Reader/Writer classes are used.
public void
fold (COM.ibm.db2.app.Clob input, int foldPoint, COM.ibm.db2.app.Clob result) throws Exception
{
// Create a new CLOB to receive output
COM.ibm.db2.app.Clob resultClob = COM.ibm.db2.app.Lob.newClob ();
Writer resultWriter = resultClob.getWriter ();
if (isNull (1) || isNull (2))
resultWriter.write ("INVALID INPUT".toCharArray ());
else
{
Reader inputReader = input.getReader ();
inputReader.skip (foldPoint); // go to folding point
copyClob (inputReader, (int) input.size () - foldPoint, resultWriter);
inputReader.reset (); // go back to beginning
copyClob (inputReader, foldPoint, resultWriter);
}
resultWriter.close ();
set (3, resultClob);
}
// Internal method for copying characters from one CLOB to another
private void
copyClob (Reader input, int count, Writer output) throws Exception
{
char[] buffer = new char[1024];
int copied = 0;
while (copied < count)
{
int readNow = input.read (buffer, 0, count - copied);
if (readNow < 0)
throw new Exception ("end of Reader reached unexpectedly");
output.write (buffer, 0, readNow);
copied += readNow;
}
}
// Find-the-vowel vowel example.
// Return position of first vowel, or signal SQL error.
public void
findvwl (String a, int result) throws Exception
{
for (int i = 0; i < a.length (); i++)
{
char x = a.charAt (i);
char y = Character.toUpperCase (x);
if (y == 'A' || y == 'E' || y == 'I' ||
y == 'O' || y == 'U' || y == 'Y')
{
set (2, i + 1); // SQL indexing begins at 1
return;
}
}
// return failure message
setSQLstate ("38700");
setSQLmessage ("findvwl: No Vowel");
}
// Counter example.
// Demonstrate use of Java instance variables instead of the scratchpad.
int counter = 0; // instance variable
public void
ctr (int result) throws Exception
{
set (1, ++counter);
}
// Example to demonstrate the use of JAVA UDF along with DATALINK
// columns to materialize contents of a DataLinked URL into a BLOB
// This UDF accepts a String argument(which is a URL obtained
// from a DATALINK column) and returns a BLOB(1K) constructed
// from first 1K bytes of the contents of the URL.
public void getBlob(String dlnk, COM.ibm.db2.app.Blob result) throws Exception
{
final int SIZE = 1000;
COM.ibm.db2.app.Blob resultBlob = COM.ibm.db2.app.Lob.newBlob();
// Create a bufferred output stream for writing to result BLOB
BufferedOutputStream out = new BufferedOutputStream(
resultBlob.getOutputStream());
// If input string is zero -length, the result BLOB will be NULL.
// So just return without initializing (which will make result NULL)
if (dlnk.length() == 0)
return;
// Create a URL object from the input string
URL dlnkUrl = new URL(dlnk);
// Create a bufferred input stream to read 1K bytes from the
// contents of the URL into the result BLOB
BufferedInputStream in = new BufferedInputStream(dlnkUrl.openStream());
int b;
for(int count = 0; (count < SIZE) && ((b = in.read()) != -1); count++)
{
out.write(b);
count++;
}
in.close();
out.close();
set(2, resultBlob);
}
// /////
// Table function examples
// /////
// Directory traversal example.
// Returns a table containing names and characteristics of files
// in directory given as UDF argument. WARNING - this is only
// an example. This function can return potentially private
// information about the database server.
File startFile = null;
String fileNames[] = null; // list of recorded files
int fileIndex = 0; // next file to return information on
public void
listDirectory (String path, // input
String nameOut, String typeOut, // outputs
String linkOut, int lengthOut) // more outputs
throws Exception
{
switch (getCallType()) {
case SQLUDF_TF_FIRST:
// do initialization which is independent of input parameters
break;
case SQLUDF_TF_OPEN:
// path may change on OPEN
startFile = new File (path.replace ('/', File.separatorChar));
fileIndex = 0;
// List files in given directory
fileNames = startFile.list ();
if(fileNames == null)
throw new Exception("Cannot list directory " + path);
break;
case SQLUDF_TF_FETCH:
if (fileIndex == fileNames.length)
{
// Set end-of-file signal and return
setSQLstate ("02000");
} else {
File thisFile = new File (fileNames[fileIndex]);
fileIndex++;
if (needToSet (2)) set (2, thisFile.getPath ());
if (needToSet (3)) set (3, classifyPath (thisFile, startFile));
if (needToSet (4)) set (4, thisFile.getAbsolutePath ());
if (needToSet (5)) set (5, (int) thisFile.length ());
}
break;
case SQLUDF_TF_CLOSE:
break;
case SQLUDF_TF_FINAL:
break;
}
}
// Classify this file by guessing whether it is a plain
// file, directory, or a link.
private String classifyPath (File child, File parent) throws Exception
{
String type = "";
// Try to identify a UNIX link by either of following heuristics:
// - if parent path name does not match beginning of child path name
// - if rest of child path name contains directory separators
String childPath = child.getAbsolutePath ();
String parentPath = parent.getAbsolutePath ();
String childPathBegin = childPath.substring (0, parentPath.length ());
if (!childPathBegin.equals (parentPath))
type = "XLINK";
else if (childPath.length () <= parentPath.length ())
type = "UPLINK";
else
{
String childPathEnd = childPath.substring (parentPath.length ());
if (childPathEnd.indexOf (File.separatorChar) > 1)
type = "DOWNLINK";
}
// use standard JDK tests
if (child.isDirectory ())
type = type + "DIR";
else if (child.isFile ())
type = type + "FILE";
else
type = type + "OTHER";
return type;
}
}