// Source File Name: UDFclie.sqlj 1.4
//
// 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.
// SQLJ Sample Program UDFclie - Calling UDFsrv
// Steps to run the sample:
// (1) create and populate the SAMPLE database (db2sampl)
// (2) (n)make UDFsrv
// (3) (n)make UDFcli
// (4) To register the UDFs and UDT and create the UDT table:
// java UDFcli
// (5) (n)make UDFclie
// (6) run UDFclie with the following command:
// java UDFclie
// (7) To drop the UDFS, UDT, and UDT table:
// java UDFclie drop
// 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
// 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 UDFclie contains four methods:
// (1) callScalarUDF
// (2) callColumnUDF
// (3) callTableUDF
// (4) main: application body
import java.sql.*; // JDBC classes
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
#sql iterator CursorForScalarUDF(int ,String ) ;
#sql iterator CursorForTableUDF(String ,String ) ;
class UDFclie
{ 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) Call the scalar UDF
public static void
callScalarUDF (Connection con) throws Exception
{ try
{ System.out.println("\n Calling a scalar UDF");
CursorForScalarUDF c;
#sql c = { SELECT scalarUDF(name), name FROM STAFF
WHERE name LIKE 'D%' };
while(true)
{ int nameLen = 0;
String name = null;
#sql { FETCH :c INTO :nameLen, :name };
if (c.endFetch()) break;
System.out.println(" Output - the length of the name: " + nameLen +
" Input - the name: " + name);
}
}
catch (SQLException e)
{ int sqlCode = e.getErrorCode();
if (sqlCode == -572)
{
throw new Exception("\n\nError: The database has changed.\n"
+ "Run UDFcli and precompile UDFclie before running this program.\n\n"
+ e);
}
else System.out.println(e);
}
}
// (2) Call the column UDF
public static void
callColumnUDF (Connection con) throws Exception
{ try
{ System.out.println("\n Calling a column UDF");
#sql { 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
double udfResult;
#sql { SELECT
double(DECIMAL(columnUDF(COL1)))
INTO :udfResult
FROM TableForColumnUDF };
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 );
}
catch (SQLException e)
{ int sqlCode = e.getErrorCode();
if (sqlCode == -572)
{
throw new Exception("\n\nError: The database has changed.\n"
+ "Run UDFcli and precompile UDFclie before running this program.\n\n"
+ e);
}
else System.out.println(e);
}
}
// (3) Call the table UDF
public static void
callTableUDF (Connection con) throws Exception
{ try
{ System.out.println("\n Calling a table UDF");
CursorForTableUDF c;
#sql c = { 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(true)
{ String name = null;
String job = null;
#sql { FETCH :c INTO :name, :job };
if (c.endFetch()) break;
System.out.println(" Row number " + rowNb);
System.out.println(" Name: " + name);
System.out.println(" Job: " + job);
rowNb++;
}
}
catch (SQLException e)
{ int sqlCode = e.getErrorCode();
if (sqlCode == -572)
{
throw new Exception("\n\nError: The database has changed.\n"
+ "Use db2profc to precompile UDFclie "
+ "before running this program.\n"
+ e);
}
else System.out.println(e);
}
}
// (4) main application: .connect to the database
// .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 UDFclie [drop] [username password]\n");
}
// Set the default context
DefaultContext ctx = new DefaultContext(con);
DefaultContext.setDefaultContext(ctx);
// Enable transactions
con.setAutoCommit(false);
// Call the UDFs and drop UDFs, UDT, and table if requested by user
try
{ callScalarUDF(con);
callColumnUDF(con);
callTableUDF(con);
if (drop)
{
UDFcli.dropAll(con);
}
}
catch( Exception e )
{ throw e;
}
finally
{ // Commit the transaction
System.out.println("\nCommit the transaction...");
#sql { COMMIT };
System.out.println("Committed.");
}
}
catch (Exception e)
{ System.out.println (e);
}
}
}