Email	Print Reprint
	add to:
	Del.icio.us Digg Google Furl	Slashdot Y! MyWeb Blink

Michiel, a programmer who lives in Rotterdam, The Netherlands, can be contacted at mdbruijn@inter.nl.net.

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.

Implementing a Lightweight Data Store

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.

A VSData Application

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.

Conclusion

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

For More Information

VideoSoft
5900-T Hollis St.
Emeryville, CA 94608
510-595-2400
http://www.videosoft.com/

Listing One

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

Listing Two

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

Back to Article

1 | 2 | 3 | 4 | 5 | 6 Next Page

RELATED ARTICLES ZigBee Alliance, DLMS User Assoc Collaborate on Metering Data Compatibility Embarcadero Takes DBMS Admin Into the Cloud McObject Updates eXtremeDB Real-Time Database System IBM Offers Free Public Cloud Beta For Software Development Zend, Oracle Hook Up for Enterprise-Class Linux and PHP		TOP 5 ARTICLES No Top Articles.