/****************************************************************************
** (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: UDFsrv.cs
**
** SAMPLE: User-defined scalar functions called by UDFcli.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
**
******************************************************************************
** UDFcli UDFcli
*****************************************************************************/
using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;
class UDFsrv
{
/****************************************************************
Routine: ScalarUDF
Purpose: Operates on input job and salary values and
determines and returns a new salary based on the
input value of the job.
Shows how to:
- define input and output parameters to a scalar
function implemented with parameter style SQL
Parameters:
IN: inJob - employee job description
inSalary - employee salary
OUT: outNewSalary - updated employee salary
******************************************************************/
public static void ScalarUDF( String inJob,
Double inSalary,
out Double outNewSalary,
Int16 jobNullInd,
Int16 salaryNullInd,
out Int16 newSalaryNullInd,
ref String sqlState,
String funcName,
String specName,
ref String diagMsg)
{
// Initialize output parameter
outNewSalary = -1;
// Return a null salary if either of
// the input values are NULL
if (jobNullInd == -1 || salaryNullInd == -1)
{
newSalaryNullInd = -1;
}
else
{
if (inJob == "Mgr ")
{
outNewSalary = (inSalary * 1.20);
}
else if (inJob == "Sales")
{
outNewSalary = (inSalary * 1.10);
}
else /* it is clerk */
{
outNewSalary = (inSalary * 1.05);
}
newSalaryNullInd = 0;
}
} // ScalarUDF funtion
/****************************************************************
Routine: ScratchpadScUDF
Purpose: Using a scratchpad, keeps track of the number of times
that the scalarUDF - effectively counting the number of
values (or rows) upon which it operates.
Shows how to:
- Pass a scratchPad into a UDF as a parameter.
- Get and set a scratchPad value
Parameters:
OUT: outCounter - count of the number of values upon
which the UDF operates
******************************************************************/
public static void ScratchpadScUDF(out Int32 outCounter,
out Int16 outCounterNullInd,
ref String sqlState,
String funcName,
String specName,
ref String diagMsg,
Byte[] scratchPad,
Int32 callType)
{
outCounter = (Int32)(0);
outCounterNullInd = -1;
Int32 counter = 1;
switch (callType)
{
case -1: // SQLUDF_FIRST_CALL:
scratchPad[0] = (Byte)counter;
break;
case 0: // SQLUDF_NORMAL_CALL:
counter = (Int32)scratchPad[0];
counter = counter + 1;
scratchPad[0] = (Byte)counter;
break;
case 1: // SQLUDF_FINAL_CALL:
break;
default:
// We should never get here. Required so that
// outCounter output is always set.
outCounter = (Int32)(0);
outCounterNullInd = -1;
break;
}
outCounter = scratchPad[0];
outCounterNullInd = 0;
} /* ScratchpadScUDF function*/
/****************************************************************
Routine: ScUDFReturningErr
Purpose: Illustrates one method of returning a user-defined
SQL error upon detection of an error condition by
setting SQLCA structure values.
Shows how to:
- Set the SCLA structure with user-defined SQL
error values
Parameters:
IN: inOperand1 - Divisor
inOperand2 - Dividend
OUT: outResult - Result of the division
******************************************************************/
public static void ScUDFReturningErr( Double inOperand1,
Double inOperand2,
out Double outResult,
Int16 operand1NullInd,
Int16 operand2NullInd,
out Int16 outResultNullInd,
ref String sqlState,
String funcName,
String specName,
ref String diagMsg)
{
// Initialize outResult
outResult = -1;
outResultNullInd = -1;
if (inOperand2 == 0.00)
{
sqlState = "38999";
diagMsg = "DIVIDE BY ZERO ERROR";
}
else
{
outResult = (Double)(inOperand1 / inOperand2);
outResultNullInd = 0;
}
} /* ScUDFReturningErr */
/****************************************************************
Routine: TableUDF
Purpose: Reads employee information from an array, determines
a new salary for the employee, and returns one
row for each employee to ultimately form a
table-reference or row data.
Shows how to:
- Pass a scratchPad into a UDF as a parameter.
- Get and set a scratchPad value
- Generate a row with each invocation to form
a table-reference
- Raise an error when there is no more data to
process that ends the invocations of the
UDF.
Parameters:
IN: factor - multiplier to determine new salary
OUT: name - employee name
position - employee position
salary - updated employee salary
******************************************************************/
public static void TableUDF( Double factor,
out String name,
out String position,
out Double salary,
Int16 factorNullInd,
out Int16 nameNullInd,
out Int16 positionNullInd,
out Int16 salaryNullInd,
ref String sqlState,
String funcName,
String specName,
ref String diagMsg,
Byte[] scratchPad,
Int32 callType )
{
Int16 intRow = 0;
salary = 0;
name = position = "";
nameNullInd = positionNullInd = salaryNullInd = -1;
// Create an array of Person type information. You
// can also retrieve data from a seperate text file.
// See "Application Development Guide" on how to work
// with a data file instead of a buffer or an array
Person[] Staff = new Person[3];
Staff[0] = new Person("Gwen", "Developer", 10000);
Staff[1] = new Person("Andrew", "Developer", 20000);
Staff[2] = new Person("Liu", "Team Lead", 30000);
switch(callType)
{
case (-2): // Case SQLUDF_TF_FIRST:
break;
case (-1): // Case SQLUDF_TF_OPEN:
intRow = 1;
scratchPad[0] = (Byte)intRow; // Write to scratchpad
break;
case (0): // Case SQLUDF_TF_FETCH:
intRow = (Int16)scratchPad[0];
if (intRow > Staff.Length)
{
sqlState = "02000"; // Return an error SQLSTATE
}
else
{
// Generate a row in the output table
// based on the Staff array data.
name = Staff[intRow-1].getName();
position = Staff[intRow-1].getPosition();
salary = (Staff[intRow-1].getSalary()) * factor;
nameNullInd = 0;
positionNullInd = 0;
salaryNullInd = 0;
}
intRow++;
scratchPad[0] = (Byte)intRow; // Write scratchpad
break;
case (1): // Case SQLUDF_TF_CLOSE:
break;
case (2): // Case SQLUDF_TF_FINAL:
break;
}
} // TableUDF
} // Class UDFsrv
// The class Person is a supporting class for
// the table function UDF, tableUDF, below.
class Person
{
private String name;
private String position;
private Int32 salary;
public Person(String newName, String newPosition, Int32 newSalary)
{
this.name = newName;
this.position = newPosition;
this.salary = newSalary;
}
public String getName()
{
return this.name;
}
public String getPosition()
{
return this.position;
}
public Int32 getSalary()
{
return this.salary;
}
} // end of class Person