Leveraging Oracle Power Objects 2.1

Client/server visual applications

Douglas is a senior scientific programmer for MDL Information Systems, and is project leader for MDLSCREEN. He can be contacted at [email protected].

High-throughput screening (HTS) is an automated research technique being adopted by pharmaceutical and biotechnology companies. In a typical HTS scenario, one or more robots handle and track specific tests (assays) of various chemical compounds (samples), associate the resultant data with the correct sample, then store that information in an efficient manner. In a laboratory, 10,000 samples may be tested across seven different assays by a robot operating continuously for a week. The immense amount of information being analyzed in a relatively short time clearly presents a significant data-management challenge. Addressing such challenges, MDL Information Systems (the company where I work) developed MDLSCREEN, an Oracle-based client/server data-management system for automated drug discovery and high-throughput screening.

Storing the enormous amounts of scientific data generated by HTS experiments is just the first step, however. The most important task in automated drug discovery is identifying which samples exhibit a desired behavior. Therefore, the data-management system needs to support powerful data-analysis capabilities, too. It is also essential to perform complex calculations based on experimental and control data, then represent this information so that researchers can identify patterns using a graphical user interface (GUI). Approximately 50 percent of the scientists in HTS laboratories are Macintosh users, so finding a cross-platform (Windows and Macintosh) client development tool was important. For this reason, we selected Oracle Power Objects (OPO).

OPO Version 2.1 is a visual client/server development tool that focuses on database integration, creating reusable components visually, and building database applications using drag-and-drop techniques. OPO is available in both professional and client/server editions. Among other features, the Professional Edition supports Basic, OLE, and Netscape's Plug-in architecture for intranet-enabled applications. The Client/Server Edition provides additional support for ODBC and SQL*Net database connections.

Compared to two-tier client/server development tools such as PowerBuilder 5 or Delphi 3, OPO 2.1 is still the new kid on the block. Like those tools, OPO 2.1 builds 32-bit Windows 95/NT applications. Unlike them, it also targets native Power Macintosh applications with little or no modification to existing code. 16bit Windows and 68K Macintosh are also supported. Version 2.1 also offers ODBC support and a new high-performance local database. The design environment offers a Windows Explorer-like object browser, in addition to a number of new built-in objects, properties, and methods. These features, combined with OPO's existing visual object orientation, drag-and-drop GUI construction, and tight database integration, make it ideal for cross-platform client/server rapid application development (RAD).

Visual Object Orientation

As Figure 1 illustrates, OPO's visual-programming paradigm is similar to Visual Basic, with an object containment hierarchy. Forms, reports, and classes are containers into which controls such as fields or scrollbars, or other containers (such as radio button frames or rectangles) can be added using drag and drop. Classes are actually a special type of form, instantiated by dropping the icon representing the class into another container. Properties and methods of the class are inherited as expected, and can be overridden. While it is possible to add new methods and properties to a class, they only exist within the framework of the class container. This can be a little frustrating at first, but the benefit is straightforward visual object orientation.

While the visual-programming approach is useful for RAD tools, it poses a problem for generating meaningful code listings. Method code is spread among various objects, which also are created by a series of drag-and-drops. For instance, connecting to a remote server might be accomplished by method code like Listing One However, creating the referenced fields, radio button frame, and so on, requires a complex series of drag-and-drop actions and tweaking of object properties.

The Session Object

OPO uses a session object to establish a database connection. The method in Listing One sets the RunConnect property of the session object to the proper connection string for an ODBC or SQL*Net connection. The Connect() method attempts the connection, and the IsConnected() function returns the connection status. Expanding the session object in the Designer object browser displays database objects (tables, views, and so on), which can be further expanded to view the constituent columns and their datatypes; see Figure 2. Icons of tables can be dropped into container objects (a form, for example) to create fields that are bound to the columns in the table through an intermediary object called a recordset.

The recordset Object

