Andy Kramek

What is a Key?

andykr — Wed, 03 Sep 2008 11:36:00 GMT

The word "key" has a very specific meaning in relational database design. It refers to a field whose value is shared between tables. The purpose of such data duplication is to enable a record in one table to be associated with a record in another, thereby allowing the extraction of information that is distributed between the tables. Such an association is referred to as a 'relationship' and is what differentiates relational databases from flat-file databases.

There are two types of relational key:
· PRIMARY A primary key is the column, or combination of columns, whose value uniquely identifies an individual record within a table. Neither the primary key, nor any part of it, may have a value of NULL
· FOREIGN A foreign key is the column, or combination of columns in one table whose value identifies an individual (i.e. related) record in another table. A foreign key may, under some situations, have a value of NULL indicating that there is no related record

When defining a relationship between two tables, the table containing the primary key is the "Parent" (also known as the "Referenced Table") and the table containing the foreign key is the "Child" (also known as the "Referencing Table"). Thus the foreign key references the primary and thereby defines how the records in the tables are linked.

It is a fundamental rule that all tables must have a primary key. This is obvious if you consider a table that contains the real names of my own immediate family as listed below.

First Name  Last Name
Stanley     Kramek
Elaine      Kramek
Andrew      Kramek
Richard     Kramek
Angela      Kramek

This is fine and shows my father (Stanley) and mother (Elaine) together with my younger brother (Richard) and sister (Angela). This table's primary key is the combination of first and last name - and that is perfectly allowable. However it is not a good design because when my brother married a girl named "Angela" (and yes, he really did) we encounter a problem when we try to add my new sister-in-law to this table:

First Name  Last Name
Stanley     Kramek
Elaine      Kramek
Andrew      Kramek
Richard     Kramek
Angela      Kramek
Angela      Kramek

Now we have no way to differentiate between my sister, and my sister-in-law! This table has no longer has a primary key and in the absence of any additional information is effectively useless. Of course, the obvious solution is to add some additional, differentiating, information perhaps 'middle initial' – although that is not a particularly good choice since not everyone has one, and the value might still not be unique, and as it happens it isn't (my brother's middle name is "Maurice", my sister's is "Mary" and my sister-in-law happens to be "Margaret").

First Name  Last Name   Middle Initial
Stanley     Kramek
Elaine      Kramek
Andrew      Kramek         E
Richard     Kramek         M
Angela      Kramek         M
Angela      Kramek         M

The point of this example is that it is critically important to define a primary key for each table so that every record can be uniquely and unambiguously identified. So far we have defined the primary key as a combination of columns that are used to store the data. This is referred to as a "compound" key.

The first problem with compound keys (as this example shows) is that it is perfectly possible to end up with duplicate values in several columns and so such keys tend to get ever-more complex as data is added to the database.

That raises a second problem for compound keys because any table that references a table that has a compound primary key must carry all of the elements of the key. So in order to link any other information to my 'family' table I would need to duplicate all the information in that table - which makes the task of updating information horrendously difficult.

A much better option would be to use some unique value associated with each individual that can be stored in a single column and, fortunately we have just such a thing available to us – our Social Security Number. SSNs can be stored in a single column and have a value that is unique to the person. This will, finally, allow us to identify each record in the table unambiguously. It is, therefore, a perfectly natural choice for the primary key in any table holding information about people.

In fact Social Security Number is an example of what is called a "Natural" Key (also known as either an "Intelligent" or "Business" Key) and, in this example it will work very nicely giving us a final, usable, version of the table as shown.

First Name  Last Name   Middle Initial   SSN
Stanley     Kramek                       814-56-8975
Elaine      Kramek                       823-22-4578
Andrew      Kramek         E             866-41-8537
Richard     Kramek         M             866-77-5411
Angela      Kramek         M             866-23-8544
Angela      Kramek         M             822-39-6425

But hang on! Are Social Security Numbers truly unique? The answer is 'no'. In fact there are rules that define what constitutes a valid SSN and consequently there is a finite limit on the actual number of truly unique SSNs that can exist – additionally there are certainly cases on record of duplicate SSNs being issued.

This highlights the first of several problems associated with the use of natural keys - are they truly unique? In fact, in order to be usable as a primary key, a candidate must possess three attributes:

· Uniqueness: The value must be unique within the table

· Consistency: The value must be capable of being validated in terms of the data type to which it is being assigned. In other words, the specified value must be consistent with the rules for the database. For example, we can be sure that 35/45/2009 is not a valid date, but it is impossible to determine whether "123456789" is an SSN that is missing the dashes that format it, or some other value altogether

· Verifiabity: The value of a natural key refers to some real entity and so we have to be sure that the entity actually exists. For example, we know that an SSN is invalid if the first three digits are higher than '770', but we cannot determine whether "123-45-6789" is a 'real' SSN or not

However these are not the biggest issues associated with the use of natural keys as primary keys. The real problem with them is that they are governed by rules that have nothing to do with their use as a primary key, and so are subject to change. Remember, the definition of a primary key is that it must be unique within a table and it may not be null. However, the rules governing SSNs, Vehicle Identification Numbers, Account Numbers, Invoice or Order Numbers, or any of the dozens of other obvious 'natural' keys have nothing whatever to do with 'being unique within a table' which is the fundamental requirement for a primary key.

Does that matter? Emphatically yes! Since the values are governed by rules that relate to their principal function they are subject to restrictions that are irrelevant in the context of a primary key. For example, a very common requirement for Invoice Numbers is that they should be issued in an unbroken sequence. If an invoice number is also used as a primary key, the system has to ensure that users do not allocate an invoice number that conflicts with that rule, and prevent users from assigning a number and then abandoning the invoice – even though it makes absolutely no difference whether the primary keys form an unbroken sequence or not (so long as they remain unique within the table). Managing the unbroken sequence requirement for a primary key introduces unnecessary complexity (and hence potential for error) into the system.

Change is, of course, the other issue. Since Natural Keys reflect real entities they are liable to change. Primary keys are also stored (as a foreign key) in all related tables, so it is imperative that any changes to the actual value of a primary key must be reflected in all related records. This can have major impacts on a database since it will require that all indexes involving the key be re-built, and may even force physical re-ordering of data within the database. Now, you may be wondering, at this point, why a natural key used as a primary key would ever change. The commonest reason is, of course, 'human error'. Which leads us to the final issue with Natural keys - that of how they are captured in the system.

Natural keys cannot, by definition, be generated by the database. They are also, almost invariably, examples of data that cannot even be derived and so must be input directly by some user (SSN is a good example of this principle; given that the last SSN entered into the system was "876-11-1655" what is the next one going to be? There is, of course, no way to know!).

