OLE objects in scripts

This chapter has described the three ways to use OLE in a
window or user object. You have learned about:

• Inserting an object in an OLE control
• Placing an ActiveX control in an OLE custom control
• Declaring an OLEObject variable and connecting to
  an OLE object

In scripts, you can manipulate these objects by means of OLE automation, getting
and setting properties, and calling functions that are defined by
the OLE server. There are examples of automation commands in the
preceding sections. This section provides more information about
the automation interface in PowerBuilder.

The automation interface

In PowerBuilder, an OLEObject is your interface to an OLE
server or ActiveX control. When you declare an OLEObject variable
and connect to a server, you can use dot notation for that variable
and send instructions to the server. The instruction might be a
property whose value you want to get or set, or a function you want
to call.

The general automation syntax for an OLEObject is:

For OLE controls in a window, your interface to the server
or ActiveX control is the control’s Object property, which
has a datatype of OLEObject.

The general automation syntax for an OLE control is:

Compiling scripts that include commands to the OLE
server When you compile scripts that apply methods to an OLEObject
(including a control’s Object property), PowerBuilder does
not check the syntax of the rest of the command, because it does
not know the server’s command set. You must ensure that
the syntax is correct to avoid errors during execution.

Make sure you give your applications a test run to ensure
that your commands to the server application are correct.

What does the server support?

A server’s command set includes properties and methods
(functions and events).

OLE server applications publish the command set they support
for automation. Check your server application’s documentation
for information.

For custom controls and programmable OLE objects,
you can see a list of properties and methods in the PowerBuilder
Browser. For more information about OLE information in the Browser,
see “OLE information in the Browser “.

Setting properties

You access server properties for an OLE control through its
Object property using the following syntax:

If the OLE object is complex, there could be nested objects
or properties within the
object that serve as qualifiers for the property name.

For example, the following commands for an Excel spreadsheet
object activate the object and set the value property of several
cells:

For an Excel 95 spreadsheet, enclose the cells’ row
and column arguments in parentheses instead of square brackets.
For example:

For properties of an OLEObject variable, the server qualifiers
and property name follow the variable name:

The qualifiers you need to specify depend on how you connect
to the object. For more information, see “Qualifying server
commands”.

Calling functions

You can call server functions for an OLE control through its
Object property using the following syntax:

If the OLE object is complex, there could be nested properties
or objects within the object that serve as qualifiers for the function
name.

Required parentheses PowerScript considers all commands to the server either property
settings or functions. For statements and functions to be distinguished
from property settings, they must be followed by parentheses surrounding
the parameters. If there are no parameters, specify empty parentheses.

Arguments and return values and their datatypes

PowerBuilder converts OLE data to and from compatible PowerBuilder datatypes.
The datatypes of values you specify for arguments must be compatible
with the datatypes expected by the server, but they do not need
to be an exact match.

When the function returns a value, you can assign the value
to a PowerBuilder variable of a compatible datatype.

Passing arguments by reference

If an OLE server expects an argument to be passed by reference
so that it can pass a value back to your script, include the keyword REF just
before the argument. This is similar to the use of REF in
an external function declaration:

In these generic examples, the server can change the values
of ls_string and li_return because
they are passed by reference:

This example illustrates the same function call using an OLEObject
variable.

Setting the timeout period Calls from a PowerBuilder client to a server time out after
five minutes. You can use the SetAutomationTimeout PowerScript
function to change the default timeout period if you expect a specific
OLE request to take longer.

Word and automation

Microsoft Word 6.0 and 7.0 support automation with a command
set similar to the WordBasic macro language. The command set includes
both statements and functions and uses named parameters. Later versions
of Microsoft Word use Visual Basic for Applications (VBA), which
consists of a hierarchy of objects that expose a specific set of
methods and properties.

WordBasic statements WordBasic has both statements and functions. Some of them
have the same name. WordBasic syntax differentiates between statements
and functions calls, but PowerBuilder does not.

To specify that you want to call a statement, you can include AsStatement! (a value
of the OLEFunctionCallType enumerated datatype)
as an argument. Using AsStatement! is the only
way to call WordBasic statements that have the same name as a function.
Even when the statement name does not conflict with a function name,
specifying AsStatement! is more efficient:

For example, the following code calls the AppMinimize statement:

Named parameters PowerBuilder does not support named parameters that both WordBasic
and Visual Basic use. In the parentheses, specify the parameter values
without the parameter names.

For example, the following statements insert text at a bookmark
in a Word 6.0 or 7.0 document:

The last two commands in a WordBasic macro would look like
this, where Destination is the named parameter:

In a PowerBuilder script, you would use this syntax to insert
text in a Word 97 or later document:

In the corresponding Visual Basic statement, the named parameter
Text contains the string to be inserted:

Automation is not macro programming You cannot send commands to the server application that declare
variables or control the flow of execution (for example, IF
THEN). Automation executes one command at a time independently
of any other commands. Use PowerScript’s conditional and
looping statements to control program flow.

Example of Word automation To illustrate how to combine PowerScript with server commands,
the following script counts the number of bookmarks in a Microsoft
Word OLE object and displays their names:

Word automation tip You can check that you are using the correct syntax for Word
automation with the Word macro editor. Turn on macro recording in
Word, perform the steps you want to automate manually, then turn
off macro recording. You can then type Alt+F11 to open
the macro editor and see the syntax that was built. Remember that
PowerBuilder uses square brackets for array indexes.

Example of Word 6.0 and 7.0 automation The following script counts the number of bookmarks in a Microsoft
Word 6.0 or 7.0 OLE object and displays their names:

Qualifying server commands

Whether to qualify the server command with the name of the
application depends on the server and how the object is connected.
Each server implements its own version of an object hierarchy, which
needs to be reflected in the command syntax. For example, the Microsoft
Excel object hierarchy is shown in Figure 19-2.

Figure 19-2: Microsoft Excel object hierarchy

When the server is Excel, the following commands appear to
mean the same thing but can have different effects (for an Excel
95 spreadsheet, the cells’ row and column arguments are
in parentheses instead of square brackets):

The first statement changes a cell in the active document.
It moves up Excel’s object hierarchy to the Application
object and back down to an open sheet. It does not matter whether
it is the same one in the PowerBuilder control. If the user switches
to Excel and activates a different sheet, the script changes that one
instead. You should avoid this syntax.

The second statement affects only the document in the PowerBuilder
control. However, it will cause a runtime error if the document
has not been activated. It is the safer syntax to use, because there
is no danger of affecting the wrong data.

Microsoft Word 6.0 and 7.0 implement the application hierarchy
differently and require the qualifier application.wordbasic when
you are manipulating an object in a control. (You must activate
the object.) For example:

Later versions of Microsoft Word do not require a qualifier,
but it is valid to specify one. You can use any of the following
syntaxes:

When you are working with PowerBuilder’s OLEObject,
rather than an object in a control, you omit the application qualifiers
in the commands because you have already specified them when you
connected to the object. (For more about the OLEObject object type,
see “Programmable OLE Objects “.)

Automation and the Any datatype

Because PowerBuilder knows nothing about the commands and
functions of the server application, it also knows nothing about
the datatypes of returned information when it compiles a program.
Expressions that access properties and call functions have a datatype
of Any. You can assign the expression to an Any variable,
which avoids datatype conversion errors.

During execution, when data is assigned to the variable, it
temporarily takes the datatype of the value. You can use the ClassName function
to determine the datatype of the Any variable
and make appropriate assignments. If you make an incompatible assignment
with mismatched datatypes, you will get a runtime error.

Do not use the Any datatype unnecessarily If you know the datatype of data returned by a server automation
function, do not use the Any datatype. You
can assign returned data directly to a variable of the correct type.

The following sample code retrieves a value from Excel and
assigns it to the appropriate PowerBuilder variable, depending on
the value’s datatype. (For an Excel 95 spreadsheet, the
row and column arguments for cells are in parentheses instead of
square brackets.)

OLEObjects for efficiency

