Details: Written by John Fauss; Category: PowerBuilder; Published: 29 December 2021; Last Updated: 04 January 2022; Hits: 28662

User Rating: 5 / 5

In this follow-up to the article titled “’Haunted Apps’ – How to Avoid Ghost (Unresponsive) Windows”, you’ll learn about the multi-threading capabilities available to PowerBuilder applications and how multi-threading can be used to perform tasks that typically freeze the application’s Graphical User Interface (GUI). Multi-threading can be utilized for many purposes, but this discussion will focus primarily on the use case of long-running data retrieval requests.

This article covers what you need to know in order to get started using multi-threading in PowerBuilder. A new example application that can be easily customized to test multi-threaded data retrieval in your development environment(s) is now available in the PowerBuilder section of CodeXchange. An expanded, but slightly earlier version of this article is included in the example application’s download package that explains the objects and code in detail.

To get started, let’s briefly review what we learned about unresponsive applications from the preceding article, available here:

https://community.appeon.com/index.php/articles-blogs/tutorials-articles/2-powerbuilder/325-why-unresponsive-application-windows-are-ghosted-by-the-windows-o-s

Users and the Windows O/S Do Not Like Unresponsive Applications

All PowerBuilder applications are single-threaded unless the developer has utilized little-known multi-threaded coding techniques. An annoying limitation of a single-threaded PowerBuilder database application is when the GUI temporarily “freezes” while the application is forced to wait for a database request to complete. Users get especially annoyed when a database request takes longer than five seconds to complete, because at that point the Windows operating system proactively alerts the user that the application window has become unresponsive. Regrettably, this five-second unresponsiveness threshold in Windows is not configurable.

How does Windows detect unresponsiveness? If for any reason a queued event message (such as the user pressing a key, moving the mouse or clicking a mouse button) is not processed within five seconds, the Windows operating system categorizes the application's active window as “unresponsive”. As a result, Windows replaces the visual representation of the application window on the Desktop with what is known as a “ghost” window… a snapshot image of the window with the words “(Not Responding)” appended to the title and a functioning “X” (i.e., close) button in the upper right corner of the window the user can click that causes Windows to terminate the application task.

Any application process can disable this “ghosting” behavior for itself via the DisableProcessWindowsGhosting Windows API function, but this can be done only while the application is executing. The next time the application starts, Windows will once again monitor the application for unresponsiveness.

It would be handy if a developer could temporarily disable ghosting prior to performing a long-running task, then enable it as soon as the task completes. Unfortunately, there is no API function to enable Windows ghosting behavior in an application once it has been disabled.

When Windows “ghosts” an application’s window, the user has no way to determine if something has actually gone awry in the application, or if the unresponsiveness is only temporary. In fact, the Windows operating system is unable to tell if the condition is only temporary. If unresponsiveness is due to a computationally-intensive task being performed, there are programming techniques available to the PowerBuilder developer that can be used to prevent Windows from categorizing the application as unresponsive. You can learn about these techniques in the earlier article, linked above.

Application unresponsiveness that is caused by waiting for a response from a database server to complete a data retrieval request cannot be addressed using these techniques, however. A different solution is needed, which brings us to this article.

Shared Object Prerequisites

I’ll be the first one to admit that using PowerBuilder shared objects is a relatively advanced topic. To understand how to code for and utilize shared objects and how to perform data retrieval using shared objects, I suggest it will helpful if you are knowledgeable about all of the following PowerBuilder topics:

Custom class user objects, commonly referred to as non-visual user objects (NVUO’s) or non-visual objects (NVO’s). In this article, I’ll frequently use the acronym “NVO” to refer to a custom class user object.
Auto-instantiation vs. manual instantiation of NVO’s.
Object reference variables and how a reference to an object can be passed by value to a method (an event or function). This is markedly different than what happens when you pass a standard PowerBuilder datatype or a structure by reference to a method.
The distinction between mapped and unmapped PowerBuilder events.
The difference between triggering versus posting the execution of events and functions.
Static versus dynamic calling of methods.
Extending the functionality of system-supplied objects by the use of inheritance.
Creating and destroying objects via the CREATE and DESTROY PowerScript commands, including the alternative “CREATE USING objecttypestring“ syntax of the CREATE PowerScript command.
PowerBuilder enumerated types.
The DataStore object.
The GetFullState and SetFullState methods of the DataWindow control and DataStore object.

I never, ever want to discourage anyone from learning… about PowerBuilder or about any other topic, for that matter. That being said, if you’re relatively new to PowerBuilder, then learning how to use shared objects is one topic you probably should postpone until you gain more experience. If you see any items listed above that you’re not knowledgeable about, please be aware you may struggle with some of the material in this article and with some of the code in the example application.

