Accessing Cursor Data using Column Labels

The purpose of Dumont is to make the life of the Commence Developer easier. It does this by subclassing existing Commence objects, and adding additional functionalities to them making them easier to work with.

What is subclassing? Subclassing is a programming term whereby an 'object' within a software program is "wrapped" with another object in another software program. When this "wrapping" occurs, all of the functions in the first object are exposed in their original form, so as to maintain compatibility with existing programs that use them, and then, usually, additional functions are added to the new object (the wrapper object) so that it looks like the original object but with additional new features.

This is what Dumont does. It sub-classes all the Commence objects (wraps them) then exposes them in their original form, while at the same time adding new features and functionalities to them, thus making them easier to work with.

In the following example, the Commence cursor object is wrapped with a Dumont object that looks just like the original cursor object. The difference is, the Dumont cursor object adds special column handling functionality to the cursor object. For one thing, it aleviates the programmer from having to deal with column numbering. The Dumont cursor object handles all the column numbering internally allowing the programmer to easily get to column values by name rather than by column index number. This small improvement can mean a great deal when it comes to working with a large program.

Secondly, when Dumont is accessing columns by name rather than index number, it is doing so internally, using internal index name/value pairs. What this means is that a translation from a column name to a column number happens very quickly, and does not involve the Commence API. Translation: not only will your programs will be easier to work with but they will also run faster!

Furthermore, by handling duplicate columns in a safe manner, and if you are in a situation where you have designated a column twice (either by accident or intentionally) Dumont will not barf on your program. It will simply accept the duplicate column designation, prevent the request from propagating into the Commence API (because that WOULD be a mistake) and continues along without error.

Finally, Dumont adds some additional functions to the cursor object for designating both static fields and connections using the same basic syntax. This makes programming far easier, makes the programs much more reliable and therefore easier to maintain in the long term.

vbScript Example of accessing column data using column labels.
 '
 ' Hook into Dumont
 '
 dim dexe: set dexe = createObject("Dumont.EXE")

 '
 ' Get to the application via the R.O.T. (running object table)
 '
 dim dapp: set dapp = dexe.applications("KES.mwp")
 
 '
 ' Open the cursor
 '
 dim cursor: set cursor = dapp.db.getCursor( "Matters" )
 
 '
 ' Assign the columns
 '
 dexe.debug "Matter No:      " & cursor.setColumn( "Matter No"                          )
 dexe.debug "Matter Name:    " & cursor.setColumn( "Matter Name"                        )
 dexe.debug "Client Contact: " & cursor.setColumn( "Client", "Contacts", "Contact Name" )
 dexe.debug "Label:          " & cursor.setColumn( "Label"                              )

 '
 ' Try connecting the same column twice, see if we get an error.
 '
 dexe.debug "Matter Name:    " & cursor.setColumn( "Matter Name" ) ' no error, no duplicate!
 
 '
 ' Show a few things
 '
 dexe.debug "rowCount " & cursor.rowCount
 dexe.debug "colCount " & cursor.columnCount
 
 '
 ' Show all the column labels
 '
 dim i: for i = 0 to cursor.columnCount - 1
   dexe.debug "col " & i & " label " & cursor.getColumnLabel(i)
 next
 
 '
 ' Dump some columns, accessing the values by index number
 '
 dim qrs: set qrs = cursor.getQueryRowSet(1)
 for i = 0 to qrs.columnCount - 1
   dexe.debug "------"
   dexe.debug "col " & i & " label " & qrs.getColumnLabel(i)
   dexe.debug "value: " & qrs.getRowValue( 0, i )
 next

 '
 ' Dump some more stuff, accessing the values by label
 '
 dexe.debug "------"
 dexe.debug "Matter No:   " & qrs.getRowValue( 0, "Matter No"                    )
 dexe.debug "Client Cont: " & qrs.getRowValue( 0, "Client Contacts Contact Name" )
 dexe.debug "Label:       " & qrs.getRowValue( 0, "Label"                        )
 dexe.debug "Matter Name: " & qrs.getRowValue( 0, "Matter Name"                  )
Produces the following output:
 Matter No:                    0
 Matter Name:                  1
 Client Contacts Contact Name: 2
 Label:                        3
 Matter Name:                  1
 rowCount 3684
 colCount 4
 col 0 label Matter No
 col 1 label Matter Name
 col 2 label Client Contacts Contact Name
 col 3 label Label
 ------
 col 0 label Matter No
 value: 00001
 ------
 col 1 label Matter Name
 value: WITTER
 ------
 col 2 label Client Contacts Contact Name
 value: TAG Consulting Services
 ------
 col 3 label Label
 value: TAG - WITTER
 ------
 Matter No:   00001
 Client Cont: TAG Consulting Services
 Label:       TAG - WITTER
 Matter Name: WITTER
Setting Columns (overkill)
 dim cursor: set cursor = cmdb.getCursor("Person")
 dim colFullName: colFullName = cursor.setColumn( "Full Name" )
 dim colLastName: colLastName = cursor.setColumn( "Last Name" )
 dim colEmplBy:   colEmplBy   = cursor.setRelatedColumn( "Employed by", "Company", "Company Name" )

 '
 ' This code duplicates the "Last Name" column setting, but the application
 '  does not generate an error, and the index number returned is the same
 '  as the colLastName value.
 '
 dim colDupeName: colDupeName = cursor.setColumn( "Last Name" )

 dim i: for i = 0 to qrs.rowCount - 1
   dexe.debug qrs.getRowValue( i, colFullName ) & vbCrLf & _
              qrs.getRowValue( i, colLastName ) & vbCrLf & _
              qrs.getRowValue( i, colEmplBy   )
 next
Note:
In the code above, the column numbers that are assigned to each column are, truthfully, of little value to your application, since it is possible to access the column values directly by column name rather than by column number, and the API wrapper functions cache the column number values making accessing columns by name a very fast procedure. See the following:
Setting Columns using the internal column number cache (preferred method)
 dim cursor: set cursor = cmdb.getCursor("Person")
 cursor.setColumn "Full Name"
 cursor.setColumn "Last Name"
 cursor.setColumn "Employed by", "Company", "Company Name"
 dim qrs: set qrs = cursor.getQueryRowSet( cursor.rowCount )
 dim i: for i = 0 to qrs.rowCount - 1
   dexe.debug qrs.getRowValue( i, "Full Name" ) & vbCrLf & _
              qrs.getRowValue( i, "Last Name" ) & vbCrLf & _
              qrs.getRowValue( i, "Employed by Company Company Name" )
 next
Note:
In the code above, the column numbers that are actually assigned to the listed columns are ignored. Columns are accessed by their "Name" values and not their column number values. The cmcCursor object caches the column number values and can, therefore, look up the actual assigned column numbers very quickly without calling in to the Commence API.
If you are determined to write good code that checks for error conditions on various calls, and deals with them properly, you might implement a function like this:

Setting Columns and Checking Return Values
 function setColumns( ByRef cursor )
   setColumns = false ' false means we ended in error
   if( cursor.setColumn("Full Name") = -1 ) then exit function
   if( cursor.setColumn("Last Name") = -1 ) then exit function 
   if( cursor.setColumn("Employed by", "Company", "Company Name") = -1 ) then exit function
   setColumns = true ' success!
 next




~ ~ ~ ~ ~ ~
Source Code without Comments is like a Cranberry Garland
without the berries. Comment your Code!
 
Commence Database Support User Group Forum
http://newsgroup.showoff-db.org/
~ ~ ~ ~ ~ ~
Author: Mark Petryk
Lorimark Solutions, LLC
mark@lorimarksolutions.com