PowerBuilder Authors: Chris Pollach, Yeshim Deniz, Jayaram Krishnaswamy, Kevin Benedict, Avi Rosenthal

Related Topics: PowerBuilder

PowerBuilder: Article

Web Enablement of DataWindows

Web Enablement of DataWindows

Let's face it - rewriting an existing application from platform to platform is not fun. But many businesses successfully served with PowerBuilder applications are rewriting parts of PB apps for the Web and maintaining the same functionality in PowerBuilder and Java, PowerBuilder and C#, etc.

Faced with this problem we decided to create a product called "XML Server Pages" that uses our DW2Java/DW2Net converters to automatically convert existing DataWindows into sets of J2EE or .NET objects - ready to run on the Web. For reporting applications of any complexity, including heavy financial calculations and nested and composite reports, it means 100% automatic deployment of the entire system on the Web for external users. For data entry systems, some additional browser-side scripting is required. Most important, though, you can maintain one set of DataWindows and use them in both native PowerBuilder applications and on the Web, whether you use IIS, WebLogic, WebSphere, or EAServer.

This article is about the problems we encountered and how we solved them. Hopefully, it will help you solve these problems too.

First Things First or How Does It Look?
DataWindows deployed with DW2Java/DW2Net converters on either J2EE or .NET servers look exactly the same as they do in PB. The only difference is that it's a browser-based application now, as shown in Figure 1 (although all browser specifics, including menu and toolbars, could be turned off).

This figure represents an HTML page displaying a report from the "employee" table of the sample database EASDemoDB. This page contains just one of 70+ controls available within the XMLSP Object Model - XMLControl. The following is the exact HTML of our page for Internet Explorer:

<HTML xmlns:xsp>
<?IMPORT namespace="xsp" implementation="/xmlsp/htc/XMLControl.htc"?>
<xsp :xmlcontrol id="dw_1" dataSource="d_employeebydepartment"
appURL="/Examples" />
<SCRIPT FOR="window" EVENT="onLoad">
dw_1.connection = "easdemo";

Let's take a closer look at this HTML. It contains a single instance of XMLControl named "dw_1":

<xsp :xmlcontrol id="xsp" dataSource=" d_employeebydepartment "
appURL="/Examples" />

As a PowerBuilder analog you can consider a window with the DataWindow control, associated with a d_employeebydepartment dataobject. The property dataSource points to a set of application server objects that are responsible for the population of the XMLControl dw_1. From the Web application standpoint, dataSource has pretty much the same meaning as a DataWindow's dataobject - you can dynamically assign data sources, for instance:

dw_1.dataSource = "d_employeebydepartment";

At any rate, dataSource drives the population of data buffers, setting up an initial filter and sort values, etc.

The actual data retrieval happens after the page is loaded. Script corresponding to the "onLoad" event invokes the retrieve() method:

dw_1.connection = "easdemo";

This looks exactly the same as one population of the DataWindow in the "Open" script of a PowerBuilder window:


The slight difference here is that the client/server transaction object is replaced with the name of the application server's connection pool.

Internet as Client/Server over the Web
If you compare a typical Web application with a PowerBuilder one, you're bound to notice that the Web application is based on the concept of fast-changing, simplistic HTML pages, while PowerBuilder apps are all about in-place data changes. Obviously, repainting the entire page is frustrating, that's why HTML frames and framesets were created. But would you accept as normal, for example, that the retrieval of the DataWindow causes the right half of your screen to blink and refresh? Why can images on the HTML page be loaded independent from the page itself, without the page changing?

The last question is not a rhetorical one. In fact, it holds the key to understanding our approach. Similar to the images that get a separate load after the hosting page is loaded, we make the data load inside the existing page via separate requests to the application server. The method "retrieve", mentioned earlier, accomplishes just that - it brings data and renders it within the "DataWindow" control.

Let's consider again the normal PB window with a DataWindow on it. First, it takes an Open() statement to bring a window up and paint it. Then, perhaps in the Open! script, the DataWindow performs database retrieval and paints its own area, not the entire window.

Either way, window or page information has to be retrieved first. In a PowerBuilder application, window information is invoked typically from a PBD hosted by the local hard drive, while DataWindow data gets delivered from the database server. In a Web app scenario, however, everything gets retrieved from the very same machine - the application server - in one long request. Ironically, the Web that started with a multitier paradigm for publishing offers a single-tier "terminal" model for application development.

Still, if we can make data-bound portions of the page paint themselves on request and independent from the rest of the page, and we can process changes accumulated on the page without refreshing the entire page - we'll stop treating the Internet browser as a "page turner". Rather, it becomes a virtual machine for the new breed of applications - distributed client/server over the Web.

