OLE Concepts in PowerBuilder

OLE — Part I

The new communication standard that everyone is interested in is called Object Linking and Embedding. By using OLE, you are working at the object level to share data by either physically embedding it into a working document or simply by providing the working document with the appropriate links to the source file.

This methodology transcends software boundaries, allowing you to pass information between different applications without the need for a third party file format that both the source and the target understand.

Objectives

  • The concepts behind OLE
  • The differences between linking and embedding
  • Difference between DDE and OLE
  • OLE Control attributes
  • OLE Server menus
  • OLE Automation

Introduction

The theory behind OLE is based upon one application using the functionality provided by another. Objects are linked to or embedded in your application and when activated, the application that originally created the alien object is used to handle any interactions that the user wishes to perform.

The application that has the object embedded or linked into it is called the OLE Client or OLE Container Object, while the application that created the embedded object is called OLE Server. OLE Client application provides space to place the object to display, while OLE Server application provides the functionality, i.e., methods to manipulate the data created by it.

For example, let us take a write document that has a PaintBrush picture placed in it. In this example, Write is OLE Client which provides space to place the OLE Object and displays it on the screen and PaintBrush is the OLE Server which actually created the bitmap and knows how to manipulate the bitmap file.

What Is Linking And Embedding?

When an object is incorporated into a document, it maintains an association with the object application that created it. Linking and embedding are two different ways to associate objects in a compound document with their object applications. The differences between linking and embedding lie in how and where the actual source data that comprises the object is stored; this in turn affects the object’s portability, its methods of activation, and the size of the compound document.

Linking

When an object is linked, the source data, or link source, continues to physically resides wherever it was initially created, either at another point within the document or within a different document altogether. Only a reference, or link, to the object and appropriate visual representation of the data is kept with the compound document.

Linking is efficient and keeps the size of the compound document small. Users may choose to link when the source object is owned or maintained by someone else because a single instance of the object’s data can serve many documents. Changes made to the source object are automatically reflected in any compound documents that have a link to the object. From the user’s point of view, a linked object appears to be wholly contained within the document.

One-Way Links

One-Way Links start with a source document sharing its data with the destination document and when the data in the source document changes, the destination automatically changes. A One-Way Link can have multiple destination documents. For example, the company logo could be linked into a spreadsheet, the spreadsheet data linked into a word processor, the word processor data linked into a presentation. Any time the company logo changes, all points along the One-Way Link will get automatically updated.

Two-Way Links

Two-Way Links with a source document sharing its data with the destination document and the destination document sharing that data (object) back to another location within the source document. An example of this might be where data originates in the word processor. This data might be marketing forecast data, for example. This data is linked to a spreadsheet where a chart is created based on the linked data. The chart is then linked back into the source document right below the forecast data. Once this Two-Way Link is established, a what-if analysis can be performed on the original source data such that if the data changes in the source, it is updated in the spreadsheet. The chart is then updated and finally, the chart is updated in the source document. So the ability to share data in a destination document and receive linked data back into the source document is a Two-Way Link.

In addition to simple links, it is possible to get arbitrarily complex by nesting links and combining linked and embedded objects.

Adaptable Links

OLE provides the mechanism for objects to be linked in a variety of ways and have those links adapt to changing situations. An object may be linked to a complete or partial object, referred to as a pseudo object. An example of a pseudo object is a range of spreadsheet cells. The source of a link may reside inside either the same compound document as the linked object or in a separate compound document. Both the compound documents containing the linked object and the link source may be stored in a standard disk file or in a file managed by the OLE storage system.

If the linked object is copied to a new location while the link source stays in place, the link remains intact and the linked object points correctly to the source. Similarly, if both the compound documents containing the linked object and the link source are moved to a new location in the same relative path, the link remains intact.

Embedding

With an embedded object, a copy of the original object is physically stored in the compound document as is all of the information needed to manage the object. As a result, the object becomes a physical part of the document. A compound document containing embedded objects will be larger than one containing the same objects as links. However, embedding offers several advantages that may outweigh the disadvantages of the extra storage overhead. For example, compound documents with embedded objects may be transferred to another computer and be edited there. The new user of the document need not know where the original data resides since a copy of the objects’ source (native) data travels with the compound document.

Embedded objects can be edited in-place; that is, all maintenance to the object can be done without ever leaving the compound document. Since each user has a copy of the object’s source data, changes made to an embedded object by one user will not effect other compound documents containing an embedding of the same original object. However, if there are links to this object, changes to it will be reflected in each document containing a link.

Linking Or Embedding

There are several things to consider when deciding between linking or embedding an object:

  • Will anyone else have access to data contained in objects outside of your application?
  • Is the data contained in your objects of a static nature?
  • Is the size of your application important?
  • Is there a chance of someone moving files containing objects?
  • Is speed important?

The following table highlights some of the differences between linking and embedding when considering these questions:

Linking Embedding
If objects are changed outside your application, then the object in your application will be updated automatically. An embedded object can only be edited from within your application.
Using linked objects will keep the size of your application down, as the objects are not stored within your application. Embedded objects are stored within your application, so the file size is bigger.
When you use linked objects, your application contains the reference to the linked file, so if the file is moved, the link will be severed. This isn’t a problem with embedded objects.
Linked objects are only loaded into your application when they are required, so start-up speed should be quicker. However, working with a linked object reduces the speed of your application, because the container has to link to the file. Start-up speed will be slower because the file is bigger, but work with the embedded objects is quicker because the object already exists in your application.
Linked objects can’t be activated in-place, for editing. Only off-site activation is allowed. In-Place and Off-site activation is allowed

 

Traditional Model, Component Object Model

When creating compound documents, whether you use OLE or a monolithic application, the results can appear to be the same from a high-level view. But if you look underneath, they are really quite different Traditional Model

As software vendors tried to add more functionality to their software programs, EXEs got very large. Take the example of a word processing program that does baseline word processing and more features need to be added such as graphing, equation editors, spreadsheet functionality, voice annotation, and more. Typically a developer would add this functionality to the base word processor, thus making the executable quite large. Another example is a presentation program that offers baseline presentation functionality and more features need to be added, such as graphing, equation editors, spreadsheet functionality, voice annotation, and more. Again, typically a developer would add this functionality to the base presentation program, thus making it’s executable large as well.

In the above figure, both the WP.EXE and Present.exe contain similar functionality. As an end-user, if you know how to use drawing tools in the word processor, you would hope that it might be similar to the drawing tools in the presentation package, but this may not be the case. If two different developers work on the drawing tools and don’t share the code, the User Interface (UI) might be totally different and furthermore, since the functionality is built into each executable, the hard drive space requirements will go up and you will have multiple tools installed that basically perform the same functionality.

There is clearly a better model, the Component Object Model (COM).

Component Object Model (COM)

The Component Object Model takes some of the core functionality out of the executables and make them stand-alone objects that can be called from any application that supports OLE.

Above figure shows, the added functionality that was discussed in the Traditional Model is now broken out into separate objects. The advantages are numerous. First, if you need to add charts into a container document, the same charting engine will be used and the same User Interface is applied, thus reducing the learning curve significantly. Secondly, each baseline executable will be smaller and the separate objects will not be duplicated on the hard disk, just simply installed in a common place so they can be commonly called from each baseline application. Lastly, this promotes the notion of “best of breed” objects, meaning that if you don’t like the drawing tools, simply install a better set of drawing tools and the newly installed drawing tools will be available in each application.

Visual Editing

With visual editing, the user can double-click an object in a compound document and interact with the object right there, without switching to a different application window. The menus, toolbars, palettes, and other controls necessary to interact with the object temporarily replace the existing menus and controls of the active window. In effect, the object application appears to “take over” the compound document window. When the user returns to the compound document application, its menus and controls are restored.

Visual editing represents a fundamental change in the way users interact with personal computers. It offers a more “document-centric” approach by allowing the user to focus primarily on the creation and manipulation of information rather than on the operation of the environment and its applications. This approach allows users to work within a single context – the compound document.

Visual editing can include a variety of operations depending on the capabilities of the object. Embedded objects can be edited, played, displayed, and recorded in-place. Linked objects can be activated in place for operations such as playback and display, but they cannot be edited in-place. When a linked object is opened for editing, the object application is activated in a separate window. To return to the compound document, the user must either close the object application or switch windows.