When your automation command refers to a deeply nested object
with multiple server qualifiers, it takes time to negotiate the
object’s hierarchy and resolve the object reference. If
you refer to the same part of the object hierarchy repeatedly, then
for efficiency you can assign that part of the object reference to
an OLEObject variable. The reference is resolved once and reused.

Instead of coding repeatedly for different properties:

you can define an OLEObject variable to handle all the qualifiers:

Example: resolving an object reference

This example uses an OLEObject variable to refer to a Microsoft
Word object. Because it is referred to repeatedly in a FOR loop,
the resolved OLEObject makes the code more efficient. The example
destroys the OLEObject variable when it is done with it:

Handling errors

Statements in scripts that refer to the OLE server’s
properties are not checked in the compiler because PowerBuilder
does not know what syntax the server expects. Because the compiler
cannot catch errors, runtime errors can occur when you specify property
or function names and arguments the OLE server does not recognize.

Chain of error events

When an error occurs that is generated by a call to an OLE
server, PowerBuilder follows this sequence of events:

1. If the error was generated
   by an ActiveX control that has defined a stock error event, the
   ocx_error event for the PowerBuilder OLE control is triggered.
2. Otherwise, the ExternalException event for the OLE
   object occurs.
3. If the ExternalException event has no script or
   its action argument is set to ExceptionFail! (the
   default), the Error event for the OLE object occurs.
4. If the Error event has no script or its action argument
   is set to ExceptionFail! (the default), any active
   exception handler for a RuntimeError or its descendants is invoked.
5. If no exception handler exists, or if the existing
   exception handlers do not handle the exception, the SystemError
   event for the Application object occurs.
6. If the SystemError has no script, an application
   runtime error occurs and the application is terminated.

You can handle the error in any of these events or in a script
using a TRY-CATCH block. However, it is not a good idea
to continue processing after the SystemError event occurs.

For more information about exception handling, see “Handling exceptions”.

Events for OLE errors

PowerBuilder OLE objects and controls all have two events
for error handling:

• ExternalException Triggered when the OLE server or control throws an exception
  or fires an error event (if there is no ocx_error event). Information
  provided by the server can help diagnose the error.
• Error Triggered when the exception or error event is not handled. PowerBuilder
  error information is available in the script.

If the OLE control defines a stock error event, the PowerBuilder
OLE control container has an additional event:

• ocx_error Triggered when the OLE server fires an error event. Information
  provided by the server can help diagnose the error.

The creator of an OLE control can generate the stock error
event for the control using the Microsoft Foundation Classes (MFC)
Class Wizard. The arguments for the ocx_error event in
PowerBuilder map to the arguments defined for the stock error event.

Responding to the error

If the PowerBuilder OLE control has an ocx_error
event script, you can get information about the error from the event’s
arguments and take appropriate action. One of the arguments of ocx_error
is the boolean CancelDisplay. You can set CancelDisplay to TRUE to
cancel the display of any MFC error message. You can also supply
a different description for the error.

In either the ExternalException or Error event script, you
set the Action argument to an ExceptionAction enumerated value.
What you choose depends on what you know about the error and how
well the application will handle missing information.

Table 19-6: ExceptionAction enumerated values

ExceptionAction value	Effect
ExceptionFail!	Fail as if the event had no script. Failing triggers the next error event in the order of event handling.
ExceptionIgnore!	Ignore the error and return as if no error occurred. Caution If you are getting a property value or expecting a return value from a function, a second error can occur during the assignment because of mismatched datatypes.
ExceptionRetry!	Send the command to the OLE server again (useful if the OLE server was not ready). Caution If you keep retrying and the failure is caused by an incorrect name or argument, you will set your program into an endless loop. You can set up a counter to limit the number of retries.
ExceptionSubstituteReturnValue!	Use the value specified in the ReturnValue argument instead of the value returned by the OLE server (if any) and ignore the error condition. You can set up an acceptable return value in an instance variable before you address the OLE server and assign it to the ReturnValue argument in the event script. The datatype of ReturnValue is Any, which accommodates all possible datatypes. With a valid substitute value, this choice is a safe one if you want to continue the application after the error occurs.

Example: ExternalException event

The ExternalException event, like the ocx_error event,
provides error information from the OLE server that can be useful
when you are debugging your application.