The issue is that there is a limit to the amount of validation and verification that can be performed and so it is perfectly possible for errors to be entered into the system. It is axiomatic that in the context of data there is no software solution to "Valid, but Wrong". In other words no matter how much validation and checking you do, you cannot detect that the SSN "123-45-6789" should really have been entered as "123-45-6798"!

Of course, it is likely that these errors will, eventually be caught. Then they have to be corrected with all the problems that this raises. In fact, in applications based on natural keys, the routines for handling changes and updates to key values are usually the most complex and difficult code in the entire system. So what is the alternative? Enter the "Surrogate Key"

A surrogate key is simply a column added to the table whose only function is to be the primary key for that table. Typically these columns are simple integers and all major databases provide for the auto-generation of the values. In SQL Server these columns are called "Identity" columns, in VFP "Auto-Incrementing Fields" and other databases use other names but the principles are the same.

All of the issues outlined above in respect of Compound keys, and Natural keys, disappear when a surrogate primary key is introduced. As records are added to a table, the database itself generates the next value for that table and assigns it to the new record. There are no issues because the actual value has no significance whatever – it merely has to be unique within the table and the database itself ensures that this is done properly.

This also means that surrogate keys never need to change. Since the actual value does not matter there is never any reason to change it. Furthermore, because the value is generated as an integer, it requires very little space (4 bytes) and is easily indexed and accessed, making the establishment and maintenance of relationships much more efficient.

In the context of normalization surrogate keys have the added benefit of not requiring analysis any deeper than the Boyce-Codd Normal extension of third normal form. (This is because all further normalization rules apply only to cases where the primary key consists of more than a single column).

In short, there are really no reasons NOT to use a surrogate primary key on all tables in a relational database. In those special cases where data must be generated at different locations and later merged, or when keys must be generated by the client rather than by the server, GUIDs provide a better choice than simple integers as primary keys. But changing the data type does not affect the principle that surrogate keys are the best way to define primary keys.

Access and Assign methods can be useful!

andykr — Sat, 21 Jun 2008 14:15:00 GMT

Access and Assign methods were introduced into VFP back inversion 6.0, but for many developers they are still very much an "unknown feature". My objective today is to show a couple of examples of where Access and Assign methods can be very useful. Let's start by reviewing just what Access and Assign methods are, and how to create them.

Basically these methods are event-driven in the sense that, if one is defined for a property, it is fired whenever the associated event occurs. So for an assign method, the event is (not surprisingly) the assignment of a value to the property. Note that it doesn't matter how that assignment is made (i.e. whether you use the STORE command, or simply an "=") the method is fired. Similarly for an Access method the triggering event is any reference to the property. Again the actual method does not matter; the method fires whether the property's value is being read into a variable, or used in an object reference or even being output using the "?".

You can add Access and Assign methods to any custom property that you create, and to most of the VFP native properties through the Edit Property/Method dialog. Exceptions are the Value property of a control and native properties of ActiveX controls (though you can add them to the properties of a VFP OLE Control that hosts an ActiveX control).

To create an access or assign method for a property all you need to do is define a method that is named for the property plus the suffix "_Access" or "_Assign". This is handled automatically in the visual designers in both the "New Property" and "Edit Property/Method" dialogs where you simply check the appropriate box to create the method. In code you simply declare the method in the class definition like any other method. Note that an Assign method MUST define an input parameter to receive the incoming value, while an Access method has no parameters at all.

DEFINE CLASS xObj AS SESSION
*** Define a new property
iLastID = 0
*** Create an Assign Method for the new property
PROTECTED FUNCTION iLastID_Assign ( tuInval )
    *** Only allows values >= 0
    IF VARTYPE( tuInval ) = "N" AND tuInval >= 0
      This.iLastid = tuInval
    ENDIF
ENDFUNC
*** Create an Access Method for the native DataSessionID property
PROTECTED FUNCTION DataSessionID_Access
    *** Returns the DataSession NOT the datasessionid!
    RETURN This.DataSession
ENDFUNC
ENDDEFINE

In this simple definition I have defined a custom property that will only ever accept numeric values that are greater than or equal to zero. Trying to set this property to anything else is simply ignored (we could, of course, return an error). In the case of the DataSessionID property, the access method means that trying to read the current value of the property will return, instead, the current datasession number. The DataSessionID property is, therefore, effectively hidden.

Creating Strongly Typed Properties

One really practical use for an assign method is to create strongly typed properties so that errors cannot occur in your code because a property has an inappropriate value. This can effectively reduce the need to check values repeatedly in code by handling the checking at the point at which a value is assigned. If it fails the test the property rejects the value. The basic methodology is illustrated above, but the code can, and usually is, much more rigorous than indicated there.

One scenario where I use an assign method like this is when storing a record number, or ID value. Obviously I do not want an invalid number in this case so I use an Assign method that forces the value to be an integer in the appropriate range. Here is the code from a property used to store the current user ID value:

PROTECTED FUNCTION iUserID_Assign( tnValue )
IF VARTYPE( tuInval ) <> 'N' OR EMPTY( tnValue )
    *** Not a number, ignore it!
    ASSERT .F. MESSAGE "User ID must be passed as an integer"
ELSE
    IF NOT INT( tnValue ) == tnValue
       *** Not an integer, ignore it
       ASSERT .F. MESSAGE "User ID must be passed as an integer"
    ELSE
      IF tnValue < 0
         *** Not 0 or higher, ignore it
         ASSERT .F. MESSAGE "User ID must be passed as positive integer"
      ELSE
        *** We may allow this one – check it
        SELECT userid FROM usertable WHERE userid = tnValue TO SCREEN NOCONSOLE
        IF _TALLY = 1
          *** This is a valid ID
          This.iUserID = tnValue
        ELSE
          *** Ignore it
          ASSERT .F. MESSAGE "User ID must be passed as positive integer"
        ENDIF
      ENDIF
    ENDIF
ENDIF
ENDFUNC

Notice the use of the "TO SCREEN NOCONSOLE" in the SQL query – this is an old trick that suppresses the output from a query and is useful when we want is to know if a record exists, or how many records meet the criterion – effectively this is executing a SQL Statement of a type that is not normally permitted in VFP:
_TALLY = SELECT COUNT(*) FROM [source_table] WHERE [condition]

Running Code with SETALL()

Another use for an assign method is to enable code to be run on multiple objects using SetAll(). This method, which exists on all VFP Container classes, is used to set the same property to some specific value on all objects in scope that have that property. If an object does not have the property in question, the instruction is ignored in the context of the object and does not cause an error.