The following figure shows what happens to an application when the user decides to activate an embedded object in-place for editing. In the top window, the embedded document appears well integrated into the PowerBuilder application window. However, when the user double-clicks the document to start in-place editing, the PowerBuilder application undergoes several changes as is demonstrated in the bottom window in the above figure.

The title bar changes to reflect the name of the object application; that is, the word processor program. The menu is the result of the PowerBuilder application and the word processor applications merging their individual menus. The word processor changes in appearance; hatch marks surround its border to indicate its activated state. In the above picture, you can also see the red underlines in the text which is the result of MS-Word automatic spell-checking. When the user has finished editing and clicks outside the document’s border, the window once again looks like it belongs to a PowerBuilder application

Drag And Drop

The most widely used method for transferring data between applications has been the Clipboard. With the Clipboard method, the user chooses the Copy operation while in the object application, moves to the target application, and chooses Paste to put the object in place. Although effective, a more natural way to exchange data between applications is simply to click an object, drag it to its destination, and drop it in place. OLE supports this “drag and drop” functionality of objects in addition to the traditional Clipboard functionality.

Drag and drop eliminates the traditional barriers between applications. Instead of perceiving window frames as walls surrounding data, users are able to freely drag information to and from a variety of applications. Drag and drop makes compound documents easier to create and manage because it provides an interactive model that more closely resembles how people interact with physical objects.

  • Inter-window dragging: Dragging data is from one application window into another application window, is called inter-window dragging.
  • Inter-object dragging: Objects nested within other objects can be dragged out of their containing objects to another window or to another container object. Conversely, objects can be dragged to other objects and dropped inside them.
  • Dropping over icons: Objects can be dragged over the desktop to system resource icons such as printers and mailboxes. The appropriate action will be taken with the object, depending on the type of resource the icon represents.

Optimized Object Storage

Objects remain on disk until needed and are not loaded into memory each time the container application is opened. OLE also supports a transaction type storage system, providing the user with the ability to commit or rollback any changes that they make to the object. This ensures that data integrity is maintained as objects are stored in the file system.

Automation

Automation refers to the ability of an application to define a set of properties and commands and make them accessible to other applications to enable programmability. OLE provides a mechanism through which this access is achieved. Automation increases application interoperability without the need for human intervention.

The main purpose of this can be best described in an example. Let’s say that you have some data in a database you would like to perform a regression analysis. Typically, the database developer would have to create a function or sub-routine that contains the regression analysis formula and then pass the data to the function. The development of this function could take a considerable amount of time. In fact, why create a routine when spreadsheets do regression analysis quite well? So, the better approach would be to “pass” the data to the spreadsheet program, “instruct” the spreadsheet program to perform the regression analysis on the data, and “pass” back the results. This allows a macro developer to open up entirely whole new worlds. In fact, you could even start to consider a spreadsheet program as one large compilation of subroutines and functions that are easily callable and accessible.

This was previously achieved through a mechanism called Dynamic Data Exchange or DDE. DDE would give application macro developers the ability to control one application from another, but the methods to achieve this were not trivial. DDE required that a significant amount of time be devoted to error-handling and only a limited amount of data could be automatically passed from one application to another at one time. Consider the example of performing a regression analysis on data in a database. The following steps quickly map out what commands would be necessary if DDE calls were made:

Establish a connection from the controlling application (source) to the application (destination) that will be controlled.

  • Test to make sure that the destination application can receive the commands and is not busy.
  • From the source application, tell the destination application what is trying to be accomplished.
  • Test to make sure the destination application can handle what is trying to be accomplished.
  • Take one data point at a time from the source application and pass it to the destination.
  • Test each data point to ensure that the destination received it and that the connection is still live.
  • Once all the data points have been sent to the destination, instruct the destination to perform the regression analysis on the entire data set (this is even another command).
  • Prepare the source to now receive the result of the regression analysis and pass the result from the destination to the source.
  • Test to ensure that the source received the result.
  • Close the connection.
  • This example appears quite simplistic, but from a DDE programming standpoint this would likely take around 60 lines of code.

With OLE and OLE Automation, the process to achieve this is significantly easier. Applications written to support OLE expose properties and commands that can be controlled from other applications. The public exposure of these properties and commands allows one application to contain code that manipulates another application. It also allows developers to create new applications that can interact with existing applications.

  • Using OLE automation in the above example, we will see how the same results can be achieved with minimum effort:
  • Establish a connection from the controlling application (source) to the application (destination) that will be controlled.
  • Define the entire data set in the source as an object and pass the object to the destination.
  • Instruct the destination to perform the regression analysis on the object.
  • Instruct the destination application to return the result of the regression analysis to the source application.

Why Implement OLE?

OLE provides great benefits to both users and developers. OLE is the foundation of a new model of computing, which is more “document-centric” and less “application-centric.” Users can focus on the data needed to create their compound documents rather than on the applications responsible for the data. As the user focuses on a particular data object, the tools needed to interact with that object become available directly within the document. Data objects can be transferred within and across documents without the loss of functionality. Documents can share data so that one copy of an object can serve many users. Users can therefore be more productive and manipulate information in a manner that is much more intuitive.

The use of OLE objects and interfaces provides developers with the tools they need to create flexible applications that can be easily maintained and enhanced. OLE applications can specialize in one area, taking advantage of features implemented in other OLE applications to increase their usability.

Since all interfaces are derived from one base interface, applications can learn at runtime about the capabilities of other applications that relate to object services. Through this dynamic binding, an application need not know anything about the objects it will use at run time. Support for additional objects and interfaces can be added without affecting either the current application or those applications that interact with it. Because the use of interfaces allows applications to access objects as black boxes, changes to an object’s member functions do not affect the code accessing them.

The extendibility that OLE provides will continue to benefit the developer as the computing environment moves more toward an object-oriented design. The OLE architecture provides a first step in presenting applications as a collection of independently installable components.

Object Linking and Embedding (OLE) is a mechanism that allows applications to inter-operate more effectively, thereby allowing users to work more productively. End users of OLE container applications create and manage compound documents. Compound documents in this context do not necessarily refer to word processing documents. A compound document could be any container of objects such as a database containing word processing document (object) stored in the database, or a presentation containing financial forecasts which is an object from a spreadsheet.

Therefore, a compound document refers to a container holding objects that were created using another application. These are the documents that seamlessly incorporate data, or objects, of different formats. Sound clips, spreadsheets, text, and bitmaps are some examples of objects commonly found in compound documents. Each object is created and maintained by its object application, but through OLE, the services of the different object applications are integrated. End users feel as if a single application, with all the functionality of each of the object applications, is being used. End users of OLE applications don’t need to be concerned with managing and switching between the various object applications; they focus solely on the compound document and the task being performed.

DDE Versus OLE

OLE and DDE allow you to perform similar actions. Both enable you to send commands to another application, perform actions in that application, and return data to your PowerBuilder application. There are, however, some fundamental differences between OLE and DDE.

OLE is currently implemented using the DDE protocol. Applications using OLE are not aware that DDE is being used, nor should they rely on this, because the implementation mechanism will not be there in future releases of the OLE components.

To illustrate some of the differences, consider a PowerBuilder application you’ve designed in which the user has access to a particular Microsoft Excel worksheet. Your PowerBuilder application contains the worksheet. In OLE operations, program control is actually temporarily transferred to Microsoft Excel for the purpose of manipulating the worksheet data. With DDE, operations occur when PowerBuilder sends a command to Microsoft Excel to start communication between the two applications. PowerBuilder, however, always has program control.

Another difference, which is an advantage of OLE, is that OLE automatically starts the object application when program control is transferred to the object application. When you use DDE, you must check to see if the source application (the DDE server) is started, and start it if necessary.

In addition, with OLE the data always is displayed in a bound or unbound object frame as it appears in the application that created the object. For example, if the object application is Microsoft Excel, a bound or unbound object frame displays worksheet data in your PowerBuilder application (the container application) as it appears in Microsoft Excel itself. DDE doesn’t allow you to view the worksheet as it appears in the application.

