3 Oracle COM Automation Feature Core Functionality

Datatype Conversion for PL/SQL

Table 3-1 shows the mappings between PL/SQL datatypes and COM Automation datatypes.

This guide follows a convention where COM Automation datatypes are prefaced by an initial p when used as IN OUT or OUT parameters. Datatypes without the initial p are IN parameters.

Datatype Conversion for Java

Table 3-2 lists the supported COM Automation datatypes and related mappings to Java datatypes.

All the datatype mappings apply to properties, arguments, and return values, except void, which only applies to return values.

PL/SQL Use of HRESULT

The PL/SQL APIs return an integer return code. The return code is 0 when successful or a nonzero HRESULT when an error occurs.

Java Use of HRESULT

In the Java API, HRESULT is a data member of the COMException class.

Information Required for COM Objects

Review the following information about the COM objects that you intend to use:

• You must determine the Program ID of the COM object. The Program ID, or progID, is a descriptive string that maps to the Globally Unique Identifier (GUID), a hexadecimal number that uniquely identifies a COM object.

  The following string is an example of a progID:

  Excel.Worksheet.1
  
  

  Use the progID with the API that instantiates the COM object.

• You must be aware of the types of properties and methods that are exposed through the COM object's IDispatch interface. Usually, the ISV provides documentation describing the names and datatype of the object's properties and the prototypes of the object's methods. Properties are referred to by a descriptive string, such as xpos or ypos. A property can be any standard COM Automation datatype, such as INT or BSTR. The GetProperty and SetProperty APIs take the property name and a variable of the appropriate datatype. Methods are referred to by a descriptive string, such as InsertChart. A method takes a set of parameters that are of different COM Automation datatypes and returns a COM Automation datatype.

  The following is an example of a COM Automation method prototype in COM Interface Definition Language (IDL) grammar:

  [id(0x6003000)]
  long Post([in, out] long* lngAccountNo,
            [in, out] long* lngAmount,
            [in, out] BSTR* strResult);
  
  

  Interfaces define object methods and properties. COM IDL is used to specify interfaces that are defined on COM objects.

OLE/COM Object Viewer

Microsoft provides a tool called the OLE/COM Object Viewer with Microsoft Visual Studio for browsing the properties and methods of COM objects on a local system. This tool enables you to quickly and easily determine the properties and methods that each COM object exposes. See Figure 3-1, "OLE/COM Object Viewer" for an example.

Figure 3-1 OLE/COM Object Viewer

Description of the illustration olecom.gif

Using COM Automation Feature APIs

In a typical use of Oracle COM Automation Feature, you design a Java class or PL/SQL block to create and manipulate a COM object. The class or code block performs the following steps:

1. Creates the COM object as follows:

   • in PL/SQL, using CreateObject.

   • in Java, using a constructor or the Create method.

2. Manipulates the COM object calling the following APIs:

   • GetProperty to get a property value.

   • SetProperty to set a property value to a new value.

3. Calls Invoke to call a method.

   As part of preparation for the Invoke API call, you use InitArg and SetArg in Java and you use InitArg and SetArg in PL/SQL to package the argument to be sent to the COM Automation method.

4. Calls GetLastError in PL/SQL, to get the most recent error information.

5. Destroys the object using DestroyObject in PL/SQL or Destroy in Java.

PL/SQL APIs

The feature externalizes the following APIs for PL/SQL development:

• CreateObject

• DestroyObject

• GetLastError

• GetProperty

• SetProperty

• InitArg

• InitOutArg

• GetArg

• SetArg

• Invoke

Java APIs

The COM Automation Feature externalizes the following APIs for Java development:

• Automation Constructor

• Automation Methods

  • Create

  • Destroy

  • GetProperty

  • SetProperty

  • InitArg

  • SetArg

  • Invoke

• Currency Constructor

• Currency Methods

  • Get

  • Set

CreateObject

Instantiates a COM object in a COM Automation server.

FUNCTION CreateObject(progid VARCHAR2, reserved BINARY_INTEGER, servername VARCHAR2, objecttoken OUT BINARY_INTEGER) RETURN BINARY_INTEGER;

The created COM Automation object is freed with a corresponding call to DestroyObject. This nullifies the internal representation of the object in the Oracle COM Automation Feature and releases all the interfaces associated with the object.

This procedure returns a 0 when successful or a nonzero HRESULT when an error occurs.

HRESULT BINARY_INTEGER;
applicationToken BINARY_INTEGER:=-1;

HRESULT :=ORDCOM.CreateObject('Excel.Application', 0, '', applicationToken);
IF (HRESULT!=0) THEN
  dbms_output.put_line(HRESULT);
END IF;

DestroyObject

Destroys a created COM Automation object.

