I've heard it said that PowerBuilder isn't going to survive the competition from other software development tools. I, like many of my friends who are software developers, can't afford to ignore such rhetoric and I continually upgrade my knowledge of other languages.

I'm impressed by Java and its simple way of doing some complex tasks and enforcing object-oriented practices when writing code. One of the areas Java makes simple is that of creating threads to run asynchronously while other code is executing.

Recently I took an assignment to help a company upgrade their Sybase database from an older to a newer version. This company had many PowerBuilder applications and so required a person with a strong knowledge of PowerBuilder as well as databases.

Like most systems that have been around for a number of years, this company's database system had many problems that needed to be solved to be successfully upgraded. For starters, database information was transferred to remote sites using ISQL (a SQL Anywhere application) to execute SQL scripts that dumped data from the database into ASCII CSV files. The ASCII files were then sent to remote sites where other SQL scripts were executed to load the data into a local database.

Ideally, everyone involved would have loved to eliminate the aging process with a more modern and elegant solution, but the reality was that the process was in place and had grown quite large, and the company relied on it to execute weekly. The time it took to finish executing was about 12 hours and involved dozens of SQL scripts. After studying them, it became obvious that, due to database version differences, some SQL scripts wouldn't work. The easiest solution was to create a PowerBuilder application to work in addition to the ISQL script processing. The new system needed to execute a Custom Class method that ran a DataStore to modify data along with the ISQL application being called from PowerBuilder to handle the remaining SQL script processing.

The Problem
That solution would have been sufficient to upgrade the database, but I was far from satisfied with the way it accomplished the task. For one thing, once the process started, the application almost never had a chance to repaint its screen while ISQL was executing. This was similar to executing a dw.retrieve() that waits for the database to return its data.

The next issue was that there was no way to interrupt the process and restart it where it left off. I recalled from my Java experience that Java threads are dependent on the underlying operating system for the way in which they're executed. PowerBuilder is the same in that it must adhere to the rules of the underlying operating system.

The Solution
As PowerBuilder improves, the number of programming tasks it can accomplish is quite impressive. SharedObjectRegister and SharedObjectGet provide the mechanism whereby we can create a separate runtime session that "waits" for ISQL to accomplish its task and relieve the main visual application, or Parent, of "locking up," not repainting its screen, and becoming useless until ISQL finishes.

A Timing object was used to start the ISQL process, start the next ISQL process (as indicated by a DataWindow control listing all the SQL filenames to process), and monitor the user's request to interrupt the entire process.

The important objects for this design were:

1. The Shared object (n_cst_exe_call): This Custom Class object required the functionality that started the ISQL process, waited, and notified the parent process of its state.

2. The Parent process (the main session): This created the Shared object and called the Shared methods.

3. The Interface object (n_intfobj): This Custom Class object was the tie between the Parent and Shared object. It's important to understand that the Shared object existed in its own runtime session. Think of the Shared object as a separate application running in the computer's memory. Passing a reference of the Interface object into the Shared object allowed the Shared object to report its state back to the Parent. It was also critical that the Shared object touch nothing of the Parent's or else the system would break. Interestingly, this includes global functions and structures. If you need a structure, then declare it within the Custom Class itself.

4. The Timer object (n_timer): This Standard Class object periodically checked the state of the Shared object via the Interface object and called the Shared object methods.

Details of the Solution
All the objects are normal Custom Class objects except the Window and a Timer object. To get a Timer object for your own use you need to select the Standard Class object menu option and then "timing" (see Figure 1).

The Interface object and the Timer object are created in the open event of the application window, w_genapp_sheet.

nvo_timer = CREATE n_timer
gnv_datatransfer.inv_Intf = CREATE n_IntfObj
// Interface reference is kept in this NVO

Since the Timer object calls the Shared object methods, the constructor event of the Timer object is used to register the Shared object.