There are some scenarios is which it would be very useful to be able to run some specific code on all objects on a form, or in some container, but only if they actually have the specified method. But SetAll() only applies to properties, not methods, and so to do this we would need to loop through the objects collection and use PEMSTATUS() to determine if each object had the relevant method. If so we would call it and then proceed with the next object.

A much easier option is to simply create a property with an assign method on the object that calls the required method from inside the Assign. After all, there is nothing in the implementation rules that say that an assign method must actually assign a value! So at any time a value is assigned to the property the appropriate method is called. Since we can use SetAll() to set properties we avoid the necessity to loop through the collection and test objects. The code is terribly simple:

PROTECTED FUNCTION iRunCode_Assign( tnValue )
This.UpdateSelf()
ENDFUNC

So in the form we can simply have:

ThisForm.SetAll( 'iRunCode', .T. )

So any object that has the 'iRunCode' property will immediately call its own 'UpdateSelf()'' or whatever other method is required. You can even create this property and associated assign method as a 'template' method. In other words create the property on your root class but leave the assign method empty. In a specific instance you can then call whatever method you want by simply supplying the necessary code in the Assign method.

The only caveat to this is that you cannot use this approach when sequential execution is needed because there is no way to control, when using SetAll() the order in which objects will execute their code. However, you could still set the property explicitly when you need objects to run their code in a specific order although in that case you might as well call the methods directly.

An additional benefit of using Assign methods to execute code is that the code being called can be hidden from external objects, or even from sub classes, because the property in question is defined at the same level as the method itself and so can execute that code even when the method cannot be called directly.

Creating Objects inside an Access Method

An access method fires whenever its property is accessed so if that property is used to hold an object reference we can use the Access method to determine whether the object exists. If the object is there, we simply return the reference otherwise we can try and create the object on the fly and, if successful, again return the reference. Why bother?

Well it avoids the necessity of testing an object reference property to ensure that the object is there. Here is the code from the access method in a form class that uses our Data Manager object.

PROTECTED FUNCTION oDM_Access
IF VARTYPE( This.oDM ) = "O"
RETURN This.oDM
ELSE
IF PEMSTATUS( _Screen, 'oDM', 5 ) AND VARTYPE( _Screen.oDM ) = 'O'
    *** There is an object reference out there already, grab it
    This.oDm = _Screen.oDM
ELSE
    *** Add the property (no error if already there...)
    ADDPROPERTY( _Screen, 'oDM', NULL )
    *** And instantiate the Data Manager
    SET PROCEDURE TO dataclass, dsetbase, datamgr ADDITIVE
    _Screen.oDM = CREATEOBJECT( 'xDatMgr', This.cDSNToUse )
    IF VARTYPE( _Screen.oDM ) = "O"
      This.oDM = _Screen.oDM
    ELSE
      This.oDM = NULL
    ENDIF
ENDIF
ENDIF
RETURN This.oDM

Our general practice when working is to use a property on the VFP _Screen object to hold the object reference to the Data Manager object. So this code first checks to see if the object exists – if it does it simply grabs the reference and populates the property with it. If there is no Data Manager, this code adds the property (even if the property already exists this will not cause an error). It then instantiates the data manager object and initializes it by using its own connection information (the cDSNToUSe property holds this). If the creation succeeds the object reference is returned otherwise a NULL is returned.

By encapsulating the process of instantiating the object like this, we can include it in the root class of our form and forget about it. Any form based on this class will now either pick up the existing object reference, or create a new one for all subsequent forms to use, without the need of any specific code in the application start-up.

Returning Other Values when accessing a property

Another use for Access methods is to return something other than the actual value held in a property. One scenario where you may want to do this is when a property is used to store an ID value but what you actually need is the look-up of that value. Obviously this can be done in code, but if you frequently need it then it makes sense to handle it in an access method like this:

PROTECTED FUNCTION nKeyCode_Access
LOCAL lcRetVal
IF SEEK( This.Value, 'KeyTable', 'KeyCode' )
lcRetVal = KeyTable.KeyDesc
ELSE
lcRetVal = ''
ENDIF
RETURN lcRetVal
ENDFUNC

By extension the same methodology could be used to return an object with multiple values – for example an entire record from a table created using SCATTER NAME. Code similar to that above would simply return a data object, or a NULL:

PROTECTED FUNCTION cDataKey_Access
LOCAL loRetVal
IF SEEK( This.Value, 'KeyTable', 'KeyCode' )
    lnSel = SELECT()
    SELECT keytable
    SCATTER NAME loRetVal
    SELECT (lnSel)
ELSE
    loRetVal = NULL
ENDIF
RETURN loRetVal
ENDFUNC

There are many more cases where Access and Assign methods can be used to simplify your code and I would be interested to hear of any other uses that you have found for these methods.

FoxRockX – Time to Put up, or Shut up

andykr — Tue, 18 Mar 2008 09:21:00 GMT

Over the years much has been made about the FoxPro Community and how important it is to the life and survival of the product. However, as I have said many times (most recently in my "So, no more Visual FoxPro. Now what?" blog entry from May of 2007), it has often been noticeable that the "Community" has been long on talk and short on reaching for their wallets.

Of course, in the past year Microsoft has announced that VFP will not get another version and that future work, if any, on the product will be limited to critical fixes only. Add to that, the fact that participation at conferences has fallen to the point where from having one major international conference and two or three regional conferences per year in the USA there is now just one regional conference dedicated to FoxPro left – SW Fox had over 150 attendees last year (out of how many thousand FoxPro users in the USA alone - where were the rest of them?) See http://www.swfox.net for details of the 2008 conference...
Subscription to magazines has dropped off to the point that the two major monthly publications dedicated to FoxPro both essentially died on their feet. Participation in the technical on-line forums has dropped significantly over the past 10-12 months.

Into this picture, at this moment in time, steps Rainer Becker.

Who is Rainer Becker?
Rainer Becker is the leader of the German Foxpro user group DFPUG since 1993, publishes the small magazine Foxx Professional, runs an interactive Visual FoxPro forum at forum.dfpug.de, a Visual FoxPro document portal at portal.dfpug.de, a Visual FoxPro eNewsletter at newsletter.dfpug.de, an international online shop at shop.dfpug.com and has organized the German Devcon since 1994. He has been a speaker at various Microsoft events as the Dev Days, Fox Teach and Ce BIT - at Ce BIT fair he was at the VFP demo machine 1993-2005. In his spare time he undertakes consulting work for "Wizards Builders" and for "ISYS GmbH" as well as publishing the Visual FoxPro framework "Visual Extend".
Additionally he has been a Microsoft MVP for his work in promoting and supporting Visual FoxPro in Europe since 1997 and in 2007 was a recipient of the FoxPro Community Lifetime Achievement Award.

