// Source File Name: DB2UdCli.java 1.4
//
// Licensed Materials -- Property of IBM
//
// (c) Copyright International Business Machines Corporation, 1996, 1997.
// All Rights Reserved.
//
// US Government Users Restricted Rights -
// Use, duplication or disclosure restricted by
// GSA ADP Schedule Contract with IBM Corp.
// Sample program - Client Side of Java User-defined Functions Sample
// Run these samples by the following steps:
// (1) you must have the "sample" database catalogued on the client
// (2) compile this Java file (javac DB2UdCli.java)
// (3) compile the UDF server program (javac DB2Udf.java) and copy
// it to the sqllib/function directory
// (4) run the sample (java DB2UdCli [username password])
// NOTES: (1) The CLASSPATH and shared library path environment variables
// must be set, as for any JDBC application.
// (2) 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.
// 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.jdbc.app.*;
import java.sql.*;
import java.io.*;
public class DB2UdCli {
public static void main (String argv[]) {
Connection con = null;
// URL is jdbc:db2:dbname
String url = "jdbc:db2:sample";
try {
System.out.println ("Java User-defined Function Sample");
// Load DB2 JDBC application driver
Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();
// Connect to database
if (argv.length == 0) {
// connect with default id/password
con = DriverManager.getConnection(url);
}
else if (argv.length == 2) {
String userid = argv[0];
String passwd = argv[1];
// connect with user-provided username and password
con = DriverManager.getConnection(url, userid, passwd);
}
else {
System.out.println("\nUsage: java DB2UdCli [username password]\n");
System.exit(0);
}
System.out.println ("Connected to the database");
// Execute DECLARE FUNCTION calls to register Java UDFs
System.out.println ("Declaring the Java UDFs");
declareUDFs (con, false); // use NOT FENCED mode
// Prepare table for UDF runs
System.out.println ("Preparing test tables for Java UDFs");
prepareTable (con);
// Call UDFs
System.out.println ("Calling UDFs in UNFENCED mode");
callUDFs (con);
// Clean-up table
System.out.println ("Cleaning up test tables");
deleteTable (con);
}
catch (Throwable x)
{
try { con.close (); } catch (Throwable y) {}
System.err.println ("Aborted due to exception.");
x.printStackTrace ();
}
}
// Run DECLARE FUNCTION to register JAVA UDFs
static void
declareUDFs (Connection con,
boolean fenced) throws Exception
{
declareUDF (con, "MATH.\"/\"", "int, int", "int",
fenced, false, "DB2Udf!divid");
declareUDF (con, "fold", "clob(100k), int", "clob(100k)",
fenced, false, "DB2Udf!fold");
declareUDF (con, "findv", "varchar(500)", "int",
fenced, false, "DB2Udf!findvwl");
declareUDF (con, "counter", "", "int",
fenced, true, "DB2Udf!ctr");
declareUDF (con, "get_blob", "varchar(200)", "blob(1K)",
fenced, false, "DB2Udf!getBlob");
declareUDF (con, "listDirectory", "varchar(250)",
"table(f_name varchar(250), f_type varchar(20)," +
"f_link varchar(250), f_len int)",
fenced, true, "DB2Udf!listDirectory");
}
// Build and run just one CREATE FUNCTION statement.
static void
declareUDF (Connection con,
String name,
String argumentTypes,
String returnType,
boolean fenced,
boolean scratchpad,
String externalName) throws Exception
{
Statement s = con.createStatement ();
String sqlClean = "drop function " + name;
try { s.executeUpdate (sqlClean); } catch (SQLException x) { }
String sql = "create function " + name +
" (" + argumentTypes + ") " +
" returns " + returnType +
(fenced ? " fenced" : " not fenced") +
(scratchpad ? " scratchpad" : "") +
" variant no sql no external action " +
" language java parameter style db2general " +
" final call disallow parallel dbinfo " +
" external name '" + externalName + "'";
s.executeUpdate (sql);
System.out.println ("Registered Java UDF " + name);
s.close ();
}
// Create sample tables for UDF runs - parallel C UDF examples
static void
prepareTable (Connection con) throws Exception
{
Statement s = con.createStatement ();
String sqlClean = "drop table udftest";
String sqlClean1 = "drop table datalink_udftest";
String sqlUrlIns1 = "insert into datalink_udftest values (16,dlvalue('";
String sqlUrlIns2 = "'))";
String sqlUrlIns;
byte[] dlUrl = new byte[400];
String dlUrlString;
try
{
s.executeUpdate (sqlClean);
s.executeUpdate (sqlClean1);
} catch (SQLException x) { }
s.executeUpdate ("create table udftest(int1 integer,int2 integer," +
"part char(5),descr clob(33K))");
// Create table for the DATALINK-UDF sample. "no link control"
// has been used just for simplicity. Powerful UDFs can be
// written with "file link control" along similiar lines.
s.executeUpdate ("create table datalink_udftest(int1 integer," +
"file_reference datalink linktype url no link control)");
s.executeUpdate ("insert into udftest values (16,1,'brain'," +
"'The only part of the body capable of forgetting.')");
s.executeUpdate ("insert into udftest values (8,2,'heart'," +
"'The seat of the emotions?')");
s.executeUpdate ("insert into udftest values (4,4,'elbow'," +
"'That bendy place in mid-arm')");
s.executeUpdate ("insert into udftest values (2,0,NULL,NULL)");
s.executeUpdate ("insert into udftest values (97,16,'xxxxx','Unknown.')");
System.out.print("\nTo call the DATALINK-UDF, type a URL\n" +
"(for example, http://www.ibm.com) at the following prompt.\n" +
"The URL must be accessible from the database server.\n" +
"To skip the DATALINK-UDF sample, just press ENTER.\n" +
"URL>");
// Get from user the URL whose contents are to be materialized into BLOB
// When user just presses ENTER, the string is zero-length.
// The result BLOB from the UDF in this case will be NULL.
// This can be used to skip the DATALINK-UDF sample.
System.in.read(dlUrl);
// Construct a string from user input, trimming whitespaces at
// both ends
dlUrlString = (new String(dlUrl)).trim();
// Use the input value to insert into a DATALINK column
sqlUrlIns = sqlUrlIns1 + dlUrlString + sqlUrlIns2;
s.executeUpdate (sqlUrlIns);
System.out.println ("Inserted DATALINK value into sample" +
" table datalink_udftest!");
s.close ();
System.out.println ("Created UDF test tables.");
}
// Call the UDFs - parallel C UDF examples
static void
callUDFs (Connection con) throws Exception
{
runQuery (con, "select substr(descr,1,30)," +
"substr(fold(descr,6),1,30) from udftest");
System.out.println ();
// exception expected for this run
try
{
runQuery (con, "select part, findv(part) from udftest");
}
catch (SQLException x)
{
if (x.getSQLState ().equals ("38700"))
System.out.println ("Got expected exception: " + x.toString ());
else
throw x;
}
// System.out.println();
runQuery (con, "select substr(descr,1,25), " +
"findv(cast(descr as varchar(60))) from udftest");
System.out.println ();
runQuery (con, "select int1, counter(), int1/counter() from udftest");
System.out.println ();
runQuery (con, "select get_blob(dlurlcomplete(file_reference)) " +
"from datalink_udftest");
System.out.println ();
runQuery (con, "select * from table(listDirectory('/')) as files");
System.out.println ();
}
// Run a query and print out its results
static void
runQuery (Connection con, String sql) throws Exception
{
Statement s = con.createStatement ();
System.out.println ("Executing SQL query:");
System.out.println (sql);
ResultSet rs = s.executeQuery (sql);
int row = 1;
int numColumns = rs.getMetaData ().getColumnCount ();
while (rs.next ())
{
System.out.print ("Row " + row + ": ");
for (int column = 1; column <= numColumns; column++)
{
if (column > 1)
System.out.print (", ");
System.out.print (rs.getString (column));
}
System.out.println ();
row++;
}
rs.close ();
s.close ();
}
// Drop the UDF tables we built
static void
deleteTable (Connection con) throws Exception
{
Statement s = con.createStatement ();
s.executeUpdate ("drop table udftest");
s.executeUpdate ("drop table datalink_udftest");
s.close ();
System.out.println ("Dropped UDF test tables.");
}
}