ErrorReturn er
er = SharedObjectRegister("n_cst_exe_call",
"n_cst_exe_call_thread") if er <>
Success! and er <>
SharedObjectExistsError! then
messagebox("Error","SharedObject
Register failed")
return
end if

The Timer object has the instance variable:

n_cst_exe_call invo_cst_exe_call_thread

This variable is assigned a reference to the Shared object in the constructor of the Timer object:

er = SharedObjectGet("n_cst_exe_call_
thread", invo_cst_exe_call_thread)
if er <> Success! then
messagebox("Error","ShareGet failed")
return
end if

The user will start the process by clicking the Run button on w_genapp_sheet, which will call a method on the Interface object called of_start_run(). Basically, this method starts the Timer object:

gnv_datatransfer.inv_Intf.iwin.nvo_
timer.Start(5) //5 second delay

The timer event of the Timer object periodically checks the state of the Shared object via the Interface object using the code:

if gnv_datatransfer.inv_Intf.ib_running then
return
end if

This instance variable is declared in the Interface object: boolean ib_running. The Parent Timer checks this flag to prevent multiple ISQL sessions running simultaneously (see Listing 1). The timer event also checks to see if the user clicked the Stop button:

if gnv_datatransfer.ib_stop then ....

But since ib_running takes precedence, the ISQL process will complete first.

In this listing the filename to process is taken from the DataWindow control:

ls_filename = gnv_datatransfer.iwin.dw_files.
getItemString(i, "filename")

"i" is determined by the instance variable i = inv_Intf.il_dw_file_row, which keeps track of the file we are processing.

Then the Shared object method is called:

invo_cst_exe_call_thread.POST
of_execute_sql(ls_filename, gnv_datatransfer.inv_Intf)

Notice that the Shared object method is POSTed. This allows the Parent process to continue and lets the Shared object execute its long-running processing independently. Note also that the method doesn't return anything. Even if it did, the return would effectively be ignored.

When the Shared object starts to execute of_execute_sql(), ib_running is set to true; when it completes, ib_running is set to false.

anv_intfobj.ib_running = true

The Shared object uses only the reference to the Interface object to refer back to the Parent application. Of_execute_sql(), besides all the logging and state-flagging tasks, has basically one method it needs to call:

li_rc = of_runandwait( ls_runString, anv_intfobj)

"ls_runString" is a string variable that's constructed to indicate the application and filename to process. This string and the reference to the Interface object are passed into of_runandwait(), which calls a Win32 API function that executes the ISQL application. The Shared object contains all the information necessary to do this: the Local External Function declarations:

FUNCTION long CreateProcessA( string AppName,
string CommLine, long l1, long l2, long binh,
long creationflags, long l3, string dir, str_
startupinfo startupinfo, ref str_
processinformation pi ) library
'kernel32.dll'

FUNCTION long WaitForSingleObject
( ulong ul_Notification, long lmillisecs )
library "kernel32.dll"

as well as the structure declarations:

str_processinformation and str_
startupinfo that CreateProcessA uses

The structures are declared locally, as mentioned previously, or else of_runandwait will cease processing as soon as the Shared object uses them. Using WaitForSingleObject, we can wait until ISQL has completed, which is accomplished by passing the constant INFINITE to the function (see Listing 2).

Conclusion
SharedObjectRegister and SharedObjectGet offer some unique programming solutions. Rather than force the user to wait for a database to return a long-running query, these functions can be used to execute the queries while the user continues working. Then, after the query completes, the user can be notified and shown the report. The PowerBuilder documentation also states: "You can use a shared object on a PowerBuilder client to simulate an asynchronous call to EAServer."

As a consultant I can't predict all the different applications I may encounter at a client site. PowerBuilder gives me confidence in that I know I have the tools to meet my needs. If it doesn't have a function built in, it gives the developer a way to get at extra functionality, such as Win32 API calls.

The source code is included in a PowerBuilder library called datatrans.pbl (see the PBDJ Web site) and was compiled with PowerBuilder 8.

• Figure 1
• Source Code