PowerBuilder Tips

As an instructor and course developer for many years over engagements for several employers, I developed proficiency in AREV, Powerbuilder, HTML, Sybase, Unix (AIX) and Oracle, among other technologies and products. As a diligent supportive instructor,  I took it upon myself to write short notes and handouts for students. The notes sought to firm up understanding of concepts covered that day, give more examples of topics, or reinforce a novel concept or powerful feature we had seen that class day.

A representative selection of my content of this type is provided here, to give evidence of my written style, and depth of technical understanding. Contact me if interested in my services. I am very good at knocking out technical and end-user oriented documentation. 


  Oracle Tips

  Miscellaneous Client/Server, IDE and Unix Tips

Dealing with checkin failures
You may occasionally experience

     'Check in failed.  Possible library file I/O error'

messages when trying to check in.  The easiest thing to do is just persist.  If you cannot save the change back to the home library, you can use the Source menu to clear check out status and use the Library Move operation (or copy, if you prefer) to get the changed version from your checkout library to its proper final home.

Parent vs. ancestor
In the Powerscript language, 'Parent' is a reference to the instance of the object on which the current object resides (a 'container' relationship). In OO parlance, 'ancestor' refers to the object class from which your current object is inherited. 'Parent' is an addressable 'object pronoun', whereas the term 'ancestor' is just a concept, and not an addressable keyword. I have seen the two terms mixed too frequently, unfortunately, which makes learning more difficult than needs to be the case

The ANY Datatype
Similar to the old Visual Basic data type of 'variant', the 'any' datatype of PowerBuilder is a very forgiving 'inbasket' for data coming from an unpredictable source, such as an OLE call to a spreadsheet cell.  Creating a 'chameleon' variable of type any lets you receive that incoming data and the variable's datatype is recast upon use. Here is a fragment of the help file from PowerBuilder; note the use of ClassName(), rather than TypeOf() to test the datatype:

any la_x

la_x = ole_1.Object.cells(1,1).value
CHOOSE CASE ClassName(la_spreadsheetdata)
CASE "integer"
CASE "string"

Editing, and undo/reset behavior in a DataWindow edit control
When the user interfaces with a DataWindow object residing within a DataWindow control, they are always, (and only) interacting with a floating editing feature called the Edit Window.  This is what supports such editing keystrokes as backspace for deletion, cursor movement within existing text, and the ability to undo changes by pressing {Esc} (unless the Cancel property for an exit/cancel command button is set). 

Another possibility for 'undo' is the GetItemXXX series, which supports the optional switch of getting original value for a column, as:
   dwcontrol.GetItemString( rowvar, colvar {, dwbuffer, originalvalue })
This last switch relates to the pseudo 'original' buffer in the datawindow. 

You may also want to know about the Undo() function, for DataWindow, EditMask, MultiLineEdit, RichTextEdit, and SingleLineEdit controls.  There is a CanUndo() function which can be tested to see if an undo is still available.

(Perhaps this next one is a reach, but you may also want to know about the ReselectRow() function, which will let you 'refresh' a DataWindow row from the database.)

The next resort is to not send the SQL to the backend, which, as you remember, is the purpose of the builtin Update() function of the DataWindow.  This generates the appropriate DML (Insert, Update, Delete) based on the status of the row and column buffers of the DW.  Talk about encapsulation!

Finally, the LAST resort is on the level of the database server itself, which must choose to do a COMMIT before the changes are actually applied and the relevant resources unlocked.  If you do a ROLLBACK, you will undo the changes which were attempted, but no distinction is made between the changes you might have made, and those made to other parts of the database, such as trace or audit trail files.

Variables which hold object references
A very powerful option to PowerBuilder programmers is the ability to address other classes at runtime. This network-wide addressability, using proxy objects, is part of CORBA-compliant distributed PowerBuilder and Jaguar CTS. (see DataWindow synchronization). The 'instantiation' of a working copy of a class requires the declaration of a local/instance/shared/global variable of that class type, as: l_service_object u_service_object02. From that point on, the actual remaining syntax will vary. User Objects, such as the one cited before, would have a formal CREATE statement, as:

   l_service_object = CREATE u_service_object02

Structures are instantiated by their mere citation in your code, (i.e., no "CREATE"). Window instances are easy: use any variant of the OPEN function.

