/****************************************************************************
** (c) Copyright IBM Corp. 2007 All rights reserved.
**
** The following sample of source code ("Sample") is owned by International
** Business Machines Corporation or one of its subsidiaries ("IBM") and is
** copyrighted and licensed, not sold. You may use, copy, modify, and
** distribute the Sample in any form without payment to IBM, for the purpose of
** assisting you in the development of your applications.
**
** The Sample code is provided to you on an "AS IS" basis, without warranty of
** any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
** IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
** MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
** not allow for the exclusion or limitation of implied warranties, so the above
** limitations or exclusions may not apply to you. IBM shall not be liable for
** any damages you suffer as a result of using, copying, modifying or
** distributing the Sample, even if IBM has been advised of the possibility of
** such damages.
*****************************************************************************
**
** SOURCE FILE NAME: UDFcli.cs
**
** SAMPLE: Client application that calls the UDFs in UDFsrv.cs
**
*****************************************************************************
**
** Steps to run the sample with the command line window:
**
** I) If you have a compatible nmake program on your system,
** do the following:
**
** 1. Compile this server source file, UDFsrv.cs. This
** erases any existing assembly of the same name and copies
** the newly compiled assembly UDFsrv.dll from the
** current directory to the $(DB2PATH)\function directory).
** This also runs a batchfile UDFCat that executes SQL to
** drop (if already existing) and create the UDFs
** in DB2 that have their UDF-body implemented by the
** methods in this file, UDFsrv.cs:
**
** nmake/make UDFsrv
**
** To run UDFcli successfully you must have succesfully
** completed step 1.
**
** 2. Compile the client source file UDFcli.cs.
**
** nmake/make UDFcli
**
** 3. Run the client UDFcli:
**
** UDFcli
**
**
** II) If you don't have a compatible nmake program on your
** system do the following:
**
** 1. Compile this file, UDFsrv.cs, containing the C#
** UDF methods that implement the UDF-bodies, with
** bldrn.bat. This batch file first erases any
** existing assembly of the same name. Then it
** compiles UDFsrv.cs and copies the assembly file
** UDFsrv.dll from the current directory to the DB2 function
** directory: $(DB2PATH)\function.
**
** bldrtn UDFsrv
**
**
** 2. Create the UDFs in DB2 by entering the following
** at the command prompt:
**
** UDFCat
**
** This will drop the UDFs from DB2, if they exist, and
** then executes SQL CREATE FUNCTION statements to create the
** UDFs in DB2. These statements associate each function
** name with an external C# method in the assembly UDFsrv.dll
** that is in the DB2 function directory.
**
** 3. Compile the UDFcli.cs file with the bldapp.bat batch file by
** entering the following at the command prompt:
**
** bldapp UDFcli
**
** 4. Run the UDFcli program by entering the program name at
** the command prompt:
**
** UDFcli
**
** UDFcli calls several methods that call the UDFs:
**
** Class UDFsrv contains the following methods:
**
** 1. ScalarUDF Updates employee salary based on job type
** 2. ScratchpadScalarUDF Increments a count each time UDF is called
** 3. ScalarUDFReturningError Shows how to return an error from a UDF
** 4. TableUDF Given a salary update multiplier returns a
** table-reference with employees' names, jobs,
** and updated salaries
**
******************************************************************************
**
** SQL Statements USED:
** SELECT
** UPDATE
**
**
*****************************************************************************
**
** For more information on the sample programs, see the README file.
**
** For information on developing JDBC applications, see the Application
** Development Guide.
**
** For information on using SQL statements, see the SQL Reference.
**
****************************************************************************/
using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;
class UDFClient
{
public static void Main(String[] args)
{
// Declare a DB2Connection and a DB2Transaction
DB2Connection conn = null;
DB2Transaction trans = null;
try
{
Console.WriteLine("\n THIS SAMPLE SHOWS HOW TO CALL THE UDFs" +
" IN UDFsrv.cs.\n");
// Connect to a database
Console.WriteLine(" Connecting to a database ...");
conn = ConnectDb(args);
// Invoke an external scalar UDF: ScalarUDF
trans=conn.BeginTransaction();
InvokeExternalScalarUDF(conn, trans);
// Invoke a scalar UDF with a scratchpad: ScratchpadScUDF
trans=conn.BeginTransaction();
InvokeScratchpadScUDF(conn, trans);
// Invoke a scalar UDF that returns an error: ScUDFReturningErr
trans=conn.BeginTransaction();
InvokeScUDFReturningErr(conn, trans);
// Invoke a table UDF with a scratchpad: TableUDF
trans=conn.BeginTransaction();
InvokeTableUDF(conn, trans);
// Disconnect from the database
Console.WriteLine("\n Disconnect from the database");
conn.Close();
}
catch (Exception e)
{
try
{
Console.WriteLine("Error caught in client of UDFsrv App.");
trans.Rollback();
conn.Close();
}
catch (Exception x)
{
Console.WriteLine(x.Message);
}
Console.WriteLine(e.Message);
}
}
// This method establishes a connection to a database
public static DB2Connection ConnectDb(String[] argv)
{
String server = "";
String alias = "";
String userId = "";
String password = "";
Int32 portNumber = -1;
String connectString;
if( argv.Length > 5 ||
( argv.Length == 1 &&
( String.Compare(argv[0],"?") == 0 ||
String.Compare(argv[0],"-?") == 0 ||
String.Compare(argv[0],"/?") == 0 ||
String.Compare(argv[0],"-h",true) == 0 ||
String.Compare(argv[0],"/h",true) == 0 ||
String.Compare(argv[0],"-help",true) == 0 ||
String.Compare(argv[0],"/help",true) == 0 ) ) )
{
throw new Exception(
"Usage: prog_name [dbAlias] [userId passwd] \n" +
" prog_name [dbAlias] server portNum userId passwd");
}
switch (argv.Length)
{
case 0: // Use all defaults
alias = "sample";
userId = "";
password = "";
break;
case 1: // dbAlias specified
alias = argv[0];
userId = "";
password = "";
break;
case 2: // userId & passwd specified
alias = "sample";
userId = argv[0];
password = argv[1];
break;
case 3: // dbAlias, userId & passwd specified
alias = argv[0];
userId = argv[1];
password = argv[2];
break;
case 4: // use default dbAlias
alias = "sample";
server = argv[0];
portNumber = Convert.ToInt32(argv[1]);
userId = argv[2];
password = argv[3];
break;
case 5: // everything specified
alias = argv[0];
server = argv[1];
portNumber = Convert.ToInt32(argv[2]);
userId = argv[3];
password = argv[4];
break;
}
if(portNumber==-1)
{
connectString = "Database=" + alias;
}
else
{
connectString = "Server=" + server + ":" + portNumber +
";Database=" + alias;
}
if(userId != "")
{
connectString += ";UID=" + userId + ";PWD=" + password;
}
DB2Connection conn = new DB2Connection(connectString);
conn.Open();
Console.WriteLine(" Connected to the " + alias + " database");
return conn;
} // ConnectDb
// This method displays the result set contained in the
// DB2DataReader input parameter.
public static void DisplayResultSet(DB2DataReader reader)
{
while(reader.Read())
{
Console.Write(" ");
for(int k=0;k<reader.FieldCount;k++)
{
String str;
if (Equals(reader.GetFieldType(k),Type.GetType("System.Decimal")))
{
str = String.Format("{0:f2}", (Decimal)reader.GetValue(k));
}
else
{
str = reader.GetValue(k).ToString();
}
if(str.Length < 10)
{
Console.Write(str.PadRight(10,' ') + " ");
}
else
{
Console.Write(str.Substring(0,10) + " ");
}
}
Console.WriteLine();
}
} // DisplayResultSet
// This method invokes external scalar UDF, ScalarUDF, in a SELECT query.
// This scalar UDF calculates and displays a new salary for employees
// in the staff table whose name starts with 'S' based on a simple algorithm.
// It does not update the salaries, but displays the employee name, job,
// salary, and the new calculated new salary.
//
// UDFs are useful for transforming values that you are
// selecting or for displaying a calculated value that is a
// function of the other select values or table values
public static void InvokeExternalScalarUDF(DB2Connection conn,
DB2Transaction trans)
{
DB2DataReader reader = null;
try
{
Console.WriteLine("\n Invoke the scalar UDF 'ScalarUDF' in a SELECT" +
" statement:\n");
Console.WriteLine(" SELECT name, job, salary, ");
Console.WriteLine(" ScalarUDF(cast(job as char(5)), salary)");
Console.WriteLine(" AS calculated_salary");
Console.WriteLine(" FROM staff");
Console.WriteLine(" WHERE name LIKE 'S%'");
Console.WriteLine("\n");
// Create a DB2Command to execute a SELECT statement with a UDF reference
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "SELECT name, job, salary, " +
" ScalarUDF(cast(job as char(5)), salary) " +
" AS calculated_salary " +
" FROM staff " +
" WHERE name LIKE 'S%'";
reader = cmd.ExecuteReader();
Console.WriteLine(" NAME JOB SALARY CALCULATED_SALARY");
Console.WriteLine(" ---------- -------- ---------- -----------------");
// Display the result set
DisplayResultSet(reader);
reader.Close();
// Rollback the transactions to preserve the SAMPLE
// database's original state
trans.Rollback();
}
catch (Exception e)
{
reader.Close();
trans.Rollback();
Console.WriteLine(e.Message);
}
} // InvokeScalarUDF
// This method invokes external scratchpad scalar UDF, ScratchpadScUDF, in
// a SELECT query. The scratchpad scalar UDF generates a row value for each
// row that is returned by the query - a quick way to display a numbered
// list to accompany row values without having to alter the table.
public static void InvokeScratchpadScUDF(DB2Connection conn,
DB2Transaction trans)
{
DB2DataReader reader = null;
try
{
Console.WriteLine("\n\n Invoke the scratchpad scalar UDF 'ScratchpadScUDF'"
+ " in a SELECT statement:\n");
Console.WriteLine(" SELECT ScratchpadScUDF() AS row_id name, job");
Console.WriteLine(" FROM staff");
Console.WriteLine(" WHERE name LIKE 'S%'");
Console.WriteLine("\n");
// Create a DB2Command to execute a SELECT statement with a UDF reference
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "SELECT ScratchpadScUDF() AS row_id, name, job " +
" FROM staff " +
" WHERE name LIKE 'S%'\n";
reader = cmd.ExecuteReader();
Console.WriteLine(" ROW_ID NAME JOB");
Console.WriteLine(" ---------- ---------- -------");
// Display the result set
DisplayResultSet(reader);
reader.Close();
// Rollback the transactions to preserve the SAMPLE
// database's original state
trans.Rollback();
}
catch (Exception e)
{
reader.Close();
trans.Rollback();
Console.WriteLine(e.Message);
}
} // InvokeScratchpadScUDF
// This method invokes a scratchpad scalar UDF in a SELECT query to show how
// how an error is returned from a UDF. The UDF performs a simple division.
// If the input divisor is 0, an error is returned.
public static void InvokeScUDFReturningErr(DB2Connection conn,
DB2Transaction trans)
{
DB2DataReader reader = null;
try
{
Console.WriteLine("\n\n Invoke the scalar UDF 'ScUDFReturningErr',"
+ " which returns an error, in a SELECT statement:\n");
Console.WriteLine(" SELECT name, job, ScUDFReturningErr(salary, 0.0)");
Console.WriteLine(" AS calculated_salary");
Console.WriteLine(" FROM staff");
Console.WriteLine(" WHERE name LIKE 'S%' ");
Console.WriteLine("\n");
// Create a DB2Command to execute a SELECT statement with a UDF reference
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "SELECT ScUDFReturningErr(cast(salary as double), 0.00) " +
" AS divided_salary " +
" FROM staff " +
" WHERE name LIKE 'S%'\n";
reader = cmd.ExecuteReader();
// Display the result set
DisplayResultSet(reader);
reader.Close();
// Rollback the transactions to preserve the SAMPLE
// database's original state
trans.Rollback();
}
catch (Exception e)
{
reader.Close();
trans.Rollback();
Console.WriteLine(e.Message);
}
} // InvokeScUDFReturningErr
// This method updates salaries in the EMPLOYEE table according to 3 values
public static void InvokeTableUDF(DB2Connection conn,
DB2Transaction trans)
{
DB2DataReader reader = null;
try
{
Console.WriteLine("\n\n Invoke the tableUDF 'TableUDF',"
+ " in a SELECT statement:\n");
Console.WriteLine(" SELECT udfTable.name, udfTable.job,");
Console.WriteLine(" udfTable.salary");
Console.WriteLine(" FROM TABLE(TableUDF(1.5))");
Console.WriteLine(" AS udfTable");
Console.WriteLine("\n");
// Create a DB2Command to execute a SELECT statement with
// a UDF table-reference (note the use of the keyword TABLE)
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "SELECT udfTable.name, udfTable.job, udfTable.salary" +
" FROM TABLE(TableUDF(1.5))" +
" AS udfTable\n";
reader = cmd.ExecuteReader();
Console.WriteLine(" NAME JOB SALARY");
Console.WriteLine(" ---------- ---------- -------");
// Display the result set
DisplayResultSet(reader);
reader.Close();
// Rollback the transactions to preserve the SAMPLE
// database's original state
trans.Rollback();
}
catch (Exception e)
{
reader.Close();
trans.Rollback();
Console.WriteLine(e.Message);
}
} // InvokeTableUDF
} //UDFClient