Clarion and ACT!: - An OLE Case Study (Part 1)

by Tom Hebenstreit, Review Editor

Published 1998-09-01    Printer-friendly version

This article is what I call a 'case study' - a snapshot of an actual ongoing project. It will show you how this particular task was approached, why choices were made, and document the techniques that have been utilized so far. It should interest anyone working (or interested in working) with the Clarion OLE interface, as well as provide an insight on how to go about integrating Clarion with a commercial product. Working Clarion code will be used to illustrate how to accomplish specific tasks.

The Task

Recently, a project was proposed which would require integrating an existing Clarion for Windows system with ACT!, a powerful and flexible contact manager developed and sold by Symantec Corporation. The plan was to move the basic contact information currently stored in the custom system to ACT!, while keeping related and child information in the CW application.

The primary benefit? To leverage the strengths of each system while reducing the amount of custom programming required. It would allow the client to freely manipulate the contact file without requiring constant changes to the Clarion application, as ACT! provides the user with the ability to redefine, create and remove fields in its contact file on the fly at any time. It also contains powerful tools for querying, reporting, mail merge (both print and email) and good integration with Symantec's popular WinFax application. The CW application, on the other hand, would take care of the child files and processes far too complicated to maintain in a single purpose system like ACT!.

The blend sounded reasonable to me, as I am a staunch proponent of not reinventing wheels (or recreating contact managers), but -- could it be done? Read on...

The direct route (or... you can't get there from here)

A quick look in the ACT! database directory revealed that it uses DBF (dBase) format files to store information. The ACT! online documentation contains simple definitions of many of the standard contact fields, but does not provide specific information as to file layouts, keys or other information needed to allow direct file access from within the Clarion application.

Obviously, the first thing to try was to read the files into a Clarion Data Dictionary, right? Well, various attempts to import the ACT! dbf file resulted only in a file definition. All attempts to read the key file (an MDX file that contains multiple keys) resulted in either import errors or obviously bogus keys. Attempts were made using all of the CW native drivers (dBase III, IV and FoxPro) as well as various flavors of ODBC dBase drivers.

A little more research turned up that ACT! uses a third party library called CodeBase for its data engine. CodeBase can be fully dBase compatible, but it also allows the developer to use some proprietary features (which Symantec obviously did). In other words, no standard driver would work.

In truth, direct data access turns out to be precisely the wrong way to approach the task, anyway. As soon as you place a file definition in a Clarion data dictionary, you have locked in the format of the file. Any changes by the user would then cause the dreaded 'Error 47 - Invalid record declaration' in the Clarion application - thereby negating the entire point of the project.

On top of that, ACT! is a relational product. Each contact record can have multiple child records, such as email addresses, notes, activities, change histories and more. In order to maintain the integrity of the ACT! database, you would have to recreate virtually their entire referential integrity scheme so as not to corrupt the system when you directly manipulated the files. Yuck. On to Plan B.

A bit of detective work...

The next step was to contact Symantec and see if they publish any kind of API (Application Programming Interface) into ACT!. In this case, exact listing of data files, keys, relationships and so on. A question posted to their support web site revealed that Symantec does offer a free SDK (System Development Kit) which describes the databases. It also details how to access ACT! via DDE and OLE, with OLE being the recommended way. Since Symantec states up front that a) direct file access is highly discouraged, and b) DDE support is to be dropped in a future release, we decided to concentrate purely on using the OLE interface.

A quick call to the Symantec fax back system got me an official ACT! SDK request form. A week or so after filling it out and faxing it back, I had in my hot little hands a disk containing the official goods. (Note: Full details on how to obtain the ACT! SDK are provided at the end of this article.)

The SDK turned out to contain many example C++ and Visual Basic programs (sigh) and, more importantly to me, 350 plus pages of very good documentation on the file formats, DDE, and the OLE methods and properties. In case you are curious, no information is provided on keys - it is database fields only (and most of those, of course, can be changed by the user).

The ACT! OLE Database object

The actual interface into ACT! is provided by what they call "The ACT! OLE Database Object". This is a 32-bit OLE object that is automatically installed and registered in your system when you install ACT! itself (it is not part of the SDK). Everything you do in communicating with ACT! goes through this object. The SDK describes the purpose of the database object thusly:

"Developers can access and manipulate ACT! data using the ACT! OLE Database Object. The primary function of the OLE Database Object is to allow non-ACT! applications to open and access an ACT! database. The design of the OLE Database Object helps maintain the integrity of the ACT! database, including its indexes and tables, when a foreign application accesses it. It also allows for simultaneous access to the ACT! databases by foreign applications while ACT! has the database open."

"The goal of the OLE Database Object is to allow developers to access an ACT! for Windows database for reading, changing, adding, and deleting data in an integral manner. Using the OLE Database Object ensures that when an ACT! for Windows database is accessed to read or change data, all default values and rules will operate, unless the defaults are intentionally overwritten. Also, the OLE Database Object provides developers with a set of tools in the form of OLE automation objects with associated methods and properties."

Sounds good, eh? It will handle all of the messy details for us - as long as we can figure out how to talk to it using Clarion.

Objects within objects within objects...

The first step is to understand the ACT! OLE Database Object (hereafter referred to as the DB object) itself. As it turns out, the DB object is actually a hierarchy about a dozen related components as shown below (each item is an object):

Database Object
|-Users
|-Relations
|-Activity -> Fields
|- Exceptions
|-Contact -> Fields
|-Email -> Fields
|-NoteHistory -> Fields
|-Group -> Fields
|- Members

At the top level is the Database itself. It handles the basic interactions, such as opening and closing a database and logging in/validating users. Note that when I say 'database', I am not referring to any single file, but rather the entire collection of data files, relationships and keys which comprise an ACT! database (Contacts, Activities, Groups, etc.).

Below the DB object are the objects (files) we are most concerned with: Contacts, Activities, Email, and Notes/History. These are our gateways to the records within the various data files; i.e., our actual data.

Underneath those objects is the final primary object we will deal with - the Fields object. This one is a bit more abstract than the others as it provides, not data, but information about the parent file objects themselves. Another way of looking at this might be that the Database contains the File objects, while the Fields object describes the various File objects. In part 2 of this article, we will find that the Fields object is our key to dynamically discovering the actual layout of an ACT! data file.

Making the connection (OLE away...)

Since we do not have the luxury of writing a book here (remember those 350+ pages of ACT! SDK documentation?), we will go through two basic scenarios:

• The purpose of our first test procedure will be to make a connection, navigate the Contacts file and finally, update ACT! data. A VCR-type form, if you will.
• Our second scenario, in part 2, will delve a bit deeper, and create a procedure which utilizes the Field object to create a list of all of the fields within any ACT! Contact file.

One final note. For our purposes here, we will assume that we are doing everything within a single Window type procedure. In our actual applications, I quite often place the ACT! DB control on the toolbar of the Main frame, thus making it accessible to every section of the program (more on that in part 2 as well).

The basics: Creating the interface object

The first step in actually putting this all together is to create a window that contains the ACT! OLE Database control. When creating our sample procedures, we would create a new Window procedure (with OK and Cancel buttons). On the Window, we would add the DB object using the 'Insert' then 'OLE/OCX control' options as shown below.

The DB Object properties

A couple of notes. You will not be able to choose the object from a list, you will need to type it in ('ACTOLE.DATABASE'). You must have 32-bit checked here, and your application must be compiled as 32-bit. If you don't, any attempt to communicate with ACT! will simply return a message that there is no interface defined. Finally, you do not need to check any of the 'Callback generation' options for the OLE control template, as they are not required to interface with the DB object.

OK. Now, when our application is running and the window opened, the top level ACT! DB object is automatically created for us. In all of the examples, we will talk to it through the field equate for our OLE control which is, by default, "?OLE".

Doing the syntax fandango (OLE!)

Probably the most confusing thing about using OLE in Clarion is the wretched property syntax that we have to use (see the LRM for details). In a nutshell, rather than using pure 'dot' syntax like Visual Basic (or native Clarion objects), the field equate becomes, in essence, the objects label. Thus, rather than saying something sensible like:

ACTOLE.Database.Open("E:\ACT\DATABASE\ACTDEMO.DBF")

we must say

?OLE{'Open("E:\ACT\DATABASE\ACTDEMO.DBF")'}