Making a Distributed "DataWindow"
Let's illustrate how this vision can be made a reality. First we'll explain the inner makings of XMLControl, the XMLSP analog of the DataWindow. As a starting point, let's review the regular PowerBuilder DataWindow objects.

A DataWindow object consists of three major elements:
1.   A description of the result set - number, names, and data types of columns; a definition of the SQL statement of stored procedures to obtain the result set
2.   A set of visual controls bound to the columns of the result set - edit fields, dropdowns, checkboxes, etc., along with their attributes such as position, fonts, colors, and visibility
3.   A definition of groups and computed expressions for totals, etc.

We have to automatically "deploy" existing DataWindow reports and forms within Java or .NET servers off the original set of PBLs, so that single source can be maintained for client/server and Web applications.

The first dilemma is: Should you go the "PowerBuilder" way and build a monolithic interpreter of the metadata or generate a set of specific objects for each DataWindow?

For two reasons we've chosen the latter. First is performance. A generic interpreter would spend precious server resources to "if-then-else" multiple cases having nothing to do with a given DataWindow. On the contrary, generating a unique JSP per DataWindow, we ensure that only relevant code makes it to the specific JSP.

The second reason is developers' acceptance. Today's IT developers are not looking for closed solutions, such as a proprietary interpreter. After all, generated JSPs are readable, simple, and "source code". The same is true for client-side JavaScript or extensible stylesheets. Packaging server requests as SOAP messages and making server requests into Web services ensures the interoperability required by modern systems. On the other hand, a binary component on the server is a "black box". But wait, isn't this idea of deployment just another "black box" of the code generator? Well, we built the converter so it can be easily extended by application developers - and all the code generation templates are available in the source.

DW2JavaPro and DW2NetPro Conversion:
Idea and Implementation

To facilitate deployment of DataWindows as J2EE/.NET objects, we created a so-called DW2JavaPro converter. This PowerBuilder program allows the batch conversion of DataWindow objects directly into the appropriate folders of the application server (see Figure 2). The main considerations we had while building the DW2JavaPro converter were the flexibility and openness of the entire solution.

Here's what we did: inside the PowerBuilder code we first converted the DataWindow's metadata into XML, then using several preexisting XSL stylesheets we transformed this metadata XML into J2EE or .NET objects. In particular, we have JSP.XSL - a template - that helps us generate "data driver" JSP for each DataWindow.

