Status Help Automated - Using SQL column descriptions as status help

As an application developer building Windows or Web apps, you have not delivered a complete application if it does not contain a form of online help.

It’s a bit harsh, but true, that writing documentation is an expensive job and there are not so many developers who enjoy doing it … most like to be creative, and writing doc just isn’t seen as very attractive.

The same can be said about adding status help, tool-tips, and placeholders to an application, although it is a bit easier as a developer can add it directly in the source code or as status help in the data dictionary class. Adding status help in the data dictionary can also been seen as documentation for the columns of a table in the database.

If you create the tables of your database, and with that the columns, it should be an easy task as you already have the details about what goes in the column in mind or in a document. If you do not own the database and you’re writing a program around an existing database, it is more difficult as you might not have these details and adding status help to our data dictionaries for what may be hundreds of tables is not an easy job.

I recently discovered that the Adventure Works 2012 example database from Microsoft contains a column description, and we could use this dynamically to provide help in the application. The advantage of using this dynamic way is that the help can be changed at the database level, and the application developer does not need to adjust, or ask for more information.

The SQL statement for the column description is:

SELECT [objname], [value] FROM [fn_listextendedproperty] (NULL, ’schema’, ’" - <SchemaName> - "’, ’table’, ’" - <TableName> - "’, ’Column’, NULL);

With this information I decided to make a cMSSQLDataDictionary class. This is a sub class of the DataDictionary class and could be easily used inside your projects based on MSSQL where the database contains column information.

Procedure RetrieveColumnDescriptions
   Handle hoSQL hTable hoConnection hoStatement
   String sSchema sTableName sColumnName
   String[][] sStatusHelpData
   Integer iColumns iColumn

   Get Create (RefClass (cSQLHandleManager)) to hoSQL
   Get Main_File to hTable
   Get SQLFileConnect of hoSQL hTable to hoConnection
   If (hoConnection <> 0) Begin
      Get SQLOpen of hoConnection to hoStatement
      If (hoStatement <> 0) Begin
         Get_Attribute DF_FILE_OWNER of hTable to sSchema
         Get_Attribute DF_FILE_TABLE_NAME of hTable to sTableName
         Send SQLExecDirect of hoStatement ("SELECT [objname], [value] FROM [fn_listextendedproperty] (NULL, ’schema’, ’" - sSchema - "’, ’table’, ’" - sTableName - "’, ’Column’, NULL);")
         Get SQLFetchResultsetValues of hoStatement to sStatusHelpData
         Get Field_Count to iColumns
         For iColumn from 1 to iColumns
            Send CopyColumnDescriptionElement iColumn hTable sStatusHelpData
         Loop
         Send SQLClose of hoStatement
      End
      Send SQLDisconnect of hoConnection
   End
   Send Destroy of hoSQL
End_Procedure

The above method retrieves the column descriptions and sends a message to add it as data dictionary status help for each column of the table. The code for that is:

Procedure CopyColumnDescriptionElement Integer iColumn Handle hTable String[][] sStatusHelpData
   String sColumnName
   Integer iElements iElement

   Get_Attribute DF_FIELD_NAME of hTable iColumn to sColumnName
   Move (SizeOfArray (sStatusHelpData) - 1) to iElements
   For iElement from 0 to iElements
      If (sStatusHelpData[iElement][0] = sColumnName) Begin
         Set Status_Help iColumn to sStatusHelpData[iElement][1]
         Procedure_Return
      End
   Loop
End_Procedure

Now all we have to have is the cMSSQLDataDictionary class sending the message RetrieveColumnDescriptions, and that can be done in the End_Construct_Object event.

I don’t know if other databases have a similar option, but if they have it is probably not much work to get this implemented.