Lastly, with OLE you can also allow the user to edit data in another application (activate that application) when the user double-clicks on the bound or unbound object frame containing the OLE object. DDE doesn’t provide this feature because DDE can only activate other applications.

In versions of OLE prior to version 2.0, it was difficult to access linked or embedded data using PowerScript. Well, PowerBuilder implemented OLE in Window object from OLE only. For example, if you created a linked object from a Microsoft Excel spreadsheet, you could allow the user to edit the spreadsheet’s data, but it was difficult for you to access that data using PowerScript. With the advent of OLE Automation, its easy to access and manipulate data in an OLE object, as long as the application that supplies the object also supports OLE Automation. If you want programmatic access to data in an application that doesn’t support OLE Automation (Paintbrush), you may still prefer to use DDE.

Which One To Choose, DDE Or OLE ?

Determining a need for DDE and/or OLE support is perhaps less clear. DDE provides an excellent method of importing live data to an application or exporting live data from it. If two applications want to share and manipulate a piece of data without the direct involvement of the user, DDE is the choice.

If an application wants to include facilities to show data types such as Microsoft Excel graphs or sound files provided by OLE servers, OLE client support should be added. If an application has data-rendering capability useful to other applications, it should become an OLE server.

Let us consider the following example: The live data comes to the application A, has the capability of managing communications and receiving and displaying the data. Application B has the capability of plotting the data into variety of graphs. Application D has the capability of recording audio and playing back. Application C is the word processor.

The customer requirement is to produce a document that gives information about the company, with a graph for the recent stock market data and chairman’s speech in audio format. The purpose of this example is to help illustrate how to choose the most appropriate type of communication support to include in an application.

In the case here, we begin with a live data feed attached to a communications port, convert it to a stream of DDE data, and finally end up with a linked OLE object.

Deciding Where To Use DDE Or OLE

Does it make sense to add OLE support to the Application A as either a client or a server? Not really. We have data that we can provide to another application, but the application could easily render it for itself, so we wouldn’t need to be an OLE server. We have no need to incorporate other data in our display, so we don’t need to be an OLE client either.

You might say that we could be an OLE server and display the data, but what purpose would this serve? If we didn’t provide the data via DDE as well, the client application would only be able to show an image of the data and not access it in any other way. That means that it couldn’t plot a graph, set trigger alarms, or whatever because an OLE client treats the data as a black box and has no knowledge of its format.

The Graph application need to be at least DDE client application, to accept data from the Application A. You might add DDE Server support, but, it is not required; this is because, for plotting the graph, it needs the data from other applications, but, it has no need of sending the data. Making this as OLE Server makes sense, because other applications can embed or link to this graph. Since the graph is in a special format, other application can invoke this application whenever it needs to be manipulated. Similarly sound recording application also should be at least OLE Server enabled.

Word-Processor application doesn’t have much use for live, changing data that it needs to manipulate, so we aren’t really interested in becoming a DDE client. Making this application into an OLE client makes a lot of sense. It enables other data types to be attached to the text being displayed, i.e., attaching a graph of our stock market prices and a sound recording of the CEO. We don’t need to be able to render the images of these data types because that’s the responsibility of the OLE server. We also get a free editor for our graph because the OLE server provides that support.

We could also add OLE server support for the word-processor. That way, the entire document could be linked to or embedded in another application document.

Registration Database

The Registration Database is a source of information regarding applications and their OLE and association capabilities. This information is used in three cases:

  • by applications that support object linking and embedding
  • when you open or print a file from File Manager
  • for associating file name extensions with applications.

The registration database is set up and maintained by Windows and Windows applications. The database “REG.DAT” is located in the Windows directory (Under Windows 16-bit version). This file should not be moved or deleted. If the file is moved or deleted, loss of functionality in File Manager, Program Manager, and applications that support object linking and embedding, may occur. The Registration Database is modified by running the Registration Editor, REGEDIT.EXE.

The Advanced Interface Of REGEDIT

The advanced interface of REGEDIT is accessed by running REGEDIT with the /V option (REGEDIT /V). The advanced interface is designed to be used by application developers and system administrators, who modify the Registration Database to manipulate the OLE and Association properties in Windows.

When you run the Registration Information Editor with the /V option, the advanced Registration Editor screen is shown with all of the information in the database. The contents of the Registration database are branched in a tree format like the directory tree in Explorer. Each branch of the tree is identified by its key name which is Window’s internal name for the program

What Can We Do With RegEdit?

RegEdit contains the commands that are available to our application when dealing with OLE objects. For instance, let’s look at a sample of the registration editor (Under Windows 16-bit):

We can see from the above picture, that SoundRec has a class id. An OLE Object is identified uniquely by its class id which is 128-bit long. You will learn more about class ids in “registering PowerBuilder OLE controls in the registration database” in “Distributing OLE Objects”. The SoundRec OLE server (which plays sounds) has two possible actions (called VERBs in Windows): Play and Edit. You will learn about verbs in “OLE Objects” topic.

Whenever you install software, those packages will automatically updates the registry database. Some of the packages allows you not to update the registry and write all those updates to a file. In this case you can merge the update file for that particular software with the registry by using ‘S’ option, i.e., REGEDIT -S FileName.

OLE Control

OLE is best integrated in PowerBuilder through OLE in the window painter. This control allows you to embed or link an OLE object and gives OLE container support. All OLE clients support container functionality. The container basically provides some space to place the OLE Server object and invokes the OLE Server for editing the object, either in-place (OLE servers, except for linked objects) or Off-site(OLE 1.0 Servers). OLE client knows nothing about the content of the OLE object and is like a black box for it; That’s why OLE client invokes the OLE server for data manipulation, since the OLE Server knows how to manipulate its own data.

Once you place the OLE control in the window it will prompt for the OLE server name. You can either create a new object or create from an existing file. When you create a new object, it will be embedded in the PowerBuilder window and saved as part of the window definition. We don’t recommend this method, since, it will lead to long time to open the window, either at the development time or run-time. Other problem is that, when you export this window and import the window definition back either into the same PBL or other PBL, you will loose the content of the OLE control. We do recommend to select cancel while selecting the object type and create the control, and select cancel again when prompted for the object type; this will create the control in the window. Basically it will appear as empty. This makes the window opening very faster.

When you create from the existing file, you will have the option to embed or link. In this case, PowerBuilder decides the server type depending on the information available in the object

OLE Control Attributes

To see an OLE control’s attributes, double-click on it in the development environment:

These options allow you to control how the OLE object behaves and how the user interacts with it at run-time. Let’s take a look at the interesting ones.

Contents

This attribute defines the content of the OLE control. This attribute only applies to circumstances where the content is dynamically allocated at run-time using the InsertObject() function. There are three possibilities:

Option Description
Any: Allows linked or embedded objects to be inserted
Embedded: Allows only embedded objects to be inserted
Linked: Allows only linked objects to be inserted

This allows you to assign an OLE object to the OLE control by clicking on the Add OLE Object button. Doing this brings up the standard Insert Object dialog box, allowing you to select the type of object you want to add.

If you select the Create from File option, you’ll get the usual screen except that the link option isn’t available.

This is because we’ve specified that the contents should be embedded. If you close the window, change the Contents attribute of the OLE control to linked and run the window again, clicking on the Add OLE Object button will then launch the select file dialog with the Link option already selected:

 

Display Type

This is used to specify whether you want to see the actual contents of the OLE object or an icon representing the server application, in the OLE container application. When you activate the OLE server application, the server application is invoked and allows you editing. When you comeback to the container application, either the icon or content would be displayed depending on the selected ‘Display type’.

Activation

This allows you to specify how the OLE Server application is activated. The default method is Double Click, but you can change it to Get Focus or Manual. If you specify Manual, then you have to take care of activating the server programmatically.

result = ole_1.Activate(Offsite!)

Activate is the function you need to call to invoke the OLE server. The Offsite! enumerated variable opens the server in its own window. You can choose to have the server open in its own window or in the same window as your PowerBuilder application, by specifying appropriate argument to Activate function. You can invoke the OLE server in-place only when the content is embedded. While placing the OLE control, if you choose either ‘Create From File’ without selecting ‘Link’ option or ‘Create New’, the content is embedded. Linked objects can be edited only offsite always. When you choose ‘Link’ while painting the OLE control and call the Activate() with InPlace! argument, PowerBuilder simply ignores the argument and invokes the OLE server off-site. When OLE Server invokes for in-place editing, the existing PowerBuilder application menus and OLE server menus would merge. This is explained in detail in the next section ‘OLE Server Menus’.