In two-tier PowerBuilder, the actual data retrieval is done by the database driver software residing on the local hard drive of the user's workstation. In the Web world, database drivers are installed on the remote machine - the application server. Still, if we build a callable application server object that streams out the same data, that object will become a "driver", although a remote one. From this point on, there's no basic difference between the two-tier and the Web approach: the rest is painting the data and calculating the formulas, something we took for granted in PowerBuilder (we'll cover this a bit later).

What is important here is not the name, but the fact that JSP.XSL is a small, readable ASCII file that's the template of all your "driver" objects. What's best, such a file can be tuned to accommodate particular business requirements. Regularly, a "driver" JSP accepts retrieval arguments including a connection pool name, connects to the database, executes a query, packs the result set with "angle brackets," and streams out the result. But, suppose there are existing EJB objects or Web services that already produce the result set. In such a case, all we have to do is modify a handful of lines in JSP.XSL to, say, call these objects instead of doing JDBC calls. And, of course, we have to rerun DW2JavaPro with the new stylesheet.

XMLControl: DataWindows for Java and .NET
JSP is just one of the files generated during the DW2JavaPro conversion. After all, generated JSP helps bring the data to the browser, but this data has yet to be nicely formatted for presentation. This formatting of the XML data is a classic task of XSL stylesheets. In our case, all the information about fonts, colors, and the positioning of the DataWindow elements is captured in the XML metadata (reminder: originally extracted from the DataWindow object). Therefore, we've built another XSL stylesheet - XSL.XSL. Unlike JSP.XSL, which transforms metadata into JSP, it transforms metadata into a presentation XSL stylesheet. During runtime, this presentation XSL will, in turn, transform the output of the JSP into DHTML to be rendered by the browser.

Finally, in addition to JSP.XSL and XSL.XSL, we need FORMULA.XSL. Suffice it to say that this XSL stylesheet works with the same metadata to build JavaScript functions required to recalculate totals, averages, etc., whenever the base XML data gets changed. Let's review our original example:

<xsp :xmlcontrol id="dw_1" dataSource="d_employeebydepartment"
appURL="/Examples" />
<SCRIPT FOR="window" EVENT="onLoad">
dw_1.connection = "easdemo";
The dataSource property of dw_1 is set to d_employeebydepartment. According to the default directory structure set up by DW2JavaPro, this means /Examples/jsp/d_employeebydepartment.jsp is the URL for JSP and /Examples/xsl/d_employeebydepartment.xsl is the URL for the presentation stylesheet. Also, the JavaScript controller of formula calculations is located at /Examples/js/ d_employeebydepartment.htc. The entire "family" is coordinated by the JavaScript of the XMLControl. This is the "Model/View/Controller" - as it's known to Java fans - in action!

Data Buffers, Statuses, and Update Flags
As we've learned, a DataWindow deployed as an XMLControl is presented to the user as HTML dynamically made from XML within the browser. This method actually preserves all PowerBuilder buffers, row and item statuses, and update flags as XML attributes. Let's show how.

Each row in the XMLControl data model is presented via the x:band node. For instance, in the very simplified form, a row may look like the following:

<x:band name="Detail">
<x:col name="emp_fname">Frank</x:com>
<x:col name="emp_lname">Pollack</x:com>
<x:col name="salary">50000</x:com>

To distinguish rows that have been filtered out, have the "status" attribute on each row. It's either "Primary" or "Filtered". Accordingly, a more realistic layout of our sample row is the following:

<x:band name="Detail" status="Primary">
<x:col name="emp_fname">Frank</x:col>
<x:col name="emp_lname">Pollack</x:col>
<x:col name="salary">50000</x:col>

To distinguish rows that have been modified, we keep an "updateFlag" attribute on each row. It takes one of the four values: "NotModified", "Modified", "New", and "NewModified". If there's no "updateFlag" attribute, the row is considered "NotModified".

Accordingly, as rows get loaded into XMLControl's data model, they're considered "NotModified". Rows inserted via the insertRow() get marked as "New". Interactive or programmatic modification of an item on "New" or "NotModified" rows turns them into "NewModified" and "Modified", respectively.

The information about the previous value of the modified item, that is the value of the item as it was known before any modifications took place, is kept per item in a special "oldValue" attribute. Items without "oldValue" are considered "NotModified". Items with an "oldValue" are considered "Modified".

For instance, if a user types "55000" into a "salary" item of the XMLControl related to our example, it will result in the following changes on the data model:

<x:band name="Detail" status="Primary" updateFlag="Modified">
<x:col name="emp_fname">Frank</x:col>
<x:col name="emp_lname">Pollack</x:col>
<x:col name="salary" oldValue="50000">55000</x:col>

When it comes to generating SQL statements, "New Modified" rows contain data for INSERT statements, "Deleted" rows contain data for DELETE statements, while "Modified" rows contain data for UPDATE statements.

Assuming that _data points to XML data (MSXML2.FreethreadedDOMDocument), Listing 1 provides the JavaScript code that illustrates how simple it is to pick the "BLOB of changes" from XML.

Data Model on the Client: Advantages and Restrictions
Once we get all the data on the client within the browser, we can fully restore the capabilities of client/server, such as the disconnected sorting, filtering, and grouping of the data. Indeed, each presentation XSL contains the following condition:

<xsl:if test="(not(@updateFlag) or @updateFlag!='Deleted') and (not(@status)
or @status='Primary')">

Accordingly, the implementation of the filter is confined to changing the status in the target rows to "Filtered" and reapplying presentation XSL against modified XML. Sorting is even simpler, since it's all done by XSL. The regular setItem()/getItem() API is all about changing the values in XML nodes now.

Of course, there are some usability restrictions. For instance, in the original PowerBuilder window you could afford any number of nonvisual controls on the page. On the Web, to keep the page opening time within the canonical seven seconds, your page should drop nonvisual DataStores in favor of the server-side logic. In client/server, populating a scrollable list with tens of thousands of records was technically possible, although pretty cruel to the user. Don't even think of it on the Web. Construction of the large XML tree within the browser's memory will freeze your system.

The advantages of the data kept on the client are hard to overestimate though. To start, the client is capable of tracking changes. No need for the hidden form values, that typical HTML mess. More important, there's no need to keep the state on the server. As a reminder, keeping state on the server is the Achilles' heel of server-centric Web architecture, since to scale across multiple boxes it requires an independent resilient mechanism such as a database.

Summary: DataWindow Unlimited
DataWindow-related code and DataWindow objects represent the largest and most visible portion within any PowerBuilder system. This article demonstrated how XML and XSL techniques facilitate unlimited Web deployment of DataWindows on any application server - J2EE or .NET platforms. This approach for the Web enablement of PowerBuilder systems created a foundation for XML Server Pages and DW2JavaPro products.

More information and source code can be found at www.xmlsp.com.

More Stories By Victor Rasputnis

Dr. Victor Rasputnis is a Managing Principal of Farata Systems. He's responsible for providing architectural design, implementation management and mentoring to companies migrating to XML Internet technologies. He holds a PhD in computer science from the Moscow Institute of Robotics. You can reach him at [email protected]

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.