PowerBuilder: Article

Data Manipulation

Data Manipulation

The first time the OO concept of inheritance and how it lends itself to reuse was explained to me, I viewed it as old news. You see, by the early '90s AC/DC had already put out the same album a dozen times. The pentatonic scale played at light speed...the blues-based rhythm guitar...four-four timing...it had all been done before. Lyrics were changed, themes were adjusted slightly, but all in all each record was simply inherited from the prior one. Inheritance and reuse was a standard to me. Information technology had finally caught up to the music industry. I'm such a fan of AC/DC and reuse that I reused the title from their song "Flick of the Switch."

This leads to my disclaimer. This article is the second in this series. The first explained the major portion of this approach by showing how to make your application smart enough to know where and how to instantiate its business objects. The goal of this article is to cover data retrieval and manipulation only as they fit the overall design explained in Part 1. In order to be clear yet succinct, I'll review what was discussed in Part 1 only as necessary. For the most part, though, I'll simply refer to it with clever terms like "as discussed in Part 1 of this series" - thus my reuse disclaimer. You may very well need to keep Part 1 handy to understand this second part. Think of it as the ancestor article object...

The goal of this series is to show how to code your applications in a manner that will allow bouncing from client server to n tier "with a flick of the switch" as opposed to a massive rewrite. Because of the differences between client server and n tier in how you instantiate and destroy objects, retrieve and manipulate data, making the move to Jaguar would normally require a lot of rescripting. By following the designs explained in these articles you'll understand how to code your client/server application in a way that will allow moving to Jaguar "with a flick of the switch." As discussed in Part 1, there's obviously a lot of work entailed in setting up your Jaguar server(s) and their properties. This approach is geared to save time that normally would be spent rewriting code to handle distribution in Jaguar.

Each of the following subheadings discusses an area wherein switching from client server to Jaguar would normally require such recoding; the solution then explains how to avoid it. The primary principles are discussed in Part 1; if you want to understand what this mystical "switch" is and how it works, I recommend you review it before reading this article.

Connecting to Your DBMS
To retrieve and manipulate data in your RDBMS, your application needs a connection to the database. With client/server architecture you usually code the connection logic in one place and fire it at application start-up, more than likely during the login process. That one (possibly more) connection stays open during the life of your application session. All interaction with the database, whether it's via DataWindows or embedded SQL, is done through this connection. At application close you issue the DISCONNECT.

Using Jaguar CTS, your distributed NVOs establish their connections as needed. Ideally, they utilize a pool of database connections available in the Jaguar server. One way to do this is to call a user method from the Activate event that initializes the SQLCA global variable and issues the CONNECT. The Deactivate event issues the DISCONNECT. It's preferred that the clients only connect to the Jaguar server, and the NVOs you've deployed to Jaguar handle their own connections to the database.

If we want our application to be smart enough to connect accordingly, we need to call the application manager's of_IsDistributed( ) method from the login window or wherever you'd code your client/server connection. This method returns a boolean indicating whether the system is running against Jaguar or running locally (as explained in Part 1). If the application isn't distributed, perform the connection as usual (see Listing 1). If it's distributed, then the Activate event of your base component object will perform the connection via a user method, of_ConnectToDBMS( ), which is also in your base object (see Listing 2). The Activate event (pbm_component_activate) is fired only if the NVO is running on Jaguar, so you don't need to worry when it will call of_ConnectToDBMS. This is handled automatically by Jaguar or ignored completely if running locally. Your Activate event would look something like this:

// Activate - Perform CONNECT
THIS.of_ConnectToDBMS("ODBC", "PBDJCache" )

To make this work you need to identify any potential connection cache names before you begin coding. Later on, when you flick the switch to make your system distributed, you'll need to have these connection caches defined on the Jaguar server exactly as you've defined them in the Activate Event(s).

Data Retrieval and Presentation
In client/server we do our data retrieval via DataWindow controls, DataStores, or maybe even embedded SQL. Whether the DataWindow object is based on a stored procedure or a SQL Select statement has no bearing on how you code the retrieval. If you're having your DataWindow control retrieve its own data, then your code would be quite simple.

