This two-part article provides a primer on OLE, some practical examples of its use, and demonstrates some methods for addressing the limitations of PowerBuilder's implementation of OLE.
In Part 1 I provided some background information for OLE and discussed the use of custom controls, in Part 2 I talk about OLE Automation and OLE objects.
OLE Automation is the interface through which one application (e.g., Microsoft Outlook) makes the methods, properties, and events of its objects (e.g., Folders, Messages, Address Book) available for use within another application. Using OLE Automation, a developer could automate another application (e.g., creating and then printing form letters from Microsoft Word) or they may just use a portion of another application within their own (e.g., using the spell-check capability within Microsoft Word to spell-check text in a control within PowerBuilder).
OLE Automation defines an application programming interface (API) for an application. Unlike something like MAPI, however, that was designed to be a common API across a number of different mail clients, the OLE Automation syntax for one application is unique to that particular application, and in fact can differ between versions of that same application. That is, while the method of OLE Automation provides a standard method through which applications can interact, the specific syntax they use to do that interaction is application-specific.
This results in two important consequences. First, because the syntax is defined by the target application, a developer faces a task whose difficulty ranges from difficult to impossible, if they are attempting to implement OLE Automation without proper documentation for the objects and their methods, properties, and events of the target application. There are object browsers available that can help the developer explore the OLE Automation interface of the target application.
Note: One is provided with Microsoft Visual Basic. The Roman Press provides a third-party alternative (http://www.sroman.com/Software/Browser/ObjectBrowser.php).
However, using those alone would be equivalent to trying to learn how to develop in PowerBuilder without using the online help file or documentation, simply by exploring the objects in the system tree. Second, because the syntax for the target application can vary between versions, it's not enough for the developer to ensure that the client has the target application installed. They must also ensure that the version of that application is one for which they have provided an implementation (or compatible with those versions).
Because they generate by far the most common questions concerning OLE Automation, I'm going to use Microsoft Word, Excel, and Outlook for demonstrations of how to use OLE Automation. The first issue raised above is documentation. What many PowerBuilder developers don't seem to realize is that Microsoft provides documentation (referred to as the "object model") for these Office applications. The reason they often don't realize that documentation is readily available is because it's not installed by default but instead requires a custom installation of Microsoft Office. The documentation is provided as a set of help files as indicated in Table 1.
The VBA scripting utility that comes with those products also contains an object browser that can be used to determine which functions are accessible on different objects and the syntax for using them (see Figure 1). You access the VBA scripting utility from Tools->Macro->Visual Basic Editor within the application, and then the object browser is available from the toolbar, from the View menu item, or by hitting F2. From within the VBA Scripting utility you can bring up the documentation for a particular method, property, or event by hitting F1 after selecting it (assuming that the help files have been installed).
The second issue raised above is the compatibility between versions of the same target application. Fortunately, at least with the Microsoft products, there is a fairly easy way to determine which version of the target application is installed. (This will be demonstrated once we get into the source code.) In addition, the OLE Automation syntax for these applications is highly backward-compatible. When objects (or methods, properties, or events of an object) are depreciated by Microsoft in a new version of the application, they usually mark those as "hidden." Hidden objects (or methods, properties, or events) still work with the newer version, but they don't appear in the object model for that new version. Obviously, because each new version of the application adds additional features that were not present in earlier versions, the syntax developed using a newer version of the object model will not always work for an older version.
As mentioned previously, PowerBuilder uses late binding to interface with OLE Automation objects, so we are unable to use the constants provided in the Word type library. However, Microsoft does provide an Excel spreadsheet on their MSDN site that contains all of the constants and their values used within Office. (Those constants are also defined in the Microsoft supplied help files and can be browsed using the VBA scripting utility.) I downloaded that file and used that information to create an autoinstantiating nonvisual user object that could be used for a similar purpose as the type library, at least with respect to the constants (see Figure 2).
The n_cst_constants_word_ole class contains the constants and is defined as an instance variable on another class, n_cst_ole_word. That class is basically a "virtual" class; it's never actually called directly. Instead, descendants of it are created to handle each of the different versions of Word we might actually interact with. The virtual class determines which methods we are going to make available, and the descendants actually implement those methods as appropriate. The virtual class returns an error message if the method is called, so if the method is not implemented on a descendant, it implies that particular version of Word does not support the method. This allows us to add additional methods to the virtual class as new versions of Word, and add new functions and implement them in whichever descendants actually support them without having to modify the classes representing earlier versions of Word. n_cst_word acts as a wrapper class so that the instantiation of the appropriate descendant of n_cst_ole_word and calls to its methods are handled automatically.
In the of_connect method of n_cst_word, the object determines which version of Word the user has installed and creates the appropriate descendant of n_cst_ole_word (see Listing 1).
Prior to version 8 of PowerBuilder, the standard method for handling errors in OLE Automation was to create a user object that inherited from the OLEObject class and then code its Error and ExternalException events. Then that class, rather than the OLEObject class, would be used for the ConnectToObject or ConnectToNewObject call. One problem with that approach is that some OLE Automation properties and method returns are themselves OLEObjects, and would not allow themselves to be cast into the descendant user object. Instead, a user object based on OLEObject would have to be used, and then the SetAutomationPointer function of that object would be used to redirect processing to an instance of the custom class.
That was all very complicated. It also tended to disassociate the error-handling logic from the code that was making the call to the object that caused the error, which made recovery from the error somewhat problematic.
Fortunately version 8 of PowerBuilder introduced structured exception handling, which greatly simplifies this for us. The object that we are using in this sample to interact with Word is the inv_word instance variable in the n_cst_word class, and you can see from Figure 3 that the class we are using for it is OLEObject. We simply wrap our OLE Automation calls in a try...catch...end try block and can deal with any errors that occur within that block.
We're going to review some of the code from the sample application (which can be downloaded from pbdj.sys-con.com), certainly not all of it, just certain code that illustrates some important concepts.
The of_visible function determines whether or not Word will actually be visible to the end user. It's fairly straightforward, simply setting the visible property of the Word application (not a PowerBuilder object visible property).
public function integer of_visible (boolean ab_status);
IF IsNull ( inv_word ) THEN Return -1
inv_word.Visible = ab_status
catch (OLERuntimeError MyOLEError )
The of_open function demonstrates the use of multiple catch portions of the try...catch...end try block to handle different types of errors.
public function integer of_open (string as_filename);
IF IsNull ( inv_word ) THEN Return -1
inv_word.Documents.open ( as_filename )
catch (OLERuntimeError MyOLEError )
catch (NullObjectError MyNullError )
The of_close function shows how the constant's instance variable is used to pass arguments to functions of the OLE object.
public function integer of_close ();
catch (OLERuntimeError MyOLEError )
PowerBuilder does not support skipping "optional" arguments in OLE Automation function calls. For example, the Word Document object Close function used above takes three optional arguments:
Since all the arguments are optional, we could have called it without passing any arguments. However, once we start passing arguments, we have to pass all the arguments through the last optional one we wish to pass. That is, we could pass the SaveChanges argument by itself, but we couldn't pass that OriginalFormat argument by itself. Instead, we would have to pass something for the SaveChanges argument as well as the OriginalFormat argument (as we have done in the sample). This may not come as much of a surprise to many PowerBuilder developers unless they have some Visual Basic background, because that development tool does support skipping optional arguments. PowerBuilder also does not support the use of named notation rather than positional notation for arguments.
The of_printpreview demonstrates the use of the AsStatement! keyword. The Word ActiveDocument PrintPreview method doesn't take any arguments and doesn't have a return value, which makes it look like a property. What's more, there is a Word PrintPreview property as well. Therefore, we need to use the AsStatement! to ensure that PowerBuilder knows we are attempting to call a function rather than refer to a property.
public function integer of_printpreview ();
IF IsNull ( inv_word ) THEN Return -1
ll_type = inv_word.ActiveWindow.View.Type
IF ll_type = inv_const.wdPrintPreview THEN
inv_word.ActiveDocument.PrintPreview( AsStatement! )
catch ( OLERuntimeError MyOLEError )
So far the method we've used to interact with OLE Automation methods and properties is no different from the method we used with OLE Custom Controls. The similarity ends when we come to handling events though. While PowerBuilder exposes the events fired by the control within the IDE, it doesn't do so for OLE Automation. For that matter, few programming languages do, and many OLE Automation targets fire few - if any - events.
Nonetheless, there is a mechanism for capturing OLE Automation events (see Figure 4), which is explained in some detail in Microsoft Knowledge Base article 183599. Basically, it's necessary to create another COM object referred to as an "eventsink" that implements the same events as the OLE Automation object you want to capture events from. That eventsink object is then registered with the OLE Automation application's "ConnectionPoint". Now when the OLE Automation object fires an event, that same event fires on the eventsink. Since the eventsink is an object we created, we can have it respond to the event.
We could have implemented this prior to PowerBuilder 9 by creating a separate COM object in PowerBuilder or by writing a custom DLL in C++, either of which would then relay the event notifications on to our PowerBuilder application. However, the introduction of PBNI in PowerBuilder 9 has simplified things for us considerably. Essentially it allows us to create objects in C++ and then use them in PowerBuilder as if they were native PowerBuilder objects. PBNI objects can in fact inherit from PowerBuilder objects, and PowerBuilder can create objects inherited from PBNI objects.
OLEStream and OLEStorage
OLE objects store data internally in objects referred to as "streams," which are contained within other objects called "storages." Conceptually, you could think of these as similar to files and directories for storage of data on your hard drive. As a directory can contain a number of different files and even other directories, a storage can contain one or more different streams and even other storages. In addition, just as a directory can contain files that were created from different applications, a storage can contain streams that were created from different OLE objects.
The PowerBuilder OLEStream and OLEStorage classes map to the stream and storage objects and provide the means for accessing the data in them (see Figure 5). Generally you should use the OLE Custom Control or OLE Automation application that created the data to manipulate that data. Accessing the data directly using the OLEStream and OLEStorage classes has somewhat limited capabilities but you may find it useful in special circumstances. For example, you may find it to be particularly useful if your target database doesn't handle BLOBs, so the OLE data information used in your application could not be stored in the database, or if some other factor required an approach other than storage in the database.
One interesting item is that the visible aspects of the OLE data are actually stored with the OLE data. Therefore, you can insert the data into an appropriate control on a PowerBuilder application and it would display properly without having to start the related application. The application would only need to be started if the user attempted to activate the control.
In the sample application provided in the source code (based largely on the sample provided in the PowerBuilder documentation), one PowerBuilder application is used to read a number of image files into a single OLE storage object, which is then saved as a file. Another PowerBuilder application then reads those images out of the OLE storage file and inserts them back into another image control.
The main advantage of this technique - other than being able to store image files used by the application outside of the database - is performance. The single file acts like a database of its own. The PowerBuilder application reads it once and then simply pulls out the images as they're referenced. If the images were stored in separate records in the database or as separate files on a file server, PowerBuilder would have to read each individually, which could impact performance considerably.
OLE provides a very powerful way to extend the capabilities of PowerBuilder either through the use of third-party controls or by allowing PowerBuilder to interact with other applications.
. . .
This article is based on PowerBuilder 9 Internet and Distributed Application Development by various authors (ISBN 0672324997), published by Sams Publishing.
--This article was originally published on PBDJ.