Notice how we must pass everything including the method itself as part of the parameter to the control. (Even though it does basically work, this can cause some major problems, as we will see later on.) For lower objects such as the Contact object, we must preface the method (function call) with the name of the desired object, like this:

?OLE{'Contact.MoveFirst'}

This would move us to the first record in the Contacts file. As a side note, you'll find that the majority of ACT! methods work for all of the file type objects (ones which correspond to data files). In other words, the 'MoveFirst' method also works for Activities as in 'Activity.MoveFirst' (moves to the first Activity record), etc.

Example #1: Making contact with Contacts

In this case, we will assume that you have some idea what is in the ACT! data files already. For example, you are using a default or demo file that has all of the usual fields listing in the ACT! documentation and SDK. (Our second example will show you how to get this information automatically for any ACT! file.) To do this, we've created a window that contains local variables that match a few of the common ACT! Contact fields and looks like this:

The VCR Form window

Obviously, the 'Next' and 'Previous' buttons will move us through the file. The 'Update' button will apply any changes we've made to ACT!, while 'Reset' throws out any changes we've made and regets the current ACT! Contact record. The 'Go to Borg' button will take us directly to one specific record.

Note also the square in the upper right hand corner -- that is the screen representation of the OLE object itself. Normally, we would hide it since serves no visual purpose, but I left it exposed here just so you could see it.

Opening and closing a database

In the "After Initializing OLE Control" embed, we use the following code to open a database, test for errors using an ACT! 'Error' method, and then move to the first record in the Contacts file:

!-- Open the ActDemo database
?OLE{'Open("E:\ACT\DATABASE\ACTDEMO.DBF")'}
IF ?OLE{'Error'} <> 0
  MESSAGE('Tried to open, error is ' & ?OLE{'Error'})
  RETURN(Level:User)
END
MESSAGE('Ask DB if open, result:' & ?OLE{'Contact.IsOpen'})
?OLE{'Contact.MoveFirst'}     ! Get first record in file
DO ActToCompany               ! Display the first record

Once again, notice how code that references the top level DB object has no 'dot' prefix, whereas code that references a lower object must specify what it is talking to. For example, 'Open' applies to the database, while 'Contact.MoveFirst' tells the DB object exactly which file you want to move to the first record in (Contact in this case).

To close the database, you call a 'Close' method in your procedure's 'Before closing the Window' embed, like this:

?OLE{'Close'}
?OLE{PROP:Deactivate} = True

The first line tells ACT! to close all of the files in the active database, while the PROP:Deactivate tells the Clarion OLE interface to gracefully end its connection with ACT!. If it is omitted, you may experience GPF's when your program shuts down.

Getting field data from ACT!

The routine 'ActToCompany' which you saw in the previous code example simply moves data from the ACT! Contact object to our local variables so that we can display them and work with them. Unfortunately, every field must be retrieved individually, like this:

!-------------------------------------------------------
ActToCompany Routine ! Move to local Company buffer
!-------------------------------------------------------
COM1:CompanyName = ?OLE{'Contact.Data(25)'}
COM1:Address1 = ?OLE{'Contact.Data(27)'}
COM1:City = ?OLE{'Contact.Data(30)'}
!-- more of the same
ActCreateTimestamp = ?OLE{'Contact.Data(2)'}
ActEditTimestamp = ?OLE{'Contact.Data(3)'}
EXIT

So what's going on here? Well, when we move to a record, ACT! reads that record in to a local buffer within the ACT! object. To access a field within that buffer, we use a method called 'Data', along with a numeric parameter that tells ACT! exactly which field we want (sort of like a field equate in Clarion). And how do we know what fields correspond to which numbers? For the default ACT! fields, the SDK provides a list of the fields and their designations. These designations do not change, even if you rename the fields or delete surrounding fields. When you add brand new fields, they are assigned a number as well - but how do you find out that number? The answer to that riddle will be shown in part 2 of this article.

Navigating a file using Next and Previous

Moving along with the current example, let's see how we would move from record to record (using the code from our 'Next' button as an example):

IF ?OLE{'Contact.IsEOF'} = 0 ! If not end of file
  ?OLE{'Contact.MoveNext'}   ! Move to next record
  DO ActToCompany ! Fill our local buffer