What has he done?
First he has undertaken the enormous (and very risky) task of starting a completely new bi-monthly magazine dedicated to Visual FoxPro – which will be available both on-line and in printed version. That is FoxRockX.
Second he has bought up the entire FoxTalk archive and is making it available, on-line to subscribers to the new magazine, at no additional charge!

You can find full details of FoxRockX here: http://www.foxrockx.com/seite.htm
And you can subscribe by going to either:
http://www.hentzenwerke.com (USA/Asia)
http://shopdfpug.com (Europe)

Of course the most important question for many people is, how much will it cost?
On-Line Subscription (including access to the archives) US$ 99.00 ( 75 EUR )
Printed Subscription (includes on-line and archive) US$ 158.00 ( 109 EUR )
(Note if you are an existing FoxTalk subscriber you can upgrade your existing on-line subscription to the printed version by paying only the difference i.e. US$ 59.00 (34 EUR).

Marcia and I have already bought our subscription to the printed version, we have an article in the first issue and plan to continue writing for FoxRockX as long as they will have us.

However, the success of this venture is going to rely totally on the FoxPro community. If the community puts its money where its mouth is, it will succeed and could help bring a new lease on life for the product that we all use and love. If the community refuses to support the initiative then the effort will die, and I am very much afraid that all hope for FoxPro will die along with it.

So it is my opinion that this really is crunch time for the FoxPro community. Whatever you may think about the situation personally, either put up and support this international community-led effort, or shut up!

More on the Advantage Database Server

andykr — Sun, 02 Mar 2008 18:23:00 GMT

If you are interested in learning more about the advantage database server that I mentioned on this blog a couple of months ago, then you should visit J D Mullin's blog here:

http://jdmullin.blogspot.com/2008/02/getting-started-with-visual-foxpro-and.html

He has put together a very nice ScreenCast (about 10 minutes long) that shows how to get started and some of the things you can do with VFP and Advantage Database Server.

Form Event Sequences

andykr — Sat, 26 Jan 2008 11:47:00 GMT

One of the things that occasionally seems to confuse people is the question of the order in which the events happen when a form is instantiated. The basic answer, in terms of the Form's own events, is given by the acronym "LISA G", which stands for:

L => Load
I => Init ()each control first, then Form last)
S => Show
A => Activate
G => GotFocus (first control in the Tab order)
Actually you could argue that this should be "LISAR G" – because whenever the Activate() event of a form is triggered, a Refresh() is also initiated. Anyway, while these are the native form events, there are other things happening when a form is instantiated, most notably when a DataEnvironment is involved.

For a form which uses a DE, and contains an exit button and pageframe with two pages the full initialization sequence is:

FORM.DATAENVIRONMENT.OPENTABLES
FORM.DATAENVIRONMENT.BEFOREOPENTABLES
FORM1.LOAD
FORM1.DATAENVIRONMENT.CURSOR1.INIT
FORM1.DATAENVIRONMENT.INIT
FORM1.CMDEXIT.INIT
FORM1.CMDREFRESH.INIT
FORM1.PGFMAIN.PAGE1.GRDCUSTOMER.COLUMN2.TXTCOL2.INIT
FORM1.PGFMAIN.PAGE1.GRDCUSTOMER.INIT
FORM1.PGFMAIN.PAGE1.INIT
FORM1.PGFMAIN.PAGE2.TB01.INIT
FORM1.PGFMAIN.PAGE2.CB03.INIT
FORM1.PGFMAIN.PAGE2.TB02.INIT
FORM1.PGFMAIN.PAGE2.INIT
FORM1.PGFMAIN.INIT
FORM1.INIT

The whole thing starts with the DataEnvironment OpenTables() which is an event that calls the BeforeOpenTables(), and then proceeds with the Form’s Load() event. After that the sequence is pretty much as expected. The objects contained by the form (Command Button and Pageframe) are initialized first, in the order in which they were added to the form, followed by the containers themselves, beginning with the DataEnvironment. This part of the sequence finishes with the Form’s Init().

This is, of course, why parameters passed to a form have to be received in the Init(). It is the first form method that fires after all the controls are available, but before any of them have actually been updated.

The next part of the process is concerned with updating the controls from their data sources (which is what the Refresh() method actually does) and then making the form visible, the events fire as follows:

FORM1.SHOW
FORM1.PGFMAIN.PAGE1.ACTIVATE
FORM1.ACTIVATE
FORM1.REFRESH
FORM1.CMDEXIT.REFRESH
FORM1.CMDREFRESH.REFRESH
FORM1.PGFMAIN.REFRESH
FORM1.PGFMAIN.PAGE1.REFRESH
FORM1.PGFMAIN.PAGE1.GRDCUSTOMER.REFRESH
FORM1.CMDREFRESH.WHEN
FORM1.GOTFOCUS
FORM1.CMDEXIT.WHEN

Notice, however, that only the active page (Page1) of the pageframe gets refreshed. This means that the controls on Page 2 have not yet been updated and this explains why you always need to do an explicit Refresh() when navigating to a new page.

There is one thing to watch here, though. If you simply add a "ThisForm.Refresh()" to the activate event of the first page of your pageframe you would actually end up calling it THREE times when initializing a form. Once (implicitly) from the Form.Activate(), a second time explicitly when the first page of the pageframe is refreshed and finally when the page itself is activated.

Note: This underlines just how important it is to avoid the (way-too-common) practice of calling ThisForm.Refresh() from all over the place. A form level refresh should really only be called when you are certain that it is needed. In other words, ThisForm.Refresh() should always be preceded by some sort of test!

