About this chapter
This chapter describes how to use OLE in DataWindow objects.
A DataWindow object can include a control that is a container for an OLE object. The container stores information about the application that created the object and it can launch the application to display or modify the OLE object.
The container can fill the whole DataWindow object, when you create a new DataWindow object using the OLE presentation style, or it can exist alongside other controls in a DataWindow object, when you add an OLE object to an existing DataWindow object. You can also read OLE data from a blob column in a database and display the objects in the DataWindow object.
You can use OLE objects in DataWindow objects in the following ways:
-
OLE object in a DataWindow object
The OLE object is displayed in its container control with the DataWindow data and other controls, such as bitmaps or text. You can associate it with data in a particular row, the rows on a page, or with all rows. You choose which columns in the DataWindow object are transferred to the OLE object. You can add an OLE container control to a DataWindow object that uses any presentation style that supports multiple DataWindow objects. (This does not include the graph and RichText presentation styles.)
-
OLE presentation style
The OLE presentation style is similar to an OLE object in a DataWindow object. The difference is that the OLE container is the only control in the DataWindow object. The underlying data is not presented in column controls and there are no other controls, such as bitmaps or text. The OLE object is always associated with all the rows in the DataWindow object.
-
OLE database blob column
OLE objects that are stored in the database in a blob column are displayed in each row of the DataWindow object.
You can also add ActiveX controls (also called OLE custom controls or OCXs) to DataWindow objects. ActiveX controls range from simple visual displays, such as meters and clocks, to more complex controls that perform spell checking or image processing.
The behavior of OLE objects in DataWindow objects is similar to the behavior of OLE controls in windows.
For more information about linked and embedded objects and automation, see the section called “Using OLE in an Application” in Application Techniques.
When you are working in the DataWindow painter, you can start the server application for an OLE object by selecting Open from the pop-up menu. Once the server application has started, you can use the tools provided by the server to edit the initial presentation of the object.
If the OLE object is associated with all rows retrieved and is in the foreground or background layer, not the band layer, users can activate the object. If the object is associated with a single row or page or is in the band layer, users can see the object but cannot activate it. DataWindows created using the OLE presentation style are always associated with all rows.
Unlike OLE objects, ActiveX controls are always active. They do not contain objects that need to be opened or activated.
Editing OLE objects
When an OLE object is activated, you can edit the presentation of the data. Changes made to DataWindow data affect the OLE object. Changes made to the OLE object do not affect the data the DataWindow object retrieved.
Each OLE object stored in the database in a blob column can be activated and changed. When the DataWindow object updates the database, the changes are saved.
What's next
Whether you are inserting an OLE object into a DataWindow object or using the OLE presentation style, you use the same procedures to define, preview, and specify data for the OLE object. Because of their similarities, the next section discusses both OLE objects in DataWindow objects and the OLE presentation style. The last section discusses OLE database blob columns.
Whether you insert an OLE object into a DataWindow object or create a new DataWindow object using the OLE presentation style, you are working with an OLE container object within the DataWindow object.
Similarities
They have these characteristics in common:
-
Icon or contents
The DataWindow object can display the OLE object as an icon, or it can display an image of the contents when display of contents is supported by the server.
-
Data from the DataWindow object
You specify which DataWindow columns you want to transfer to the OLE object. The data that is sent to the OLE server replaces the OLE object template specified in the painter.
Differences
The OLE object in a DataWindow object and the OLE presentation style have these main differences:
-
Associating the object with rows
When the OLE object is added to a DataWindow object, you can associate it with individual rows, groups of rows, or all rows. In the presentation style, the OLE object is always associated with all rows.
-
Properties view
The Properties view for an OLE object has different pages and some different properties from the OLE DataWindow object. For example, the Properties view for an OLE object in a DataWindow object does not contain detailed print specification settings because these are set in the DataWindow object's own Properties view. However, it does have settings related to the position of the OLE object within the DataWindow object.
Not all servers are appropriate
The features of the OLE server application determine whether it can provide useful information in a DataWindow object.
If the server does not support display of contents, it is not useful for objects associated with rows. The user sees only the icon. Some servers support the display of contents, but the view is scaled too small to be readable even when the object is activated.
In this section
This section includes procedures for:
-
Adding an OLE object to a DataWindow object
-
Using the OLE presentation style
-
Defining the OLE object
-
Previewing the DataWindow object
-
Specifying DataWindow data for the OLE object
To add an OLE object to a DataWindow object, you begin by specifying where you want the OLE object and opening the Insert Object dialog box so you can define the OLE object.
Adding an ActiveX control
Adding an ActiveX control to a DataWindow object is similar to adding an OLE object. Both exist within the DataWindow object with other controls, such as columns, computed fields, and text controls.Use the following procedure whether you want to add an OLE object or an ActiveX control to an existing DataWindow object.
To place an OLE object in a DataWindow object
-
Open the DataWindow object that will contain the OLE object.
-
Select Insert>Control>OLE Object from the menu bar, or from the toolbar, click the Object drop-down arrow and select the OLE button (not OLE Database Blob).
-
Click where you want to place the OLE object.
PowerBuilder displays the Insert Object dialog box.
To use the Insert Object dialog box, see Defining the OLE object.
Use the OLE presentation style to create a DataWindow object that consists of a single OLE object. The following procedure creates the new DataWindow object and opens the Insert Object dialog box.
To create a new DataWindow object using the OLE presentation style
-
In the New dialog box, select OLE 2.0 from the DataWindow tab and click OK.
-
Select data for the DataWindow object as you do for any DataWindow object.
For more information about selecting data, see Defining DataWindow Objects.
-
Specify how the OLE object will use the DataWindow object's data on the Specify OLE Data page:
You can drag the columns you want the OLE object to use to the Target Data box. You can also control the grouping of data and edit the expression for a column. If necessary, you can change these specifications later.
For more information, see Specifying data for the OLE object.
-
Click Next, and then click Finish.
PowerBuilder displays the Insert Object dialog box in which you define the OLE object.
To use the Insert Object dialog box, see Defining the OLE object.
You define the OLE object in the Insert Object dialog box. It has three tab pages:
|
If you want to |
Select this tab page |
|---|---|
|
Embed an OLE server object in the DataWindow object |
Create New |
|
Link or embed the contents of an existing file as an OLE object so that it can be activated using the application that created it |
Create From File |
|
Insert an ActiveX control in the DataWindow object |
Insert Control |
This section contains procedures for each of these selections.
Create New
Use the following procedure if you want to embed a new OLE server object.
To embed a new OLE server object using the Create New tab
-
Select the Create New tab.
-
In the Object Type box, highlight the OLE server you want to use.
You can click Browse to get information about the server from the registry.
Optionally display the OLE object as an icon by doing one of the following:
-
Check Display as Icon to display the server application's default icon in the control.
-
Check Display as Icon and then select Change Icon to supply a nondefault icon and icon label.
-
-
Click OK.
The OLE object is inserted in your DataWindow object and the OLE server is activated. Depending on the OLE server and whether or not you have already specified how the OLE object will use the DataWindow object's data, the object may be empty or may show an initial presentation of the OLE object. Close the server application and, if you are inserting an OLE object in a DataWindow object, specify the object's properties (see Specifying properties for OLE objects).
Create From File
Use the following procedure if you want to link or embed the contents of an existing file as an OLE object so that it can be activated using the application that created it. Most of the steps in this procedure are the same as those for embedding a new OLE server object.
A server application must be available
You (and the user) must have an application that can act as a server for the type of object you link or embed. For example, if you insert a BMP file, it displays because an application that can handle bitmaps is installed with Windows. If you insert a GIF or JPEG file, it displays only if you have a third-party graphics application installed.
To link or embed an existing object using the Create From File tab
-
Select the Create From File tab.
-
Specify the file name in the File Name box. If you do not know the name of the file, click the Browse button and select a file in the dialog box.
-
To create a link to the file, rather than embed a copy of the object in the control, select the Link check box.
-
Click OK.
The OLE object is inserted in your DataWindow object and the OLE server is activated. Depending on the OLE server and whether or not you have already specified how the OLE object will use the DataWindow object's data, the object might be empty or might show an initial presentation of the OLE object. Close the server application and, if you are inserting an OLE object in a DataWindow object, specify the object's properties (see Specifying properties for OLE objects).
Insert Control
Use the following procedure if you want to insert an ActiveX control (OLE custom control) in the DataWindow object.
To insert an ActiveX control using the Insert Control tab
-
Select the Insert Control tab.
-
In the Control Type box, highlight the ActiveX control you want to use, or, if the ActiveX control you want has not been registered, click Register New.
If you select an existing ActiveX control, you can click Browse to get more information about it. ActiveX controls are self documenting. PowerBuilder gets the property, event, and function information from the ActiveX control itself from the registry.
If you click Register New, you are prompted for the file that contains the registration information for the ActiveX control.
-
Click OK.
If you did not specify how the OLE object will use the DataWindow object's data when you created the DataWindow object, do so on the Data property page.
If you have inserted an ActiveX control that does not display data, such as the Clock control, you do not need to transfer data to it.
For more information, see Specifying data for the OLE object.
Specifying properties for OLE objects
For OLE objects, you need to specify how the OLE object will use the DataWindow object's data. If you used the OLE presentation style, you did this when you created the DataWindow object.
If you are inserting an OLE object in an existing DataWindow object, you can also associate the object with the current row. If you are using the OLE presentation style, the OLE object is always associated with all rows.
To specify properties for an OLE object
-
Select the Data property page in the Properties view.
-
Specify how the OLE object will use the DataWindow object's data.
For more information, see Specifying data for the OLE object.
-
(Optional) To associate the object with the current row, select the Position property page and change the value in the Layer box to Band.
-
Click OK when you have finished.
You set data specifications for an OLE object in a DataWindow object on the Data property page in the Properties view. You can also use the Data property page to modify the data specifications you made in the wizard for a DataWindow object using the OLE presentation style.
What the data is for
When an OLE object is part of a DataWindow object, you can specify that some or all of the data the DataWindow object retrieves be transferred to the OLE object too. You can specify expressions instead of the actual columns so that the data is grouped, aggregated, or processed in some way before being transferred.
The way the OLE object uses the data depends on the server. For example, data transferred to Microsoft Excel is displayed as a spreadsheet. Data transferred to Microsoft Graph populates its datasheet, which becomes the data being graphed.
Some ActiveX controls do not display data, so you would not transfer any data to them. For an ActiveX control such as Visual Speller, you would use automation to process text when the user requests it.
Group By and Target Data boxes
Two boxes on the Data property page list data columns or expressions:
-
Group By
Specifies how PowerBuilder groups the data it transfers to the OLE object. Aggregation functions in the target data expressions use the groupings specified here.
-
Target Data
Specifies the data that you want to transfer to the OLE object.
Populating the Group By and Target Data boxes
If you are using the OLE presentation style, you populated the Group By and Target Data boxes when you created the DataWindow object. If you placed an OLE object in an existing DataWindow object, the boxes are empty. You use the browse buttons next to the Group By and Target Data boxes to open dialog boxes where you can select the data you want to use or modify your selections.
Modifying source data
You cannot modify the source data for the DataWindow object on the Data property page. Select Design>Data Source from the menu bar if you need to modify the data source.
To select or modify how data will be grouped in the OLE object
-
Click the Browse button next to the Group By box.
-
In the Modify Group By dialog box, drag one or more columns from the Source Data box to the Group By box.
You can rearrange columns and specify an expression instead of the column name if you need to. For more information, see the next procedure.
To select or modify which data columns display in the OLE object
-
Click the Browse button next to the Target Data box.
-
In the Modify Target Data dialog box, drag one or more columns from the Source Data box to the Target Data box.
The same source column can appear in both the Group By and Target Data box.
-
If necessary, change the order of columns by dragging them up or down within the Target Data box.
The order of the columns and expressions is important to the OLE server. You need to know how the server will use the data to choose the order.
-
Double-click an item in the Target Data box to specify an expression instead of a column.
In the Modify Expression dialog box, you can edit the expression or use the Functions or Columns boxes and the operator buttons to select elements of the expression. For example, you may want to specify an aggregation function for a column. Use the range for object if you use an aggregation function; for example, sum (salary for object).
For more information about using operators, expressions, and functions, see DataWindow Reference.
Example of a completed Data property page. This example of the Data property page specifies two columns to transfer to Microsoft Graph: city and salary. Graph expects the first column to be the categories and the second column to be the data values. The second column is an aggregate so that the graph will show the sum of all salaries in each city:
Specifying a value for Rows
The last setting on the Data property page specifies how the OLE object is associated with rows in the DataWindow object. The selection (all rows, current row, or page) usually corresponds with the band where you placed the OLE object, as explained in this table. If you used the OLE presentation style to create the DataWindow object, this setting does not display on the property page: the OLE object is always associated with all the rows in the DataWindow object.
|
Range of rows |
When to use it |
|---|---|
|
All |
When the OLE object is in the summary, header, or footer band, or the foreground or background layer. Rows must be All and Layer must be Foreground or Background if you want the user to be able to activate the object. Target data for all rows is transferred to the object. |
|
Current Row |
When the OLE object is in the detail band. There is an instance of the OLE object for every row. Target data for a single row is transferred to each object. Because ActiveX controls must be in the foreground or background layer, they cannot be associated with individual rows in the detail band. |
|
Page |
When the OLE object is in the group header or trailer, foreground, or background. Target data for the rows on the current page is transferred to the OLE object. |
Range of rows and activating the object
When the range of rows is Current Row or Page, the user cannot activate the OLE object. The user can see contents of the object in the form of an image presented by the server but cannot activate it.
If you want the user to activate the object, Rows must be set to All and Layer on the Position property page must be Foreground or Background.
Additional settings in the Properties view
The Options property page in the OLE object's Properties view has some additional settings. They are similar to the settings you can make for the OLE control in a window. These settings display on the General property page for OLE DataWindow objects. The following table describes the settings you can make.
|
Property |
Effect |
|---|---|
|
Client Name |
A name for the OLE object that some server applications use in the title bar of their window. Corresponds to the ClientName DataWindow property. |
|
Activation |
How the OLE object is activated. Choices are: Double click When the user double-clicks on the object, the server application is activated. Manual The object can only be activated programmatically. The object can always be activated programmatically, regardless of the Activation setting. |
|
Contents |
Whether the object in the OLE container is linked or embedded. The default is Any, which allows either method. |
|
Display Type |
What the OLE container displays. You can choose: Manual Display a representation of the object, reduced to fit within the container. Icon Display the icon associated with the data. This is usually an icon provided by the server application. |
|
Link Update |
When the object in the OLE container is linked, the method for updating link information. Choices are: Automatic If the link is broken and PowerBuilder cannot find the linked file, it displays a dialog box in which the user can specify the file. Manual If the link is broken, the object cannot be activated. You can let the user re-establish the link in a script using the UpdateLinksDialog function. |
Previewing the DataWindow object lets you see how the OLE object displays the data from the DataWindow object. You can preview in the Preview view or in preview mode
To preview the DataWindow object with the OLE object in preview mode
-
Select File>Run/Preview from the menu bar, or click the Run/Preview button on the PowerBar.
-
Select Rows>Retrieve from the menu bar.
The DataWindow object retrieves rows from the database and replaces the initial presentation of the OLE object with an image of the data that the OLE server provides.
If you associated the OLE object with all rows, activate the OLE object by double-clicking on it.
Although you can edit the presentation or the data in the server, your changes do not affect the DataWindow object's data.
You cannot always activate the OLE object
If the OLE object is associated with individual rows in the detail band or with the page, you (and the user) cannot activate it; you can only view it.
-
Close the preview window.
Closing the preview window or the Preview view deactivates the OLE object.
In the Design view
PowerBuilder stores an initial presentation of the OLE object that it displays before data is retrieved and in newly inserted rows. When you activate the OLE object in the Design view, you are editing the initial presentation of the OLE object. Any changes you make and save affect only this initial presentation. After rows are retrieved and data transferred to the OLE object, an object built using the data replaces the initial presentation.
In preview or at execution time
PowerBuilder displays the initial presentation of the OLE object while it is retrieving rows and then replaces it with the retrieved data.
After you activate the OLE object in preview or at execution time, you can edit the presentation of the data. However, you cannot save these changes. The object is recreated whenever data from retrieved rows is transferred to the OLE object.
For more information, see Activating OLE objects.
Saving as a PSR
You can save the object with its data by saving the DataWindow object as a Powersoft report (PSR). Select File>Save As File or File>Save Rows As from the menu bar.
You can create OLE columns in a DataWindow object. An OLE column allows you to:
-
Store blob (binary large-object) data, such as Microsoft Excel worksheets or Microsoft Word documents, in the database
-
Retrieve blob data from a database into a DataWindow object
-
Use an OLE server application, such as Microsoft Excel or Microsoft Word, to modify the data
-
Store the modified data back in the database
You can modify the document in the server, then update the data in the DataWindow object. When the database is updated, the OLE column, which contains the modified document, is stored in the database.
Database support for OLE columns
If your database supports a blob datatype, then you can implement OLE columns in a DataWindow object. The name of the datatype that supports blob data varies. For information on which datatypes your DBMS supports, see your DBMS documentation.
This section describes how to create an OLE column in a DataWindow object. The steps are illustrated using a table that you can create in the Database painter. It must contain at least two columns, id and object:
-
The id column is an integer and serves as the table's key.
-
The object column is a blob datatype and contains OLE objects associated with several OLE servers.
To create the database table
-
In the Database painter, create a table to hold the blob (binary large-object) data.
The table must have at least two columns: a key column and a column with the blob datatype. The actual datatype you choose depends on your DBMS. For example, in SQL Anywhere, choose long binary as the datatype for the blob column. For information about datatypes, see your DBMS documentation.
-
Define the blob columns as allowing NULLs (this allows you to store a row that does not contain a blob).
Adding a blob column to the DataWindow object
The following procedure describes how to add a blob column to a DataWindow object.
To add a blob column to a new DataWindow object
-
Create a new DataWindow object.
-
Specify the table containing the blob as the data source for the DataWindow object.
Be sure to include the key column in the data source. You cannot include the blob column in the data source; if you try, a message tells you that its datatype requires the use of an embedded SQL statement. You add the blob column later in the DataWindow painter workspace. (If you use Quick Select, the blob column is not listed in the dialog box.)
-
Select Insert>Control>OLE Database Blob and click where you want the blob column in the Design view.
The Database Binary/Text Large Object dialog box displays:
Setting properties for the blob column
The following procedure describes the properties you need to set for the blob column.
To set properties for a blob column
-
(Optional) Enter the client class in the Client Class box. The default is DataWindow.
This value is used in some OLE server applications to build the title that displays at the top of the server window.
-
(Optional) Enter the client name in the Client Name box. The default is Untitled.
This value is used in some OLE server applications to build the title that displays in the title bar of the server window.
-
In the Table box, select the database table that contains the blob database column you want to place in the DataWindow object.
The names of the columns in the selected table display in the Large Binary/Text Columns list.
-
In the Large Binary/Text Columns box, select the column that contains the blob datatype from the list.
-
If necessary, change the default key clause in the Key Clause box.
PowerBuilder uses the key clause to build the WHERE clause of the SELECT statement used to retrieve and update the blob column in the database. It can be any valid WHERE clause.
-
Use colon variables to specify DataWindow columns. For example, if you enter this key clause:
id = :id
the WHERE clause will be:
WHERE id = :id
-
Identify the OLE server application by doing one of the following:
-
If you always want to open the same file in the OLE server application, enter the name of the file in the File Template box.
For example, to specify a particular Microsoft Word document, enter the name of the DOC file. If the file is not on the current path, enter the fully qualified name.
Use the Browse button to find the file
If you do not know the name of the file you want to use, click the Browse button to display a list of available files. Select the file you want from the resulting window.
-
If you do not want to open the same file each time, select an OLE server application from the OLE Class: Description drop-down list.
When the server does not match the OLE blob data
If you specify a server that does not match the OLE blob object or if your database contains objects belonging to different servers, the OLE mechanism can usually handle the situation. It looks for the server specified in the object and starts it instead of the server you specified.
-
-
Enter text or an expression that evaluates to a string in the Client Name Expression box.
The server might use this expression in the title of the window in the OLE server application. The expression you specify can identify the current row in the DataWindow object.
Use an expression to make sure the name is unique
To make sure the name is unique, you should use an expression. For example, you might enter the following expression to identify a document (where id is the integer key column):
"Document " + String(id)
-
Click OK.
PowerBuilder closes the dialog box. The blob column is represented by a box labeled Blob in the Design view.
-
Save the DataWindow object.
The following screenshot shows what a completed Definition page for a Blob object in a table called ole looks like in the Properties view:
Making the blob column visible
If the blob column is invisible in the DataWindow object until you activate the OLE server, you can make it easy to find the blob column by adding a border to the object.
Previewing an OLE column
Before using the DataWindow object in an application, you should preview it in the Preview view or in preview mode to see how it works.
To preview an OLE column in preview mode
-
Select File>Run/Preview from the menu bar and select the DataWindow object.
-
Click the Insert Row button.
PowerBuilder adds a blank row.
-
In the blank row, enter a value in the key column.
-
Double-click the column that contains the blob datatype.
The OLE server application starts and displays the file you specified in the File Template box, or an empty workspace if you specified only the OLE server name.
-
Review the file in the OLE server application and make changes if you want.
When you use an OLE column to access an OLE server application, the server application adds an item to its File menu that allows you to update the data in the server application and in the client (the DataWindow object). The text of the menu item depends on the OLE server application. In most applications, it is Update.
-
Select the menu item in the OLE server that updates the OLE client with the modifications.
In the example, you would select Update from the File menu in Microsoft Word. The OLE server application sends the updated information to the DataWindow object.
-
Close the file in the server application (typically by selecting Close from the File menu).
-
To save the blob data in the database, click the Save Changes button in the PainterBar.
The new row, including the key value and the blob, is stored in the database.
Later, after you retrieve the rows from the database, you can view and edit the blob by double-clicking it, which invokes the OLE server application and opens the stored document. If you make changes and then update the database, all the modified OLE columns are stored in the database.