Recipe for Polymorphic Menus
We try to use a minimum number of menus, often needing only one menu per module, rather than one per window, as is often the case.  This simpler design is possible using a simple recipe (once you understand the syntax of these features): 1.) Code, using inheritance, a generic ue_save or we_save event, in your Window class 2.) In the menu clicked event for an abstract event dedicated to saving (updating) data in a DataWindow within a Window, code this. 
   Window lwin /* or some more specifically cast reference,
                            when more specific functionality required */

   lwin = w_main.GetActiveSheet
   If IsValid( lwin ) Then lwin.TriggerEvent("ue_save")

Be careful with the Application Object
The Application object is the entry point to the Powerbuilder application. There are 6 events in the application object.  Two of them have misleading names: connectionbegin and connectionend.  These have nothing to do with database connections, but rather to connecting with an app server written in PowerBuilder running as a separate process somewhere on the network.  Such app servers can centralize business rules and do 'middleware' type work, such as load balancing using alternate database connections. I was using the Open event of the App Object to create a number of custom NVO's, which in turn had posted events in them.  I had a lot of problems with this architecture.  I subsequently moved almost all the processing to the Open event of my MDI frame window, and the problems cleared up.

I have also had trouble with importing the modified source of an app object (in the Library Painter), and with trying to do Check Out on this object.

To conclude --- try not to mess with this!

Relationship of a database connection to DataWindow Painter
When you try to paint an existing DataWindow, you may think you need to have the actual table(s) that the DataWindow accesses at runtime.  This is not true, and can actually be quite handy, if you are trying to spruce up the visual layout of the DataWindow, and modify other characteristics and parts of the DataWindow, such as bands, conditional attributes, and the like.

The only thing you need to avoid is trying to modify the Data Source.  If you try to do that, the actual table(s), and the correct column names of same, must be present in the database. 

DataWindow Painter Tip
When you are using the DataWindow painter and trying to move things around with the mouse, it's all too easy to click on something and inadvertently move it. What I have found to be a good habit is to immediately use keyboard sequence {Ctrl} Z to 'undo' the last change. This is useful because I don't have to use the mouse again to try to reposition the object, and I am guaranteed that the previous position is restored. Again, you need to become habituated to this approach, but the amount of frustration which might accumulate by using the other approach should finally prove persuasive.

Limitations of the PowerBuilder Debugger
Don't try to use the debugger to trace through to a script which has been invoked through posting (i.e., PostEvent or its more modern equivalent).  The debugger will get lost.  Posting to methods can be appealing, but making synchronous calls to scripts using functions does provide better control over the flow of processing, and also works fine in the debugger.  You probably also know ( and certainly should know ) that focus-related events cannot be debugged using the debugger, since it contends with the object script for focus.  That is why an alternate approach is warranted, using FileOpen, FileWrite and related methods in a 'trace' or 'log' function, which can be run from any script.  Such an approach also pays off when you have deployed, and have runtime problems with the application.  You could also consider temporarily installing an extra listbox or external source datawindow to which you 'write' your trace/debug information, showing 'processing got this far' and 'variable contains this value' type of information.

Object Browser
One of the benefits of the object browser is that, for large applications with lots of PBL's, you can find an object alphabetically in the browser, then right click on it to edit it. This seems to be the fastest way to find an object (as long as you know its name, or at least would recognize it).

How to announce non ANSI-compliant syntax in Sybase
Use the fipsflagger setting of Sybase to get acquainted with which parts of a Sybase application can be ported to other RDBMS. Here is an isql session illustrating its import of fipsflagger:

1> set fipsflagger on
2> select db_name()

Line number 2 contains Non-ANSI text. The error is caused due to the use of
Sybase built-in function: db_name.

Other errors will occur when you use Sybase's T-SQL variables.  For example, a batch may cause this error:  Line n contains Non-ANSI text. The error is caused due to the use of
Sybase declared global/local variables.

Here's another:
use pubs2 will cause:
    SQL statement on line number 2 contains Non-ANSI text.

The error is caused due to the use of USE DATABASE.
PowerBuilder and Transactions on the Server
If you are not aware of the differences, and if you use a lot of the PowerBuilder training materials out there, you may entirely miss the very important differences in how your PB app is actually handling transactions.The functionality pioneered by Sybase, and incorporated into the Microsoft product, supports the use of 'Chained' or 'Unchained' mode of transaction handling. The Sybase default, the 'unchained' mode of transaction handling, is that EVERY DML statement is an atomic transaction.  In order to batch several statements which must stand or fall as a LUW, you need to use the BEGIN TRANsaction statement. The ANSI standard is that the server automatically and silently sends an effective Sybase-style 'BEGIN TRAN', and only requires (and supports) the COMMIT or ROLLBACK syntax.   The PowerBuilder training materials I have seen only refer to COMMIT and ROLLBACK in relation to transactions, and are therefore ANSI-compliant syntax, but those who are running Sybase or MS SQL Server must ensure that the 'chained' mode of operation is appropriate to the syntax in their PowerBuilder scripts, or vice-versa. It would be nice to just include conditional syntax in your PB scripts to test for whether chained mode is available or not, but you must be able to connect to such a server during compile time, which makes things a bit more problematic for those who don't have such connectivity.