FUNCTION DestroyObject(objecttoken BINARY_INTEGER) RETURN BINARY_INTEGER;

Calling DestroyObject nullifies the internal representation of the object in the Oracle COM Automation Feature and releases all the interfaces associated with the object.

This procedure returns a 0 when successful or a nonzero HRESULT when an error occurs.

HRESULT BINARY_INTEGER;
applicationToken BINARY_INTEGER:=-1;

/* Assume applicationToken is initialized. */

HRESULT:=ORDCOM.DestroyObject(applicationToken);
IF (HRESULT!=0) THEN
   dbms_output.put_line(HRESULT);

GetLastError

Obtains the COM Automation error information about the last error that occurred.

FUNCTION GetLastError(source OUT VARCHAR2, description OUT VARCHAR2, helpfile OUT VARCHAR2, helpid OUT BINARY_INTEGER) RETURN BINARY_INTEGER;

Each call to an Oracle COM Automation Feature API (except GetLastError) resets the error information, so that GetLastError obtains error information only for the most recent Oracle COM Automation Feature API call. Because GetLastError does not reset the last error information, it can be called multiple times to get the same error information.

This procedure returns a 0 when successful or a nonzero HRESULT when an error occurs.

See "Microsoft COM Automation Errors" for a description of the types of errors that can be returned by this function.

HRESULT BINARY_INTEGER;
applicationToken BINARY_INTEGER:=-1;
error_src VARCHAR2(255);
error_description VARCHAR2(255);
error_helpfile VARCHAR2(255);
error_helpID BINARY_INTEGER;

HRESULT:=ORDCOM.CreateObject('Excel.Application', 0, '', applicationToken);
IF (HRESULT!=0) THEN
  ORDCOM.GetLastError(error_src, error_description, error_helpfile, 
      error_helpID);
  dbms_output.put_line(error_src);
  dbms_output.put_line(error_description);

  dbms_output.put_line(error_helpfile);
END IF;

GetProperty

Gets a property value of a COM Automation object.

FUNCTION GetProperty(objecttoken BINARY_INTEGER, propertyname VARCHAR2, argcount BINARY_INTEGER, propertyvalue OUT any_PL/SQL_datatype) RETURN BINARY_INTEGER;

If the property returns a COM object, you must specify a local variable of datatype BINARY_INTEGER for the propertyvalue parameter. An object token is stored in the local variable, and this object token can be used with other COM Automation stored procedures.

When the property returns an array, if propertyvalue is specified, it is set to NULL.

This procedure returns a 0 when successful or a nonzero HRESULT when an error occurs.

/*
 * This is an excerpt from a Microsoft Excel application.
 */

HRESULT BINARY_INTEGER;
ChartObject BINARY_INTEGER := -1;
ChartToken BINARY_INTEGER := -1;

/* Assume ChartObject is initialized. */

HRESULT := ORDCOM.GetProperty(ChartObject, 'Chart', 0, ChartToken);
IF (HRESULT!=0) THEN    
  dbms_output.put_line(HRESULT);
END IF;

SetProperty

Sets a property of a COM Automation object to a new value.

FUNCTION SetProperty(objecttoken BINARY_INTEGER, propertyname VARCHAR2, newvalue any_PL/SQL_datatype, datatype VARCHAR2) RETURN BINARY_INTEGER;

This procedure returns a 0 when successful or a nonzero HRESULT when an error occurs.

/*
 * This is an excerpt from a Microsoft Excel application.
 */

HRESULT BINARY_INTEGER;
RangeToken BINARY_INTEGER := -1;

/* Assume RangeToken is initialized. */

HRESULT := ORDCOM.SetProperty(RangeToken, 'Value', 'EmpNo', 'BSTR');
IF (HRESULT!=0) THEN    
  dbms_output.put_line(HRESULT);
END IF;

InitArg

Initializes the parameter set passed to an Invoke call.

PROCEDURE InitArg();

The InitArg call initializes the parameter set. After InitArg has been called, a SetArg call sets the first parameter to the specified value. A second SetArg call sets the second parameter in the parameter list. Subsequent calls set the nth parameters in the parameter list, where n is the number of times SetArg has been called after an InitArg call. Another call to InitArg resets the argument list and a call to SetArg sets the first parameter again.

See "Invoke" for sample code.

InitOutArg

InitOutArg must be called after a COM method is invoked in preparation for getting the values of OUT and IN OUT parameters using GetArg. After calling InitOutArg, the first call to GetArg gets the value for the first OUT or IN OUT parameter, the second call to GetArg gets the value for the second OUT or IN OUT parameters, and so on. Calling InitOutArg again restarts this process.

PROCEDURE InitOutArg();

See the section on SetArg datatype strings in "SetArg " for information about IN and OUT parameters.