Link Update

This option is applicable only for ‘linked’ types. If you choose ‘Automatic’, the content in the OLE container will be updated automatically whenever the content of the linked object changes. If you choose ‘Manual’, you need to update the link in the script by calling UpdateLinksDialog().Opening a File In a OLE Control

This function comes in lot of flavors. Only the first format is discussed here and other topics are explained in the “OLE Storages and Streams” session. Open opens the specified file in the OLE control. The following example opens an OLE file, however you can open any OLE server file such as a word document, excel spread sheet, etc. and save as a OLE file. All the examples shown in this section are available in the w_ole_20 window in the ole2.pbl library.

All the code in the library uses a directory called “c:\workdir”. Make sure you have the directory on your computer and unzip the zip file you download into the “c:\workdir” directory. You can run the window and test the functionality, since all the support files are also available with this.

Open the application “object_embedding_n_linking-2_0” from “ole2.pbl” library. Run the application. Select ‘Module > OLE Control Examples’ menu option. Click on the Open Doc CommandButton.

String lFileNameWithFullPath, lFileNameInteger lResultlResult = GetFileOpenName( “Select Document File ” + &        “to Open in OLE Control”, lFileNameWithFullPath, &        lFileName, “DOC”, “DOC Files (*.doc), *.doc” ) If lResult <> 1 Then ReturnlResult = ole_1.Open( lFileNameWithFullPath )If lResult <> 0 Then   MessageBox( “Error while opening the ” + &         specified File”, “Error No: ” + String( lResult ))   Return -1000End If

We are asking the user to select the file to open by calling GetFileOpenName() function. We are then opening that docu Opening a Sub-Storage

Another flavor of Open() allows you open a sub-storage into the OLE control. OLE supports storages and sub-storages. In simple terms, Storages are like DOS file structures. From the operating file structure, the storage file looks like a single file, however, you can have lot other sub-storages within a storage, even though you can’t see from the operating system. To open a sub-storage in the OLE control, first we need to open the storage file into a variable of type OLEStorage and you can use that variable as the argument for the Open() for the OLE control.

To see it in the action, run the application. Select ‘Module > OLE Control Examples’ menu option. Click on the ‘Open SubStorage’ CommandButton.

The following example, opens the storage file OLE_TEST.OLE into the ole storage file iOLEStorage. The next line opens OLE_TEST.XLS which is stored in the storage file OLE_STORAGE.OLE into the OLE control. More on storages, sub-storages and streams in the next session.

Integer lResult, IlResult = iOLEStorage.Open( “c:\workdir\ole_test.ole” )lResult = ole_1.Open( iOLEStorage, “ole_test.xls” )If lResult <> 0 Then   MessageBox( “File Open Error”, lResult )   Destroy iOLEStorage   Return -1Else   MessageBox( “Open”, “Opened Successfully. ” + &”Double-click on the OLE control ” + &” to edit the Excel spread sheet” )End If

document in the OLE control using Open() function.

 

 

Inserting an Existing File In the OLE Control

Calling the InsertFile() embeds a copy of the existing object into the control. The following example inserts the specified object.

String lFileNameWithFullPath, lFileNameInteger lResultlResult = GetFileOpenName( “Select File to Open ” + &        “in OLE Control”, lFileNameWithFullPath,lFileName)If lResult = 0 Then ReturnlResult = ole_1.InsertFile( lFileNameWithFullPath )If lResult <> 0 Then   MessageBox( “File Insert Error”, lResult )   Return -1000End Ifole_1.Activate( InPlace! )

Activate() function activates the inserted file, i.e., invokes the OLE Server (The program that created the file).

To see it in the action, run the application. Select ‘Module > OLE Control Examples’ menu option. Click on the Inser Inserting an Object In the OLE Control

Calling InsertObject() prompts allows you to choose an object and allows to either to create from a new object or from an existing object. If you choose to create from create from the existing file, you can also link to that file. If the “content ” attribute is set to “Any” then only you are allowed to link the existing object. If the content type is set to “Linked”, you choices are narrowed down to linking to the existing object.

ole_1.InsertObject()

To see it in the action, run the application. Select ‘Module > OLE Control Examples’ menu option. Click on the Insert Object CommandButton.

Saving the Object From the OLE Control

Calling the Save() saves the content of the OLE control to the opened file. If you open the file by using Open() format 1 or InsertObject() functions, file is saved to the opened file. If the file is opened from the OLE storage (explained in the next session), it will be saved to the OLE storage.

ole_1.Save()

To see it in the action, run the application. Select ‘Module > OLE Control Examples’ menu option. Insert a file by clicking on the Insert File CommandButton and click on the Save CommandButton.

Linking the OLE Control’s Object To a File

Calling LinkTo() allows you to link the OLE object to another object.

String lFileNameWithFullPath, lFileNameInteger lResultlResult = GetFileOpenName( “Select Document to ” + &              “link from OLE Control”, + &              lFileNameWithFullPath, lFileName, “Doc”, &              “Documents (*.doc), *.doc” ) If lResult <> 1 Then ReturnlResult = ole_1.LinkTo( lFileNameWithFullPath )If lResult <> 0 Then   MessageBox( “File Link Error”, lResult )   Return -1000End If

To see it in the action, run the application. Select ‘Module > OLE Control Examples’ menu option. Open a OLE Storage file in the OLE Control by clicking on the Open SubStorage CommandButton and click on the Link CommandButton.

Getting Image From the Database To OLE Control

OLE Control has an attribute ObjectData. When an object is embedded in the control, the actual content of the object is stored in this attribute. We can assign the value of this attribute (object content) to a BLOB (Binary Large Object) variable and update the database or write to a file. The following example is using “easdemodb.db” that comes along with the PowerBuilder software for example application. It reads the “screen” column from the “examples_previews” table and loads in the OLE control.

SELECTBLOB “examples_previews”.”screen”     into :iImageBlob     from “examples_previews”     where “examples_previews”.”window”= ‘w_dialer’ ;If SQLCA.SQLCode <> 0 Then   MessageBox( “Error”, SQLCA.uf_GetError() )   Return -1End Ifole_1.ObjectData = iImageBlob

PowerBuilder has a datatype BLOB which means Binary Large Object. If the database contains binary objects such as sound files, image files, we can’t retrieve in PowerBuilder in the normal way. We need to use the BLOB type variable. We have declared a BLOB type variable “iImageBlob” as an instance variable and we are retrieving the “screen” column into this variable. Since we are using embedded SELECT statement, we have to make sure the SELECT statement returns one row only. We have only one row for the “w_dialer”. The SELECT statement is also not a regular SELECT statement, it is SELECTBLOB statement.

To see it in the action, run the application. Select ‘Module > OLE Control Examples’ menu option. Click on the Open Database Object CommandButton.

Updating the Database BLOB Column From OLE Control

Similar to populating the object in the OLE Control, we can also update the database column once user updates the object in the OLE control. The following code updates “screen” column in the examples_previews table in the database.

Blob lBlobInteger lResultlBlob = ole_1.ObjectDataSqlCa.AutoCommit = TrueUPDATEBLOB “examples_previews”set “examples_previews”.”screen” = :lBlobwhere “examples_previews”.”window” = ‘w_dialer’;If SqlCa.SqlCode <> 0 or SQLCA.SQLNRows = 0 Then   Rollback using Sqlca ;   MessageBox( “Error”, Sqlca.uf_GetError())   Return -1Else   Commit using Sqlca ;End If

Similar to the SELECTBLOB, we are calling UPDATEBLOB, not a regular UPDATE database statement.

To see it in the action, run the application. Select ‘Module > OLE Control Examples’ menu option. Click on the Save to Database Using BLOB CommandButton.

Copying the OLE Control

This function copies the content of the OLE control to the clipboard. Remember, if you are using a SinleLine/MultiLine edit control and use this function, it copies only the selected text to the clipboard. But, the same function here copies the entire content of the OLE control to the clipboard. For example, a word document is placed in this control and you selected first three lines of the document. Calling this function copies entire document to the clipboard, not the only first three lines.