/* DataWindow Control Retrieval */
dw_1.Retrieve( )
In recent years, however, we've been encouraged to use the DataWindow control primarily for presentation and user interface. A lot of us have gotten into the healthy habit of performing the actual work against a DataStore held within an NVO and using ShareData( ) to link to the DataWindow control. Of course we've gotten more involved than just sharing data between the nonvisual and visual objects. With our business logic encapsulated within our nonvisual business objects we've done some pretty intensive code against these DataStores. The users see only what they need to, and we keep the client nice and thin.

Once you go distributed, however, you can no longer use the ShareData( ) method to allow your presentation layer to talk to the business layer. Your business objects, or components, are instantiated on the Jaguar server. The DataStores you're using cannot link directly to the client's DataWindow control. This is the type of logic that needs to be rewritten if you want to take your client server application and move its appropriate objects to Jaguar. All of your data sharing between DataStores and DataWindow objects would never work across the network due to the nature of the n-tier architecture.

Coding your data retrieval to work on both client/server and n tier is actually quite simple. All you need to do is code as though you'll be running only against Jaguar. Beginning with Powerbuilder 6.0, the DataStore and DataWindow have had the synchronization methods that make all of this possible:

  1. GetFullState
  2. SetFullState
  3. GetChanges
  4. SetChanges

Regardless of where your business objects are instantiated, these methods can be used to synchronize DataStores with their corresponding DataWindow. You'll need to create public functions in your business objects that perform the Retrieve for its DataStore(s) and return the data in a blob by using the GetFullState method. The DataWindow in the client then applies this blob to itself using the SetFullState method. Because you want to know the success or failure of the function, the blob should be a reference argument to the function, and the return type should be a Long, its value being -1 for failure, and the RowCount( ) for success. Take a look at Listing 3 to see how you'd code such a function for your distributed NVO. For our example we have a user object called n_pbdj_employees that's instantiated as inv_employees, and our method is of_RetrieveEmployees. The following code snippet shows how the client uses of_RetrieveEmployees.

/* Retrieve Employees and present to user */
Blob lblob
IF inv_employees.of_RetrieveEmployees(lblob) = -1 THEN



This is the easiest way to code your retrievals so your NVOs can deliver data locally or remotely. The synchronization methods are great because the DataWindow status flags (as well as the presentation definition) come as part of the blob. You won't always need these flags, though. Take reports, for instance. All you're interested in is displaying the data to the user. They'll never be able to update the data, so who cares if the status flags are delivered? Depending on the size of your result set, the blob could become quite large, and carrying all of the "extra" DataWindow information could hinder performance across the network. In such a case you'd code your of_RetrieveEmployees method to accept a reference string argument instead of a blob. The method would populate the string only with data you'd import into the DataWindow object at the client using the ImportString method (see Listing 4). There are several other ways to get the data from your NVO to the DataWindow. You'll need to ascertain which method is best for which instance.

Ultimately, you must be sure to code your retrievals as though your application is already on Jaguar. When you flick the switch back and forth between client/server and Jaguar, the retrieval will perform transparently. Sticking to this design requires a bit of self-discipline, as it needs to be applied consistently throughout the application.

Updating Changes to the Database
Just as we've been accustomed to executing the Retrieve method directly against the client DataWindow control, we've also become quite used to issuing the Update directly. Even if we've been good boys and girls by having the actual result set reside in a DataStore within our NVO and displayed it via the ShareData method, we've still cheated by coding Update( ) in the client itself. Maybe, just maybe, we've been exceptional and coded an of_Update( ) method in our NVO and performed the Update against the DataStore. No matter how we've done this in the past, once you try to distribute your NVOs to the Jaguar server(s) you're going to run into the same issue we discussed regarding data retrieval and presentation. The ShareData method doesn't work with remotely instantiated objects.

Fortunately, this is easy to code. Here also we need to code our Update methods as though we're already running on Jaguar. Remember, the native synchronization methods work whether your business objects are instantiated locally and remotely. The part of this process that requires special consideration is the transaction management that follows the updates.