Managing Expectations

Multi-threading is not a cure-all for everything that may ail a single-threaded application, but it can be a handy, useful tool to have in your PB developer “toolbox”. As you’ll learn, there are some limitations that can affect the retrieval of information when a shared object is used. The most critical limitation is that multi-threading is unable to successfully handle a very large volume of data in a single retrieval, due to the way result sets have to be transferred between threads. You’ll learn more about the reasons why a little later in this article.

Keep in mind that in addition to the off-loading of a long-running data retrieval, there are several additional types of tasks where a multi-threaded approach can be useful. File import/export tasks, file processing and file transfer tasks, and the execution of compute-intensive tasks are some examples. Once you learn how to use multi-threading in a PowerBuilder application, new options for solving problems become available to you.

Asynchronous Database Operations

Even though the primary goal of this article is to help you understand how shared objects in PowerBuilder can be used to create multi-threaded applications, the example I’ve chosen to illustrate how multi-threading can be accomplished is the retrieval of data from a database. Earlier, I listed some additional types of tasks where shared objects and multi-threading could conceivably be utilized to improve the user experience. I think that nearly all PB developers at one point or another in their career have experienced firsthand an unresponsive application where it waits for the database to respond to a data retrieval request, so selecting this task as the example to focus on was an easy choice that nearly everyone can relate to.

I’d be remiss, however, if I neglected to mention an alternative to executing data retrieval requests in a separate thread; asynchronous database operations. The PowerBuilder Connection Reference publication describes the asynchronous database operations feature in this manner:

“(It) allows you to perform asynchronous operations on your database in PowerBuilder. If you have coded a RetrieveRow event for a DataWindow object or report, you can cancel the current database retrieval operation or start another (non-database) operation that does not use the same database connection before the current operation completes. You can also switch to another Windows process while the retrieval takes place.”

Sounds promising, doesn't it? You enable the ability to utilize this feature (without activating it) by including the database parameter “Async=1” in the DBParm property of the Transaction object. Once enabled, you activate asynchronous database operations by placing code in the RetrieveRow event of a DataWindow control or DataStore. When activated, the application no longer waits while the database request is being completed and you have the ability to cancel the request before it completes or as data is being returned.

Note: It's not intuitively obvious how you can place code in a DataStore object. To do this, you must create a new, descendant DataStore object by inheriting from either the DataStore standard class object or from an existing object that is descended from the DataStore standard class object.

Canceling an Asynchronous Data Retrieval Request

The DBCancel method can be used to cancel a pending/in-progress data retrieval request in either a DataWindow control or a DataStore. Once data has started to be transferred from the database server to the DataWindow control or DataStore, the DBCancel method can then be called from the RetrieveRow event to cancel the transmission of any remaining rows. Refer to the Help topic named “DBCancel method (DataWindows)” for more information.

Disadvantages of Using Asynchronous Database Operations

While asynchronous database operations have the advantage of being fairly simple to use, there are some disadvantages:

It’s not supported by all of the PB database provider interfaces.

It is not supported by the deprecated SNC (SQL Server Native Client) provider interface and its replacement, the MSOLEDBSQL provider interface. It is supported by ODBC only when the underlying vendor-supplied ODBC driver supports the necessary asynchronous protocols. Data retrieval through the use of multi-threading (i.e., a shared object) does not have this limitation, because a shared object executes in the PB application, not in the database provider interface.
While an asynchronous database operation is pending/in-progress, you cannot start another database operation using the same database connection (i.e., Transaction object).

A shared object is also limited by this restriction, but an application can have multiple shared objects, each using its own Transaction object in a separate execution thread, working simultaneously. You can see this working in the example application by opening multiple instances of the Data Retrieval window and starting concurrent retrieval requests.
The existence of code in a RetrieveRow event can potentially degrade application performance.

The presence of any code (even a comment) in the RetrieveRow event script will cause PowerBuilder to execute the event for every row that is retrieved. Depending on what the code in this event does, if the result set is large the application could slow down noticeably when data is being retrieved.

While a shared object that performs data retrieval is not required to have code present in the RetrieveRow event, you may still elect to use asynchronous data operations in a shared object in order to be able to cancel a database retrieval request that is pending (i.e., has not yet started to return data).

The example application utilizes one type of an inherited DataStore object that contains code in its RetrieveRow event when asynchronous database operations are in use (Async=1), and it utilizes another type of an inherited DataStore object that does not contain any RetrieveRow event code when asynchronous database operations are not in use (Async=0).