Ole_control1.Copy()

 

Pasting an Object In the OLE Control

This function pastes into the control from the clipboard. If you have place some text and call this function, it will not paste into the control. It will paste if you are pasting into the edit control, but, not in the OLE control. To paste in the OLE control by calling this function, the content on the clipboard should be a OLE object. The syntax is:

OLEControlName.Paste()

 

PasteSpecial()

It presents a dialog box to select the format of the content to paste. The syntax is:

OLEControlName.PasteSpecial()

PasteLink()

Pastes the link to the content in the clipboard to the OLE control. The syntax is:

ole_control2.PasteLink()

Editing/Printing the Object In the OLE Control

Executes the verb that is supported by the server. The verbs supported by the OLE server you can find out from two sources. One from the OLE Server documentation and other is from the Registration database. For example, Paintbrush supports one verb, that is “Edit”. DoVerb() takes a number as the argument. Generally argument 1 is for “Edit”. For example, you placed a bitmap in the OLE control and called:

OLEControlName.DoVerb(1)

This will invoke PaintBrush in its own window. Calling this function with 1 as argument is equal to activating the OLE Server, however, there is a small difference. When you activate the server with this function, the OLE Server always activates off site, even though the server supports OLE (For example: Word 6.0). This function is basically intended for OLE 1.0 servers.

There are few more functions related to the OLE control. These are explained in detail in the advanced section of OLE, i.e., next session. Now let us see how the menus affect when we activate the OLE Server in-place.

OLE Columns In the DataWindow

Data types that store large amount of data can’t be used in the DataWindow in the same way you use traditional data types such as string, integer, etc.. These special data types are called BLOB (Binary/Text Large Objects) and are used to store large amount text like sample session of a book, image, audio, video, etc. PowerBuilder handles these BLOB data types in a different way, in the DataWindow as well as in embedded SQL. In case of embedded SQL you can use SELECTBLOB and UPDATEBLOB statements. In case of DataWindows, you need use “OLE database Blob” columns and use OLE technology. In product.db database, we have a column of ‘long binary’ data type, product_image column in the product_images table. Let us see how to select data from this column to display in the DataWindow and how to update this column.

Invoke the DataWindow painter and select ‘SQL Select’ data source and Tabular presentation style. Select product_images table. Select product_no column, you are not allowed to select product_image column and if you try you will see an error message as shown in the following picture, since you have to handle BLOB data types differently. Return to the design mode.

Select ‘Objects > OLE Database BLOB’ from the menu and place in the detail band next to window column. You will be prompted for the BLOB definition as shown in the following picture. You will find a different dialog box, but don’t worry about it. Just concentrate on the values.

You can leave the Class Name, Client Name to the defaults. These two values are used by some OLE servers to display in the OLE Server windows. If the primary key is defined, PowerBuilder automatically populates Table and Large Binary/Text columns for you, otherwise select product_images for the Table and product_image for the other prompt. Remember, if the primary key is not defined to the table, you are not able to update the DataWindow. The value in the Key Clause is used by the PowerBuilder in the WHERE clause in the UPDATE/DELETE statement to update the database table. If you want to use a default template every time you invoke the OLE server, you can provide one or leave it empty.

Select the OLE server name from the drop-down ListBox. You will see the OLE Server Class long name, once you select the class name, only the short name will be displayed. The value in the Client Name Expression is used to identify the row and column number in the DataWindow from the OLE Server. When you invoke the OLE Server from the DataWindow, the result of the Client Name Expression is displayed.

To see how it works, go to preview mode and insert a row and provide a value for the product_no and double-click on the database BLOB column.

If you see in the SQLPreview event, you can see data for the “window” only, not for the “screen” column. PowerBuilder makes some internal calls to update this column. If you are using SQL Server, make sure you set AutoCommit to True, before you update this DataWindow and change it back to False once update is done. When the use double clicks on this column, PowerBuilder automatically invokes the OLE Server. If you want to invoke programmatically, you can use OLEActivate function.

Dw_1.OLEActivate( GetRow(), 2, 0)

The last argument is the OLE Server verb. Typically 0 is activate the server. As explained above, to find other supported verbs, use RegEdit/V. You can change the OLE Server dynamically by setting “Class” attribute as shown below:

dw_1.Modify( ‘item_image_blob.oleclass=”word.document”‘ )

Make sure to name the column when you create However, when you change the OLE Server, it is applicable for new rows only. For example, there is one row in the DataWindow and it has a word document. Now you change the OLE Server to PaintBrush and insert a row and invoking the server invokes the PaintBrush. If you invoke the OLE Server for the first row, it will invoke the Word not the PaintBrush.

OLE Presentation Style DataWindow

As explained earlier, OLE Database BLOB is used to retrieve date from BLOB data type column and save back to the database. But, it doesn’t allow you to use OLE for other data type columns. You may want to allow users editing the database data in their favorite application, say, Word, Excel and apply all the validation in PowerScript and save the changes back to the database. You can accomplish this with DDE, but it needs lot of programming and error handling.

With version 5.0, PowerBuilder allows you to present data in the OLE presentation style, i.e., retrieve data from the database and present the data to the user in one of the OLE server, say, Word, Excel, PowerPoint, etc. PowerBuilder takes care of formatting the data according to the OLE Server requirement and sending it to the OLE Server and getting the data back and formatting back to the PowerBuilder format.

As a programmer, what you need to do is, just simply select OLE presentation style, instead of one of the traditional presentation styles. That’s all, you are done. For example, let us present all the product information to the user in Word 6.0. To do this, invoke the DataWindow painter and select ‘SQL Select’ data source and OLE Presentation Style.

Select product_master table and select all the columns and click on SQL toolbar icon. Select ‘Microsoft Word Document’ from the “Insert Object” dialog box. Drag-and-Drop all the columns from the “Source Data” List Box into the “Group by” List Box. PowerBuilder automatically groups the data into the specified groups before sending to the OLE Server. PowerBuilder automatically adds appropriate aggregate function for each column in the ‘Target Data’ ListBox. Double click on each column and remove the aggregate function definition for each column. PowerBuilder automatically opens the OLE Server, MS Word in this example. Type what ever you want to see such as headings, format it and select ‘File > Close &Return Data to Untitled’ menu option and you will be back to PB. If you ever want to edit the data you typed in OLE Server, display popup menu in the DataWindow design view and select Open menu option.

OLE Control In the DataWindow

OLE Presentation style allows you to display entire data in the OLE Server. Sometimes you may want to display data in the DataWindow and when the user selects, then you want to display in the OLE Server, say, MicroSoft PowerPoint. This can be accomplished by placing OLE control in any one of the traditional presentation style DataWindow.

Once you paint the DataWindow, select ‘Insert > Cotrol > OLE Object’ menu option and click wherever you want to place in the DataWindow control work place. Select the OLE server from the resulting prompt and create content in the OLE control and return back to PB. Click with the right mouse button on the placed OLE control and select Properties from popup menu and specify which columns data you want to display in the OLE Server from the ‘data’ tab.

Advanced Scripting, DataWindows – II

You have learned PowerScript, DataWindows and advanced stuff like DDE, OLE and so on. This is the time to develop some re-usable objects, by which you can reuse them in the projects that you will be doing as part of this PowerBuilder Online course. And also, we will introduce you to the advanced DataWindow coding. This is the kind of session that we will never say “this is the end of the session”. We will keep updating this session frequently, so, don’t forgetting this session now and then, till the completion of your subscription.

An object to export the DataWindow with more user-friendly prompts
An object that allows print preview zooming of a given DataWindow
An object that allows interactive sorting of a DataWindow
Advanced DataWindow scripting
Advanced scripting using window controls, etc..
Drag and Drop

 

Interactive DataWindow Sort Utility

We are sure that you know how to sort the data in the DataWindow. You just need to call Sort() function. Then, why we are developing this utility? When we call Sort() function, PowerBuilder displays all the columns in the DataWindow in the sort criteria dialog box. As you know, the database names and the headings in the DataWindow may not be exactly same. In the column names there are a lot of restrictions, and, typically, the names would be cryptic for the end user. What we are going to do in this utility is that, let the user click on the DataWindow column interactively and let’s take care of sorting the data.