See "Invoke" for sample code.

GetArg

Gets the argument of OUT and IN OUT parameters after the COM method has been invoked.

PROCEDURE GetArg(data OUT any_PL/SQL_datatype, type VARCHAR2);

See the section on SetArg datatype strings in "SetArg " for information about IN and OUT parameters.

See "Invoke" for sample code.

SetArg

Used to construct the parameter list for the next Invoke call.

SetArg sets a parameter's value to be passed by value.

PROCEDURE SetArg(paramvalue any_PL/SQL_datatype, datatype VARCHAR2);

Each SetArg procedure sets the nth parameter value. The InitArg call initializes the parameter set. After InitArg has been called, a SetArg call sets the first parameter to the specified value. A second SetArg call sets the second parameter in the parameter list. Subsequent calls set the nth parameters in the parameter list, where n is the number of times SetArg has been called after an InitArg call. Another call to InitArg resets the argument list and a call to SetArg sets the first parameter again.

Datatypes without the initial p are IN parameters. Those datatypes prefaced by an initial p are IN OUT or OUT parameters.

See "Invoke" for sample code.

Invoke

Calls a method of a COM Automation object. This function uses the parameter list, previously created by the calls to InitArg and SetArg as input for the COM Automation method.

FUNCTION Invoke(objecttoken BINARY_INTEGER, methodname VARCHAR2, argcount BINARY_INTEGER, returnvalue OUT any_PL/SQL_datatype) RETURN BINARY_INTEGER;

If the method's return value is a COM object, then the developer must specify a local variable of datatype BINARY_INTEGER for the returnvalue parameter. An object token is stored in the local variable, and this object token can be used with other Oracle COM Automation Feature APIs.

This procedure returns a 0 when successful or a nonzero HRESULT when an error occurs.

/*
* Following is the IDL definition of the COM Automation method
* being called:
*
* HRESULT TestOutArg([in, out] short *x1,
* [in] short x2,
* [out] short *x3,
* [out, retval] short *x4);
*/

HRESULT BINARY_INTEGER := -1;
applicationToken BINARY_INTEGER := -1;
x1 DOUBLE PRECISION := 12;
x2 DOUBLE PRECISION := 7;
x3 DOUBLE PRECISION := 0;
x4 DOUBLE PRECISION := 0;

/* Assume applicationToken is initialized. */

ORDCOM.InitArg();
ORDCOM.SetArg(x1, 'pI2');
ORDCOM.SetArg(x2, 'I2');
ORDCOM.SetArg(x3, 'pI2');

HRESULT := ORDCOM.Invoke(applicationToken, 'TestOutArg', 3, x4);
IF (HRESULT!=0) THEN    
  dbms_output.put_line(HRESULT);
END IF;

ORDCOM.InitOutArg();
ORDCOM.GetArg(x1, 'pI2');
ORDCOM.GetArg(x3, 'pI2');

Automation Constructor

Creates a COM object.

public Automation()
        public Automation(String progID)
        public Automation(String progID, String serverName)

The default constructor public Automation() does nothing. It is used with a Create method.

Using the constructor that takes only the progid parameter forces Oracle COM Automation Feature to check the registry for the location of the COM object. Registry information indicates whether the COM object is local or remote.

COM Automation objects created using the nondefault constructors are freed with a corresponding call to Destroy. This nullifies the internal representation of the objects in the Oracle COM Automation Feature and releases all the interfaces associated with the objects.

Oracle COM Automation Feature for Java does not allow in-process COM Automation servers. Developers can use dllhost to support in-process servers.

The exception COMException is thrown if an error occurs.

The following code sample demonstrates the nondefault constructors.

 // Use the registry to determine where to create the COM object.
   Automation word = new Automation("Word.Basic");

   // Create the COM object on the specified server machine.
   Automation excel = new Automation("Excel.Application", 
                                     "\\ServerMachineName");

   // Free the COM objects.
   word.Destroy();
   excel.Destroy();

Create

Instantiates a COM object in a COM Automation server.

public void Create(String progID)
public void Create(String progID, String serverName)

The COM Automation object created with the Create method is freed with a corresponding call to Destroy. This nullifies the internal representation of the object in the Oracle COM Automation Feature and releases all the interfaces associated with the object.

Using the constructor that takes only the progid parameter forces Oracle COM Automation Feature to check the registry for the location of the COM object. Registry information indicates whether the COM object is local or remote.

Oracle COM Automation Feature for Java does not allow in-process COM Automation servers. Developers can use dllhost to support in-process servers.

The exception COMException is thrown if an error occurs.

// Use the default constructor.
   Automation word = new Automation();
   Automation excel = new Automation();

   // Use the registry to determine where to create the COM object.
   word.Create("Word.Basic");

   // Create the COM object on the specified server machine.
   excel.Create("Excel.Application", "\\ServerMachineName");

   // Free the COM objects.
   word.Destroy();
   excel.Destroy();

