PowerBuilder, Appeon, SQLAnywhere, ASE, EAServer and .Net

Chris Pollach

Subscribe to Chris Pollach: eMailAlertsEmail Alerts
Get Chris Pollach via: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn

PowerBuilder: Article

PowerBuilder Feature Story: "Context-Sensitive Help"

As I started a new PocketBuilder application, I wanted to encompass some of the "context sensitive" help features

The Las Vegas heat must have gotten to me as I started a new PocketBuilder application and wanted to encompass some of the "context sensitive" help features that I have previously used in a PowerBuilder application. I knew that this was not going to be easy, as the Pocket PC operating system does not support the (Multiple Document Interface) behavior. As a result, neither the Micro-Help bar nor pop-up help (also known as "bubble help") are available as they would be in a standard PowerBuilder application. PowerBuilder developers also use a nice feature named "PowerTipText" that allows automatic "bubble" help to appear during a mouse hover.

Just to be on the safe side, I selected the F1 key on my laptop inside of PocketBuilder and checked out the ShowPopupHelp ( ) and SetMicroHelp ( ) commands. Guess what..."Not supported." Okay...I knew that! However, being a stubborn Great White North developer (you have to be tough here in the Canadian winter), I set out to tackle this limitation. One of the mechanisms I used to get there was the use of the database "system tables" that the PowerBuilder and PocketBuilder IDEs create and maintain for the developer.

Still one of the great features of PowerBuilder and PocketBuilder is their ability to use any RDBMS vendor's database to store development data in the "system table" area (see Figure 1). The system table area is normally where you (or your DBA) place the database schema that defines the metadata (information about your data). This area is also available to the PowerBuilder and PocketBuilder development tools along with Sybase's InfoMaker report writer. All these Sybase development tools make use of the system table area (would you believe designed and available since PowerBuilder release 1.0) to store "metadata" about your database. The first developer who connects to a new database area using InfoMaker, PocketBuilder, or PowerBuilder with DBA privileges (i.e., "Create Table") has the development tool automatically add five new tables to the system table area. These five tables are PBCatCol, PBCatEdt, PBCatFmt, PBCatTbl, and PBCatVld. To view these new System Tables, open the database painter and connect to your favorite database, then select the "Tables" item in the TreeView of the database Painter and use the "alternate" (aka politically incorrect "right-hand") mouse button to produce a pop-up menu. From the pop-up menu, select the "Show System Tables" option. Once selected, this will force InfoMaker, PocketBuilder, or PowerBuilder to show you all the system table area - notice the PBCatxxx tables in the list.

The PBCatxxx tables are where the Sybase IDE tools store extensions to the database schema tables and columns that pertain to the developer's delivery of information to the GUI user. The DBMS does not care about the GUI world and, in fact, it detaches itself from it. In the graphic environment, however, we are concerned about properties such as font, height, units of measure, formatting, and validating. The PBCatxxx tables supply this exact information to the Sybase development tools. It should be noted here that this applies only to database designers who use tools like PowerDesigner or System Architect and know how to also load this GUI information into the Sybase PBCatxxx tables. Another alternative, before any development team starts its project work, is to have one developer take the time to use the Database Painter to include this extra "metadata." Doing so can improve the development time of a project from 3 to 15 times.

Assuming that your manager is too naive or cheap to purchase a design/modeling tool like PowerDesigner, fear not, you can still use the InfoMaker, PocketBuilder, or PowerBuilder's DataBase painter to achieve this tremendous productivity gain; as you will see shortly, this will tie into the need and delivery of the "Context Sensitive" help.

Loading Your Metadata
To begin loading your PBCatxxx System Tables using the InfoMaker, PocketBuilder, or PowerBuilder database painter, open the database painter and carefully note the "extended" properties of either a "table" or a "column" entity. For a table, the extended property categories are in the General tab's "Comments," all of the Data Font, Heading Font and Label Font tab pages (see Figure 2). For the column entity, the extended properties are "Comments" in the General Tab, all of the Headers, Display, Validation, and Edit Style tab pages (see Figure 3). Any property that you fill out in the areas listed above are automatically saved to the PBCatTbl system table (for a table) and the PBCatCols system table for any column properties that are beyond the vendor's ANSI implementation.

I should also take the time to mention that the database painter is included in the current DataWindow.NET product and will be present in the new DataWindow.NET plug-in for Visual Studio 2005. So the tips I am referring to in this article focus mainly on the PowerBuilder and PocketBuilder development tools but will also work for this new environment as well. The PBCatxxx tables are always present after the first connection to your Oracle, Sybase, Microsoft, IBM, or other DBMS. If there are security implications for creating these five new system tables, Sybase provides DBA scripts to populate these table structures manually by using your favorite DBA utility.