Paint a tabular style DataWindow with an external data source. Define the result set for the DataWindow as follows:

Name Type Length Decimals
column_name string 40 0
sort_order string 1 0

Change the edit style for the sort_order column as RadioButton. The allowed values for the sort_order column should be “A” (Asc), “D” (Dsc) and save the DataWindow as “d_sort”.

Paint a window as shown in the following picture. There are four controls in the window. A DataWindow “dw_sort”, the three other controls are CommandButtons. Those are “cb_sort”, “cb_delete” and “cb_close” from left to right. Save this window as w_sort. Make sure to set the window type as “Child” window. Disable all window options expect visible, boarder and title. Set the title to “Sort”. Associate “d_sort” DataWindow to the “dw_sort” DataWindow control.

Declare two instance variables: iWindow as a Window, and iDw as a DataWindow:

Window iWindowDataWindow iDw

The purpose of iDw is to store the DataWindow that the user wants to sort on, while iWindow stores the window. Actually, storing the window isn’t necessary, but if we do, it allows the sort window to be more flexible. For example, suppose that two sheets are open and the user invoked the sort window from Sheet2. As you know, we are allowing the user to select the column by clicking on the appropriate sheet, so if the user clicks on a column from Sheet1, we won’t be able to find the column name. Sending both the Window and the DataWindow overcomes this problem.

We need to declare a function wf_initialize() at this window level. The purpose of this window is to initialize the instance variables. We can’t hard code the DataWindow name to sort, since we are creating a general purpose utility. After opening this window, let the user of this utility call this function saying which DataWindow to sort and in which window the DataWindow is in.

 

// Object: w_sort// Function: wf_initialize// Access Level: public// Returns: (none)// Arguments: p_window window By value// p_dw datawindow By valueString lSortOrderStr, lSortOrderStr1, lColName, lSortOrderInt lEndPos = 0, lSpacePosLong lNewRow If ( NOT IsValid( p_window )) OR &      ( NOT IsValid( p_dw )) THEN      Close( This )End If iWindow = p_windowidw = p_dw lSortOrderStr = idw.Describe( “datawindow.Table.Sort” ) If lSortOrderStr <> “!” and lSortOrderStr <> “?” Then   dw_sort.Reset()   Do While Len(Trim(lSortOrderStr)) <> 0      lEndPos = Pos(lSortOrderStr, “,”)      If lEndPos = 0 Then      lEndPos = len(lSortOrderStr)      End If      lSortOrderStr1 = Left( lSortOrderStr, lEndPos -1 )      lSortOrderStr = trim(Mid ( lSortOrderStr, lEndPos+1 ))      lSpacePos = Pos(lSortOrderStr1, ” “)      lColName = Left(lSortOrderStr1, lSpacePos)      lSortOrder = Trim(Mid(lSortOrderStr1, lSpacePos))      lNewRow = dw_sort.InsertRow(0)      dw_sort.SetItem( lNewRow,1, lColName )      dw_sort.SetItem( lNewRow,2, lSortOrder )   LoopEnd If If dw_sort.RowCount() = 0 Then   cb_sort.enabled = False   cb_delete.enabled = FalseElse   cb_sort.enabled = TRUE   cb_delete.enabled = TRUEEnd If Return

This function assigns the Window and DataWindow to instance variables and determines the existing sort order by calling Describe(). If there is any problem, Describe() will return either ‘!’ or ‘?’. The following loop parses the sort order string returned by Describe() and inserts each column name and sort order in the dw_sort DataWindow.

Okay, in the above function, we know which DataWindow to sort. But, we don’t know the sort criteria. We need know each column the user clicks and add it to the sort criteria. We need one more function wf_add_to_sort_cols. Declare the following function at the window level.

// Object: w_sort// Event: wf_add_to_sort_cols// Access: Public// Returns: (none)// Arguments: p_window   window By value// p_dw      datawindow   By value// p_clicked_col_name    string value String lArg1, lClickedColNameLong lNewRow If IsValid( p_dw ) Then    If p_dw <> idw Then       idw = p_dw       dw_sort.Reset()   End IfElse   ReturnEnd If If IsValid( p_Window ) Then   If p_Window <> iWindow Then       iWindow = p_Window   End IfElse   ReturnEnd If lNewRow = dw_sort.InsertRow(0)dw_sort.SetItem( lNewRow, “column_name”, p_clicked_col_name )dw_sort.SetItem( lNewRow, “sort_order”, “A” )//idw.AcceptText()cb_sort.enabled = TRUEcb_delete.enabled = TRUE Return

This function checks that the DataWindow and Window are valid and then adds the column name to the dw_sort DataWindow control and enable the Sort and Delete buttons.

The final thing to do is to apply the sort criteria when the user clicks on the Sort button. Write the following code for the clicked event of the cb_Sort button:

// Object: cb_sort// Event: Clicked Long lTotRows, iString lSortStr dw_sort.AcceptText() lTotRows = dw_sort.RowCount()If lTotRows > 0 Then    lSortStr = dw_sort.GetItemString(1,1) + ” ” + &             dw_sort.GetItemString(1,2) End If If lTotRows > 1 Then   For i = 2 to lTotRows Step 1      lSortStr = lSortStr + “, ” + dw_sort.GetItemString(i,1) + &            ” ” + dw_sort.GetItemString(i,2)    NextEnd If idw.SetSort( lSortStr )SetPointer( HourGlass! )idw.Sort()SetPointer( Arrow! )

This sets the values from each row in the dw_sort DataWindow control and prepares the sort strings with a loop. Before we actually sort the DataWindow, we call SetSort() with the new sort criteria. We then change the pointer to an hourglass, and call the Sort() function, before changing the pointer back.

As a final touch, allow the user to delete a sort criteria by clicking on the Delete button:

// Object: cb_delete// Event: Clicked Long lCurrentRowlCurrentRow = dw_sort.GetRow()If lCurrentRow > 0 Then      dw_sort.DeleteRow( lCurrentRow )      If dw_sort.RowCount() = 0 Then             This.enabled = FALSE            cb_sort.enabled = FALSE      End IfEnd If

Now, we are done with developing the utility. Let’s use it. Open the “w_product_master” window and comment all the code in the “ue_sort” event for the widnow and type the following code:

Open( w_sort )w_sort.wf_initialize( this, dw_product )

As explained above, we are opening the window and calling the initialize function with the window and DataWindow names.

Now we have to allow the user to select a column by clicking on it. The following code has to be written for the clicked event for the DataWindow control on w_product_master:

// Object: dw_product// Event: Clicked If Handle( w_sort ) > 0 AND &      Upper(dwo.Type) = “COLUMN” Then      w_sort.wf_add_to_sort_cols( Parent, This, dwo.Name)      Return 0End If Return 0

This code checks whether or not the w_sort is open using the Handle() function. If the return value is greater than zero, the window is open and we call another function to add the column to the sort list.

Want to see this in action? Run the application and test it. Working? Hey Guys, take it and use it in your real applications. You pay No royalities to us.

An Object to export a DataWindow

You can save the data contained in a DataWindow in most of the popular file formats, including .XLS, .DBF, tab delimited text and so on. We know that you learned about this in previous sessions. You have used SaveAs() to export the DataWindow. You might ask us, “Why we need to develop an object to export a DataWindow, when we can export it using a simple function call SaveAs()?” The answer is that, the text in the “SaveAs” dialog box is not user-friendly. For example, it says, CSV. It’s a kind of cryptic. It would be more user-friendly if we display “Comma Separated Text (CSV)”. There is no facility that allows us to change the text of the prompts in the SaveAs dialog box. So, we need to develop our own.

What we’re going to do is painting a popup window with required controls. We haven’t used a popup window till now. Now, you can see it in action. Paint a window as shown in the picture below. The controls are ( left top to bottom, right top to bottom ) Static Text control (st_file_name), SingleLineEdit control (sle_file_name), Static Text control (st_file_format), DropDownListBox (ddlb_file_format), CheckBox ( cbx_heading ). The control that is next to the sle_file_name is a PictureButton (pb_file_browser). The controls in the right side are cb_export and cb_cancel.