The recordset object works behind the scenes as a transaction buffer, allowing sophisticated client-side data manipulations without having to create in-memory data arrays. Containers such as forms, embedded forms, and classes associated with a specific table or view (by dropping a table into the container or by manually setting the RecordSource property) will have an associated recordset object automatically created at run time. A display control (such as a field object) is considered "data aware" if it can be bound to a specific column in a recordset (specified by its DataSource property). The DataSource is essentially a pointer to part of the recordset. The recordset usually contains a subset of rows from the bound table, automatically buffered as needed for display or update. Listing Two shows how to access data in a recordset.

The function udmFindString() takes the parameters:

• pRecordSet, an object reference to a recordset.
• pColumnName, a string defining the column name in the table bound by the recordset.
• pString, a string defining the value to search for.

The OPO convention for returning a value from a function is to set the function name to the return value. GetRowCount(), SetCurRow(), and GetColVal() are standard methods of a recordset. The recordset is a powerful OPO feature. Manipulating data within the recordset avoids creating unwieldy data arrays. In addition, display controls bound to the recordset are automatically updated as needed when the recordset is modified.

Bound Controls

The field object is one type of bound control. OPO 2.1 also adds the grid control to OPO's tool palette. Familiar to Visual Basic programmers, the grid control displays data in a spreadsheet-like grid of resizable columns (see Figure 3). Additionally, a number of data-aware ActiveX display controls ship with OPO 2.1. However, they will only allow 32-bit Windows applications to be built, which defeats one of OPO's strongest advantages -- cross-platform portability. (OPO's planned support of OpenDoc for cross-platform component extensibility never materialized; OLE and ActiveX are currently Windows-only features.) Luckily, most programming needs can be met using the built-in OPO display objects.

Cross-Platform Support

Cross-platform portability is a goal of most client/server developers. Tools like Delphi do offer impressive performance and power, but only for Windows. OPO applications, on the other hand, can be created as:

• Platform-independent bytecode (similar to Java), which requires a run-time engine; or as
• Stand-alone executables, which are actually the bytecode bundled with the run-time engine for the desired platform.

OPO performs a sort of just-in-time (JIT) compilation for both bytecode and stand-alone applications.

There are many programming issues to keep in mind if an OPO application is to be successfully deployed for both Windows and Macintosh environments. OPO 2.1 has the ability to set the default unit of measurement (inches or pixels) required by the specific platform. (OPO 1.0 defaulted to inches instead of pixels, which caused undesirable scaling effects when designing for Windows versus Macintosh. Font scaling continues to pose a problem.) Additionally, OPO 2.1's DECLARE syntax allows calls to Windows DLLs or Macintosh resources to be sensitive to the compilation or run-time environment.

Still, OPO 2.1 clearly favors Windows development. Perhaps this is to be expected, since OPO is fashioned after Visual Basic. Nevertheless, OPO applications developed on the Macintosh generally look better under Windows than those developed under Windows and run on the Macintosh. As long as the use of OLE and ActiveX controls is avoided, the cross-platform challenges in OPO are mostly cosmetic.

Using the Designer

Like many client/server development tools, OPO is focused primarily on client-side GUI building to create a database front end. While OPO provides graphical tools for creating and managing database objects such as tables, views, and indexes, you will still need to resort to DDL scripts for creating referential constraints, check constraints, and the like. Where OPO really shines is in the rapid creation of highly functional interfaces and the ability to easily create reusable objects (using classes) that can be shared across applications.

While drag-and-drop will take you a long way in building simple forms (binding controls to database objects), more sophisticated forms may require adding user-defined properties or methods to various objects within your application. For example, the built-in OPO field object can be extended to automatically resize itself to fit its container by adding a user-defined method udmResizeToContainer(). Putting this field in a class allows the additional functionality to be reused. Since classes in OPO are themselves container objects, the class and the field must be resized.

Listing Three shows example method code for udmResizeToContainer(). The newly created class can either be part of the OPO application file or part of an OPO library. The benefit of creating a class in a library is that it can be shared across applications. Moving a class from an application to a library, or vice versa, is performed via drag and drop.

Automating the Build Process

With OPO 2.1, Windows versions of the OPO Designer act as an OLE automation server. This allows OLE automation clients to drive the application building process. For example, Oracle's Designer/2000 contains a code generator that will automatically build OPO forms based on the table definitions and design preferences stored in its repository of database objects. One added benefit of this approach is a consistent, standardized UI. Manual additions can then be made to the generated application.

Web Deployment

OPO 2.1 also includes a Netscape plug-in for running OPO applications within web pages. Currently only available for Windows 95, the plug-in allows OPO bytecode files to be downloaded from within an HTML document using the <EMBED> tag, similar to a Java applet. There is one caveat, however: Database connection is still performed via a SQL*Net connection, which limits deployment to mainly intranet solutions.

Notice that, when writing applications for web deployment, toolbars are not displayed, and you should avoid use of the TestCommand() method.

Less is More

One final note: recordset objects can help to greatly reduce the number of lines of code required, facilitating rapid development and reducing maintenance effort. One common mistake made by beginner OPO programmers is writing many unnecessary lines of method code instead of taking advantage of the methods already available in existing objects. You need to become familiar with the recordset object; it is one of the most powerful and time-saving tools in OPO. In addition, built-in support for creating and maintaining master-detail relationships and shared recordsets can potentially save hundreds of lines of code that would be needed for synchronizing data display.

Conclusion

The gradual movement toward n-tier client/server architectures and distributed objects could eventually leave two-tier development tools like PowerBuilder, Delphi, and OPO behind. While OPO 2.1 adds support for more client platforms, such as Windows NT, I suspect that future versions of OPO will add the Java Virtual Machine to the list. Oracle has already hinted that its Network Computing Architecture will support an OPO application cartridge, perhaps allowing OPO applications to run on an application server and display client interfaces using Java. In any case, OPO's cross-platform support, visual object orientation, and rapid development via drag-and-drop make it ideal for developing an HTS data-management solution.

Listing One

'Sub Click()' Connect to database using proper connection string syntax
' for ODBC or ORACLE connection as specified in the popup
' list "popConnectionType" with radio buttons for "ODBC" and
' "ORACLE" values, and using values from fields "fldLogin",
' "fldPassword", and "fldDatabase" on user click.
' ==========================================================


</p>
DIM vConnectString as String


</p>
SELECT CASE popConnectionType.Value
  CASE "ODBC"
    vConnectString = "odbc:UID=" & fldLogin.Value &      &
                     ";PWD="     & fldPassword.Value &   &
                     ";DB="      & fldDatabase.Value
  CASE "ORACLE"
    vConnectString = "oracle:" & fldLogin.Value &        &
                     "/"       & fldPassword.Value &     &
                     IIF( ISNULL( fldDatabase.Value ),   &
                          "", "@" & fldDatabase.Value    &
                        )
END SELECT
 
sesDatabase.RunConnect = vConnectString
sesDatabase.Connect()


</p>
IF sesDatabase.IsConnected() THEN
    frmMainMenu.OpenWindow()
ELSE
   MsgBox( "Unable to connect to database.  " &  &
           "Check login name, password, and database name." )
END IF


</p>
'End Sub Click()

Back to Article

Listing Two

'Function udmFindString( pRecordset, pColumnName, pString ) as Integer' Loop through each row in pRecordSet, checking if
' pColumnName is equal to pString.  Return the row
' if a match is found, or a zero when no match.
' ====================================================================


</p>
FOR vRow = 1 TO pRecordset.GetRowCount()
  pRecordset.SetCurRow( vRow )
  IF pRecordset.GetColVal( pColumnName ) = pString THEN
     udmFindString =3D vRow
     EXIT FUNCTION
  END IF
NEXT
udmFindString = 0
'End Function udmFindString()

Back to Article

Listing Three

'Sub udmResizeToContainer()' Set SizeX of field's class container and field itself to the number
' of pixels from class's PositionX and its container's SizeX.  
' ======================================================================
Container.SizeX = Container.GetContainer().SizeX - Container.PositionX


</p>
Self.SizeX = Container.SizeX


</p>
'End Sub udmResizeToContainer()

Back to Article

DDJ