ELSE
  MESSAGE('End of the file, no more records')
END
DISPLAY                      ! Display field contents

Here, we first check if we are at the end of the Contact file by using the 'IsEOF' method. If we are not, we use a method called 'MoveNext' to get the next record, call our ActToCompany routine to move the data, and then use the Display command to refresh the fields in our Window. Moving the other direction is just as simple, only we use methods such as 'IsBOF' (test for beginning of file) and 'MovePrevious'. Hey, this isn't so bad, is it?

Updating a record is a bit more involved, as seen here:

IF ?OLE{'Contact.IsLocked'} = 0 ! If record is not locked
  ?OLE{'Contact.Edit'} ! Request an edit lock
ELSE
  MESSAGE('Unable to update - record already locked')
  DO ResetFromAct ! Reget the current record
  CYCLE
END
CASE ?OLE{'Contact.LockLevel'}
OF 0 !-- Record is not locked
  MESSAGE('Lock failed - Rec is not locked')
OF 1
  UPDATE
  ?OLE{'Contact.Data(25)'} = CLIP(COM1:CompanyName)
  ?OLE{'Contact.Data(27)'} = CLIP(COM1:Address1)
  ?OLE{'Contact.Data(28)'} = CLIP(COM1:Address2)
  !-- more of the same here
  ?OLE{'Contact.Update'}
  IF ?OLE{'Contact.Error'} <> 0
    MESSAGE('Update Failed: ' & ?OLE{'Contact.Error'})
  ELSE
    0{PROP:Text} = 'Record Updated OK'
  END
OF 2
  MESSAGE('Lock failed - Locked by Network user')
ELSE
  MESSAGE('Lock Error = ' & ?OLE{'Contact.LockLevel'})
END

If we step through the code, we see that what it does is actually quite standard. First, we ask ACT! to lock the current Contact record for us by using a method called 'Edit'. The Case statement deals with the three possible outcomes of the Edit request: 0 - The lock fails for some reason, 1 - the lock succeeds, or 2 - the record is already locked by another user. If we do get a successful lock, we move the information from our local fields to the ACT! buffer and then call an 'Update' method which actually applies our changes to the ACT! Contact file. One more error test and we are done.

A few final thoughts on this code. Note the use of the Clarion Update command before we start moving data field by field into the ACT! buffer. This is needed to ensure that whatever we have been typing into the display fields has actually been moved from Clarion's screen buffer to our local variables. Also, note how we still must use the ACT! numeric field designations to let ACT! know which field the data is going into. Finally, an edit record lock remains in place until you either a) call the update method, or b) access another record using a method such as 'MoveNext'.

Moving to a specific record

We have seen how to move forward and backward through the Contact file, but how do we get a specific record? Just like Clarion has its Get() statement, the ACT! DB object has a similar method called 'Goto'. This method takes advantage of the fact that every ACT! record has a field called UNIQUE_ID, a twelve character string that is unique to that specific record. It is assigned by ACT! and cannot be changed (attempts to change it are simply ignored by the DB object). The Contact UNIQUE_ID field is also used to link the various child Activity, Email and Notes/History records to a Contact Record.

It is used like this:

?OLE{'Contact.Goto(' & SavedID & ')'}
DO ActToCompany
DISPLAY

In this case, SavedID is a string variable containing the UNIQUE_ID for a Contact record.

Seems simple enough, doesn't it? Unfortunately, here is where we hit the biggest pothole in our road to Clarion OLE nirvana...

Translation Troubles

Remember where I stated above that the property style syntax could cause problems with the CW OLE interface? Well, here is a perfect example.

The ACT! SDK defines UNIQUE_ID like this: "Unique ID field values are created by calculating a unique value, then modifying it to contain printable characters. The resulting value is a left justified string, which is padded with enough trailing spaces to complete the 12 character fixed-length Unique ID field". Why they didn't just use a number, I'll never know, but this is what it is and we are stuck with it.

So how does this cause problems? First, let's go back and take a look at the syntax for the Goto method:

?OLE{'Contact.Goto(' & SavedID & ')'}