Disable the maximize, minimize and resize options of the window and set the window type as “popup” window and save the window as “w_export”.

Declare an instance variable called idw_to_save as a DataWindow.

DataWindow idw_to_save

Now, add the following code to the “open” event of this window:

// Object: w_export// Event: Open If IsValid( Message.PowerObjectParm ) Then   idw_to_save = Message.PowerObjectParm Else   Close ( This )End IfSetFocus( ddlb_file_format )Return

In the above code, we are expecting the DataWindow name that is supposed to be exported, as the parameter. When this window is opened using OpenWithParm() function, you can pass the DataWindow as the parameter in the function. As explained in the InterObject Communication” topic in the “MDI Windows” session, the parameters passed in this way are stored in the Message object. Later, we are setting the focus to the DropDownListBox to allow the user selecting the format to export.

Write the following code for the “clicked” event of the cb_file_name CommandButton.

// Object: cb_file_browser// Event: Clicked /* This script makes sure that user selects one of the file types andif selected, invokes a dialog box to accept the file nameAll the if conditions below simply display the selected typeof files in the Select File dialog box.*/ string lFileTypes, lFileExt, lFileName, lTitle1int lRetValue If Trim(ddlb_file_format.text) <> “” Then   lFileName = Right(ddlb_file_format.text,5)Else   MessageBox( “Info”, “Please select the file format,” + &      ” before you select the file name”, Information!, OK!,1 )   ReturnEnd If CHOOSE CASE lFileName      CASE “(CSV)”          lFileTypes = “Comma Separated Files (*.CSV),*.CSV”      lFileExt = “CSV”      CASE “(DBF)”          lFileTypes = “Dbase Files (*.DBF),*.DBF”      lFileExt = “DBF”      CASE “(DIF)”       lFileTypes = “DIF Files (*.DIF),*.DIF”         lFileExt = “DIF”      CASE “(XLS)”       lFileTypes = “Excel Files (*.XLS),*.XLS”         lFileExt = “XLS”      CASE “(WKS)”       lFileTypes = “Lotus 1-2-3 Ffiles (*.WKS),*.WKS”         lFileExt = “WKS”      CASE “(SLV)”       lFileTypes = “Multiplan Files (*.SLV),*.SLV”         lFileExt = “SLV”      CASE “(SQL)”        lFileTypes = “SQL Files (*.SQL),*.SQL”         lFileExt = “SQL”      CASE “(TXT)”        lFileTypes = “Text Files (*.TXT),*.TXT”         lFileExt = “TXT”      CASE “(HTM)”        lFileTypes = “HTML Tables (*.HTM),*.HTM”         lFileExt = “HTM”      CASE “(PSR)”        lFileTypes = “PowerSoft Report (*.PSR),*.PSR”         lFileExt = “PSR”      CASE “(WMF)”        lFileTypes = “Windows Meta Files (*.WMF),*.WMF”         lFileExt = “WMF”END CHOOSE lRetValue = GetFileSaveName(“Save file as”, sle_file_name.text, lTitle1, &      lFileExt, lFileTypes) If lRetValue = 1 then cb_ok.enabled = TRUE

We simply check which file type the user selects from the DropDownListBox and call the GetFileSaveName() function. This function invokes a standard dialog box, to allow the user to select or supply a file. It takes the following five parameters:

  1. Heading to the dialog box
  2. Variable to store the user selected file name with full path in the dialog box
  3. Variable to store the file name returned
  4. Type of files to display
  5. Default file extension to add to the file if the user neglects to provide this

If the user supplies a valid filename, we enable the Export CommandButton. The code for this control’s clicked event is:

// Object: cb_export// Event: Clicked/* Basically, this script uses the SaveAs() function. Depending on the format type user selects, the appropriate Enumerated file type is supplied to the function. You cannot use macro like in dBase/FoxPro. That’s why the script looks very big for a simple thing. */ boolean lSaveHeadingsInt lUserAnswerString lFileName, lFileExt lFileName = Trim(sle_file_name.Text)lFileExt = Upper(right(ddlb_file_format.text,5)) If lFileName <> “” thenIf FileExists( lFileName ) then        lUserAnswer = MessageBox( “Warning!”, lFileName + &               ” already exists.” + “~r” + &                “Do you want to override the existing file? “, &               StopSign!, YesNo!, 2)        If lUserAnswer = 2 Then ReturnEnd If If cbx_heading.checked then        lSaveHeadings = TRUEelse        lSaveHeadings = FALSEEnd IfIf lFileExt = “(CSV)” then        idw_to_save.SaveAs( lFileName, CSV!, lSaveHeadings )elseif lFileExt= “(DBF)” then        idw_to_save.SaveAs( lFileName, dBASE3!, lSaveHeadings )elseif lFileExt= “(DIF)” then        idw_to_save.SaveAs( lFileName, DIF!, lSaveHeadings )elseif lFileExt= “(XLS)” then        idw_to_save.SaveAs( lFileName, Excel!, lSaveHeadings )elseif lFileExt= “(WKS)” then        idw_to_save.SaveAs( lFileName, WKS!, lSaveHeadings )elseif lFileExt= “(SLK)” then        idw_to_save.SaveAs( lFileName, SYLK!, lSaveHeadings )elseif lFileExt= “(SQL)” then        idw_to_save.SaveAs( lFileName, SQLInsert!, lSaveHeadings )elseif lFileExt= “(TXT)” then        idw_to_save.SaveAs( lFileName, Text!, lSaveHeadings )elseif lFileExt= “(HTM)” then        idw_to_save.SaveAs( lFileName, HTMLTABLE!, lSaveHeadings )elseif lFileExt= “(PSR)” then        idw_to_save.SaveAs( lFileName, PSREPORT!, lSaveHeadings )elseif lFileExt= “(WMF)” then        idw_to_save.SaveAs( lFileName, WMF!, lSaveHeadings )end ifClose ( Parent )end if

This script makes use of the SaveAs DataWindow function. Depending on the selected file format, it calls the SaveAs with appropriate enumerated data type. If the user has selected the Save Headings also checkbox, we also set lSaveHeadings to TRUE. This causes the function to include the headings with the data.

The Close button simply closes the window:

// Object: cb_close// Event: Clicked close( Parent )

Want to see this in action? Open the w_product_master window and comment all the code for the “ue_export” event and write the following code and run the application.

OpenWithParm( w_export, dw_product )Return 0

Print Preview – Zoom Utility

We’ve already added some code to implement the Print Preview function, but as you will remember, it wasn’t very useful – all it did was format the object ready for printing; no other functionality was provided.

We can improve on this by allowing the user to zoom in or out on the object when they Print Preview. As our user interface is now becoming much more graphic based, let’s use a scrollbar to implement this extra feature.

Paint a child window as shown in the following picture. Disabling the control menu, the resize, maximize and minimize buttons. At the top of the window we have two StaticText controls with “40%” and “200%” as the text. The control below the StaticText controls is a Horizontal Scrollbar, leave its name to the default. The rest are two CommandButtons, “cb_print” and “cb_close’. Save this window as “w_print_preview_zoom”.

Declare an instance variable called iDw of type DataWindow.

DataWindow iDw

Write the following code to the window’s open event.

/* This script sets the minimum and maximum values for the horizontal      scroll bar, takes the print preview zoom percentage of the datawindow through Describe function and sets the position of the scroll bar to the same position.*/ Integer lDwZoomiDw = Message.PowerObjectParm If NOT IsValid( iDw ) Then Close( This ) iDw.Modify( “datawindow.print.preview=yes” )lDwZoom = Integer( iDw.Describe( “datawindow.print.Preview.Zoom”))hsb_1.MinPosition = 40hsb_1.MaxPosition = 200hsb_1.Position = lDwZoomiDw.Modify(“datawindow.Print.Preview.Rulers=yes”)

The comments in the code explain exactly what this does. The final line turns on the rulers for print preview.

There are five specific events associated with a scrollbar, as depicted in the following table. These events are executed depending on what the user does or where he clicks.

 

Event When It Occurs
lineleft Occurs when the user clicks on the left arrow
pageleft Occurs when the user clicks between the current position and the left arrow
move Occurs whenever the user drags the current position indicator
pageright Occurs when the user clicks between the current position and the right arrow
lineright Occurs when the user clicks on the right arrow