One way to handle this is to create a wrapper method for the Form Refresh() and include a test for a form level property that is set by events that will require a form level refresh (like moving the record pointer, or changing the form's mode from "Add" to "View" for example). We use a method named "RefreshForm" to handle this and it looks like this:

LPARAMETERS tlSetObjMode
WITH Thisform
IF .FormRefreshNeeded
    .LockScreen = .T.
    IF .BeforeRefresh()
      IF tlSetObjMode
        .SetObjMode( This )
      ENDIF
      .Refresh()
      .AfterRefresh()
    ENDIF
ENDIF
.LockScreen = .F.
ENDWITH

The parameter is used to determine whether to re-evaluate the form's mode and change any object whose state depends on that mode (handled by the SetObjMode() method). Notice that we also have hooks to "Before" and "After" methods that allow us to modify the behavior, and even cancel a refresh (by returning .F. from BeforeRefresh()) if necessary. By always calling this RefreshForm() method instead of directly calling Refresh() we buy ourselves a lot of flexibility.

Incidentally, if you instantiate a form from a VCX instead of an SCX the event sequence is identical except, of course, that there are no dataenvironment events.

So what happens when we release a form? The answer is that it depends on on HOW the form is released.

There are basically three ways of killing a form. First you can explicitly call its Release() method (i.e. ThisForm.Release() ). Second, you can click on the ‘close’ button, which actually calls the form’s QueryUnload() method and third you can release the object reference to the form: ( i.e. RELEASE ThisForm)

[1] Form Release() called:

            R => Release
            D => Destroy (Form FIRST, then all contained controls in reverse of the Init order)
            U => Unload
[2] Close button used:
            Q => QueryUnload
            D => Destroy (Form FIRST, then all contained controls in reverse of the Init order)
            U => Unload
[3] Object Reference released (note that neither Release(), nor QueryUnload() are fired in this case):
            D => Destroy (Form FIRST, then all contained controls in reverse of the Init order)
            U => Unload
In each case, the form's DE does not get released until AFTER the form Unload - so tables are still available in the Unload as well as properties - but no controls.

There is a potential problem here though. Notice that the first event common to all three methods is the Destroy(). However, once the Form's Destroy fires there is no getting out of it, even a NODEFAULT will not stop the release process. That means that there is no single native method or event in which you can place code that checks to determine whether a form should be released or not. (It also means that if you release the object reference, it absolutely will be killed - there is no way to undo such this action because it fires the Destroy() directly).

If you do need to check that it is Ok to release a form you will either have to disable the 'close' button on all your forms, or place the checking code in a custom method that is called from BOTH the form's Release() and QueryUnload() events because a NODEFAULT in either of these methods will halt the destruction of the form. This code, in both events, will do the trick:

IF NOT ThisForm.OK2Destroy()
NODEFAULT
ENDIF

The most usual reason for this sort of code is to prevent users from closing a form with uncommitted changes pending, so the OK2Destroy() will typically call the form's CheckForChanges() method. As usual we prefer to call 'control methods' that then call the actual 'action' methods so that if we do need to modify behavior there is a place to do it from.

Advantage Database Server V9.0 released as Beta

andykr — Sat, 05 Jan 2008 10:58:00 GMT

Sybase Anywhere have been working on a Visual FoxPro compatible version of the Advantage Database Server for some time and, just before Christmas, the long-awaited Version 9.0 was released in Beta. You can download the Beta version (time limited until the end of March 2008) from here:

http://devzone.advantagedatabase.com/dz/content.aspx?Key=20

For a full version you will need to download the Server, the Data Architect and the Advantage OLEDB and ODBC drivers.

Now some of you may be wondering why this should be news, or maybe you have never heard of the Advantage Database server. There is a lot of very good information on this neat little tool on their web site and I have been very impressed with it but there are a couple of things that should make it of immediate and very real interest to all VFP Users:

[1] The ODBC driver will access all current VFP Data Table and Data Types. Yes, including Auto-Inc, Varchar() and all the newest features. For any of you who have been unable to move off VFP 6.0 because you need to access your data through ODBC there is now a solution!

[2] The advantage server can be used to connect directly to DBF files, replacing the DBC with a true multi-threaded, multi-user, remote server that includes, among many other features, a SQL Debugger (yes, you can step through SQL Queries one line at a time!), replication and merge functionality, and that runs on Windows, Linux or Netware.

I have been playing with this version for a couple of weeks now and have been very impressed by its capabilities and potential. I am not going to try and cover all the things that the Advantage Database Server can do, but perhaps the one thing that is worth mentioning is that it can remove the necessity for users to have direct access to your DBF files. This requirement has long been one of the major objections to the DBF file as a data store because of the ease with which these files can be read and even corrupted. The Advantage Database Server makes this direct access unnecessary. DBF Files can now be hidden away from prying eyes (only the server needs access) and the server also manages locking and concurrency issues.

Coming so soon on the news that Microsoft are abandoning VFP to its fate, it is nice to see that someone out there thinks that there is still some value in the product and is willing to put some effort into it.

Here is what the Help File has to say about VFP support:

Visual FoxPro 9 File Format Support

Advantage support for FoxPro DBF tables and CDX/IDX index files has been enhanced to include all data types supported by Visual FoxPro 9. This includes support for Auto-increment, BLOB (Binary Large Objects), Currency, DateTime (timestamp), VarBinary, and VarChar field types.
Null field support has also been added to give true SQL-style NULL handling for Visual FoxPro DBF tables. Support for true unique (candidate) indexes is now also available on DBF tables; this allows for better SQL optimization, the definition of primary keys, and the creation of referential integrity rules.
Additionally, support for long field names with DBF tables has been added. In addition to supporting the robust and high-performance Advantage proprietary locking on the new table format, Advantage also supports Visual FoxPro compatibility locking, which allows you to use Advantage-enabled applications to share tables with existing Visual FoxPro applications that are not using Advantage.

Visual FoxPro Upsizing Utility

To complement the enhanced support for Visual FoxPro tables, this release includes an upsizing utility that aids in updating your application to use your existing Visual FoxPro tables with Advantage Database Server.
It is not necessary to update your tables or indexes to allow them to be used with Advantage Database Server. However, the upsizing utility will export much of the information in your Visual FoxPro Database Container (.DBC) to an Advantage Data Dictionary (.add) to allow support for features such as long field names, primary keys, referential integrity, and triggers.
This utility, written as a Visual FoxPro application that you can modify, currently has the ability to export view definitions, referential integrity rules, default field values, field and table validation rules, and primary keys.

If you are still not sure what this is all about, here is an extract from the Overview section of the (very comprehensive) help file for Version 9.0:

Advantage Database Server is a high performance client/server RDBMS for stand-alone, networked, Internet, and mobile database applications. Advantage Database Server allows developers the flexibility to combine powerful SQL statements and relational data access methods with the performance and control of navigational commands. Advantage has native development interfaces designed to leverage existing knowledge of popular development tools. With optimized data access methodology for easily delivering unparalleled performance, Advantage provides security, stability, and data integrity while being completely maintenance-free.
The Advantage Database Server is the key to improved database performance in network environments. The server can be visualized as an intelligent controller that reduces competition for resources and off-loads much of the work normally performed by each client workstation. It is responsible for all database access, including all reading and writing of data, and lock management. Working with the network operating system, the Advantage Database Server processes data requests and returns the information to the network clients.
The Advantage Database Server supports the NetWare, Windows, and Linux operating systems. The Advantage Database Server for NetWare is implemented as a NetWare Loadable Module (NLM). An NLM is simply an executable for the NetWare operating system. The Advantage Database Server for Windows operates as a Windows Service.

Note The Advantage Database Server for Windows is a Service. It cannot be run as a standard Windows application. See Installing and Starting the Advantage Database Server for Windows , for more information on Windows Services and how to start them.

The Advantage Database Server retrieves requests for database operations to be performed on behalf of the clients. The Advantage Database Server locates tables on the server and processes the database operations. The result of the operation is then returned to the client across the network, eliminating the need to send the database to the client for processing. This provides far better concurrency control and system integrity than is otherwise available.
Traditional non-client/server applications send raw data from the server across the network to be processed on the workstation. With the Advantage Database Server, much of the data is processed by the Advantage Database Server on the file server. By decreasing network traffic, you increase performance.
The Advantage Database Server integrity system ensures that database updates either run to completion or do not begin. The Advantage Database Server will not execute partial commands. This means that the integrity of your database no longer depends on the stability of the workstations on the network. Because the Advantage Database Server is responsible for all database access (on behalf of the clients), it can do a far better job of concurrency control than traditional systems, where concurrency must be synchronized between remote workstations. Better concurrency control means better multi-user performance.

What we have still to find out is what the new version will cost! But if you are looking for ways to leverage your VFP data and applications without the costs involved in porting to SQL Server or MySQL, this may be something that you should take a long hard look at - for the next couple of months at least it will be freely available for download and evaluation.

Frankfurt Developer Conference is just 1 Week Away

andykr — Mon, 29 Oct 2007 09:34:00 GMT

By now I am sure you will have seen the rave reviews for SW Fox held last week in Mesa, Arizona. Rick, Doug and Tamar put together a fabulous conference which was really positive and upbeat. I have not been to such a great VFP conference in the US in many years!

However, for those of you who missed it, there is still time to get to the 14th Microsoft Visual FoxPro Developer Conference 2007 to be held between 8/10th November in Frankfurt, Germany.

Apart from the setting, the food, the free beer and the warmth of the welcome, the Speakers for the English Track list is impressive including:

Marcia Akins
Craig Bernston
Steven Black
Yair Alan Griver
Doug Hennig
Vennelina Jordanova
Andy Kramek
Rick Schummer

For just 999 Euros (less than £700!) you get the full conference registration indcluding meals, (and WHAT meals they are). There are special conference rates at the hotel, and the flights are really good value at this time of year too.

The range of topics available is extensive and there is no doubt that it is great value for money. You can find details here: http://devcon.dfpug.de/ and an English Registration form is at http://portal.dfpug.de/dFPUG/Dokumente/Konferenzen/Konferenzprogramm2007/Anmeldeformular%20engl.doc

For reviews of previous Frankfurt Devcons check out the Wiki at http://fox.wikis.com/wc.dll?Wiki~GreatThingsAboutDevconGermany~VFP

Hopefully a few more of my countrymen will make the effort this year and I look forward to seeing you there. (Apart from anything else, it is always embarassing when I am asked why I am the only Brit in the place )

Creating Classes with a Factory

andykr — Sat, 04 Aug 2007 12:22:00 GMT

This implementation uses the abstract factory pattern to avoid one of the most difficult code maintenance issues. This is the problem that arises whenever we need to change either the class name, or the source library (or both) from which we want to instantiate an object, or simply need to make changes to an existing class definition.

To change a name is fraught with peril and the only way to do it is to search the source code for all occurrences of the item that we want to change and replace it with the new one. This works just fine providing that we don’t miss one, or mistype either the name (or library, or both!) The more occurrences we have to fix the more likely it is that a mistake will be made – even using tools like “Code References”.

Even routine maintenance and enhancements can cause problems, especially when a team of developers is involved because a class library cannot be shared. So there is always a danger that changes made by one person get overwritten by changes in a different class made by another.

The implementation described here addresses both of these issues.

What is a Factory?

A factory class is basically one whose function is to instantiate other classes. It is an example of the more general ‘Abstract Factory’ design pattern, which is defined in “Design Patterns – Elements of Reusable Object-Oriented Software” (Gamma, Helm, Johnson & Vlissides, Addison Wesley 1997) as:

Provide an interface for creating families of related or dependent objects without specifying their concrete classes

What are the components of the factory?

The factory pattern requires a minimum of two components and, usually three (See Figure 3).

The Client – the object that requires an instance of a class. This is where, in conventional code, the CREATEOBJECT() (or NEWOBJECT()) function call would normally be located
The Factory - this is usually instantiated as a stand-alone object at application startup, although it can also be implemented as a method on an Application Object.
The Metadata - this, is what makes this implementation so useful in my opinion, and why VFP is such an ideal tool in which to implement this particular pattern

Figure 1: Abstract Factory Components

When should I use a Class Factory?

The short answer is always! There is really no reason not to use a factory to instantiate objects and the only real question is whether it should be implemented as a global object in its own right, or as a method on some other (i.e. Application) object. My personal preference is to create the factory as a separate object because I always use metadata to drive it, and it helps keep things tidy if the factory can run in its own private datasession.

The key thing to remember is that the role of the metadata in the abstract factory is to separate the functionality from the information that is used to drive it. In this case we are concerned with instantiating objects which would normally be handled explicitly by using code like this:

loObject = CREATEOBJECT( ‘MyClass’ )

loObject = NEWOBJECT( ‘MyClass’, MyClassLibrary’ )

As noted above, problems arise with this if either the class name, or the source library changes, or even if we want to test a new version of a class because we must either change the original class definition, or change the calling code. Either way we have to update, recompile and re-distribute the application. The solution is that, instead of hard coding the instantiation directly into the application, we hand the task off to another object.

In order to do that we need to associate a non-definitive “key” (that will be used in the code) with the actual class, and library names that can be looked up at run time to instantiate the correct class. Since the interpretation of the key name is handled inside the factory, there is never any need to amend the source code when changes to either class, or class library, are made. All that is needed is to change the metadata.

Implementation

The metadata table for the factory class is shown in Table 1. Notice that the metadata, in addition to the Key, Class and Library names includes a memo field which we use to define default values for specific properties by listing them as Attribute/Value pairs. There is also a logical field which allows us to disable particular classes when necessary (more on that later).

Table 1: Factory Class Metatdata

The factory class itself is defined programmatically using the session base class so that it always runs in its own private datasession. This obviates any possibility that the tables it uses will be affected by the application itself. The interface is very simple as shown in Table 2:

Table 2: Factory Class Interface

In practice we define TWO identical tables. The first is named “classes.dbf” and is the production version of the table, the second is named “wipclasses.dbf” (for Work-In-Progress). The code in the factory always checks for the key in the wipclasses table first so that we don’t actually have to modify things on the production side (classes.dbf) until we are sure that the new version is correct. The consequence is that new classes can be tested by the developer, actually in the EXE, without re-compiling the code. All that is needed is an active entry in wipclasses.dbf to override the current ‘production’ version.

Another consequence is that, in a team environment, each developer can have their own local wipclasses table, and a read-only copy of the main classes.dbf. That way when someone is developing a class they don’t affect everyone else’s code while they are de-bugging their changes. Once a new class, or sub-class, is ready for production, all that needs to be done is to update the appropriate class library and (if needed) modify the entry classes table and the application begins using the new class immediately.

The factory class is defined in code as a sub-class of the Session base class. This, as noted above, means that the factory can be instantiated in the application environment as a global object that maintains its own private datasession. The class includes a property (lWIPTable) this serves as a flag that is set when the class is instantiated so that we don't have to keep testing for the presence of the WIP table. The Init() method is responsible for setting up the metadata, and setting this flag.

All of the real work is handled in the exposed New() method. It accepts a key name, and up to five additional parameters which (if present) are passed directly to the Init() of the object. If the object is instantiated correctly, a reference to the new object is returned to the client, otherwise a NULL value is handed back.

Note that the class determines, from the extension of the specified class library, whether to use CREATEOBJECT() if the library has been explicitly declared, or NEWOBJECT() if not. If the object is instantiated successfully, and there are settings defined as defaults in the Properties column, then the factory will attempt to set those values as well (see the GetProperties() and Str2Exp() methods) – this is, of course, in addition to any parameters that may be passed and handled by the class code directly.

So to create an instance of an object defined in the application source code all that has to be done is for the application to have access to the factory object and to call its New() method with the key name. The return value will either be an object reference or NULL, and the client handles both possibilities:

TRY
loObject = oFactory.New( ‘Required_Object_KeyName’ )
IF ISNULL( loObject )
ERROR “Unable to instantiate” + Required_Object_KeyName )
ENDIF
CATCH TO loErr
MESSAGEBOX( loErr.Message + “ in ” + loErr.Procedure, 16, “Factory Error” )
ENDTRY