Notice how we have to pass both the method name and its parameter list to the CW OLE layer within a single string. This means that there is a lot of parsing going on in there, as the CW interface tries to figure out what the method name is, what the parameters are (and how many of them there are), and where the whole mess ends. Only after it has done all this can it invoke the actual OLE object's methods.

Now, in a majority of cases, it does a good enough job - unless you are trying to pass anything that could be mistaken as a delimiter. We are talking characters like a double quote, a comma, open or close parentheses, curly braces, brackets and probably a number of other characters. Nine times out of ten, passing one of these characters will result in nothing being returned. No errors or anything - it's just like you didn't even call the method.

I'm sure you can see where we are going with this now. A common ACT! unique ID field value might look something like this (this is real data - I'm not making it up):

EK\.G)"P^,R

When you put that together with the rest of the method call, the CW layer is trying to translate something that looks like this: Contact.Goto(EK\.G)"P^,R)

"Hmmm," it says. "There are too many parentheses here -- they don't match up. There is also an incomplete string (the one double quote). Oh, yeah, is there one parameter or two (either side of the comma)?" And then right about this time it falls over dead.

The end result is that some Goto method calls will work (those that don't happen to contain the 'evil' characters) while others won't (valid records will not be found). Not exactly the basis for a reliable system. And no, before you ask, you cannot use the <decimal value> escape syntax for anything except the single quote character.

The workaround

Thus far, the only workaround I have found is to not use any ACT! methods which require a UNIQUE_ID value (this is not a good thing). An alternate method of fetching a specific record works like this:

1. Define a new field in the ACT! Contact file, which will contain a unique value that you create and fill. It can be either string or numeric, it doesn't matter.
2. Tell ACT! to create an index containing only that one field.

Once you have done that, you can use an alternate method called 'Lookup', which looks up values in indexes. Once it has located the record, you would use 'MoveNext' to actually retrieve it (just like using SET and NEXT within Clarion). The sequence might look like this:

?OLE{'Contact.Lookup(57,' & ValueToFind & ',1)
?OLE{'Contact.MoveNext'}

In this case, ValueToFind would be a unique value we have previously added to the ACT! record. Perhaps an autoincrement number from a CW file, for example. The first parameter is the ACT! field number (remember from updating?) so that ACT! knows which field (and thus which index) to search. The third parameter tells ACT! that we want only the matching records back as our 'result set'. Since there should only be one record returned, we can just grab it using MoveNext. Note: Using the third parameter, ACT! lookups can also be combined with previous lookups, or you can limit the lookup to searching only the result of the previous lookup.

Coming up in Part 2

In the second part of this article, we will be delving deeper into the ACT! OLE hierarchy, and see how to derive objects from other OLE objects (for example, the Fields object is derived from the Contact object). Note: This is a great trick - and you will not find it documented in any CW manuals! Additionally, we will dig deeper into the ACT! Contact object, and find out how to deal with multi-user ACT! databases.

Finally, I will be providing a sample application which demonstrates both the example discussed here and the Fields topic for next time. With it, you will be able to open and list the Contact file layout of any ACT! database and, as a bonus, save both the layout AND an automatically generated equates file.

See you next time!

Article Resources

To get the ACT! SDK:

The ACT! for Windows Software Development Kit (SDK) is now available through Symantec Customer Service and Order Services. To receive a copy of the ACT! SDK a developer fills out an ACT! SDK questionnaire/order form and faxes it to Order Services.

Developers can obtain the ACT! SDK questionnaire/order form from:

• Customer Service. Customer Service representatives will fax developers a copy of the ACT! for Windows SDK questionnaire/order form. Customer Service can be reached at: +1 (800) 441-7234 (USA & Canada only) +1 (541) 334-6054 (all other locations)
• Fax on demand. The Symantec fax on demand number, and the ACT! for Windows SDK questionnaire/order form document number is: +1 (800) 554-4403 (USA & Canada only) Document #121

The SDK will be mailed to you on floppy disk.

Web Sites

Symantec Corporation: http://www.symantec.com
Sequiter Software (CodeBase): http://www.sequiter.com

Printer-friendly version

Copyright © 1999-2008 by CoveComm Inc. All Rights Reserved. Reproduction in any form without the express written consent of CoveComm Inc., except as described in the subscription agreement, is prohibited.

Clarion Magazine ISSN 1718-9942