OLE (Object Linking and Embedding) automation is part of the OLE 2.0 architecture from Microsoft Corporation. With OLE automation, your applications, regardless of the language in which they are written, can expose their properties and methods in OLE automation objects. Other applications, such as Lotus Notes or Microsoft Exchange(R), can then integrate these objects by taking advantage of these properties and methods through OLE automation.
The applications exposing the properties and methods are called OLE automation servers or objects, and the applications that access those properties and methods are called OLE automation controllers. OLE automation servers are COM components (objects) that implement the OLE IDispatch interface. An OLE automation controller is a COM client that communicates with the automation server through its IDispatch interface. COM (Component Object Model) is the foundation of OLE. For OLE automation UDFs, DB2 acts as an OLE automation controller. Through this mechanism, DB2 can invoke methods of OLE automation objects as external UDFs.
Note that this section assumes that you are familiar with OLE automation terms and concepts. This book does not present any introductory OLE material. For an overview of OLE automation, refer to Microsoft Corporation: The Component Object Model Specification, October 1995. For details on OLE automation, refer to OLE Automation Programmer's Reference, Microsoft Press, 1996, ISBN 1-55615-851-3.
For a list of sample applications included with the DB2 Application Development Client that demonstrate OLE automation UDFs, see Table 49.
OLE automation UDFs are implemented as public methods of OLE automation objects. The OLE automation objects must be externally creatable by an OLE automation controller, in this case DB2, and support late binding (also called IDispatch-based binding). OLE automation objects must be registered in the Windows registration database (registry) with a class identifier (CLSID), and optionally, an OLE programmatic ID (progID) to identify the automation object. The progID can identify an in-process (.DLL) or local (.EXE) OLE automation server, or a remote server through DCOM (Distributed COM). OLE automation UDFs can be scalar functions or table functions.
After you code an OLE automation object, you need to register the methods of the object as UDFs using the SQL CREATE FUNCTION statement. Registering an OLE automation UDF is very similar to registering any external C or C++ UDF, but you must use the following options:
The external name consists of the OLE progID identifying the OLE automation object and the method name separated by ! (exclamation mark):
CREATE FUNCTION bcounter () RETURNS INTEGER
EXTERNAL NAME 'bert.bcounter!increment'
LANGUAGE OLE
FENCED
SCRATCHPAD
FINAL CALL
NOT DETERMINISTIC
NULL CALL
PARAMETER STYLE DB2SQL
NO SQL
NO EXTERNAL ACTION
DISALLOW PARALLEL;
The calling conventions for OLE method implementations are identical to the conventions for functions written in C or C++. An implementation of the above method in the BASIC language looks like the following (notice that in BASIC the parameters are by default defined as call by reference):
Public Sub increment(output As Long, _
indicator As Integer, _
sqlstate As String, _
fname As String, _
fspecname As String, _
sqlmsg As String, _
scratchpad() As Byte, _
calltype As Long)
OLE automation UDFs (methods of OLE automation objects) are applied on instances of OLE automation objects. DB2 creates an object instance for each UDF reference in an SQL statement. An object instance can be reused for subsequent method invocations of the UDF reference in an SQL statement, or the instance can be released after the method invocation and a new instance is created for each subsequent method invocation. The proper behavior can be specified with the SCRATCHPAD option in the SQL CREATE FUNCTION statement. For the LANGUAGE OLE clause, the SCRATCHPAD option has the additional semantic compared to C or C++, that a single object instance is created and reused for the entire query, whereas if NO SCRATCHPAD is specified, a new object instance may be created each time a method is invoked. Separate instances are created for each UDF reference in an SQL statement.
Using the scratchpad allows a method to maintain state information in instance variables of the object, across function invocations. It also increases performance as an object instance is only created once and then reused for subsequent invocations.
DB2 handles the type conversions between SQL types and OLE automation
types. The following table summarizes the supported data types and how
they are mapped. The mapping of OLE automation types to data types of
the implementing programming language, such as BASIC or C/C++, is
described in Table 17.
Table 16. Mapping of SQL and OLE Automation Datatypes
| SQL Type | OLE Automation Type | OLE Automation Type Description | ||
|---|---|---|---|---|
| SMALLINT | short | 16-bit signed integer | ||
| INTEGER | long | 32-bit signed integer | ||
| REAL | float | 32-bit IEEE floating-point number | ||
| FLOAT or DOUBLE | double | 64-bit IEEE floating-point number | ||
| DATE | DATE | 64-bit floating-point fractional number of days since December 30, 1899 | ||
| TIME | DATE | |||
| TIMESTAMP | DATE | |||
| CHAR(n) | BSTR | Length-prefixed string as described in the OLE Automation Programmer's Reference. | ||
| VARCHAR(n) | BSTR | |||
| LONG VARCHAR | BSTR | |||
| CLOB(n) | BSTR | |||
| GRAPHIC(n) | BSTR | Length-prefixed string as described in the OLE Automation Programmer's Reference. | ||
| VARGRAPHIC(n) | BSTR | |||
| LONG GRAPHIC | BSTR | |||
| DBCLOB(n) | BSTR | |||
| CHAR(n)1 | SAFEARRAY[unsigned char] | 1-dim Byte() array of 8-bit unsigned data items. (SAFEARRAYs are described in the OLE Automation Programmer's Reference.) | ||
| VARCHAR(n)1 | SAFEARRAY[unsigned char] | |||
| LONG VARCHAR1 | SAFEARRAY[unsigned char] | |||
| BLOB(n) | SAFEARRAY[unsigned char] | |||
| ||||
Data passed between DB2 and OLE automation UDFs is passed as call by reference. SQL types such as BIGINT, DECIMAL, or LOCATORS, or OLE automation types such as Boolean or CURRENCY that are not listed in the table are not supported. Character and graphic data mapped to BSTR is converted from the database code page to the UCS-2 (also known as Unicode, IBM code page 13488) scheme. Upon return, the data is converted back to the database code page. These conversions occur regardless of the database code page. If code page conversion tables to convert from the database code page to UCS-2 and from UCS-2 to the database code page are not installed, you receive an SQLCODE -332 (SQLSTATE 57017).
You can implement OLE automation UDFs in any language. This section shows you how to implement OLE automation UDFs using BASIC or C++ as two sample languages.
Table 17 shows the mapping of the various SQL data types to the
intermediate OLE automation data types, and the data types in the language of
interest (BASIC or C++). OLE data types are language
independent, (that is, Table 16 holds true for all languages).
Table 17. Mapping of SQL and OLE Data Types to BASIC and C++ Data Types
| SQL Type | OLE Automation Type | UDF Language | |||
|---|---|---|---|---|---|
| BASIC Type | C++ Type | ||||
| SMALLINT | short | Integer | short | ||
| INTEGER | long | Long | long | ||
| REAL | float | Single | float | ||
| FLOAT or DOUBLE | double | Double | double | ||
| DATE, TIME, TIMESTAMP | DATE | Date | DATE | ||
| CHAR(n), VARCHAR(n), LONG VARCHAR, CLOB(n) | BSTR | String | BSTR | ||
| GRAPHIC(n), VARGRAPHIC(n), LONG GRAPHIC, DBCLOB(n) | BSTR | String | BSTR | ||
| CHAR(n)1, VARCHAR(n)1, LONG VARCHAR1, BLOB(n) | SAFEARRAY[unsigned char] | Byte() | SAFEARRAY | ||
| |||||
To implement OLE automation UDFs in BASIC you need to use the BASIC data types corresponding to the SQL data types mapped to OLE automation types.
The BASIC declaration of the bcounter OLE automation UDF in Creating and Registering OLE Automation UDFs looks like the following:
Public Sub increment(output As Long, _
indicator As Integer, _
sqlstate As String, _
fname As String, _
fspecname As String, _
sqlmsg As String, _
scratchpad() As Byte, _
calltype As Long)
You can find an example of an OLE table automation in Example: Mail OLE Automation Table Function in BASIC.
Table 17 shows the C++ data types that correspond to the SQL data types and how they map to OLE automation types.
The C++ declaration of the increment OLE automation UDF is as follows:
STDMETHODIMP Ccounter::increment (long *output,
short *indicator,
BSTR *sqlstate,
BSTR *fname,
BSTR *fspecname,
BSTR *sqlmsg,
SAFEARRAY **scratchpad,
long *calltype );
OLE supports type libraries that describe the properties and methods of OLE automation objects. Exposed objects, properties, and methods are described in the Object Description Language (ODL). The ODL description of the above C++ method is as follows:
HRESULT increment ([out] long *output,
[out] short *indicator,
[out] BSTR *sqlstate,
[in] BSTR *fname,
[in] BSTR *fspecname,
[out] BSTR *sqlmsg,
[in,out] SAFEARRAY (unsigned char) *scratchpad,
[in] long *calltype);
The ODL description allows the specification whether a parameter is an input (in), output (out) or input/output (in,out) parameter. For an OLE automation UDF, the UDF input parameters and its input indicators are specified as [in] parameters, and UDF output parameters and its output indicators as [out] parameters. For the UDF trailing arguments, sqlstate is an [out] parameter, function name and function specific name are [in] parameters, scratchpad is an [in,out] parameter, and call type is an [in] parameter.
Scalar functions contain one output parameter and output indicator, whereas table functions contain multiple output parameters and output indicators corresponding to the RETURN columns of the CREATE FUNCTION statement.
OLE automation defines the BSTR data type to handle strings. BSTR is defined as a pointer to OLECHAR: typedef OLECHAR *BSTR. For allocating and freeing BSTRs, OLE imposes the rule, that the callee frees a BSTR passed in as a by-reference parameter before assigning the parameter a new value. This rule means the following for DB2 and OLE automation UDFs. The same rule applies for one-dimensional byte arrays which are received by the callee as SAFEARRAY**:
All other parameters are passed as pointers. DB2 allocates and manages the referenced memory.
OLE automation provides a set of data manipulation functions for dealing with BSTRs and SAFEARRAYs. The data manipulation functions are described in the OLE Automation Programmer's Reference.
The following C++ UDF returns the first 5 characters of a CLOB input parameter:
// UDF DDL: CREATE FUNCTION crunch (clob(5k)) RETURNS char(5)
STDMETHODIMP Cobj::crunch (BSTR *in, // CLOB(5K)
BSTR *out, // CHAR(5)
short *indicator1, // input indicator
short *indicator2, // output indicator
BSTR *sqlstate, // pointer to NULL
BSTR *fname, // pointer to function name
BSTR *fspecname, // pointer to specific name
BSTR *msgtext) // pointer to NULL
{
// Allocate BSTR of 5 characters
// and copy 5 characters of input parameter
// out is an [out] parameter of type BSTR, that is,
// it is a pointer to NULL and the memory does not have to be freed.
// DB2 will free the allocated BSTR.
*out = SysAllocStringLen (*in, 5);
return NOERROR;
};
An OLE automation server can be implemented as creatable single-use or creatable multi-use. With creatable single-use, each client (that is, a DB2 FENCED process) connecting with CoGetClassObject to an OLE automation object will use its own instance of a class factory, and run a new copy of the OLE automation server if necessary. With creatable multi-use, many clients connect to the same class factory. That is, each instantiation of a class factory is supplied by an already running copy of the OLE server, if any. If there are no copies of the OLE server running, a copy is automatically started to supply the class object. The choice between single-use and multi-use OLE automation servers is yours, when you implement your automation server. A single-use server is recommended for better performance.