By Don Draper

I recently had the wonderful opportunity to speak at the first "PowerBuilder Conference 96" in Cadro-Lugano, Switzerland. One of my presentation topics was on using PowerBuilder’s any data type. I was often asked, "how much can you say about a data type?" Well, actually, more that you might think. Many PowerBuilder developers are unaware of the techniques presented here so stick around for some interesting ideas. This article will show how you can use the any data type with both the Retrieve() function and in SQL format 4 to promote encapsulation of your retrieval code.

The datawindow, although very powerful, does not promote object oriented development. The issue is the difficulty to completely encapsulate the datawindow retrieval inside an object. This difficulty is seen on two main fronts. First, how do we easily and cleanly get a variable number of arguments and data types passed to the retrieval code. Secondly, how can the encapsulated Retrieve function be coded to work with the many combinations of arguments and data types which may get passed to it.

For example, assume the common scenario of a window & datawindow control used for reporting. Simply change the datawindow control’s datawindow object attribute (or should I now say property?) to a different datawindow name and issue another call to the retrieve function with the appropriate number and type of arguments. However, if the retrieve() function is encapsulated inside the window object, how can the arguments be passed to the window which the retrieve() function will need? Furthermore, how will the Retrieve function utilize the many possible combinations of arguments and data types?

Developers new to PowerBuilder might be tempted to simply place the retrieve() function outside the window and code each combination of number and argument type as needed. More experienced developers may choose to code the datawindow object without a where clause. The where clause is built at run time and passed as a string argument to the window. While this is a viable and common solution, it still requires building the where clause with column names and argument values and using Modify() and Describe() to append the where clause to the datawindow object. Furthermore, this solution does not work well with typical retrieval requirements of more complex reporting where a stored p procedure is the more suitable data source.

I will show you the in’s and out’s of using the any data type to solve this dilemma. Techniques for both PowerBuilder 4.0 and 5.0 will be discussed. Fortunately, Powersoft fully implemented the any data type in PB 5.0 and it becomes much easier to use with the Retrieve() function.

First, I will cover some basics of the any data type. You may declare a variable to be of type any just like other data types. You may even assign the same variable values of differing data types within the same scope. Try doing this with other standard data types.

Listing 1: Declaring variables of type any

Listing 2 shows how you may assign a value at the time of declaration .

any a_myany = 23

The next code snippet shows how to create an array of type any. This is where the any data type makes passing our retrieval arguments much easier. A variable number and order of different data types may be passed in a single, compact array. Simply create an array element for each argument you wish to pass to the Retrieve() function.

Listing 3: Creating an array of any

any a_myargs[3]
a_myargs[1] = "CUSTOMER"
a_myargs[2] = "Monday"
a_myargs[3] = 4

Once inside the encapsulated object, the UpperBound() function is used determine the maximum number of arguments in the array and is demonstrated later. Our encapsulated Retrieve() function might look something like listing 4.

Listing 4: Retrieve with arguments of type any

However, in PB 4.0, two primary problems exist with using the Retrieve() function in this manner. Let’s look at the first problem and a possible solution.

Problem #1: The PB 4.0 Retrieve() function cannot accept the first argument as type any.

Solution: Use the Classname() function to determine the underlying base data type of the first element in the any array (our first argument). Assign the contents of this variable to another variable of the appropriate data type and pass this standard variable to the retrieve() function. All subsequent arguments to the Retrieve() function may be passed as type any directly from any array. A CASE structure works well for this process. Each section of the CASE structure handles one of the possible base data types that may be passed as the first argument. Listing 5 shows an example of how three different data types is handled. An actual implementation would typically handle additional types.

Listing 5:

The example above only handles three retrieval arguments. For true encapsulation, must be able to handle a variable number of retrieval arguments. This leads us to our second problem and its solution.

Problem #2: The PB 4.0 Retrieve() function can only handle a fixed a number of arguments and will return an error when too few arguments than expected are received().

Solution: Code nested CASE structures to handle the maximum number of arguments you expect to pass to your datawindows. Each nested CASE structure has multiple Retrieve() functions, each with a different number of retrieval arguments. The first a argument must always being a standard data type. While this may appear to be getting ugly, remember this may be coded into a single non-visual object and forgotten. This nvo then provides your application with a generic datawindow retrieval service.

