Dr. Dobb's Sourcebook July/August 1997
If you're using a Microsoft programming language such as Visual Basic, you've no doubt considered using the Jet database engine to implement your data store. Although Jet is a great tool and supports a number of advanced features (like data replication), it also has a number of distinct disadvantages that can cripple your application. In this article, I'll show you an alternative to Jet for some projects.
So, what's the problem with Jet? After all, if it's good enough for three major releases of Microsoft Access, it should be just fine for you as well, right? Not necessarily. Adding the Jet engine to your application also means adding megabytes of DLLs. This will not only impact the size of your distribution, but also increase the amount of memory your program requires. This can reduce performance to a level that's no longer acceptable to users.
Apart from the performance issue, Jet is also complex. This can be an advantage at times, because there's not much you can't do with it. On the other hand, you probably will not need all of this advanced functionality all of the time. In cases like this, tasks that might seem easy at first, such as storing multimedia data, are not really that straightforward due to Jet's complexity. And because of the way Jet handles record locking, it's sometimes difficult to determine if the code you're writing is really multiuser safe.
This is not to suggest that you should always avoid Jet, only that it's important to consider your options. As always, use the right tools for the job. If your data-storage requirements are modest, or if you only want to store some configuration data, it's obviously a waste of resources to use a multimegabyte database package. Instead, keep your program's configuration options in the Registry, and use a lightweight tool to store your data records. The advantages of this approach include a clean programming interface with (hopefully) few surprises, as well as optimal performance due to a minimal impact on the resources of your target system.
Using a data store that is fast and efficient may sound like a great idea, but how easy is it to implement such a solution? After all, if you have to spend several weeks writing your own database engine, this alternative may be far more troublesome than using Jet. However, if you don't expect to store too many records and don't require any fancy features, you might be surprised by how efficient a do-it-yourself database engine can be.
For more demanding situations, or when you just don't want to spend extra time coding, you might consider a third-party database control such as VideoSoft's VSData. Its most appealing feature is that it is truly lightweight -- the entire database engine is implemented as a single ActiveX control that doesn't rely on any proprietary support DLLs. The control adds only 160 KB to your distribution, but it's by no means lacking in functionality.
Adding an index, for example, is possible at any moment using any field with a fixed-size data type. Performance is more than acceptable; in fact, record sorting is fast even if you're storing large chunks of data in your database. VSData includes special capabilities for file storage, allowing you to embed multiple files in records without a speed penalty. This enables easy storage and retrieval of multimedia data, like .AVI and .WAV files. Also, unlike some xBase-oriented add-on tools, VSData supports the familiar FindFirst/FindNext methods, which makes searching for data as easy as it is with Jet.
Support for automatic file locking on networks is also implemented in VSData, assuring data integrity when more than one user is trying to edit the same record. Basic database security is provided, allowing you to prevent users from adding records to a database, replacing data that's already present, or writing to the database at all. Also, if you embed Internet URLs in your records, VSData will optionally load the user's web browser to display the data at that location.
In my examination of VSData, I built a catalog application in Visual Basic for my collection of MIDI files. (A demo version of the engine is available at VideoSoft's web site at http://www.videosoft.com/. The complete project is available electronically, see "Programmer's services," page 3. This file also includes the shareware/demo version of VSData.) The first step is, of course, to design the layout of the database. You can do this either through the VSData Manager utility (VideoSoft supplies its source code with the control), or from within your program. Creating the sample database in Figure 1 is programmatically straightforward, requiring only the single statement in Example 1. Note that you only have to specify the layout of the database file -- in this case, three string fields, an integer field, file, web address, date fields, and another string -- and that you don't have to specify any field names. VSData doesn't care about field names and lets you refer to fields through the Field() array. If you want to, you can assign specific names to fields by using the FieldNames() array. Databases that you create using the Manager utility will have an associated description file.
Once the database has been created, data entry works comparably to data entry with VB's native data control. The only difference is that you can't bind VB controls to a VSData control, and you'll have to write some additional code to handle these bindings yourself. As the update code in Example 2 shows, this doesn't add much complexity.
In this example, each field in the VSData database corresponds to an element in a Visual Basic control array of text boxes. You might expect the real code (see Listings One and Two) to contain some special cases for saving the file data, but the only actual exceptions made here are to handle the date field, which VSData expects to be a Variant of type 7 (vbDate), and the combo box; see Figure 2. The combo box needs special treatment because Visual Basic won't allow you to have a different control type in the same control array; otherwise, a simple check for the control class would have been sufficient. Storing the MIDI file used in the example is as simple as assigning its full path name to the appropriate field array variable. The VSData control takes care of the rest. If you're worried about the resulting update and retrieval performance, don't. Even if you're not using any indexing (as in this example) and not storing large data files, record access is almost instantaneous.
When you retrieve a record containing a field of type File from the database, VSData will, at first, return only the string 'FILE' to you. However, you can use the ExtractFile method to retrieve the original file specified when creating or updating the record. This poses one small problem, though: If you read a record from the database and want to write it back, VSData will complain because it can't locate the file 'FILE' (which is the value of MIDIfile field). To prevent the original filename from getting lost in this way, you'll have to store it in an additional database field and put it back at an appropriate moment -- when reading the record data, for example. This is the purpose of the MIDIfileName field in our example database: It keeps a "backup copy" of the filename that was entered in the MIDIfile field originally.
Once all the data is entered in the database, you need to be able to get it out again. Adding navigation buttons to the form is trivial: Invoking the MovePrevious or MoveNext method in the corresponding event routine does the trick. To prevent users from trying to move past the beginning or end of the database, it's also a good idea to disable/enable the navigation buttons depending on the Beginning Of File (BOF) and End Of File (EOF) properties. This is only a cosmetic measure, though, as VSData will simply ignore record navigation commands that can't be executed.
Adding a find option is also easy. In the example presented here, it's only possible to search for certain song titles, so after asking the user for the desired search string (using an input box), you simply execute Example 3. By using the IN operator, it's also possible to search on partial titles. Implementing searches on multiple fields and using different operators, as well as allowing the user to navigate within result sets, is left as an exercise to the reader, but VSData supports all of this.
The only thing that's left to do to wrap up the sample application is to use the special data (the MIDI file and the URL) stored in the database. VSData provides all the code needed to launch the user's web browser and go to the site stored in the WebSite field: A simple VSData1.MediaPlay "WebSite" is sufficient. Sound and video clips (.WAV and .AVI files, respectively) can be played back with equal ease. You also have some control over the appearance of the resulting Media Player window using the MediaLeft/Top, MediaParent, MediaVisible, and MediaZoom properties. If you want to provide a friendly way of browsing for files at data-entry time, simply use the SetMediaField method.
VSData is a versatile control. In addition, with its small size, impressive speed, and multiuser capabilities, it might be a good replacement for your current database engine, especially if you store files and multimedia data in your databases. Keep in mind, however, that a lightweight data control will not have all the capabilities of more-advanced tools. So, if you heavily depend on SQL-like queries, transactions, and the like, a Jet solution might very well be an optimal one: A smaller distribution size and a possible speed increase aren't worth the major rewrites and loss of functionality in such cases.
VideoSoft offers a demo version of VSData at http://www.videosoft.com/. If you want to discuss database design in general, you might want to visit the VBPJFORUM and the MSBASIC forums on CompuServe, or one of the Microsoft news groups at http://www.msnews.microsoft.com/.
DDJ
Public fIsNew As Integer
Private Sub cmdFind_Click()
Search$ = InputBox$("Enter song title to find", "vsData sample")
VsData1.SearchString = Chr(34) & Search$ & Chr(34) & " IN Field(1)"
VsData1.FindFirst
If Not VsData1.EOF Then
UpdateFields
Else
MsgBox "Not found!"
VsData1.MoveFirst
End If
End Sub
Private Sub cmdGoWeb_Click()
VsData1.MediaPlay "WebSite"
End Sub
Private Sub cmdNew_Click()
On Error Resume Next
'//Initialize state to 'entering new record'
fIsNew = True
For fld% = 1 To 7
txtField(fld% - 1) = ""
Next
cmbField.ListIndex = -1
End Sub
Private Sub cmdNext_Click()
VsData1.MoveNext
cmdPrevious.Enabled = True
If VsData1.EOF Then cmdNext.Enabled = False
End Sub
Private Sub cmdPlayMIDI_Click()
'//Extract the file from the database to its original
'// source, then launch the Media Player to play it
VsData1.ExtractFile "MIDIFile", VsData1.Field(8)
Shell "MPlay32 " & VsData1.Field(8), vbNormalNoFocus
End Sub
Private Sub cmdPrevious_Click()
VsData1.MovePrevious
cmdNext.Enabled = True
If VsData1.BOF Then cmdPrevious.Enabled = False
End Sub
Private Sub cmdUpdate_Click()
If fIsNew = True Then
VsData1.AddNew
End If
On Error Resume Next
For fld% = 1 To 6
Err = 0
VsData1.Field(fld%) = txtField(fld% - 1)
'//Did we hit the hole in our control array?
If Err Then
'//Yup -- this is the combo box
VsData1.Field(fld%) = cmbField.ListIndex
End If
Next
On Error GoTo 0
'//Store date...
VsData1.Field(7) = CVDate(txtField(6))
'//Store extra copy of MIDI file name, as vsData will
'// replace it with 'FILE'...
VsData1.Field(8) = txtField(4)
VsData1.Update
End Sub
Private Sub Command2_Click()
End Sub
Private Sub Form_Load()
'//Init our Category combo box
cmbField.AddItem "Popular/Dance"
cmbField.AddItem "Ballad"
cmbField.AddItem "Rock"
'//If no database file is present, create one
If Dir$("midifiles.isd") = "" Then
CreateSampleDatabase
End If
OpenSampleDatabase
VsData1.MoveFirst
If VsData1.EOF = True Then
'//Empty database, so set state to 'entering new record'
fIsNew = True
Else
UpdateFields
End If
End Sub
Private Sub OpenSampleDatabase()
VsData1.OpenDataBase "MIDIfiles"
VsData1.FieldName(1) = "Title"
VsData1.FieldName(2) = "Composer"
VsData1.FieldName(3) = "Made Famous By"
VsData1.FieldName(4) = "Category"
VsData1.FieldName(5) = "MIDIfile"
VsData1.FieldName(6) = "WebSite"
VsData1.FieldName(7) = "EntryDate"
VsData1.FieldName(8) = "MIDIfileName"
End Sub
Private Sub CreateSampleDatabase()
'//Make sure there is no existing database
'// (only a sample, not to be used when actual data persistence is desired...)
VsData1.KillDataBase
VsData1.CreateDataBase "MIDIfiles", "$$$%FWD$"
VsData1.CloseDataBase
End Sub
Private Sub VsData1_Error()
'//Simple error handler (make it the user's problem...)
MsgBox VsData1.ErrorMessage, vbCritical + vbOKOnly, "vsData reported an error!"
End Sub
Private Sub UpdateFields()
On Error Resume Next
For fld% = 1 To 6
Err = 0
txtField(fld% - 1) = VsData1.Field(fld%)
'//Did we hit the hole in the control array?
If Err Then
'//Hmm, this must be our combo box!
cmbField.ListIndex = Val(VsData1.Field(fld%))
End If
Next
On Error GoTo 0
'//Put date back where it belongs, properly formatted...
txtField(6) = Format(VsData1.Field(7), "general date")
'//Kludge to get real filename back in MIDI file field:
txtField(4) = VsData1.Field(8)
End Sub
VERSION 5.00Object = "{66A3FDC8-EF71-11CF-881F-00001B4D5DFB}#1.0#0"; "VSDATA.OCX"
Begin VB.Form MainForm
BorderStyle = 3 'Fixed Dialog
Caption = "My vsData MIDI Database"
ClientHeight = 3195
ClientLeft = 45
ClientTop = 330
ClientWidth = 4740
ClipControls = 0 'False
Icon = "MainForm.frx":0000
LinkTopic = "Form1"
MaxButton = 0 'False
MinButton = 0 'False
ScaleHeight = 3195
ScaleWidth = 4740
ShowInTaskbar = 0 'False
StartUpPosition = 3 'Windows Default
Begin VB.CommandButton cmdFind
Caption = "&Find"
Height = 375
Left = 1920
TabIndex = 11
Top = 2760
Width = 975
End
Begin VB.CommandButton cmdNext
Caption = ">>"
Height = 375
Left = 3000
TabIndex = 10
Top = 2760
Width = 615
End
Begin VB.CommandButton cmdPrevious
Caption = "<<"
Height = 375
Left = 1200
TabIndex = 12
Top = 2760
Width = 615
End
Begin VB.ComboBox cmbField
Height = 315
Left = 2040
Style = 2 'Dropdown List
TabIndex = 3
Top = 1200
Width = 2535
End
Begin VB.CommandButton cmdNew
Caption = "&New"
Height = 375
Left = 120
TabIndex = 13
Top = 2760
Width = 975
End
Begin VB.CommandButton cmdUpdate
Caption = "&Update"
Height = 375
Left = 3720
TabIndex = 9
Top = 2760
Width = 975
End
Begin VB.CommandButton cmdGoWeb
Caption = "&Go"
Height = 255
Left = 3960
TabIndex = 7
Top = 1920
Width = 615
End
Begin VB.CommandButton cmdPlayMIDI
Caption = "&Play"
Height = 255
Left = 3960
TabIndex = 5
Top = 1560
Width = 615
End
Begin VB.TextBox txtField
DataSource = "vsData1"
Height = 285
Index = 6
Left = 2040
TabIndex = 8
Top = 2280
Width = 2535
End
Begin VB.TextBox txtField
DataSource = "vsData1"
Height = 285
Index = 5
Left = 2040
TabIndex = 6
Top = 1920
Width = 1815
End
Begin VB.TextBox txtField
DataSource = "vsData1"
Height = 285
Index = 4
Left = 2040
TabIndex = 4
Top = 1560
Width = 1815
End
Begin VB.TextBox txtField
DataSource = "vsData1"
Height = 285
Index = 2
Left = 2040
TabIndex = 2
Top = 840
Width = 2535
End
Begin VB.TextBox txtField
DataSource = "vsData1"
Height = 285
Index = 1
Left = 2040
TabIndex = 1
Top = 480
Width = 2535
End
Begin VB.TextBox txtField
DataSource = "vsData1"
Height = 285
Index = 0
Left = 2040
TabIndex = 0
Top = 120
Width = 2535
End
Begin VB.Label Label7
Caption = "Date Entered"
Height = 255
Left = 120
TabIndex = 20
Top = 2280
Width = 1695
End
Begin VB.Label Label6
Caption = "Web site"
Height = 255
Left = 120
TabIndex = 19
Top = 1920
Width = 1695
End
Begin VB.Label Label5
Caption = "MIDI file"
Height = 255
Left = 120
TabIndex = 18
Top = 1560
Width = 1695
End
Begin VB.Label Label4
Caption = "Category"
Height = 255
Left = 120
TabIndex = 17
Top = 1200
Width = 1695
End
Begin VB.Label Label3
Caption = "Made famous by"
Height = 255
Left = 120
TabIndex = 16
Top = 840
Width = 1695
End
Begin VB.Label Label2
Caption = "Composer"
Height = 255
Left = 120
TabIndex = 15
Top = 480
Width = 1695
End
Begin VB.Label Label1
Caption = "Title"
Height = 255
Left = 120
TabIndex = 14
Top = 120
Width = 1695
End
Begin VSDATALib.VsData VsData1
Left = 0
Top = 2640
_Version = 65536
_ExtentX = 847
_ExtentY = 847
_StockProps = 0
End
End
Attribute VB_Name = "MainForm"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False