// Source File Name: UDFcli.java 1.5
//
// Licensed Materials -- Property of IBM
//
// (c) Copyright International Business Machines Corporation, 1999.
// All Rights Reserved.
//
// US Government Users Restricted Rights -
// Use, duplication or disclosure restricted by
// GSA ADP Schedule Contract with IBM Corp.
// Sample Program UDFcli - Calling UDFsrv
// Steps to run the sample:
// (1) create and populate the SAMPLE database (db2sampl)
// (2) (n)make UDFsrv
// (3) (n)make UDFcli
// (4) run UDFcli with the following command:
// java UDFcli
// (5) run UDFcli and drop the UDFs, UDT, and
// table with the following command:
// java UDFcli drop
// NOTES: (1) You must set the jdk11_path database manager configuration
// parameter
// (2) You must set the CLASSPATH and shared library path
// environment variables, as for any JDBC application.
// (3) Visit http://www.software.ibm.com/data/db2/java
// for current DB2 Java information
// 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.
// Class UDFcli contains seven methods:
// (1) registerUDT: register a test UDT for the column UDF
// (2) registerUDFs
// (3) callScalarUDF
// (4) callColumnUDF
// (5) callTableUDF
// (6) dropAll
// (7) main: application body
import java.sql.*; // JDBC classes
class UDFcli
{ static
{ try
{ Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();
}
catch (Exception e)
{ System.out.println ("\n Error loading DB2 Driver...\n");
System.out.println (e);
System.exit(1);
}
}
// (1) Register the UDT for the column UDF
public static void
registerUDT (Connection con) throws Exception
{ try
{ Statement stmt = con.createStatement ();
System.out.println ("\n Registering Java UDT for the column UDF");
stmt.executeUpdate (
" CREATE DISTINCT TYPE UDTforColumnUDF AS DECIMAL(9,2) " +
" WITH COMPARISONS ");
stmt.close ();
}
catch (SQLException e)
{ int sqlCode = e.getErrorCode();
if (sqlCode == -601)
{ // ignore "UDT already exists" error
}
else System.out.println(e);
}
}
// (2) Register the UDFs
public static void
registerUDFs (Connection con) throws Exception
{ try
{ Statement registerStmt = con.createStatement ();
// scalar UDF
System.out.println (" Registering Java UDF: scalarUDF");
registerStmt.executeUpdate (
" CREATE FUNCTION scalarUDF ( VARCHAR(20) ) " +
" RETURNS int " +
" EXTERNAL NAME 'UDFsrv!scalarUDF' " +
" LANGUAGE java " +
" PARAMETER STYLE db2general " +
" DETERMINISTIC " +
" FENCED " +
" NOT NULL CALL " +
" NO SQL " +
" NO EXTERNAL ACTION " +
" NO SCRATCHPAD " +
" NO FINAL CALL " +
" ALLOW PARALLEL " +
" NO DBINFO");
// column UDF
System.out.println (" Registering Java UDF: columnUDF");
registerStmt.executeUpdate (
" CREATE FUNCTION columnUDF ( UDTforColumnUDF ) " +
" RETURNS UDTforColumnUDF " +
" SOURCE \"SYSIBM\".AVG( DECIMAL() ) ");
// table UDF
System.out.println (" Registering Java UDF: tableUDF");
registerStmt.executeUpdate (
" CREATE FUNCTION tableUDF ( VARCHAR(20) ) " +
" RETURNS TABLE ( name VARCHAR(20), " +
" job VARCHAR(20) ) " +
" EXTERNAL NAME 'UDFsrv!tableUDF' " +
" LANGUAGE java " +
" PARAMETER STYLE db2general " +
" NOT DETERMINISTIC " +
" FENCED " +
" NULL CALL " +
" NO SQL " +
" NO EXTERNAL ACTION " +
" NO SCRATCHPAD " +
" FINAL CALL " +
" DISALLOW PARALLEL " +
" NO DBINFO");
// table for column UDF
registerStmt.executeUpdate("CREATE TABLE TableForColumnUDF ( COL1 UDTforColumnUDF) ");
registerStmt.close ();
}
catch (SQLException e)
{ int sqlCode = e.getErrorCode();
if ((sqlCode == -601) || (sqlCode == -454))
{ // ignore "object already exists" error
}
else System.out.println(e);
}
}
// (3) Call the scalar UDF
public static void
callScalarUDF (Connection con) throws Exception
{ try
{ System.out.println("\n Calling a scalar UDF");
Statement stmt = con.createStatement ();
ResultSet rs = stmt.executeQuery(
" SELECT scalarUDF(name), name FROM STAFF " +
" WHERE name LIKE 'D%' ");
while(rs.next())
{ int nameLen = rs.getInt(1);
String name = rs.getString(2);
System.out.println(" Output - the length of the name: " + nameLen +
" Input - the name: " + name);
}
stmt.close ();
}
catch (Exception e)
{ System.out.println (e);
}
}
// (4) Call the column UDF
public static void
callColumnUDF (Connection con) throws Exception
{ try
{ System.out.println("\n Calling a column UDF");
Statement stmt = con.createStatement ();
stmt.executeUpdate(
"INSERT INTO TableForColumnUDF " +
"VALUES ( UDTforColumnUDF(CAST( 4.1 AS DECIMAL(9,2))) )," +
" ( UDTforColumnUDF(CAST( 10.7 AS DECIMAL(9,2))) )," +
" ( UDTforColumnUDF(CAST( 11.5 AS DECIMAL(9,2))) )," +
" ( UDTforColumnUDF(CAST( 14.4 AS DECIMAL(9,2))) ) ");
// use the column UDF
ResultSet rs = stmt.executeQuery(" SELECT " +
" double(DECIMAL(columnUDF(COL1)))" +
" FROM TableForColumnUDF " );
rs.next(); // the result set contains one record
double udfResult = rs.getDouble(1);
System.out.println(" The input for the column UDF is:");
System.out.println(" the column COL1 of the table TableForColumnUDF" );
System.out.println(" The output from the column UDF is:");
System.out.println(" the average for COL1 - " + udfResult );
rs.close();
stmt.close ();
}
catch (Exception e)
{ System.out.println (e);
}
}
// (5) Call the table UDF
public static void
callTableUDF (Connection con) throws Exception
{ try
{ System.out.println("\n Calling a table UDF");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(
" SELECT name, job " +
" FROM TABLE(tableUDF('Hello')) AS smallStaff ");
int rowNb = 1;
System.out.println(" The input for the table UDF is:");
System.out.println(" Hello" );
System.out.println(" The output from the table UDF is:");
while(rs.next())
{ String name = rs.getString(1);
String job = rs.getString(2);
System.out.println(" Row number " + rowNb);
System.out.println(" Name: " + name);
System.out.println(" Job: " + job);
rowNb++;
}
rs.close();
stmt.close ();
}
catch (Exception e)
{ System.out.println (e);
}
}
// (6) Drop the UDFs, UDT, and table
public static void
dropAll (Connection con) throws Exception
{ try
{ Statement dropStmt = con.createStatement ();
try
{ dropStmt.executeUpdate ("DROP TABLE TableForColumnUDF");
dropStmt.executeUpdate ("DROP FUNCTION scalarUDF");
dropStmt.executeUpdate ("DROP FUNCTION columnUDF");
dropStmt.executeUpdate ("DROP FUNCTION tableUDF");
dropStmt.executeUpdate ("DROP DISTINCT TYPE UDTforColumnUDF");
}
catch (SQLException e)
{ int sqlCode = e.getErrorCode();
if (sqlCode == -204)
{ // ignore "Object does not exist" error
}
else System.out.println(e);
}
dropStmt.close ();
}
catch (Exception e)
{ System.out.println (e);
}
}
// (7) main application: .connect to the database
// .register and call the UDFs
// .commit the transaction
public static void main (String argv[])
{ try
{ System.out.println (" Java User Defined Functions Sample");
// Connect to Sample database
boolean drop = false;
Connection con = null;
// URL is jdbc:db2:dbname
String url = "jdbc:db2:sample";
if (argv.length == 0)
{ // connect with default id/password
con = DriverManager.getConnection(url);
}
else if (argv.length == 1 && argv[0].equalsIgnoreCase("drop"))
{ // connect with default id/password
con = DriverManager.getConnection(url);
// call dropAll() to clean up
drop = true;
}
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 if (argv.length == 3 && argv[0].equalsIgnoreCase("drop"))
{
// connect with user-provided username and password
String userid = argv[1];
String passwd = argv[2];
con = DriverManager.getConnection(url, userid, passwd);
// call dropAll() to clean up
drop = true;
}
else
{ throw new Exception("\nUsage: java UDFcli [drop] [username password]\n");
}
// Enable transactions
con.setAutoCommit(false);
// Drop, register and call UDFs
try
{ dropAll(con); // ensure the UDFs, UDT and table do not exist
registerUDT(con); // for the column UDF
registerUDFs(con);
callScalarUDF(con);
callColumnUDF(con);
callTableUDF(con);
if (drop)
{ // drop UDFs, UDT, and table when program finishes
dropAll(con);
}
}
catch( Exception e )
{ throw e;
}
finally
{ // Commit the transaction
System.out.println("\nCommit the transaction...");
con.commit();
System.out.println("Committed.");
}
}
catch (Exception e)
{ System.out.println (e);
}
}
}