by Ross Mack - GUI Computing
...a filthy little schema of mine for comparing databases quickly and simply.
Distributing applications can be a real trial. You spend a heap of time working on the code so that it works and then you need to write more code just to install it. Fortunately tools like WISE make the creation of installation scripts very easy. However, when writing complicated installs, particularly if they upgrade existing applications can still require a little real coding.
One situation I ran into just recently (and it's really not uncommon) was a need to distribute updated databases to a number of clients. Seems simple, right? Yes and no. The problem was that there were a few previous versions of the application that had varying database formats. We needed to make sure that we were upgrading only the version that we wanted to. So, how do you get an installation script to do that? Simple answer is that you don't. The solution ended up being that we would install a small VB3 app which would check that the databases were the right version.
Unfortunately databases don't usually carry a version stamp and their last modified date is typically when they were last opened. These days I like to include a table in all my databases that holds just version information, but that's not always an available option when there are existing databases in the field. So I needed a quick way to determine if the structure of two databases are the same from VB3.
The approach I chose to take was to collect all the information about the two databases I could into one place and then compare them. I started out simply by getting the names of all the tables in each database, concatenating them into a string and then comparing them. There are a couple of problems with this approach :
1. It does not take into account the QueryDefs in the database or fields within the TableDefs.
2. If the TableDef names were retrieved in a different order this would indicate a difference which was of no significance.
I then decided that what I needed to do was include the QueryDefs in the database. Unfortunately the only way to do that from VB3 is to use the ListTables() method of the database object which returns a snapshot of TableDef and QueryDef information. VB3 does not expose a QueryDefs collection so I could not query that directly and I could not query the system tables of the database without adjusting permissions first, so that was not an option. So the next step was to iterate through the ListTables snapshot reading the names of the objects into an array of strings. This allowed me to then sort that array ensuring that the order in which they were retrieved was irrelevant. The result of that was a simple function that sorts arrays of strings. Here it is:
Sub SortStrings (sArray() As String) Dim nCount As Integer Dim nStop As Integer Dim bStopNow As Integer ' boolean Dim sTemp As String ' This is a simple bubblesort algorithm. I'm almost embarassed to have used it. nStop = UBound(sArray) - 1 Do bStopNow = True For nCount = LBound(sArray) To nStop If sArray(nCount) > sArray(nCount + 1) Then ' Swap the elements to be the right way around sTemp = sArray(nCount) sArray(nCount) = sArray(nCount + 1) sArray(nCount + 1) = sTemp bStopNow = False End If Next nCount nStop = nStop - 1 If nStop = LBound(sArray) Then ' Have we got to the end ? bStopNow = True End If Loop Until bStopNow End Sub
Now I had got to a point where the QueryDefs in the database were at least being considered and order was being controlled.
OK, so now we need to consider the fields in all the tables. Also, we should consider the structure of those QueryDefs. To handle the TableDefs I simply created a function that when passed a database object and a Table name will return an array of all the field names. The tricky bit was searching through the TableDefs collection to find the right TableDef and dealing with the case of System tables where you may not have permission to retrieve any field names. The routine in question looks very similar to this :
Sub GetFields (db As Database, ByVal sTableName As String, sArray() As String, ByVal bTypes As Integer) Dim nCount As Integer Dim tdf As TableDef On Error GoTo GetFields_Err ' First find the tabledef we want For nCount = 0 To db.TableDefs.Count - 1 If db.TableDefs(nCount).Name = sTableName Then Set tdf = db.TableDefs(nCount) End If Next nCount If tdf.Fields.Count > 0 Then ' Dimension the array to the right size ReDim sArray(1 To tdf.Fields.Count) As String ' Loop through all the fields grabbing the data For nCount = 0 To tdf.Fields.Count - 1 ' Get the name sArray(nCount + 1) = tdf.Fields(nCount).Name ' If we are being picky grab the type and size as well If bTypes Then sArray(nCount + 1) = sArray(nCount + 1) & ":" & CStr(tdf.Fields(nCount).Type) sArray(nCount + 1) = sArray(nCount + 1) & ":" & CStr(tdf.Fields(nCount).Size) End If Next nCount Else ReDim sArray(0) As String End If GetFields_End: Exit Sub GetFields_Err: Resume GetFields_End End Sub
You will notice that dependant upon the last parameter this function will also add the Type and Size of each field to the fieldname stored in the array as well. I separate those with colons just so I could sort of see what was going on while I was debugging it and saw no reason to remove them. Having retrieved all the field names (and other field stuff, optionally) I used the same String sorting routine to sort the array so that the order of the fields was irrelevant as well.
But what of QueryDefs that have no fields collection? The only way I could ensure that they were the same was to open the QueryDef object and extract the SQL so that it could be included in the comparison. This would catch different parameters, fields retrieved, tables queried, restrictions and other such differences. As a side benefit it was also pretty simple to do. As you might expect (by now) I wrote a quick routine to return the SQL statement from a specified QueryDef. It's evil twin brother Alfons can be found here :
Function GetQueryDefSQL (db As Database, ByVal sQueryName As String) As String Dim qdf As QueryDef Dim sResult As String On Error GoTo GetQueryDefSQL_Err ' Open a QueryDef object of the requested QueryDef Set qdf = db.OpenQueryDef(sQueryName) ' Retrieve the SQL sResult = qdf.SQL qdf.Close GetQueryDefSQL_End: GetQueryDefSQL = sResult Exit Function GetQueryDefSQL_Err: ' just continue as best we can. Resume GetQueryDefSQL_End End Function
An interesting thing I discovered is that if the QueryDef references system tables that you do not have permission to retrieve the design information for then the OpenQueryDef method will cause an error. In that case the above function will return an empty string, but there is little else that could be done and you can't even call such QueryDefs from VB3 so I don't really see it as much of a problem.
So what we ended up with after all that was for a single database we had an array of strings representing the names of all the TableDefs and QueryDefs with their attribute information appended (either field info or SQL). Then we sort that array and concatenate it all into one string. To do the concatenation I used Join, one of the functions from last month's article about strings. Once this has been done for each database you have two strings which represent a database each and that you can directly compare simply using StrComp. If StrComp indicates that the strings are the same then obviously the databases have the same structure, otherwise the structure must differ in some way. Unfortunately this does not tell us in what ways the databases differ (maybe by next issue) but the overhead required to track that information was comparatively large when really all I needed to know is if they are the same or not. Because I implemented the library in a few stages of increasing complexity it became easy to simply add a parameter to specify how in-depth the comparison should be. The comparison can be based upon just TableDef and QueryDef names or can include fields, or both fields and field attributes. QueryDef SQL is included for both the last two comparison options. It's sort of lake case sensitivity for String comparison when you think about itů
The main function looks like this.
Function DBComp (ByVal dbAName As String, ByVal dbBName As String, ByVal nCompType As Integer) As Integer ' boolean Dim sNames() As String Dim db As Database Dim sA As String Dim sB As String Dim bResult As Integer ' Open the first database Set db = OpenDatabase(dbAName, True) ' Extract all the info from it (specifying type) GetNamesList db, sNames(), nCompType ' Sort the info so we have comparable reference ' If we didn't sort here we could find differences due only to order of data retrieved SortStrings sNames() ' Shut down the database db.Close ' Whack that all into one string sA = UCase$(Join(sNames(), "|")) ' This stuff is same as above but for next db Set db = OpenDatabase(dbBName, True) GetNamesList db, sNames(), nCompType SortStrings sNames() db.Close sB = UCase$(Join(sNames(), "|")) ' Now we do the actual compare. Because we stuffed it ' all into a string we can just use string comparison ' to compare the two databases. If StrComp(sA, sB, 1) = 0 Then bResult = True Else bResult = False End If DBComp = bResult End Function
Note how the breaking up the code into functions like the ones above makes the code comparatively brief at each level and improves readability (I read that in a textbook somewhere).
You might call the DBComp function something like this:
If DbComp(sOldDBPath, sNewDBPath, 1) then ProcessDBs sOldDBPath, sNewDBPath Else Msgbox "These databases are different. I can't work under these conditions !", 48, "Outta Here" End If
A Note about VB Versions.
Keep in mind that much of the code presented in this article is specific to VB3, ListTables for example does not work in later versions of VB, but they expose QueryDef collections so it is not needed. However, it is in many ways the lowest common denominator, if you can get it to work in VB3 it's typically simple to port to VB4 or 5. That's one of the reasons I tend to write libraries like this in VB3 to start with. It also tends to make me rely less on wizz-bang features available only in later versions, and can often be ported easily not only to later versions of VB, but also to VBA, VBScript, and other VB like implementations (the Basic Macro language in Reflections, comes to mind). You can get a copy of the whole library and a simple demonstration app in a downloadable ZIP file.