Getting the DW Painter to recognize complex result sets in Sybase
A colleague of mine was having trouble with a Sybase stored procedure which had a lot of if constructs.  Even though the sp ran fine thru ISQL and the PW ISQL window, the DataWIndow would not build.  I recommended creating an extra SELECT query at the top of the routine which had a WHERE clause which was always false, and armed with this 'extra' result set, we could build the DataWindow.  We then subsequently modified the sp and all was copacetic.

Using the Repository for Dynamic DataWindows
To have more complete control over the starting properties of a DataWindow you create dynamically, be sure to populate the PB Repository table named 'pbcatcol' with custom values in the pbc_hght, pbc_wdth, pbc_mask, pbc_edit and others.

PowerBuilder 7 and the Web
The only direct relationship between PowerBuilder and the Web is the ability to leverage your understanding of DataWindows.  Using a Design Time Control (DTC), developers can generate a DataWindow which at run time does its powerful retrieve thing, with retrieval arguments, computed columns on server and on the DataWindow, and supports drop downs and expressions. At runtime, if you use PowerDynamo, you will then be able to create a ton of Javascript automatically.  You will also be able to take PowerBuilder NVO's  and drop them in the new Enterprise Application Server, original nickname Jaguar, and make your business logic available to anyone in the world with internet, CORBA or DCOM connectivity.  In any event, think NVO!

How Polymorphism and Menus work
If you are creating an MDI application, you must either create a menu for every sheet, or use polymorphism to make calls to the currently active sheet, either through inheritance or by having one menu per module or application.  As a novice, your best bet is to at least use inheritance to have a common look and feel for the menus, and to assign a specific menu to each window.

To have a polymorphic menu, have its scripts interrogate the MDI frame with GetActiveSheet, return a reference from there to the active window, then use TriggerEvent() to send a generic message, as TriggerEvent("we_save").

Using the Repository for Dynamic DataWindows
To have more complete control over the starting properties of a DataWindow you create dynamically, be sure to populate the PB Repository table named 'pbcatcol' with custom values in the pbc_hght, pbc_wdth, pbc_mask, pbc_edit and others.

Library painter tip for 'straddling' multiple folders, drives
If you have PBL's in your Library List which are on more than one drive, be sure to take advantage of a feature which started with PB 7.0, namely the ability to right click in the left-hand pane of the library painter, choose Set Root, then choose Library List.  This puts all of your PBL's, no matter how scattered, into a uniform view, making copy/move and other maintenance operations easier.

'Sneaky connect' behavior of Data Window Painter
When you preview a Data Window in the builder, you get a whole world of functionality which you would otherwise have to program. Normally a transaction object must be populated and used in a CONNECT operation. Then the SetTransObject() method is used to associate the runtime rereference of the connection with the DataWindow object. Finally, the Retrieve() and Update(), plus COMMIT/ROLLBACK operations and decisions are needed to finish the effect.

A 'quick and dirty' Entity-Relationship viewer/editor
If you are building a query or stored procedure and want to visualize the relationships between tables of interest, and you don't have ERwin, Rational Rose, Embarcadero or some other modeling tool available, access the Database Painter of PB and open the tables in question into the display area/workspace.  You right-click on the table in the 'objects' panel, then choose 'add to layout'. 

Graphic depictions of the RI links between tables, if any, will automatically appear; you can also, of course add PK-FK relationships from the Database Painter.  Although this diagram can't be directly printed, I usually use the {PrintScrn} key to put the image in the clipboard, then print it from there via Word, WordPad, or Paint.

Finding the stored procedure data source in the DW Painter
If you are maintaining a DataWindow object which is based on a stored procedure, and you want to see the sp name and calling syntax, you need to right click on the column definitions at the bottom of the screen.