The code for each of these is fairly simple. First, the lineleft” event:

/* This event occurs when you click on the left arrow of the scrollbar. You need to set the position of the scrollbar and take care when the position goes below the minimum position and change datawindow print preview zoom percentage accordingly*/ // Object: hsb_1// Event: LineLeft String lArg1This.Position = This.Position – 1If This.Position < This.MinPosition then This.Position = This.MinPositionlArg1 = “datawindow.Print.Preview.Zoom=” + String( This.Position)iDw.Modify( lArg1 )

We decrease the current position of the scrollbar by one unit and use Modify() to alter the print preview zoom value. Note that the above script is making sure that the zoom value doesn’t exceed the minimum value.

// Object: hsb_1// Event: LineRight String lArg1This.Position = This.Position + 1If This.Position < This.MinPosition then This.Position = This.MinPositionlArg1 = “datawindow.Print.Preview.Zoom=” + String( This.Position)iDw.Modify( lArg1 )

The code for the “pageleft” event is exactly the same, except that we move the current position by five units, as opposed to one:

// Object: hsb_1// Event: PageLeft String lArg1This.Position = This.Position – 5If This.Position < This.MinPosition then This.Position = This.MinPositionlArg1 = “datawindow.Print.Preview.Zoom=” + String( This.Position)iDw.Modify( lArg1 )

The amount by which you move the current position is entirely optional.

The scripts for “lineright” and pageright” are exactly the same, except that they increase the current position by five and check that we don’t go over the maximum value.

// Object: hsb_1// Event: PageRight String lArg1This.Position = This.Position + 5If This.Position < This.MinPosition then This.Position = This.MinPositionlArg1 = “datawindow.Print.Preview.Zoom=” + String( This.Position)iDw.Modify( lArg1 )

The code for the “moved” event is slightly different, but simpler than its cousins:

/* Sets the print preview zoom percentage to the position of the horizontal scroll bar */ String lArg1lArg1 = “datawindow.Print.Preview.Zoom=” + String( This.Position)iDw.Modify( lArg1 )

This code simply sets the print preview zoom property to the value of the current position of the scrollbar.

Finally, we want to actually be able to print the DataWindow once we’ve previewed it, so we add the following code to the Print button:

// Object: cb_print// Event: Clicked idw.Modify( “datawindow.Print.Prompt=yes” )idw.Print( TRUE )

When the user close this window, we need to set the DataWindow back to normal, i.e., not in print preview mode. Write the following code to the clicked event of “cb_close” CommandButton.

//Object: cb_close// Event: Clicked iDw.Modify( “datawindow.print.preview=no” )Close( Parent )

Want to see this in action? If you already don’t have, add a menu item “Print Preview Zoom” just above the “Print Preview” under the “File” menu bar option in the “m_sheet_menu” menu. Write the code to trigger the “ue_print_preview_zoom” event in the active window. Declare “ue_print_preview_zoom” user-defined event and write the following code:

OpenWithParm( w_print_preview_zoom, dw_product )

Run the application and test it. Working? Use it in your real applications. You pay No Royalities. But, you need to help us with a small thing. Right now, “40%” and “200%” is hard coded in the code. Make changes to set these values any number the user-developer of this utility wants. That’s your exercise

Drag and Drop

Drag and Drop functionality allows you to create more user-friendly interface. With this interface, instead of selecting a series of menu options, user can drag the object and drop on to the required object. For example, to print a document, user can drag the document and drop on the printer icon, which will get printed. Similarly, dragging and dropping the same document on a recycle bin will delete the document. Whether you wrote the code to provide drag-n-drop functionality or not, you are using this in your daily life. Simple, example would be the File Manager. What you do to copy files from one drive to another drive. You simply select the files and drag them to the target drive. That’s all.

If you do drag and drop files from one directory to another which are on the same drive, Windows will move the files instead of copying. The solution to this problem is, press the Ctrl key and hold it down as you drag the files.

If you drag and drop files between directories under Windows ’95, the files won’t get copied, instead, Windows creates links to the files in the target directory. You need to copy and paste using the right mouse button menu.

Drag-n-Drop Events

Can I drag any PowerBuilder object? No. All descendent objects to the “DragObject” are draggable. Objects that are inherited from “Draw” object, such as line, circle, rectangle, etc, do not have drag-n-drop functionality. All draggable objects have the following events:

DragEnter: This event gets fired in the target object, as soon as dragged object enters the target object boundaries. What you can do in this event? You can write code to check the object type & class that is being dropped and change the icon to stop icon if the dragged object is not allowed to drop.
DragWithIn: As long as the user keeps dragging the dragged object within the target object boundaries, this event keep firing in the target object. The need of writing code in this event is very rare.
DragDrop: When the dragged object is dropped on the target object, this event fires in the target object. As soon as the user drops the object, drag-n-drop cycle ends. Most of the coding in the drag-n-drop functionality, you need to write here. First thing in the code you need to do after checking for the object type & class name is to change the drag icon to normal mouse pointer, so that user knows drop part is done from his side. If you are doing any heavy duty processing in this event, change the mouse pointer to “HourGlass” and set it to normal when the processing is done.
DragLeave: If the user drags the dragged object out of the target object boundaries without dropping, this event fires in the target object. Typical coding in this event is, changing the icon to the one which it was while entering into target object boundaries.
BeginDrag, BeginRightDrag: These two events are available only for ListView and TreeView objects. These events occur when the user presses the mouse button and drags. All the above mentioned drag related events are fired only when the dragging is enabled (we will explain about enabling drag functionality in a moment). But, these two events are fired whether or not dragging is enabled.

 

Drag-n-Drop Attributes

There are two attributes related to drag-n-drop functionality. One is “DragIcon” and the other one is “DragAuto“.

DragIcon specifies the icon to display when the object is in drag mode. Remember, in the PowerBuilder sort criteria dialog box, when you drag a column to drop on the criteria box, PowerBuilder displays a small rectangle along with the mouse pointer. That small rectangle is the drag icon.

What if I don’t specify any drag icon? Well, when the object is put in the drag mode, PowerBuilder displays the outlines of the object, rectangle, as the icon along with the mouse pointer. How big the icon would be? It’s exactly the same size of the object. If you drag a DataWindow control that has no DragIcon specified, you will see the icon equivalent to the size of the DataWindow control.

Where can I specify the DragIcon? You can specify in the painter, or in the script. When you go to the properties dialog box to any window control that is inherited from the “Drag” object, you will see “Drag Drop” tab page.

Drag & Drop properties Dialogbox for DataWindow Control.

You can use from the existing PowerBuilder’s stock icons, such as Rectangle!, StopSign!. These stock icons are part of PowerBuilder DLLs, so when you deploy the application you don’t have distribute these icons. With version 5.0, PowerBuilder is shipping a lot of icons and bitmaps and you can find them in the art gallery. If you have painted your own icons, you can also use them ( use the Browse button).

You can also change these icons at runtime in the script:

This.DragIcon = “c:\workdir\images\record.ico”
This.DragIcon = “record.ico”
This.DragIcon = “rectangle!”

When you use one of the PowerBuilder stock icons, you need to suffix the icon name with the exclamatory mark “!” as shown in the above example. If you want to paint icons/bitmaps on your own, you don’t have spend money to purchase painting tools. PowerBuilder comes with “Watcom Image Editor” which is really a lot powerful than PaintBrush.

DragAuto is a boolean attribute. If this attribute is set to true, PowerBuilder displays the drag icon as soon as the user clicks on the object and user can start dragging the object. Don’t set this option to true for objects like DataWindow controls when they are designed for data entry. Why? Because, as soon as the user clicks on the DataWindow, the object is set into the drag mode and the code that you wrote for the Clicked event won’t get executed. That’s why, start the drag mode in the script in the appropriate event as shown below:

This.DragAuto = True

 

 

About pbgeeks 11 Articles
PBGeeks is a website for PowerBuilder Professionals to come together and explore the new features being released in PB. Participate in the Forum and exchange your thoughts with the other PB Gurus. Job seekers can find plenty of most recent jobs all around the world in the Jobs Listing section. Employers can post their job openings to be made available to the entire PB community.