Destroy

Destroys a created COM Automation object.

public void Destroy()

Calling Destroy nullifies the internal representation of the object in the Oracle COM Automation Feature and releases all the interfaces associated with the object.

See "Create" for code sample.

GetProperty

Gets a property value of a COM Automation object.

public allowed_type GetProperty(String propName, allowed_type[] propVal)

If the property is a COM object, it can be retrieved using the allowed_type of oracle.win.com.Automation. The Automation Java object that is returned can be used to get and set properties and call methods on the property.

GetProperty uses an array parameter to return the property value in order to overload the GetProperty method. Overloading would not be possible if the property value were simply returned as a return value. The array solves the problem caused by Java not having an out parameter.

The property is still returned as a return value for convenience.

The exception, COMException, is thrown if an error occurs.

// A Microsoft Excel ChartObject object.
   Automation chartObject = null;
   // A Microsoft Excel Chart object.
   Automation chart = null;
   // Used for properties of type Automation.
   Automation[] autoProp = { null };

   // Assume the Microsoft Excel ChartObject object is initialized.

   // Get the Chart property.
   chartObject.GetProperty("Chart", autoProp);
   chart = autoProp[0];

   // Set the Chart property.
   chartObject.SetProperty("Chart", chart);

SetProperty

Sets a property of a COM Automation object to a new value.

public void SetProperty(String propName, allowed_type propVal)

If the property is a COM object, it can be set using the allowed type of oracle.win.com.Automation. The property value must be a valid Automation Java object.

The exception, COMException, is thrown if an error occurs.

See "GetProperty" for sample code.

InitArg

Initializes the parameter set passed to an Invoke call.

public void InitArg()

The InitArg call initializes the parameter set and must be called even if the COM method does not take any parameters. After InitArg has been called, a SetArg call sets the first parameter to the specified value. A second SetArg call sets the second parameter in the parameter list. Subsequent calls set the nth parameters in the parameter list, where n is the number of times SetArg has been called after an InitArg call. Another call to InitArg resets the argument list and a call to SetArg sets the first parameter again.

See "Invoke" for sample code.

SetArg

Used to construct the parameter list for the next Invoke call.

public void SetArg(allowed_type val)

If a parameter is a COM object, then the allowed_type of the corresponding argument should be oracle.win.com.Automation. The argument should be a valid Automation Java object.

No exceptions are thrown at this time. However, if an error occurs, for example, if the wrong argument type is passed, then it will be caught when the Invoke method is called.

See "Invoke" for sample code.

Invoke

Calls a method of a COM Automation object. This function uses the parameter list, previously created by the calls to InitArg and SetArg, as input for the COM Automation method.

public void Invoke(String methodName, allowed_type[] retVal)
public void Invoke(String methodName)

If the COM method returns a COM object as the return value, the allowed_type of the return value is oracle.win.com.Automation. The Automation Java object that is returned can be used to get and set properties, and call methods on the return value.

In order to overload the Invoke method, Invoke uses an array parameter to return the values of COM object methods. Overloading would not be possible if the property value was simply returned as a return value. The array solves the problem caused by Java not having an out parameter.

The version of Invoke that takes only one parameter, public void Invoke(String methodName), is used for COM object methods with void return types.

The property is still returned as a return value for convenience.

The exception COMException is thrown if an error occurs.

// A Microsoft Excel Worksheet object.
   Automation workSheet = null;
   // A Microsoft Excel ChartObjects collection object.
   Automation chartObjects = null;
   // A Microsoft Excel ChartObject object.
   Automation chartObject = null;
   // Used for return values of type Automation.
   Automation[] autorv = { null };
   // Dimensions for a Microsoft Excel ChartObject object.
   short xpos = 100, ypos = 30, width = 400, height = 250;

   // Assume the Microsoft Excel Worksheet object is initialized.

   // Invoke a method which takes no arguments.
   workSheet.InitArg();
   workSheet.Invoke("ChartObjects", autorv);
   chartObjects = autorv[0];
   
   // Invoke a method which takes multiple arguments.
   chartObjects.InitArg();
   chartObjects.SetArg(xpos);
   chartObjects.SetArg(ypos);
   chartObjects.SetArg(width);
   chartObjects.SetArg(height);
   chartObjects.Invoke("Add", autorv);
   chartObject = autorv[0];

Currency Constructor

Creates a currency Java object.

public Currency(long value)

Get

Gets the 8-byte CURRENCY number.

public long Get()

Returns the 8-byte CURRENCY number.

Set

Sets the 8-byte CURRENCY number.

public void Set(long value)