The nested CASE structures added to our first CASE example might look like the following example which would handle from 0 to 3 arguments with the first argument varying among three different data types.

Listing 6:

The inner and outer CASE structure should be coded to handle all of the varying data types which you expect to pass as arguments as well as the maximum number of arguments, both of which are typically limited to some reasonable domain. Assuming our generic retrieval code was placed inside a user object named "of_Retrieve" of object "nvo_Retrieve", we could retrieve our datawindow with little preparation. For example, if our data window control is dw_cust, and we need to pass two retrieval arguments, we simply pass the datawindow and the any array by reference. Listing 7: nvo_Retrieve srv_retrieve
srv_retrieve = CREATE nvo_Retrieve
Any a_args[2]
a_args[1] = 23456
a_args[2] = "Active"
srv_Retrieve.of_retrieve(dw_cust, a_args[])

The actual retrieve( ) function may now encapsulated in a window, nvo or any object which is appropriate and passing the retrieval information it needs is now much easier.

The code above works well for PB 4.0, but what about version 5.0. Well all it takes to migrate our 4.0 sample to 5.0 is dragging your mouse and deleting most of the code. The reason is that Powersoft listened to us and addressed the problems with the Retrieve() function.

PB 5.0 is simply full of wonderful little improvements and here is one my favorites. The PB 5.0 Retrieve() function will now accept the first argument as type any. In addition, the function may receive fewer than expected arguments and won’t complain with a message. Retrieve() will now simply ignore the extra, unneeded arguments. This translates into a single "super" retrieve function coded with the maximum number of any arguments you would expect to send.

Listing 8:

This Retrieve will now accept from 0 to 5 arguments, all of which can be of type any! See, the any data type really is for anybody!

Using Any with Dynamic SQL Format 4.

Another little know fact is that the any data type may be used to set arguments in a WHERE clause when using Dynamic SQL format 4. This form of Dynamic SQL is used when the developer does not know the number of input (or data type) or the number of outputs (or data type) at run time.

While most of us rarely elect to use embedded SQL instead of a datawindow, there are times when it makes sense. For example, I use Format 4 to retrieve and update comments or images in the form of blob type data (text, image etc.) in DBMS such as Microsoft SQL Server or Sybase Server. Due to the large quantity of data which may be stored in each row, I elect to act on a single row at a time. While I may know the return data type and number of columns, I don’t know how many or type of arguments (the primary key) will be needed to retrieve the row so SQL Format 4 comes to the rescue. In fact, you can UPDATE with Format 4 even though it’s not well documented.

In a nutshell, Dynamic SQL Format 4 permits you to create an SQL WHERE clause with a ‘?’ place holder for each necessary argument. Later, a special function named SetDynamicParm() is called which replaces each ‘?’ which the appropriate retrieval argument value. It is this function which takes the any data type directly. So, like our Retrieve() example above, we may encapsulate the Format 4 code inside an object and simply pass our necessary retrieval arguments to it via an array of any. We use the UpperBound() function to determine the number of elements in the array (thus the number of arguments) and simply feed each value directly to the SetDynamicParm() function. Having the arguments in an array structure allows us to use a simple loop for this function as show in listing 9.

Listing 9:

Listing 10 shows a more complete picture of using Dynamic SQL Format 4 with this technique. This code may be placed inside a non-visual user object, special window or object of your choice and then forgotten. The code becomes very generic and encapsulated. Other developers need not be concerned with how it works but only how to pass the retrieval arguments to it using an array of any.

Summary

The any data type provides a powerful way to permit developers to encapsulate their retrieval code by permitting retrieval arguments to be passed using an array of type any. We reviewed some ideas on to handle problems with using this data type with the PB 4.0 retrieve() function and how this gets much easier with PB 5.0. Finally, we provided ideas on using this same technique to pass data to embedded SQL, particularly, the SetDynamicParm() function of Dynamic SQL Format 4.

I hope you will experiment with these techniques and work toward better encapsulation of your retrieval code. You will be glad you did.

Back to Developer Resources