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",
      "pbcatcol"."pbc_cnam",
      "pbcatcol"."pbc_tnam"
    FROM "pbcatcol",
      "pbcatfmt"
   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",
      "pbcatcol"."pbc_cmnt"
    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.

• Cracking the IT Da Vinci Code