To update any changes the user has made to our employee data, we need to call the of_UpdateEmployees method in inv_employees (see Listing 5). This method has a reference blob argument it uses to prepare the DataStore for the Update method. The of_UpdateEmployees method not only handles the calling of the DataStore's Update( ) method, but it calls the component's methods of_Commit (see Listing 6) or of_Rollback (see Listing 7) based on the success or failure of the Update. The client checks the return code of of_Update and, if successful, reapplies the blob argument to the client DataWindow. Listing 8 shows the client's code for all we just discussed.

You might be wondering why the blob argument must be reapplied to the client DataWindow. After all, the changes are already in the presentation. Because the actual update is happening in the DataStore and we're not using ShareData to link these objects, none of the update flags in the client DataWindow control are getting updated. If the user makes any more changes and issues another update command, all the changes will be reissued as new changes. This might not be a problem if the changes generate the UPDATE database command, but you'll have real problems if the changes were for INSERT or DELETE. The user will be presented with "duplicate key insert" and "row does not exist" error messages. Fun! Reapplying the blob to the client isn't the only solution, of course. You could, at the client, issue a ResetUpdate to clear the update flags. It's up to you to decide what approach is best for each instance.

Ultimately, you must be sure to code your updates as though your application is already on Jaguar. When you flick the switch back and forth between client/server and Jaguar, the methods you coded will perform transparently. Sticking to this design requires a lot of self-discipline, as it needs to be applied consistently throughout the application.

Rules of the Game
In Part 1 of this series I listed seven laws to follow for this approach to work in regard to your business objects. Here I'm restating those seven along with an additional seven that apply specifically to data retrieval and manipulation.

  1. Clearly identify all objects that will potentially be deployed on Jaguar CTS as well as any PUBLIC methods therein and name them accordingly.
  2. Clearly identify all package names and which components they'll house.
  3. Code every instantiation of such objects the same way, using of_ CreateInstance.
  4. Code every destruction of such objects the same way, using of_DestroyInstance.
  5. Instantiate your custom Connection object even when not distrib- uted. This allows you to still use its of_CreateInstance and of_DestroyInstance methods for your local objects.
  6. Proxy names and NVO names must be identical.
  7. Don't use the "Prepend Jaguar package name to object name" option.
  8. Clearly identify any database connections that will be required and define connection cache names for each.
  9. Code the Activate event of your NVO/Jaguar components to call of_ConnectToDBMS using the appropriate connection cache name.
  10. Code all your retrieval and update functionality in your NVOs. Don't issue retrieves or updates against the client DataWindow control.
  11. Identify the appropriate synchronization methods for the Data- Store/DataWindow relationships, and code them consistently.
  12. Document all your object names, connection names, synchroniza- tion methods, and so on. You'll need them throughout development as well as the first time you "flick the switch."
  13. Test every script using this approach locally and on Jaguar. You don't need to have a separate physical Jaguar server up and running; you can run Jaguar on your machine as localhost.
  14. Last but not least, make sure your base NVO has Activate (pbm_com- ponent_activate) and Deactivate (pbm_component_deactivate) events declared. The best thing would be to create Jaguar Component NVOs via the wizard. If you already have a Custom Class UO you intend to use as the base component object, you'll need to declare these events yourself.

I haven't discussed every aspect of application development where this approach can be used. Nor have I discussed every conceivable scenario for data retrieval and updates. I'm quite sure that while you read this you thought, "Yeah, but in my application..." Just remember that this is a skeleton design for you to build applications to be code-ready for implementation on Jaguar. You'll need to learn and understand the rules and functionality of Jaguar to choose the best solution for your project. As with any other major application change, it won't come without heartache. You'll experience the joys of realizing you named a proxy object incorrectly, or you forgot to create your connection cache(s) or some other minute oversight that causes runtime or database errors. But those are scars that come with the territory. Overall, though, you'll save an enormous amount of time simply because your base code won't require rewriting.

One bit of advice: When you do flick the switch you'll get a lot more satisfaction from the experience by putting on your best Frankenstein madman voice and shouting, "It's alive, it's alive!" Trust me.

More Stories By Tom J. Peters

Tom J. Peters is an independent consultant. A CPD professional with 14 years of IT experience, he teaches regularly at the Sybase TechWave conferences.

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.