The next three PBCatxxx tables are for Edit Styles, Validation Rules, and Display Formats (see Figure 4). These extended properties can be added to the System Table area using the "Extended Attributes" panel of the database painter. This allows the developer to input these extended properties, which I would imagine most seasoned PowerBuilder developers would recognize also appears in the DataWindow painter. Taking the time to add this metadata in the DBMS will allow this extra information to be available to the DataWindow painter during the creation of the DataWindow object. Take note of this last statement as this also applies to the Table and Column extended properties as well.

Remember though that the PBCatxxx system tables are just standard DBMS tables that can be read by your development tool. While in the database painter, you can locate the ISQL pane and select information from any of the PBCatxxx tables. For example, in the Employee table - if I attach a "Display Format" of '$#,##0;($#,##0)' to the Salary column, then, using the following query:

SELECT "pbcatfmt"."pbf_frmt",
    FROM "pbcatcol",
   WHERE ( "pbcatfmt"."pbf_name" = "pbcatcol"."pbc_mask" );

we can see if there is a correlation between the Display Format and any column - in particular, the Salary column. Running the above query resulted in the following result set in my development environment and indicated that the display format I just created was indeed connected to the salary column within the Employee table.

Using the above information and test we can also extend this behavior to the "Comments" part of the PBCatCols system table. The reason I might be interested in this would be to automate the Help file generation. Then I could use the ShowPopUp ( ) command in PowerBuilder to dynamically support this feature. An SQL query for this information might look like this:

SELECT "pbcatcol"."pbc_cnam",
    FROM "pbcatcol"
    WHERE "pbcatcol"."pbc_cmnt" is not NULL

The above query should return the column name and the column's comment textual value as located in the PBCatCol table. For this test I added a comment to the Employee's Sex column and the query above resulted in the ISQL result set shown in Figure 5.

Help Compilation
Once you have taken the time to populate appropriate comments on the data elements within your data model (schema), the extended properties will also be loaded into your database's system tables. You can use the technique I just described earlier to extract the column name and the column's comment text using the previous SQL example. To test this, I commented the "Contact" table's columns and ran the SQL, which resulted in a two-column output in the ISQL pane of the database painter. To export this information, I used the "alternate" mouse button (RHMB) and chose the "Save Rows As" option in the resulting pop-up menu. I then proceeded to save the result set as a tab delimited text file. Using this format allows the developer to easily include this key information into any help compiler.

More Stories By Chris Pollach

Chris Pollach is a Senior Consultant with over 30 years experience in Systems and Software Analysis, Development, Maintenance and Technical Support, mainly in the areas of GUI Design, MS-Windows Programming, Java / .NET Programming, Wireless, Application / Web Server Design & Programming, Object Oriented Development Tools and Methodologies, Data Base, Data Communications and Network application development. He has participated in numerous technical, planning and management roles, as well as consulted and educated in these fields for a diverse clientele. He is also the owner of “Software Tool & Die Inc.” a company dedicated to provide custom software and education solutions on Object Oriented business systems.

As an educator, Chris is certified to teach PowerBuilder (first in Canada), MS-SQLServer, Sybase’s Enterprise Application Studio and EAServer integrated application/web development environment. He is former Certified SilverStream developer (CSSD) and current Certified PowerBuilder Developer – Associate / Professional (CPD-P) as well as a Certified Sybase Tools Instructor (CSI).

Chris has written numerous articles in various popular personal computer magazines, newsletters and is the author of the PowerGuide and PowerExpert products as well as the STD Foundation Classes. Currently, Chris has developed a Foundation Class library for Sybase's PocketBuilder, SAP's PowerBulder and EAServer products and now Appeon Web & mobile products that integrates JSP or ASP web development, Section 508 / CLF web standards and mobile applications. A new Web Service framework has also been released for IIs to support PowerBuilder based web service NVUO's!

Chris recently became a 2nd Degree Black in the TaeKwonDo martial art and has developed a Martial Art multimedia study guide using the Component-One “Doc2Help” and Sybase PowerBuilder products. Since the fall of 2004 he became a TaeKwonDo instructor for the City of Ottawa’s Goulbourn program. He has also been certified with the World TaeKwonDo Federation (February 2005 - 1st Dan and October 2008 - 2nd Dan).

Chris was awarded the Sybase “Innovation and Achievement” award for 2005 as voted for by the International Sybase User Group (ISUG). This award was presented for innovations to the PocketBuilder mobile development product, contributions to the PowerBuilder News groups and support of the Ottawa Sybase User Group.

To round his management and leadership skills, Chris is the former president of the Kiwanis Club of Goulbourn and still volunteers his time with the service clubs in his area. He is also the coordinator of the Ottawa Sybase User group and a certified NAUI scuba instructor. For the last three years, Chris has been voted onto the ISUG Board of Directors and holds the position of "Director - North American User Groups".

Comments (2)

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.