Maintaining a running status display in a Window
In addition to the Microhelp feature, you can inform users of a long-running process how things are going by using either a ListBox or a DataWindow (external).  As you progress through the various steps of the process, either use AddItem() or InsertRow() to add a new line of info to the display.  One advantage of the DataWindow is its inherent print capabilties, which is good if you put the date and time, and maybe timings of how long the steps of the process runs, in the DataWindow.  If you use a Listbox, remember to turn off sorting!!

'Trust Me' complilation for references to objects which don't yet exist
If you have to refer, within one script, to a function which has not yet been coded in another object, or elsewhere within the current object, you have a way to satisfy the compiler and 'get on with your life'.  Assume you need to (finally) have a function named uf_return_rate() in an object named u_business_obj, to which you will refer via a global reference variable named, say g_bus.  The function, again,  has not yet been defined, not even its signature.  To get the current script to compile, use:

        g_bus.TRIGGER DYNAMIC FUNCTION uf_return_rate()

Because of the inefficiency of this method call, you should always go back and clean this code up, once the function in question has actually been created in the other object.

Java Notes for PowerBuilder Developers - OO compliance
Both Java and PowerBuilder base their architecture on inheritance, 'right out of the box'. For example, every Java object is finally inherited from java.lang.Object, and the PowerObject which sits at the top of every PB family tree. Both of the 'ultimate ancestors' support some basic functionality, but not much, in keeping with good OO design principles.

In Java, the term 'ancestor' will not register with most. Rather, you use the term 'superclass', and the descendant class is then called a 'subclass'. The this keyword is similar. By the way, PB is considered 'object based' because it derives all objects you can create from built-in class PowerObject. Java is truly OO, just like its counterpart, C++.

Whenever you inherit from a superclass in Java with the 'extends' keyword, you are specifying your direct ancestor (I mean 'superclass'). Like PB, Java only supports one direct ancestor/superclass. What java provides is the additional ability to use as many 'interfaces' as you need, to stipulate your ability to implement functionality specified in the interface). This 'implements' syntax lets you guarantee a large chunk of functionality so that you can amass some significant functionality in short order. In Java, you will need to use the SUPER keyword to cause the superclass code in the same method to run. This is implicit in PowerBuilder, so remember to make the adjustment.

Easier addressing of embedded controls

Because a Control on a tab page, such as a DataWindow or command button, must normally be addressed using at least two levels of dot notation, as 'tab_1.tabpage_3.dw_master', I often find it useful to create instance variables of the appropriate type (i.e., DataWindow or any subclassed version, CommandButton, etc.) and set references to those nested controls as the Window is initializing.  Seems a lot cleaner, but of course the nesting is not as apparent in scripts.

Thoughts on Jaguar-PowerBuilder integration
PowerBuilder DataWindows can be used in n-tier applications quite easily if you use Jaguar, and DataWindow synchronization.  What you do is use non-visual objects to sit on the Jaguar server as components.  Those components will have no visual characteristics at all (i.e. they will not be built with inheritance from any visual PowerBuilder class, such as Edit and Window), but will have instance variables which refer to one or more datastores.  You would connect to the database from the Jaguar-based component, and let the application server worry about database transactions, and optimizing use of backend connections.  You provide a public set of function calls in those components, from where you pass by reference a dataset from that component-based datastore to the DataWindow on the 'surface'.

(Related note: if you are using the PFC, you need to override several methods in the DataWindow class which look for a transaction object.  You will not be using a database connection from the client anymore, so rethink that, and just focus on function calls to get the data, with SetFullState() and GetFullState() methods on the NVO.)

Application Object events
Of the 6 events in the application object. 2 of them have misleading names: 
     connectionbegin and connectionend

These have nothing to do with database connections, but rather to connecting with an app server written in PowerBuilder running as a separate process somewhere on the network.  Such app servers can centralize business rules and do 'middleware' type work, such as load balancing using alternate database connections.

Building good validation into DataWindows
There are two common areas of misunderstanding when trying to build validation rules.  One of them is the requirement to use the GetText() function to interrogate user input.  This is the ONLY way to tell what they have typed; column name references refer to original (pre-input) contents if any.  Since the input is always passed by PowerBuilder as a string, use the data conversion functions to permit date arithmetic, math, etc.  The second common mistake is to misinterpret what the 'Invalid Expression' message means in that area of PowerBuilder.  This message can refer to either the validation rule OR the custom error message you may choose to assign.  Custom error messages MUST BE IN QUOTES!