You may be wondering why this does not create a dangling object reference. The reason is simply that the object is created as LOCAL inside the Factory.New() method. So when the method completes, the local reference goes out of scope and is released, leaving the returned reference as the only one in existence.

Summary

The implementation of the factory pattern illustrated here offers two major benefits. First, and most obviously, it avoids the issues that arise when class and library names are embedded directly in code. Second, it allows developers to work on, and test in a production environment, code that is still ‘work-in-progress’ without breaking anyone else’s version of the application.

The code for our factory class is here:

******************************************************************************************
*** Program: Factory.prg
*** Written by Andy Kramek & Marcia G. Akins
*** Abstract: Abstract factory responsible for object instantiation
*** Parameters: keyword from classes table to look up class info and up to 4 optional parameters
*** Compiler.: Visual FoxPro 09.00.0000.3504 for Windows
*****************************************************************************************
DEFINE CLASS factory AS Session
*** Give it a private data session so we can set exact ON
*** so we can accurately use a SEEK into the classes table
DataSession = 2
lWIPTable = .F.
******************************************************
FUNCTION Init()
******************************************************
*** This is a private data session so just force exact ON
SET EXACT ON
*** Open the classes table
IF NOT USED( 'Classes' )
    USE Classes AGAIN IN 0
ENDIF
*** Open WipClasses if we have it available, and set property
IF NOT USED( 'WIPclasses' )
    IF FILE( 'WIPclasses.dbf' )
      USE WIPclasses AGAIN IN 0
      This.lWIPTable = .T.
    ENDIF