If the limitations described above are acceptable, then by all means, investigate the use of asynchronous database operations in your applications.

What Exactly is a PowerBuilder Shared Object?

A PowerBuilder shared object is a custom class user object, also known as a non-visual object. Because a shared object executes in a different thread than the main GUI thread that the application uses, and because it uses a thread type that does not support any visual capabilities, I’ve assembled four rules you should carefully heed regarding shared objects:

Rule 1: Shared objects cannot reference or interface directly with any visual control or visual object.

In the Windows operating system, threads can either support visual controls or they can be limited to non-visual tasks. There is considerably less overhead for the Windows O/S when it manages a non-visual thread. A large majority of the threads executing in Windows are of the non-visual type.

PowerBuilder shared objects always execute in a non-visual thread.

This means a shared object cannot directly interact with a window, or a DataWindow control, a command button, or any other type of visual object. Shared objects should not issue functions that initiate GUI-dependent functionality, like MessageBox, or open a window, or retrieve data into a DataWindow control, nor can you use the PB debugger (which, of course, is an interactive, visual interface) to debug a shared object or other objects/code that execute in a non-GUI thread.

Note: The lack of debugging support is an excellent reason to include some kind of internal logging in the design of your shared object. The example application shows you a possible way to accomplish this.

If you pass an object reference of a visual object to a method in a shared object (this is not the same thing as passing an object by reference), your code will compile successfully. But, when executing the method, PowerBuilder will issue runtime error 77, as shown below:

Figure 1: An example of PowerBuilder runtime error 77.

Rule 2: To ensure thread safety, a shared object cannot make changes to global variables or global objects in the main thread or in other shared object threads.

Experiments I’ve performed show that PowerBuilder provides every shared object with its own “local” copy, or perhaps more correctly, its own thread-specific copy of the application’s global variables, global structures and global auto-instantiated objects. The copy of these items that is provided to each shared object is a snapshot of the initial state of the application’s global variables, just as they exist when the application starts. They are not a copy of the current state of the global variables at the time the shared object and its execution thread are created.

A shared object is permitted to make changes to its own copy of the global variables, but these changes are not reflected back into the “real” global variables… global variables become, in essence, thread-specific “global” variables whose scope is limited to the objects and code executing in the shared object’s thread. Refer to the upcoming section in this article about thread safety for insights as to why PowerBuilder does this.

Rule 3: When invoked from code running in the main GUI thread, shared object methods (events and functions) should be posted instead of triggered.

Methods in a shared object can be safely triggered, however, when issued from code within the shared object or from code in other objects executing in the same thread as the shared object.

Rule 4: Shared objects cannot be auto-instantiated.

This rule exists due to the unique manner in which shared object NVO’s are created and destroyed, which will be described immediately after the upcoming discussion regarding global variables and thread-safe execution.

Global Variables and Thread-Safe Execution

It’s not only important to understand how global variables work in shared objects, you also need to understand why they work differently… and the “why” can be answered in two words: Thread Safety.

Not familiar with the concept of thread safety? If you look up the entry for “thread safe” in Wikipedia, it says:

“The term ‘thread safe’ refers to code that manipulates data structures in a manner that ensures that all threads behave properly and fulfill their design specifications without unintended interaction.”

In other words, code is considered to be thread safe when it cannot indirectly affect the outcome of code executing in another thread.

Conceptually, global variables smash thread safety to smithereens (that’s a technical, computer science term, by the way). The following hypothetical and slightly absurd example should help illustrate this point for PowerBuilder developers.

Let’s assume for this hypothetical scenario that shared objects can change global variables and objects and that other threads have immediate access to those changes. Now consider SQLCA, the default global Transaction object; the application, executing in the main GUI thread, sets the properties of SQLCA and establishes a connection to a database, in a typical, normal, PB-like manner when the application starts.

Later, the application creates a shared object, which as we’ve learned, executes in a different thread. This shared object needs to connect to a different database to do what it needs to do, so it (unwisely) disconnects SQLCA from its current database, defines a new set of property values for the Transaction object, and connects it to the other database, perhaps even using a completely different DMBS interface. While this is happening, the main GUI thread is blissfully unaware that its database connection is being usurped by the shared object and it merrily goes about its business.

It’s not difficult to imagine the software carnage that likely ensues in this hypothetical scenario. In the main thread, SQL statements fail, data access rights and privileges have probably changed, DataWindows cannot retrieve or update data, forty days of darkness descends upon the land, dogs and cats lay together, It’s mass hysteria!

