The MemoText Control
|
|
|
One of the common needs of an application is the ability to store text of unknown length. The dBASE table format can provide a field of up to 254 characters, but this limit is quickly reached when you want to store free form notes or dialogue.
Some table formats provide for variable-length fields, where the space used on the hard drive varies depending on the field contents. This type of table may provide greater scope for storing long-length text within the table itself, but because of the unpredictable position of field boundaries on the hard drive, if you are unfortunate enough to suffer file corruption, then you may find that your data is very much more difficult to recover. dBASE tables on the other hand have historically always been relatively easy to deal with in terms of salvaging data, because the fields for each row are marked out on the hard drive whether they contain data or not.
The most common way for fixed-length field tables to provide variable-length storage, is to provide a field which links to a “blob” file. The latter is a separate file, and in addition to storing text, can store binary data, thus allowing the storage of image or sound files and the like. The Memo field provided in the dBASE table format is the link-field which points to variable-length text stored in a .dbt file.
Back in the days of dBASE IV our own experience was that while the main dBASE table was fairly robust, the .dbt file was a bit more fragile. A lot of our problems may have been attributable to the hardware cache cards we were using at the time. Nevertheless it has meant that we have been wary of relying on .dbt files for long-length text storage ever since. It seems from occasional messages in the dBASE newsgroups that this concern is shared by others.
We do use .dbt files quite happily for image data, mainly in catalogue-type applications where there is little or no change to the data once it is compiled. It seems that some developers avoid using a .dbt file for this purpose by simply storing a link to a discreet image file. Similarly, long-length text for use in a standard editor control can be stored in a discrete “txt” file, and a link to this stored in a normal table. This does mean that you could end up with a directory containing a huge number of txt files, and to me this seems somewhat to defeat the purpose of using a database. With good data backup procedures you could argue that there is nothing to worry about, and using a .dbt file should be fine. You could also argue that the most basic storage requirement of a table is text. Why should you have to use a complex file structure capable of storing binary data when all you want to do is store simple text? It would also be nice to know that you are using the most robust storage method that you can.
The MemoText control allows you to store long-length text in the rows of a standard dBASE table.
There is no reason why this same basic technique of chopping up and re-stitching cannot be put to good use for dBASE tables. This is what the MemoText control does, in conjunction with a simple table in place of a .dbt file.
Just as multiple memo fields in a table are stored in a single .dbt file, the MemoText control will allow multiple instances of the MemoText control to share a single table.
In theory, there is no limit to the amount of text that can be stored in this way, other than the normal limits of the dBASE table format itself.
The name of this table is up to the developer, but it would seem to make sense to match it to the name of its “parent” table, and you could use a .mtf extension to indicate that the table contains MemoText. One way to do this is to create the MemoText table with a different name and a standard .dbf extension. Once created, simply rename the file. Once renamed with the non-standard extension, the file will not be readily visible in the dB2K development environment, and in this way perhaps more closely mimics the .dbt file. It also reduces the risk of someone opening the table and making changes directly, which is obviously not desirable. If you would rather be able to easily see and work with this table directly, why not use the name of the “parent” table and put the mtf suffix before the standard .dbf file extension. An alternative way to create the MemoText table is simply to set up the control without the table being present, and in this case the control will provide a prompt offering automatic creation of the table on first use.
The Memo_ID field contains a unique ten-character string, and this same string is stored to a ten-character field in the “parent” table. This is used to link to the constituent parts of the MemoText. Normally you would think of using autoinc fields to provide a unique value to use for linking, but we cannot do that here, because we will have several rows in the MemoText table which must have the same link value. We therefore need to provide our own unique value, and we have to consider that there may be several MemoText controls on a form which may all almost simultaneously be wanting to create new entries in our MemoText table. There may also be several instances of the same form open at one time, perhaps in a multi-user environment. In addition, there may be a requirement to merge tables from different machines, and we need to be able to do this without the risk of the donor tables using the same ID.
I have created
a function to do this in just two lines of dBL code. The function takes
the year, month, day, and the number of hundredths of a second passed midnight
at the time of calling, forming a decimal string which is far in excess
of the ten-character field width. This decimal string is then converted
to an eight-character hex string, and a two character pseudo-random number
is suffixed, as follows:
|
Function Generate_ID() return itoh(val(year(date()) ; + str(month(date()),2,0,"0") ; + str(day(date()),2,0,"0") ; + str(int((seconds() * 100)),7,0,"0")),8) ; + str((random()*100),2,2,"0") |
|
So far, this has never failed to provide a unique result. If you can demonstrate otherwise, please let me know!!
The Memo_Sequence field is probably not required. It will sequentially number up to 9999 parts of a single MemoText entry. In theory, this means that the maximum storage for a single entry is 999,900 characters, but in practice this could easily be exceeded. Why? Well, the MemoText control, although it populates this field, doesn’t actually use it. The table is used in natural order, and there is no way that I can see that the rows for a single MemoText entry could become out of sequence. I chose to leave this field in the table, however, so that in the unlikely event of table corruption, it would be easier for the developer to “see” the required sequence. Perhaps it is old memories of stitching damaged dBASE IV files back together at low level with a Hex editor that makes me happier to do this. I hope I never need it!!
The Memo_Text field is the field that holds the data. In choosing the field width for this purpose we should consider economy of disk space. There is no reason why this control could not have used the maximum field width available, but for an entry length which rolls just a few characters over a multiple of the field length, I thought this was a bit wasteful. Also 100 is a nice round number!!
My preferred method of making a
custom control available to a form has in the past been to put a copy of
it in the same directory as the form, and use the source editor to insert
the following line of code into the Constructor code for that form, as
the second line of code for the class:
|
Class MemoTextCCForm of FORM set procedure to MemoText.cc additive |
|
Having done that, open the form in the designer, and you will find that the MemoText control is available on the custom tab of the Component Palette. It is now a simple matter to drag and drop an instance of the MemoText control onto your form. The control can then be named to suit, sized and positioned.
With the first release of dB2K we now have the option of using Source Code Aliasing, which allows you, if you wish, to reference each custom control you use in its own development directory, ensuring that you are always using the most up-to-date version. For more information on this, see Ken Mayer’s article in the special dB2K edition of the dBulletin.
Now we have to tell the control
what it has to interact with, and this is done by calling the
mtSetup() method of the
control from the onOpen() event
for the form
|
Function MyForm_onOpen form.MemoText1.mtSetup("MemoTextTable.mtf", this.MyRowset, "MemoText1_ID") |
|
There are three parameters required by the control. The first is the name of the table the control is going to use to store the MemoText in. The second is a reference to the rowset for the table which contains the linkfield for the MemoText control. The control needs to know this so that it can alter the rowset modified property, if required, when a change is made to the text in the MemoText control. This is to ensure that the rowset save() fires, which in turn will ensure that the changed contents of the MemoText control are saved. The third parameter is the name of the link field in the “parent” table, and it is the content of this field which the control will use to find the appropriate MemoText entries for the control.
With custom controls, I have always thought that ideally they should be completely self-contained with no outside dependencies other than perhaps the initial passing of parameters to the control on opening. This is not always possible. We are trying to create a control which is intended to behave exactly like a datalinked control, but in this instance, because we are not using the normal datalink of the editor control, and because the control needs to respond to rowset events, a little more setting-up is required.
We have to ensure that the MemoText
control repopulates itself when its “parent” rowset navigates. We similarly
have to ensure that the control responds correctly to a save, abandon,
append, etc.. In order to do this we have to ensure that the rowset events
for the “parent” rowset fire the corresponding event handlers for the control
itself. I have chosen to set up my data access in a datamodule which can
be used for reports as well, but however you choose to do this, the basic
principle remains the same. If we want the datamodule to be able to fire
the methods of our MemoText control, we need to give it a reference to
the form which is using it. This is probably best done in the
onOpen()
event code for
the form, and we are attaching a custom property
ParentForm which will
contain an object reference for the form, like so:
|
Function form_onOpen this.DATAMODREF1.ref.ParentForm = this |
|
Rather than have separate functions
containing the code for the event handlers, I think it is neater and tidier
to use codeblocks, keeping everything together. If we have more than one
MemoText control, however, this will take more than
one line of code in the codeblock. It can still be done, and the syntax
needed to do it is given below. The source editor doesn’t take too kindly
to this type of construction, so you’ll have to ignore its somewhat distracting
colour interpretation of this block. Despite this, it does work!!
|
with (this.MyQuery.rowset) onNavigate = {; ;try; ;this.parent.parent.ParentForm.MemoText1.mtNavigate(); ;this.parent.parent.ParentForm.MemoText2.mtNavigate(); ;catch ( Exception e ); ;endtry; ;} onEdit = {; ;this.parent.parent.ParentForm.MemoText1.mtEdit(); ;this.parent.parent.ParentForm.MemoText2.mtEdit(); ;} onSave = {; ;this.parent.parent.ParentForm.MemoText1.mtSave(); ;this.parent.parent.ParentForm.MemoText2.mtSave(); ;} onAbandon = {; ;this.parent.parent.ParentForm.MemoText1.mtAbandon(); ;this.parent.parent.ParentForm.MemoText2.mtAbandon(); ;} onAppend = {; ;this.parent.parent.ParentForm.MemoText1.mtAppend(); ;this.parent.parent.ParentForm.MemoText2.mtAppend(); ;} onDelete = {; ;this.parent.parent.ParentForm.MemoText1.mtDelete(); ;this.parent.parent.ParentForm.MemoText2.mtDelete(); ;} endwith |
|
You may have noticed that the onNavigate() code is wrapped up in a try/endtry, and we will cover this when we come to reports with MemoText.
The control is now ready to use.
If you are interested in the code which is being fired in the control, open it up in the source editor, and take a look. It should be pretty much self-explanatory. The biggest problem has been to try and make sure that every possible user action is properly handled. If you find a sequence of user actions which is not properly handled, please let me know.
A sample test /demo form MemoTextCC.wfm is provided with this article, along with it’s datamodule MemoTextCC.dmd and tables mtCustomer.dbf/.mdx, mtOrders.dbf/.mdx, and mtOrders.mtf. Run the form and have a play with it. Add records, change existing records, delete records. Do whatever you can think of, and see what you think. I am sure that you will agree that although a little setting-up is required, it is pretty easy to do, and in use you would be unaware that anything different was happening behind the scenes.
You might think that it is all very well having a nice control which will chop up text, store the bits in table rows, and reconstruct them to repopulate the control, but how on earth are you supposed to produce a report containing MemoText?
The answer
is remarkably simple. It is very easy using dBL to add a calculated field
to the rowset containing the link field. We can use this to produce a field
in the rowset which will be populated with the reconstructed MemoText.
Here is a sample of the code that is required to create the field:
|
Function MyQuery_onOpen f = new Field() f.fieldName = "MemoText1" f.mtLinkField = "MemoText1_ID" f.mtTableName = "mtOrders.mtf" f.beforeGetValue = class::mtBuildMemo this.rowset.fields.add( f ) |
|
The beforeGetValue() method of the field is set up to call mtBuildMemo() which is a generic function to reconstruct MemoText. How do we use a generic function for multiple fields, where the link field is obviously going to be different, and the MemoText may not even be stored in the same table? Normally when calling a function, we would simply pass the parameters required, but in this instance we have an example of how to pass parameters without passing parameters.
One of the
great things about dB2K and the dBL language is the strong implementation
of object hierarchy. Having come from using dBASE IV, you tend to think
of a field as a field in a table, and you probably copied the field’s
contents into a memory variable to work with it. dB2K effectively does
this for you, by copying the field contents into the rowset’s
field array, the elements of which are field objects. This means that just
as you might attach a custom property to a visual object on a form, it
is possible to attach custom properties to the field object. We have given
our calculated field two custom properties,
mtLinkField,
which is assigned the name of our link field (which is in the same rowset),
and mtTableName
containing
the name of the table containing the MemoText. This means that our generic
MemoText rebuilding function can get what you would normally view as parameters,
simply by examining these two custom properties. The power and flexibility
of dBL is evident in how little code is required to do this.
|
Function mtBuildMemo() local sConcant, tpp sConcant = "" tpp = this.parent.parent if tpp.fields[this.mtLinkField].value = null return sConcant endif if not tpp.endofset // Set up Query sQueryString = "'select * from " + '"' ; + this.mtTableName + '"'; + " where Memo_ID = " + '"' ; + tpp.fields[this.mtLinkField]Value ; + '"' + "'" // this.q = new Query() this.q.parent = this with (this.q) sql = &sQueryString. active = true endwith // // Build memo contents this.q.rowset.first() do while not this.q.rowset.endofset sConcant = sConcant + ; this.q.Rowset.Fields["Memo_Text"]Value this.q.Rowset.next() enddo // Close query this.q.active = false this.q = null // endif // return sConcant |
|
Because I have set up the data access in a datamodule, this function needs to be present in that datamodule, but it can equally be made available wherever you are setting up your data access. This could be within a form’s class code, or a report’s class code. Once this is set up, it is simplicity itself to add MemoText to a report. In the Report designer you will find the calculated field in the field palette, from whence it can be dragged and dropped onto the Report designer surface. A sample report with two MemoText fields can be run from a button at the bottom of the supplied demo form, or it can be run directly.
We are using the same datamodule for our report and the form. When the report runs, the rowset in the report instance will navigate, and this will try to fire the onNavigate() event code which we set up for our MemoText control. However we don’t really want our MemoText controls to have their contents changed unless the form itself is the source of the navigation. The ParentForm reference that we set up to allow the datamodule to reference the MemoText controls will be out of scope as far as the report is concerned, or may not exist, if for example the report was run directly, or if, as we are doing here in the sample code, we use a second instance of the datamodule for the report. This is why we have wrapped the onNavigate() code in a Try/EndTry. We know that if the ParentForm reference is out of scope an error will be generated, and handling this allows us to use the same code to do two jobs.
You have probably worked out by now that if we can produce a MemoText calculated field for use in a report, we can use the same technique to display MemoText on a form. All we need to do is use a standard editor control datalinked to the calculated field we added to the rowset. Obviously this is only of use in “Display Only” situations, and the best way to ensure that the user does not try to change the contents of the editor is to ensure that the editor cannot receive focus.
During testing, Bob Rimmington wanted to establish if he could use MemoText in conjunction with his User Report Generator (dBulletin issue 9), which provides a quick head-start with self-contained listing-type reports, which have their own query code. I think the best approach in doing this is to design your report using the link-field in place of the MemoText it links to. Once the initial report has been generated by the URG, open it in the editor. The calculated fields can be added to the appropriate query, and the BuildMemo() function code pasted into the class code. The link-field entries can now be changed to the corresponding calculated field, and the job is done — remarkably quickly and easily. A copy of this report, URGlist.rep is included in the zip file available at the top of this article.
In fact, using a standard datalinked control has some advantages. Several of the rowset events which we had to deal with for our custom control will be dealt with automatically for us. Navigation will automatically populate the control. Abandoning changes will do the same. By dealing with the link-field ID generation for appended rows as part of the saving process we don’t need any append code, and this has the added advantage of making our link field ID behavior more like that of an autoinc field. In fact, the only rowset events we need to deal with are saving and deleting.
One of the difficulties in doing all of this using a calculated field is that such a field is intended to provide one-way traffic only: from the rowset field array to the datalinked form control, and not the reverse. This means that we have to do the work to ensure that any changes made in our standard editor control are saved correctly. Because we are using datalinked editor controls for our MemoText we have to save the contents of our editor control before the rowset is saved rather than just after, otherwise we will see our MemoText changes disappear as the control is repopulated with it’s original contents. For this reason we are using the rowset’s canSave() event rather than the onSave event. The code used for this is a very easy adaptation of the code from the custom control. For similar reasons we are using the rowset’s canDelete() method in place of the onDelete() event. It is difficult to know what MemoText rows to delete, when the linkfield in the “parent” table has just disappeared!! Again, the code needed for deletion is a very easy adaptation from the custom control.
In the creation
of our calculated field, we have added another custom property to it —
mtLinkFieldPointer.
|
f.mtLinkFieldPointer = this.rowset.fields["MemoText1_ID"] |
|
The two previous custom field properties are strings, which are used to construct the query SQL string for our mtBuildMemo() code. This time we are creating an object reference, or pointer, to the link-field itself. This is so that a generic function can write back to the link-field by changing the value of this custom field property. This ability will be used the first time MemoText is added to an editor control, and also if and when all the MemoText is deleted in an editor control.
When we were
using our MemoText custom control, the control’s
“rowset” events were fired by the corresponding rowset event. Each MemoText
control took care of its own housekeeping once set up. If we want to use
generic functions from a datamodule to do the same job, then the datamodule
needs to know which fields in the rowset are MemoText fields. We do this
by calling the mtSetup() function
in the custom datamodule from the
onOpen() event
of the query for the rowset containing our calculated fields.
|
this.parent.mtSetup(this.rowset, "mtOrders.mtf") |
|
The second parameter containing the MemoText file name allows the mtSetup() function to check for the existence of the MemoText table, and if not found, offers automatic creation. With the first parameter, we are passing an object reference for the rowset to the function. The function then works its way through all the fields in this rowset looking for the custom properties we have created, thus identifying fields with these properties as MemoText fields, and once found, the field is added as an object reference to an array. This array will then be used when performing the saving and deleting code required for each MemoText field when the event is fired.
So far so
good. We have calculated fields containing our MemoText. We have an array
of MemoText fields for the custom datamodule functions to use. We need
to save any changes made in our “MemoText” standard editor controls. How
do we get our data to flow the wrong way up a one-way street? The first
problem that we have here is that changing the contents of our standard
editor control will not change the rowset’s
modified property
to true.
A datalinked calculated field is considered to be used in read-only mode,
so this would not normally be a requirement. We have to ensure, however,
that if we make changes to our MemoText, that the Save events fire, and
to do this we have to ensure that the rowset’s
modified property
is changed to true.
Interestingly, any change made to the MemoText in a standard editor control
will fire the onChange() event
for the datalinked calculated field itself, and we can harness this by
including the following line of code into our calculated field Construction
code:
|
f.onChange = class::mtOnChange |
|
This in turn
fires the following code in our custom datamodule:
|
this.parent.parent.modified = true |
|
This could
have been done in one step using a codeblock, but if we want to think of
our custom datamodule as a pseudo-custom control then it is probably better
to have all the required event code visible in that one place. Now we know
that the save events for the rowset will fire. It should now be simple
to save our changed MemoText. Well, not quite. What are we going to save?
The value property
of our calculated field still contains the original contents of our field
before we made our changes. It is the editor’s
value property
which contains our new MemoText. The problem here is that while our editor
control knows about our calculated field, and is datalinked to it, neither
the field nor the rowset have a clue about the existence of the editor
control. How do we access the contents of the editor control from our custom
datamodule code? The solution I have used here is to use the
onOpen() event
for the editor control to give the calculated field to which it is datalinked
a new custom property VisualControlRef
which
will contain an object reference to itself (the editor control):
|
Function MemoTextEditor1_onOpen Form.datamodref1.ref.mtordersquery.rowset.fields["memotext1"] ; .VisualControlRef = this return |
|
This is easy to do; just copy the line from the datalink property of the editor control, and paste it into the onOpen() event for a quick head-start, and finish the line off manually. We can now get at our changed MemoText by examining our calculated field’s VisualControlRef.value.
A working set of samples is provided with this article; the custom datamodule MemoText.cdm, the datamodule for the demo, MemoTextCDM.dmd (which uses our custom datamodule as its base class, therefore inheriting all it’s methods), the demo form MemoTextCDM.wfm, and a demo report MemoTextCDM.rep. The tables are shared for both methods. Examine the code contained in these samples to see how this has all been implemented, and play with the form. You may see subtle differences in the sequence of events compared with the custom control approach, but they both do exactly the same job.
|
Function MyQuery_onOpen // f = new Field() f.fieldName = "LogEntryMemoText" f.mtLinkField = "LogEntryMT" f.mtTableName = "SuppliersLogMTF.dbf" f.mtLinkFieldPointer = this.rowset.fields["LogEntryMT"] f.beforeGetValue = class::mtBuildMemo f.onChange = class::mtOnChange this.rowset.fields.add( f ) // |
|
|
this.parent.mtSetup(this.rowset, "SuppCustLogMTF.dbf") |
|
|
canDelete = class::MTCANDELETE canSave = class::MTCANSAVE |
|
|
.VisualControlRef = this |
|
|
Class MemoTextDataModule of DATAMODULE custom |
|
to:
|
Class MemoTextBaseForm of FORM custom |
|
and save as using a .cfm extension. That really is all there is to it! Now you can create a new form based on this custom form, and all the MemoText functionality is sitting there waiting to be used. The MemoText.cfm and a sample test /demo form MemoTextCFM.wfm is provided with this article. It took less than five minutes in the source editor to take the custom datamodule version of this demo form, and re-jig it to work with the custom base form instead. This versatility and ease of re-use is one of the major strengths of dB2K and the dBL language.
The same principle could be used to produce a custom report, on which to base any report containing MemoText, and Bob’s self contained URGlist.rep could be produced in this way too. I have not done this, but if you need it you can produce the base report in under a minute.
Which method is best? If you like the visual control concept of the custom control, then use it. If you are happy with the concept of moving this same functionality to a custom datamodule, then use it instead. If you don’t use datamodules, or prefer to keep forms completely self-contained, then use the custom form. There is probably a place for all approaches. This article is no more than a documentary of the development of this functionality, which started with the custom control, dealt with the need to produce reports with MemoText, and then moved the whole lot to a custom datamodule. The custom form approach was an afterthought, when I decided to add MemoText functionality to a pre-existing application which used self-contained forms. This was the quickest method in this instance, as there is less setting-up needed in comparison with the custom control. I do find, however, that with new applications, I seem to choose the custom datamodule approach.
You can download all of the files for this article from the top of this page, and are free to use them as you wish. I will be happy to receive any feedback by e-mail, or by posting in the dBASE Newsgroups. If you make use of this control, or any of this code, an e-mail to that effect would be nice, just to let me know that it has all been worthwhile!
Have Fun!
Thanks to Romain Strieff for his help in the dBASE Newsgroups with the syntax required to put multiple lines of code in a codeblock. Thanks to Dan Howard and Bob Rimmington for beta testing, and their comments and suggestions. Thanks to my wife Sarah for proof reading, and putting up with this being written on our flight from Glasgow to Denver. Finally, thanks to David L. Stone for the improvements he brought to this text.
Ronnie MacGregor started developing dBASE IV applications around 15 years ago to help administer his businesses in Scotland. Some of these applications are still in daily use, but are gradually being replaced with 32bit dB2K apps. Although with no formal programming training, and essentially a "hobby" programmer, some of his niche applications are in daily use by other businesses in Britain. He can be contacted at:
December 2001:
Thanks to Dr Howard Mintzer for
feedback in on using MemoText. Howard found that in his tests with 1,000,000 records,
performance degraded beyond an acceptable level. The solution to this was to create a
simple ascending index on the Memo_ID
field in the MemoText table.
No other change is required. This fix works on the basis that if a simple index exists on
a field, then the BDE will use that index to advantage, even when that index is not explicitly in use.
If I remember correctly, it was also Howard who commented that when batch appending
large volumes of data to MemoText in a tight programming loop, there could be the very
occasional duplication of Memo_ID
value. I was unable to
reproduce this problem, but it was solved by introducing a small time delay into the loop.
There have been no reports of duplicated ID values in normal use.
February 2002:
Thanks to Peter Schneider for
feedback on the Generate_ID()
function. He offered a
shorter alternative method using the date class : itoh(new Date().getTime(),8) + str(random()*100,2,2,"0")
August 2003:
Thanks to Bob Graham for feedback
on the custom dataModule version. Bob modified this class to use a rowset instead of a
table name, and reports that he has “a datamodule in which four different tables use
the MemoText rowset to store their text, and it works just as it did using the table
name”.
March 2005:
The latest versions of
these controls are available from: www.dBASEdeveloper.co.uk