Since version 9, PowerBuilder has provided the ability to create front-end clients functioning in a service-oriented architecture application via Web service interface technology. Originally the ability was based on the open source EasySOAP driver. In version 10.5 it was expanded to use a .NET 2.0 compatible driver, and in version 12.0 .NET SOA interfacing was made compatible with the latest MS WCF technology. In addition, in version 11.0 it became possible to create a DataWindow object from a Web service data source definition. Recently I used PB 12.0 to create four versions of a SOA client: Classic Win32, .NET WebForm, .NET WinForm, and WPF .NET. All business logic and data access was provided by a set of middle-tier services hosted in an MS server. In this article I'll focus on one aspect of that development effort - a comparison of several techniques for joining values from multiple Web service calls into one DataWindow result set.

Background
The nature of remote services is that they are frequently under the control of a different IT group than the client developer. The client developer often does not have the ability to adjust the structure or contents of returned remote method value objects. The developer must "make do" with the values as they are returned from remote method calls and work around any roadblocks or limitations the data structures place in his way. This situation, in a way, is analogous to the one faced by any developer working with legacy data storage mechanisms. The structure of the data in the tables often doesn't fit the needs of the client presentation. Fortunately, with relational DBMS, the developer can adjust SQL Select statements to retrieve needed columns by leveraging SQL's ability to form relations among tables. In the PowerBuilder universe this is true for SQL Select DataWindows, where the developer has control over the client data source SQL. In the case of Web services, however, there is no specification for joining multiple Web service calls together to find a single unified result set. A DataWindow can only be built from the definition of a single service method and the client developer can't change the contents of the return.

There is SQL feature that DataWindow developers often leverage to allow them to add outside values to a DataWindow buffer. SQL allows you to define virtual or computed columns in a column list. The DataWindow will recognize these columns and allocate client-side buffer space for their values. A developer can "manually" populate these columns from an outside source using SetItem(  ) calls or .Object expressions. However, with Web service data source DataWindows, the result set contents are fixed based on the return values described in the service description (WSDL or SVC) document. The developer does not have the ability to add or delete "virtual" columns in the buffer. Interestingly, with an External DataWindow Object the painter allows the editing and adding of columns on the column specification view. Not so for the Web service DataWindow; the painter does not allow you to add or delete column definitions. What is a developer to do if he needs to present values from multiple WS calls in a single DataWindow presentation? There is no place in the buffer to put the additional values!

I'm going to describe how I approached this problem. One caveat: any solution must leverage the native abilities of PowerBuilder DataWindow Technology and not involve writing lots of ugly code to compensate for what appears to be a shortcoming in the platform's abilities. In the case of joining result sets, one ugly code-heavy solution would be to define an external data source DataWindow and manually populate in PowerScript from the return values of two separate Web service calls (3 DataWindow solution). Hey wouldn't it be nice to have a DWQL scripting language that would allow the joining and relating of DataWindow buffers?

The Use Case
Here's the scenario I was working with. The majority of the result set in the Portfolio display is directly returned from a single Web service call. I implemented it using a WS DWO. Figure 1 shows the finished DWO with the problematic items highlighted. The current price for each holding must be obtained from a second service call. The current price is also an input to the Market Value and Gain (Loss) computed object expressions. Figure 2 shows the methods that need to be called to get the needed values. Figure 3 shows the returned value objects and their data properties. In case you are unfamiliar with it, please note that the WS DataWindow automatically translates a returned array of value objects into a DataWindow result set.

Figure 1: Portfolio DataWindow

Figure 2: Service Methods

Figure 3: Returned Value Objects

Approach One: Computed Expression Calling a Global Function
The first approach involves placing a computed object in the DataWindow presentation whose expression called a global function wrapping a call to a Web service. This solution fit my criteria in that it was a PowerBuilder-esqe approach that required minimal coding. Testing revealed that although the solution worked, in that correct results were returned, performance was slow. A bit of debugging pointed to repeated service calls on each row. The culprit: the global function calling expression (Current Price) was referenced by other expressions (Market Value and Gain/Loss). Seems like the expression engine wants a fresh value every time an expression is referenced so it called the global function each time. Obviously, for performance reasons, I abandoned this approach. However, I still think the approach is valid when the expression is not referenced elsewhere within the DataWindow object.

Approach Two: Hacking the DataWindow Column Definition List
This second approach came about in a flash of innovation when studying the column list in the 12.0 Classic WS DataWindow Painter. Figure 4 shows the column list compared to the returned value object property definition.

Figure 4: Column Specifications vs. Bean Properties

You can readily notice the highlighted columns in the DWO that are not in the returned bean. For every Out parameter, the DWO definition tool added an additional "throw away" column of type long; HoldingID got HoldingIDspecified, etc. I coupled this fact together with two other facts. One, in the Classic Web Service DWO Painter both column names and data types are editable on the column specification view. Two, the numeric value I would be working with would never exceed the storage capacity of a long. What I did was to hijack (edit) the column name and type to suit my needs. This little hack gave me buffer items whose values I could set using standard SetItem( ) method calls. Figure 5 shows the result of my hacking.

Figure 5: Hacked Column Specifications

Figure 6 shows the loop in which the items are populated with return values from the Web service call.

Figure 6: SetItem from WS call return value

Conclusion
SOA applications are slowly becoming the vogue in business application software. PowerBuilder provides quality tools and constructs that enable you to rapidly construct effective service clients. Although the technology and tools are still evolving, the persistent developer can discover workarounds to create quality applications.

As with all things, if you keep these three principles in mind, you can always find a proper and effective solution to a programming problem:

1. The first solution is not necessarily the best one
2. A proper solution fits the personality of its environment
3. If at first you don't succeed, keep trying!