Can't find what you are looking for? Try these pages!


Status Help Automated - Using SQL column descriptions as status help

June 3,2019
By Vincent Oorsprong

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
Send SQLClose of hoStatement
Send SQLDisconnect of hoConnection
Send Destroy of hoSQL

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]

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.