This hypothetical example helps show why thread safety is important and why PowerBuilder gives each shared object (thread) its own initialized copy of the application’s global variables. PowerBuilder already has the initial state of the application’s global variables tucked away in the definition of the Application object, so it makes use of that information to produce a pristine copy of the initial state of the application’s global variables for every shared object the application creates. Since each thread is forced to use its own copy of the application’s global variables, each thread is prevented from making changes to the “global” variables in other threads.

What About “Reusing” SQLCA and Other Global Objects?

Since every shared object receives and utilizes its own, initialized copy of the application’s global variables and global objects (such as the Message object, the Error object, and SQLCA, to name a few examples), it’s natural to ask: Can a shared object make use of global variables and objects? The answer is of course, yes, it can, even though these variables and objects are local to the execution thread. Perhaps a more important question is should you use them?

For what it’s worth, I don’t think you should, particularly for SQLCA, and my rationale for taking this position is simple: Doing so is potentially confusing to any developer that does not realize the shared object is using a different SQLCA than what is being used by other application threads. I believe it is better to explicitly use an alternative Transaction object, because it will be obvious to any developer that the shared object does not use SQLCA.

You need to be aware that SQLCA is not the only object affected in this manner. Each thread receives its own Message object, for example.

You’ll learn shortly that an NVO designed to be used as a shared object is not required to be used only as a shared object. If coded properly, it can be instantiated as a “normal” NVO that executes in the main GUI thread, if need be. In the case of the example application, the database thread object that performs the data retrieval can be used as either a shared object or as a normal NVO. Regardless of how it is instantiated, it creates and manages its own transaction object. If it didn’t, it might work correctly as a shared object, but it likely would mess up the application if it needed to connect SQLCA to an alternative database in order to perform the requested data retrieval as a normal NVO.

The Shared Object PowerScript Functions

The PowerScript language provides four functions to manage shared objects:

SharedObjectRegister
SharedObjectUnregister
SharedObjectGet
SharedObjectDirectory

I recommend you refer to PB Help or the online PowerBuilder documentation to review the complete syntax for each of these functions, especially for the first three in the list.

Briefly, here is an overview of the actions each of these functions performs:

SharedObjectRegister internally registers the name of a non-visual object’s class as a shared object and associates an instance of the shared object class with a unique instance name, which you provide.

Once an NVO class has been registered as a shared object, PB immediately opens a separate, non-visual runtime session (execution thread) and creates (instantiates) the shared object. The unique instance name you register for the shared object is how you subsequently obtain access to the newly-created object instance with the SharedObjectGet function.

Every shared object in an application must be assigned a unique instance name. The example application shows one way this can be accomplished.
SharedObjectUnregister unregisters a previously registered shared object.

You supply the unique instance name given to the shared object when it was registered. The object instance (the in-memory object itself) is marked for destruction; however, the object is not actually destroyed until the PB Virtual Machine (PBVM) determines that there are no valid references to the shared object instance anywhere in the executing application.

Because of this potential delay in housecleaning, you should manage all references to shared objects carefully. Unregister shared objects at the earliest opportunity and invalidate (via SetNull) all shared object reference variables as soon as you’re through using them. Using SetNull makes the content of an object reference variable inaccessible and therefore makes the object eligible for garbage collection and eventual destruction.
SharedObjectGet obtains a reference to a registered shared object instance.

You supply the unique instance name assigned to the shared object when it was registered, along with an object reference variable that will receive the reference to the shared object instance. This reference variable can be used to post events and functions (remember Rule 3: Don’t trigger shared object events and functions from other threads) to the shared object instance and the object reference can be passed to other objects as needed.
SharedObjectDirectory retrieves a list of the currently registered shared object instance names.

You supply an unbounded String array which the function will populate. An optional second unbounded String array can be supplied to receive the class names of each of the registered shared objects.

Each of these functions returns an ErrorReturn enumerated datatype value that indicates success or one of several possible error conditions. There exists over two dozen enumerations for the ErrorReturn enumerated datatype, but the ErrorReturn enumerations which are applicable to shared objects is limited to the following six:

Success!
SharedObjectCreateInstanceError!
SharedObjectCreatePBSessionError!
SharedObjectExistsError!
SharedObjectNotExistsError!
FeatureNotSupportedError!

How Do You Make a Shared Object Do Something?

When the SharedObjectRegister PowerScript function creates a non-visual thread and instantiates the shared object’s class, the shared object instance does not automatically “take off” and perform work. It sits idle, waiting to be told what to do. This begs the very important question: How do you tell a shared object what you want it to do?