Suppose your window has two instance variables: one for specifying
the exception action and another of type Any for storing a potential
substitute value. Before accessing the OLE property, a script sets
the instance variables to appropriate values:

If the command fails, a script for the ExternalException event
displays the Help topic named by the OLE server, if any. It substitutes
the return value you prepared and returns. The assignment of the
substitute value to li_currentsetting works
correctly because their datatypes are compatible:

Because the event script must serve for every automation command
for the control, you would need to set the instance variables to
appropriate values before each automation command.

Error event

The Error event provides information about the PowerBuilder
context for the error. You can find out the PowerBuilder error number
and message, as well as the object, script, and line number of the
error. This information is useful when debugging your application.

The same principles discussed in the ExceptionAction value
table for setting the Action and ReturnValue arguments apply to
the Error event, as well as ExternalException.

For more information about the events for error handling,
see the PowerScript Reference
.

Creating hot links

Some OLE servers support property change notifications. This
means that when a property is about to be changed and again after
it has been changed, the server notifies the client, passing information
about the change. These messages trigger the events PropertyRequestEdit
and PropertyChanged.

PropertyRequestEdit event

When a property is about to change, PowerBuilder triggers
the PropertyRequestEdit event. In that event’s script you
can:

• Find out the name of the property
  being changed by looking at the PropertyName argument.
• Obtain the old property value and save it
  The property still has its old value, so you can use the standard
  syntax to access the value.
• Cancel the change by changing the value of the CancelChange
  argument to TRUE

PropertyChanged event

When a property has changed, PowerBuilder triggers the PropertyChanged event.
In that event’s script, you can:

• Find out the name of the property being changed by looking
  at the PropertyName argument
• Obtain the new property value
  The value has already changed, so you cannot cancel
  the change.

Using the PropertyName argument

Because the PropertyName argument is a string, you cannot use
it in dot notation to get the value of the property:

Instead, use CHOOSE CASE or IF statements
for the property names that need special handling.

For example, in the PropertyChanged event, this code checks
for three specific properties and gets their new value when they
are the property that changed. The value is assigned to a variable
of the appropriate datatype:

If a larger change occurred

In some cases the value of the PropertyName argument is an
empty string (“”). This means a more general change has occurred—for
example, a change that affects several properties.

If notification is not supported

If the OLE server does not support property change notification,
then the PropertyRequestEdit and PropertyChanged events are never
triggered, and scripts you write for them will not run. Check your
OLE server documentation to see if notification is supported.

If notifications are not supported and your application needs
to know about a new property value, you might write your own function
that checks the property periodically.

For more information about the PropertyRequestEdit
and PropertyChanged events, see the PowerScript Reference
.

Setting the language for OLE objects and controls

When you write automation commands, you generally use commands
that match the locale for your computer. If your locale and your
users’ locale will differ, you can specify the language
you have used for automation with the SetAutomationLocale function.

You can call SetAutomationLocale for OLE
controls, custom controls, and OLEObjects, and you can specify a
different locale for each automation object in your application.

For example, if you are developing your application in Germany
and will deploy it all over Europe, you can specify the automation
language is German. Use this syntax for an OLE control called ole_1:

Use this syntax for an OLEObject called oleobj_report:

The users of your application must have the German automation
interface for the OLE server application.

What languages do your users’ computers support? When your users install an OLE server application (particularly
an OLE application from Microsoft), they get an automation interface
in their native language and in English. It might not be appropriate
for you to write automation commands in your native language if
your users have a different language.

For more information, see the SetAutomationLocale function
in the PowerScript Reference
.

Low-level access to the OLE object

If you need low-level access to OLE through a C or C++ DLL
that you call from PowerBuilder, you can use these functions:

• GetNativePointer (for
  OLEControl and OLECustomControl)
• GetAutomationNativePointer (for
  OLEObject)

When you have finished, you must use these functions to free
the pointer:

• ReleaseNativePointer (for
  OLEControl and OLECustomControl)
• ReleaseAutomationNativePointer (for
  OLEObject)

For more information, see the PowerScript
Reference
.