Relationship of a database connection to DataWindow Painter
When you try to paint an existing DataWindow, you may think you need to have the actual table(s) that the DataWindow accesses at runtime. This is not true, and can actually be quite handy, if you are trying to spruce up the visual layout of the DataWindow, and modify other characteristics and parts of the DataWindow, such as bands, conditional attributes, and the like. The only thing you need to avoid is trying to modify the Data Source. If you try to do that, the actual table(s), and the correct column names of same, must be present in the database.

A more certain way to control when scripts run
If you need to have a called script run to completion before the next statement in the calling program is executed, put the first script into a function, and have the second test for the returned result of the called script. This is the easiest way to have your programs 'take turns' and run in a predictable order.

Client or Server --- Who should do what?
It is possible to perform the same work on either the client or the server in a client-server installation.  Those operations relating to sorting or filtering can be done in the PowerBuilder DataWindow with SetSort(), Sort(), SetFilter() and Filter(), but this should  be done on the server, since it can be optimized to do this work much more easily than the PowerBuilder client application.  That does not mean that you cannot give the user additional filtering and sorting capabilities in the client app once retrieval has been done.

It is also important to recognize the value of using stored procedures whenever they are available, since the query is then compiled into an executable and stored on the server, and is often stored in cache memory.  PowerBuilder applications can invoke the stored procedure and return a result set; the speed advantage over interactive queries grows as the database grows.

Changing the ancestry of existing PowerBuilder Objects
In the early days of 'growing' a class library, you may not know where in an inheritance scheme a particular window or user object may finally go.  What we have found very useful is to always create new objects with inheritance, but to deliberately inherit from very abstract placeholder objects, whose purpose in life is to, literally, be 'placeholders' in anticipation of later reassignment of the descendant.  Once we have matured that area of the architecture to understand where a given user object or window should belong, we 'retro-fit' that object by exporting its source using the Library Painter, then doing a search and replace of the 'placeholder' name with the desired 'true' ancestor.  Works like a charm unless you fail to inherit in the first place.  Windows inherited from type 'Window' (the default) cannot be reassigned.

Conditional Expressions in DataWindow Columns
You can conditionally hide, protect, move and otherwise govern the look and feel of a DataWindow column without resort to programming, by right clicking on the column in the Painter, then choosing 'Expressions'.  The test conditions of note are the IsRowModified() and IsRowNew() functions, to hide/show a bitmap or colored drawing object, or to protect any key columns if the Row is not new, etc.  Remember to double click to launch a friendly painter for your expressions.

Java Notes for PowerBuilder Developers - Messaging
Messaging between object instances is what you are used to with PowerBuilder, except that PostEvent style processing is not supported.   Note also the Java Messaging Service API (JMS), which will support asynchronous messaging over a distributed environment.

As with PowerBuilder, you can pass reference to rich objects through variables of that 'type'.  There is a 'new' keyword which instantiates an object and also runs its constructor method, which can be overloaded on the arguments portion of the signature.

The Java world supports 'callback' methods, so that EJB's, for example, being forced to implement interfaces which make it addressable, has callback methods so that the container / server can communicate with them.  You can do this with PowerBuilder; it's just that the EJB container chooses to do the messaging, and it's out of your control in EJBs.

Java Notes for PowerBuilder Developers - Instantiation
Both Java and PowerBuilder support what is called a 'factory method', whereby a runtime reference is generated dynamically.  The PB CREATE USING {String} syntax has its counterpoint in Java.  See, for example, the EBJObject and the 'home' in EJBs.

When an instance is being created in Java, you can take advantage of the requirement that every class being instantiated will have its own constructor event (in PB parlance), in the sense that if you do not explicitly code a constructor, the runtime environment creates an empty executable version of the constructor during instantiation.

Use overloading to permit you to pass arguments to the class during the 'new' invocation of an instance.

(By the way:   In Java, a 'constructor' is not an event with that name; rather, it is a function (method, to be more correct) which is given the same (case sensitive)  name as the class which it services. 

Thus, for a class named MyClass, there should (and finally, will)  exist a method with the same name.  More than one version of the constructor method may be created, using different argument lists.  For example, the String class can be fed a content during its instantiation .

Checkout and Connect

Library checkout is easy once you remember to CONNECT to the source control regime for your current installation. If the Check in / Check out choices are greyed out in the popmenu, choose Source then Connect from the Library toolbar.  As with previous versions, you must provide a name which is associated with the checkout.  This is a freeform label, and has no relationship to any other network or application or database names.

Powered by WebRing.