An easy way to make a shared object perform a task is to post (not trigger… keep Rule 3 in mind) an event you’ve defined within the shared object, and use argument values in the event (which must always be passed by value) to supply the shared object with any needed data values.

In general, events in PowerBuilder can either be triggered (the default technique) for immediate execution or they can be posted. Rule 3 states that events and functions in a shared object should never be triggered when they are called from code that is executing in another thread. Post them instead.

In case you are not familiar with posting of events and functions, here are some implications of invoking shared object methods via Post you need to be aware of:

Because the shared object executes in its own thread, there is no way to know or predict when the shared object will execute a posted event or function.

When a method is posted, the request (message) to execute the method gets placed on the shared object’s message queue. When any current, in-process task running in the shared object is finished, Windows will check the thread’s message queue, and the next queued method, if any, is processed. If no messages are queued, the thread (and the shared object) goes idle.
Return values issued by any posted method cannot be accessed by the caller.

This is not a shared object restriction… this is how posted events and functions work, because the execution of the posted method may be delayed.
Argument values passed into a shared object can only be passed by value.

This is because the shared object executes in its own thread and thread safety might be jeopardized if arguments were permitted to be passed by reference. If you attempt to pass an argument value by reference to a method in a shared object, at execution time PowerBuilder will issue runtime error 73:

Figure 2: An example of PowerBuilder runtime error 73.

To have a shared object perform a sequence of tasks or actions, one option is to create and invoke a “master” event that runs the set of tasks (events and/or functions in the shared object) in the desired order, and then post that one “master” event.

Alternatively, create separate events for each task and post the events in the order you want them to be performed. This latter technique is more flexible because there is not a “master” event script in the shared object that must be updated to effect changes in the order in which tasks are performed. It is also easier to pass argument values (passing them by value, of course) to each shared object event separately than it is to pass a lot of argument values to one “master” event in the shared object.

Using multiple events can be a little trickier if there is a possibility that any of the events in the sequence can fail due to an error condition. In this case, the shared object needs to be able to record event success/failure status information (typically, in instance variables) where the scripts for subsequent events can determine if it is permissible for them to run.

In the example application, you’ll see this second technique used to invoke multiple user events in a shared object to perform all of the steps necessary to retrieve data from a database. For instance, the database thread (shared) object contains separate events to (1) register the callback object, (2) create a transaction object and connect to a database, (3) create and prepare a DataStore to perform the data retrieval, and (4) retrieve data. Each event first checks to ensure the preceding event has completed successfully before doing anything.

No single technique is better than the other, so use whichever one suits your needs the best.

How Do You Retrieve Data Using a DataWindow Data Object in a Shared Object?

In a traditional PowerBuilder application, you typically retrieve data using a DataWindow data object assigned to either a DataWindow control or a DataStore object. A DataWindow control is a visual control, so you are cannot directly reference or use one in a shared object, due to Rule 1. That leaves you with the DataStore. A shared object can make use of a DataStore, because a DataStore has no visual component.

You should also be able to execute in-line SQL from PowerScript or, if your DBMS supports them, a stored procedure or a declared cursor and fetch statements, but at the time of this writing I have not yet actually attempted to do so. I’ll leave that as an exercise for you to explore if the need arises.

How Do You Get Retrieved Data Out of a Shared Object?

A shared object executes in its own thread, but the type of thread used does not support visual capabilities. Therefore, code executing in the thread cannot interact directly with a visual control or visual object (there’s that pesky Rule 1 getting in the way, again). You need to use an intermediary NVO as a “go between” to pass information between the shared object and a visual object, such as a window or DataWindow control, for example. This intermediary object is often referred to as a callback object.

A callback object is a separate NVO, that:

Should not be auto-instantiated (you’ll learn why shortly).
Gets created, executes and is destroyed in the main (GUI) thread. Usually, the callback object is managed by the window that registers/uses the shared object.
Can access the main application’s global variables and objects, since it is created and executes in the main (GUI) thread. Conversely, it cannot access the shared object's copy of global variables and objects.
Contains methods that can be called by the shared object or by other objects in any thread as needed.
Can post events in the shared object, if it has been given a valid reference to the shared object.
Can invoke methods (events and functions) in other objects (windows, controls, NVO’s, etc.) executing in the main (GUI) thread, either by triggering or posting of the method.

Why the Callback Object Should Not Be Auto-Instantiated

In order to be able to access and interact with windows and avoid the restriction of Rule 1, DataWindows and other visual objects, the callback object needs to be created in the main GUI thread. However, in order for a shared object to be able to invoke events and functions in a callback object, the shared object needs to have access to a reference variable for the callback object.