ELSE
    This.lWIPTable = .T.
ENDIF
ENDFUNC

****************************************************************************
FUNCTION New( tcKey, tuParam1, tuParam2, tuParam3, tuParam4, tuParam5 )
*****************************************************************************
LOCAL lcLibType, lcCommand, lnParm, lnParmCount, loObject, llFound, lcKey, lnSelect, llCreate, lcProperties
LOCAL lcLibrary, lcClassName

*** Make sure we got a keyword
IF EMPTY( tcKey )
RETURN .NULL.
ENDIF

*** Get the class information
lcKey = UPPER( ALLTRIM( tcKey ) )

*** Check to see if the developers are using a local classes table
*** to test work in progress. If there is one, use the information
*** from the local table if it is there. Only check the application
*** classes table if the keyword can't be found in the local one
lnSelect = SELECT()
IF This.lWIPTable
SELECT WIPClasses
llFound = SEEK( lcKey, 'WIPclasses', 'cKey' ) AND WipClasses.lActive
ENDIF

*** Now check the application classes table if
*** we need to
IF NOT llFound
SELECT Classes
llFound = SEEK( lcKey, 'Classes', 'cKey' ) AND Classes.lActive
ENDIF

IF NOT llFound
*** Make sure keyword was found in classes table
RETURN .NULL.
ENDIF

*** Save pertinent info and force to upper case
lcProperties = ALLTRIM( Properties )
lcLibrary = UPPER( ALLTRIM( cLibrary ) )
lcClassName = UPPER( ALLTRIM( cClassName ) )

*** Is this class in a vcx or a prg?
lcLibType = This.ChkLibType( lcLibrary )

*** Make sure our class library has an extension
*** Just in case one was not specified in Classes.dbf
IF EMPTY( lcLibType )
RETURN .NULL.
ELSE
lcLibrary = FORCEEXT( lcLibrary, lcLibType )
ENDIF

*** Now, see if our prg or vcx has been set
IF lcLibType = 'PRG'
llCreate = ( lcLibrary $ SET( 'PROCEDURE' ) ) OR ;
    ( FORCEEXT( lcLibrary, 'FXP' ) $ SET( 'PROCEDURE' ) )
ELSE
llCreate = lcLibrary $ SET( 'CLASSLIB' )
ENDIF

IF llCreate
lcCommand = 'CreateObject( "' + lcClassName + '"'
ELSE
lcCommand = 'NewObject( "' + lcClassName + '", "' + lcLibrary + '"'
ENDIF

*** Now tack the parameters on to the end of the command
*** if any were passed
lnParmCount = PCOUNT() - 1
IF lnParmCount > 0
*** Only add the third parameter if we are using NewObject
IF NOT llCreate
    lcCommand = lcCommand + ', ""'
ENDIF
FOR lnParm = 1 TO lnParmCount
    lcCommand = lcCommand + ', tuParam' + TRANSFORM( lnParm )
ENDFOR
ENDIF
lcCommand = lcCommand + ' )'

*** Go ahead and instantiate the object
loObject = &lcCommand