OLE objects in DataWindow objects

The preceding sections discuss the automation interface to
OLE controls and OLE objects. You can also use scripts to change
settings for an OLE object embedded in a DataWindow object, and
you can address properties of the external OLE object.

This section describes how to use the Object property in dot
notation to set DataWindow properties and issue automation commands
for OLE objects in DataWindow objects.

Naming the OLE object

To use dot notation for the OLE object, give the object a
name. You specify the name on the General page in the object’s
property sheet.

Setting properties

You set properties of the OLE container object just as you
do for any object in the DataWindow object. The Object property
of the control is an interface to the objects within the DataWindow
object.

For example, this statement sets the Pointer property of the
object ole_word:

It is important to remember that the compiler does not check
syntax after the Object property. Incorrect property references
cause runtime errors.

For more information about setting properties,
handling errors, and the list of properties for the OLE DWObject,
see the DataWindow Reference
.

OLE objects and the Modify function You cannot create an OLE object in a DataWindow object dynamically
using the CREATE keyword of the Modify function.
The binary data for the OLE object is not compatible with Modify syntax.

Functions and properties

There are four functions you can call for the OLE DWObject.
They have the same effect as for the OLE control. They are:

• Activate
• Copy
• DoVerb
• UpdateLinksDialog

To call the functions, you use the Object property of the
DataWindow control, just as you do for DataWindow object properties:

Four properties that apply to OLE controls in a window also
apply to the OLE DWObject.

Table 19-7: Properties that apply to OLE controls and DWObject

Property	datatype	Description
ClassLongName	String	(Read-only) The long name for the server application associated with the OLE DWObject.
ClassShortName	String	(Read-only) The short name for the server application associated with the OLE DWObject.
LinkItem	String	(Read-only) The entire link name of the item to which the object is linked. For example, if the object is linked to C:FILENAME.XLS!A1:B2, then LinkItem would contain C:FILENAME.XLS!A1:B2.
ObjectData	Blob	If the object is embedded, the object itself is stored as a blob in the ObjectData property. If the object is linked, this property contains the link information and the cached image (for display).

Automation

You can send commands to the OLE server using dot notation.
The syntax involves two Object properties:

• The Object property of the DataWindow
  control Gives you access to DataWindow objects, including the OLE
  container DWObject
• The Object property of the OLE DWObject Gives you access to the automation object

The syntax is:

For example, this statement uses the WordBasic Insert function
to add a report title to the beginning of the table of data in the
Word document:

OLE columns in an application

OLE columns in a DataWindow object enable you to store, retrieve,
and modify blob data in a database. To use an OLE column in an application,
place a DataWindow control in a window and associate it with the
DataWindow object.

For users of SQL Server If you are using a SQL Server database, you must turn off
transaction processing to use OLE. In the Transaction object used
by the DataWindow control, set AutoCommit to TRUE.

For how to create an OLE column in a DataWindow
object, see the PowerBuilder Users Guide
.

Activating an OLE server application

Users can interact with the blob
exactly as you did in preview in the DataWindow painter: they can
double-click a blob to invoke the server application, then view
and edit the blob. You can also use the OLEActivate function
in a script to invoke the server application. Calling OLEActivate simulates
double-clicking a specified blob.

The OLEActivate function has this syntax:

Specifying the verb

When using OLEActivate,
you need to know the action to pass to the OLE server application.
(Windows calls these actions verbs.) Typically, you want to edit
the document, which for most servers means you specify 0 as the
verb.

To obtain the verbs supported by a particular OLE server application,
use the advanced interface of the Windows Registry Editor utility
(run REGEDT32 /V).

For information about Registry Editor, see
the Windows online Help file REGEDT32.HLP.

Example

For example, you might want to use OLEActivate in
a Clicked script for a button to allow users to use OLE without
their having to know they can double-click the blob’s icon.

The following statement invokes the OLE server application
for the OLE column in the current row of the DataWindow control dw_1 (assuming
that the second column in the DataWindow object is an OLE column):

For more information

For more information about using OLE in a DataWindow object,
see the PowerBuilder Users Guide
.

---