If the callback object were to auto-instantiate, then the declaration of a callback object reference variable that resides in the shared object would automatically instantiate the callback object in the shared object (and therefore, in the shared object's thread)… and that’s not what we want or need to have happen. Having the callback NVO be manually instantiated allows you to manage the creation and destruction of this object appropriately.

Creating and Restoring a Snapshot of a DataWindow/DataStore

If you use a DataStore in a shared object to produce a result set from a DataWindow data object, then how can you transfer the contents of the primary data buffer (the retrieved data) in the DataStore back to a DataWindow control or a DataStore that resides in a window? The simplest technique is for the shared object to use the retrieval DataStore’s GetFullState function to create what I like to refer to as a “snapshot”. As the name implies, a "full state" includes everything; the data object, all of the data buffers (primary, filter, delete) and the column/row status flags. It is essentially a snapshot of the entire DataWindow or DataStore.

The GetFullState function places all of this information into a Blob variable. The SetFullState function extracts a FullState snapshot from a Blob and loads it into a DataWindow or DataStore. Once a Blob variable contains a FullState snapshot, your code can call a function you’ve created in the callback object that passes the blob as an argument value to the function. This function is often referred to as a “notify” function in the callback object.

In the callback object, if a reference to a target DataWindow control or a DataStore in the requesting window has been previously supplied to the callback object and kept in an instance variable (this is commonly referred to as “registering” the control or window in the callback object), then the callback object’s “notify” function (which you create) can call the SetFullState function in the target DataWindow/DataStore and pass it the blob that contains the “full state” information.

Note: It is permissible, even advantageous, to pass the Blob variable that contains the “full state” information out of the shared object to the notify function by reference, as long as the Blob variable in the shared object does not go out of scope (and gets reclaimed during PowerBuilder’s garbage collection process) before the notify function finishes executing the SetFullState function. Defining the Blob variable as an instance variable in the shared object is an excellent way to ensure this, as the Blob variable will exist as long as the shared object exists.

Passing the Blob variable to the notify function by reference is advantageous for the following reasons:

A copy of the Blob does not have to be created solely for passing it to the called function. This reduces the consumption of available memory.
Only the memory address of the Blob variable in the shared object gets passed. Passing the address of a Blob variable that contains, say, 100+ megabytes or more. for example, is much much more efficient than passing the same Blob variable by value.

The Disadvantage of Using GetFullState / SetFullState

Even though the functionality they perform is very sophisticated, the GetFullState and SetFullState functions are quite simple to use, and they work well, when they’re not asked to do too much. A disadvantage of using these functions is that the blob that is produced may be extremely large if the DataStore in the shared object contains a large amount of data, either in the number of rows, the quantity of data within a row, or more commonly, a combination of both.

To illustrate, here are numbers I obtained during the creation and testing of the example application. I used a modest grid report data object. It contains roughly 60 columns; 12 short string values (50 characters or less, most were 12 characters or less) and almost four dozen numeric columns, mostly decimal. When retrieved, the result set contained 614 rows and the blob created by the GetFullState function contained 1,247,414 bytes. The reason I used this particular data object was for the retrieval delay; it uses a long-running stored procedure as its data source that took roughly 30 seconds to generate and return its modest result set. That gave me ample time to verify the GUI remained responsive during data retrieval.

The data object portion of the FullState snapshot (excluding the data buffers) took up approximately 400KB. The approximately 600 data rows resided in the remaining 850KB, which works out to about 1400 bytes/row on average. Extrapolating the memory requirement for a much larger result set, the estimated size of a FullState snapshot of this DataWindow when it contains one hundred thousand rows would be approximately 135MB.

Keep in mind that the DataStore in use by the shared object itself already contains one copy of the in-memory FullState information, and when you populate the Blob variable with a snapshot of the DataStore contents via GetFullState, that’s a second copy that has to exist simultaneously in memory.

In order to keep the number of copies of the Blob containing the retrieval data object and retrieved data to a minimum, the Blob variable in the shared object that contains the snapshot can be passed to the callback object by reference, as described earlier. The passing of an argument value by reference from a shared object to the main GUI thread is permissible.

The callback object places the retrieved data in a DataWindow control and/or a DataStore in the window that requested the data retrieval by using the reference to the Blob containing the snapshot. Once restored into a DataWindow control or a DataStore, a third in-memory copy of the information then exists across the main GUI and shared object threads.

If the size of the blob is only 1.2MB as in my development/testing example, then memory utilization quite likely should never be a concern. But if it occupies 135MB, then three in-memory copies of the full state information occupy about 405MB of memory, and that’s probably beginning to become a concern.

If either the GetFullState or SetFullState functions are unable to obtain the memory that is required, the functions fail with a return value of -1. I was able to test this kind of failure using a different data object when it returned approximately 225,000 rows, running on a Windows 10 desktop PC that has 6GB of real memory and a solid-state C-drive. The GetFullState function worked successfully in this particular case when retrieving 112,000 rows, but failed at 225,000 rows. Your mileage will vary.

The SetNull Function Does NOT Reclaim Memory

Why not use the SetNull PowerScript function to free up the memory occupied by the Blob variable when it’s no longer needed? The main argument against doing this is... SetNull doesn’t free up anything. You might be surprised to learn that using the SetNull PowerScript function on a variable of any type (including Blob) does not affect memory utilization. Setting a variable to null affects only what I refer to as an internal “descriptor block” or structure that PB uses, under the covers, to manage every PB variable.

When SetNull is used on a variable, the null status of the variable is simply recorded in the variable’s behind-the-scenes descriptor block/structure. The contents of the memory used to hold the value of the variable does not change. If PowerBuilder were ever to provide a “SetNotNull” function that turned off the null setting in a variable’s descriptor block, the most-recently assigned value of the variable would once again be accessible.

A technique you can use to reset the contents of a Blob variable is to assign an un-initialized Blob variable that does not specify the Blob size in its declaration to the variable you wish to reset. For example:

Blob lblob_fullstate, lblob_empty // Note: No preset blob size is defined
Long ll_rc

// Take a snapshot of DW object and data buffers.
ll_rc = dw_1.GetFullState(lblob_fullstate)

// SetNull makes the blob’s value inaccessible.
// However, the FullState data still resides in memory.
SetNull(lblob_fullstate)

// Instead, assign an un-initialized blob variable to the FullState
// blob to reclaim most of the FullState blob’s memory.
lblob_fullstate = lblob_empty

Multi-Threaded Retrieval of Very Large Result Sets Should Be Avoided

As you can see from the preceding discussion, the mechanisms that are used to pass a result set between a shared object and a window or other type of visual object are not well-suited for handling a very large result set. What is the upper limit? I don’t know, as it depends on many factors. Generally, in my opinion, if your application routinely produces very large result sets, be aware that multi-threaded data retrieval might not be feasible.

The CodeXchange Example Application – An Overview

The example of a PowerBuilder application that can retrieve data in a separate execution thread via a shared object is now available in the CodeXchange section of the Appeon Community website:

https://community.appeon.com/index.php/codeexchange/powerbuilder/307-a-multi-threaded-data-retrieval-example

The application was developed using PB 2017, but it has been successfully migrated and tested in PB 2019 and PB 2021.

As we've learned, a primary benefit of using a shared object to perform data retrieval is it allows the application’s visual interface to remain responsive while the retrieval is being performed. If you have any DataWindow data objects you would like to test in a multi-threaded environment, the example application should be able to help you accomplish this with very little coding. Even if you do not have any DataWindows of your own you wish to test, the application contains five sample DataWindows where the application simulates data retrieval delays of five to sixty seconds. This feature allows you to verify that the GUI remains responsive during data retrieval.

You may import your own DataWindows, of course. In order to properly interface with imported DataWindows, you’ll need to specify the Transaction object properties to be used, retrieval argument values (if any are needed), and DataStore Retrieve function calls (you need to code these only when retrieval argument values are used). Each DataWindow you import into this application can have its own unique Transaction object requirements. All of the customization needed to support imported DataWindows is accomplished in a single, well-documented non-visual object.

When running the application, you may open multiple data retrieval windows from the main application window. In each retrieval window you may select the DataWindow data object to be retrieved, choose whether or not multi-threaded data retrieval is to be used, and you may also optionally request the use of Asynchronous Data Operations, or “Async”, provided the database connection you are using supports and implements this feature (not all database provide interfaces do).

A “retrieval log” is displayed in each retrieval window so that you can monitor the progress of each retrieval request as it happens, when multi-threading is used. Each retrieval window can be re-positioned and resized during a multi-threaded data retrieval, and one of several included animated GIFs display in the retrieval window while multi-threaded data retrieval is in progress.

The example application's download package includes a Word document that combines a slightly earlier version of this article about multi-threading in PowerBuilder with an explanation of the internal workings of the multi-threaded example application.

Comments Regarding the Example Application

As you examine the code, keep in mind the example application contains several features and capabilities which are not required to be able to perform multi-threaded data retrieval. These “extras” are included so that:

You may more easily import and test DataWindow data objects from your own project(s) that retrieve data from your database(s).
You are given a working demonstration of multi-threaded data retrieval without requiring you to customize the application.
It is clearly evident how the GUI continues to work while multi-threaded data retrieval is in progress.
You may see asynchronous data operations (Async=1) in practice either in conjunction with or as an alternative to multi-threaded data retrieval, if the database provider interface(s) you use support this feature.
You can learn possible ways to prevent the user from taking actions that might cause unfortunate side effects, such as closing the window or entire app while a multi-threaded data retrieval operation is in progress.
You may see an example of an alternative notification technique that does not require the creation or use of a callback object (refer to Appendix A in the companion Word document).

Should you wish to add multi-threading data retrieval functionality to your application, your implementation will almost certainly be simpler than what I’ve provided for you in the example application.

Additional Multi-Threading Examples

The PowerBuilder code samples in CodeXchange contains a few small demonstration applications that use shared objects:

https://community.appeon.com/index.php/codeexchange/powerbuilder/64-multithreaded-count-example

This app counts from one to ten in one second intervals simultaneously in three threads. A very simple example that illustrates the interaction between a window, a shared object, and a callback object. The author is unknown.
https://community.appeon.com/index.php/codeexchange/powerbuilder/63-multithreaded-service

This app, dated 2005, offers what the author claims is an extendable collection of multi-threaded service objects. The author is Jason Fentor.
https://community.appeon.com/index.php/codeexchange/powerbuilder/62-simple-multithreaded-sample

This app was presented at Techselect UK 2007. It connects to a SQL Anywhere demo database for EAServer (the database is not included, unfortunately) via ODBC, is supposed to perform a data retrieval request into a DataStore, then display the retrieved data in a DataWindow. The author is unknown.
https://community.appeon.com/index.php/codeexchange/powerbuilder/60-prime-number-enumerator-multi-threading

This computational app enumerates the prime numbers between one and a user-specified upper limit simultaneously in each of up to ten threads. The author is Roy Kiesler.
https://community.appeon.com/index.php/codeexchange/powerbuilder/59-wiggly-worms-multi-threading

This game-like app continually draws multiple “worms” that appear to traverse within the boundaries of the window in a wiggling motion. The author is unknown.

Taking Multi-Threading in PowerBuilder to the Next Level

Now that you’ve learned about how to add multi-threading capabilities to a PowerBuilder application, what are you going to do with your new-found knowledge? In the example application, you'll see how to issue a potentially long-running request to retrieve data from a database and populate a single DataWindow control and/or DataStore with the results, without “freezing” the GUI and having Windows “ghost” the application window.

Does this mean you should use shared objects to populate every DataWindow control in an application? No, of course not! The technique incurs additional overhead, has some disadvantages, and it is not trivial to develop/code, so use it wisely, my young Jedi!

Keep in mind there can be many types of data retrieval scenarios in an application, such as parent-child, parent-child-grandchild, and tab controls where each tab page may contain one or more DataWindow controls. How would you handle data retrieval in these scenarios with multi-threading?

What about populating one or more drop-down DataWindows using shared objects? The DataWindowChild object does not support the SetFullState and SetFullState methods, so that poses an additional challenge.

And don’t forget there are tasks other than data retrieval that might benefit from the use of shared objects. For example, file import/export tasks, file processing and file transfer tasks, and the execution of computationally-intensive tasks. Any place in an application where a task routinely takes longer than five seconds to complete (the threshold above which Windows categorizes an application/window as unresponsive) can be considered as a possible candidate for multi-threading.

Thread Task Objects and a Thread Manager

If you have several types of tasks that could benefit from the selective use of multi-threading, you might wish to consider creating a thread task object for each type of task and perhaps also create a thread manager object to take care of creating, configuring, executing, and destroying the various thread task objects.

Thanks for reading! Hopefully, I’ve helped you get started with multi-threading. I’ll leave the concept of a thread manager as an exercise for the reader ?.

Please take a moment to provide some feedback, ask questions, or suggest ideas for future articles.

Comments (14)

Glenn Scamman
Tuesday, Aug 16 2022

Great article, John. We've been using multiple threads for about 10 years now in an app that calls a 3rd party API. We look to see how many invoices must be processed, and if over a certain number, divide the work into 4 threads and it completes significantly faster. However, I learned plenty from your article about global/instance variable access from these threads, how SetNull works, and memory implications of using a nonvisual thread to pass large datasets back to the GUI thread - which I've considered doing to try to speed up some lengthy retrieve operations.

Attachments