*** Now see if we have some properties to set
IF NOT EMPTY( lcProperties )
*** First make sure we have an object
IF VARTYPE( loObject ) = 'O'
    This.GetProperties( loObject, lcProperties )
ENDIF
ENDIF

SELECT ( lnSelect )

RETURN loObject

ENDFUNC

*****************************************************************************
PROTECTED FUNCTION GetProperties( toObject, tcProperties )
*****************************************************************************
LOCAL lnTotal, laProps[ 1 ], lnCnt, lcPropName, lcPropValue, lcType, loItem, luValue

*** get all the attribute/value pairs in the memo field into a single array element
lnTotal = ALINES( laProps, tcProperties )
*** And process each pair in the array
FOR lnCnt = 1 TO lnTotal
*** Get the name of the property
lcPropName = ALLTRIM( GETWORDNUM( laProps[ lnCnt ], 1, '=' ) )
lcPropValue = ALLTRIM( GETWORDNUM( laProps[ lnCnt ], 2, '=' ) )
*** Make sure it is a property on the object
IF PEMSTATUS( toObject, lcPropName, 5 )
    *** See if we can get its data type
    loItem = 'toObject.' + lcPropName
    lcType = TYPE( loItem )
    *** Now get back the value in the appropriate data type
    luValue = This.Str2Exp( lcPropValue, lcType )
    &loItem = luValue
ENDIF
ENDFOR

********************************************************************************
PROTECTED FUNCTION Str2Exp( tcExp, tcType )
********************************************************************************
LOCAL lcExp, luRetVal, lcType, lcStr

*** Verify parameters
IF VARTYPE( tcExp ) # 'C'
ASSERT .F. MESSAGE TRANSFORM( tcExp ) + ' is NOT a character expression and you MUST pass a character expression to Str2Exp!'
RETURN tcExp
ENDIF
IF EMPTY( tcType )
ASSERT .F. MESSAGE 'You MUST pass a data type to Str2Exp!'
RETURN tcExp
ENDIF

*** If no type passed -- map to expression type
lcType = UPPER( ALLTRIM( tcType ) )
*** Remove any NULL characters, and leading/trailing spaces
lcExp = CHRTRAN( ALLTRIM( tcExp ), CHR( 0 ), '' )
*** Convert from Character to the correct type
DO CASE
  *** Integers
CASE INLIST( lcType, 'I', 'N' ) AND INT( VAL( lcExp ) ) == VAL( lcExp )
    luRetVal = INT( VAL( lcExp ) )
*** Other Numeric
CASE INLIST( lcType, 'N', 'B' )
    luRetVal = VAL( lcExp )
*** Currency
CASE lcType = "Y"
    luRetVal = NTOM( VAL( lcExp ))
*** Character or memo
CASE INLIST( lcType, 'C', 'M' )
    *** Remove delimiting marks if present.
    IF INLIST( LEFT(lcExp,1), CHR(91), CHR(34), CHR(39))
      *** We begin with a delimiter
      lcExp = SUBSTR( lcExp, 2 )
      *** So we should end with a delimiter
      IF INLIST( RIGHT(lcExp,1), CHR(93), CHR(34), CHR(39))
        lcExp = LEFT( lcExp, LEN( lcExp )- 1 )
      ENDIF
    ENDIF
    luRetVal = lcExp
*** Logical
CASE lcType = 'L'
    luRetVal = IIF( !EMPTY( CHRTRAN( lcExp, 'Ff0.', "" ) ), .T., .F.)
*** Date
CASE lcType = 'D' && Date
    *** Check for separators in the string
    IF CHRTRAN( lcExp, "/.-", "" ) == lcExp
      *** We are in yyyymmdd format
      lcStr = LEFT( lcExp, 4) + "," + SUBSTR( lcExp, 5, 2 ) + "," + RIGHT( lcExp, 2)
      luRetVal = DATE( &lcStr )
    ELSE
      *** We are in DTOC() format
      luRetVal = CTOD( lcExp )
    ENDIF
*** DateTime
CASE lcType = 'T' && DateTime
    *** Check for date separators in the string
    IF CHRTRAN( lcExp, "/.-", "" ) == lcExp
      *** No separators so we have something other than TTOC() format
      IF LEN( lcExp ) > 8
        *** This one must be in yyyymmddhhmmss format
        *** So get the date part first
        lcStr = LEFT( lcExp, 4) + "," + SUBSTR( lcExp, 5, 2 ) + "," + SUBSTR( lcExp, 7, 2)
        *** and convert to the correct date string format
        lcStr = DTOC( DATE( &lcStr ))
        *** Now tack on the hours part
        lcStr = lcStr + " " + SUBSTR( lcExp, 9, 2 )
        *** Minutes
        IF LEN( lcExp ) > 10
          lcStr = lcStr + ":" + SUBSTR( lcExp, 11, 2 )
        ELSE
          lcStr = lcStr + ":00"
        ENDIF
        *** Seconds
        IF LEN( lcExp ) > 12
          lcStr = lcStr + ":" + SUBSTR( lcExp, 13, 2 )
        ELSE
          lcStr = lcStr + ":00"
        ENDIF
        luRetVal = CTOT( lcStr )
      ELSE
        *** This must be a date in yyyymmdd format which we want to force to DateTime format
        lcStr = LEFT( lcExp, 4) + "," + SUBSTR( lcExp, 5, 2 ) + "," + RIGHT( lcExp, 2)
        luRetVal = DTOT( DATE( &lcStr ))
      ENDIF
    ELSE
      *** We are already in TTOC() format
      luRetVal = CTOT( lcExp )
    ENDIF

OTHERWISE
    *** We have an invalid combination of value and data type
    MESSAGEBOX( "Cannot convert " + lcExp + " to Data Type " + tcType, 16, "Conversion Failed " )
    luRetVal = lcExp
ENDCASE
*** Return value as Data Type
RETURN luRetVal

*****************************************************************************
PROTECTED FUNCTION ChkLibType( tcLibrary )
*****************************************************************************
LOCAL lcLibType

*** Checks for file extension on library name, and
*** figures out what it should be if not supplied
lcLibType = UPPER( JUSTEXT( tcLibrary ) )

IF NOT EMPTY( lcLibType )
lcLibType = IIF( FILE( tcLibrary ), lcLibType, '' )
ELSE
*** See if we have a vcx here
lcLibType = IIF( FILE( FORCEEXT( tcLibrary,'VCX' ) ), 'VCX', '' )
IF EMPTY ( lcLibType )
    lcLibType = IIF( FILE( FORCEEXT( tcLibrary,'PRG' ) ), 'PRG', '' )
ENDIF
ENDIF

RETURN lcLibType

ENDDEFINE