Changing Databases at the Customer's Location Using XML and VB

Introduction and Scope

As a database developer, you may be fed up with never-ending customer changes. Actually, this is not only relevant to database developers but also for all other developers. But, because database developers have to meet customer requirements, they may need to change the databases. If these changes are done manually, you must list all the changes to be done and do it perfectly. It is not a sound method, is it? So, we have to find an elegant way of doing it.

What Are the Changes?

What are the changes that you may need to make for the databases? They can be creating a new table, dropping an existing table, altering an existing table, deleting the existing data of a table, or inserting some data. All the changes need not be done only for one database. There can be more than one database, depending on the customer's needs. For example, I have a done a project that contains configuration data in one database and day-to-day transactions are saved in some other database.

How Can We Do This?

We can simply write a VB application so that it can change the databases. These changes may not be done at once. There can be changes for two or three times. If we use a simple VB application, every time we need to change application it does the necessary changes to the relevant database. The problem will become worse if you have several customers and they are asking for several changes. Then, you will have to keep more than one EXE for this simple and straightforward task. We have made our life uneasy, haven't we?

If we can write an EXE so that it reads some other source that users can change, and then do the changes, it will be handy. We just need to change the source file rather than the VB application. A text file would do the job for us. But, an XML file would be the more suitable one. This gives you another advantage over the VB application. That is that the XML file size will be just over 2-3 Kb, which you can send through an e-mail.

XML File

First, let's create the XML file.

Listing 1

- <Change Ver="1.20">
- <DBChanges>
- <Database Name="Config">
<Command>Drop Table Schdules</Command>
<Command>CREATE TABLE Devices ([ID] int ,[Tire] int ,[Outputs] int ,
                               [Inputs] int ,[TrendSamplesMax] int ,
                               [Arrays] int)</Command>
</Database>
- <Database Name="Transacation">
<Command>alter table TrendViewCache add
         ControllerTrend Bit</Command>
</Database>
</DBChanges>
- <CopyData>
- <Database Name="Config">
<Table>Devices</Table>
</Database>
</CopyData>
</Change>

Listing 1 is the listing of the XML file. We may call it SCRIPT.XML.

There are are several things that I have not discussed earlier in the XML file. The first of them is the Version. This is for reference. The Version would be enough to identify the changes that have been done to the database. As I discussed earlier, commands are separated for the databases. Here, I have used two databases: Config and Transaction. As you can see, there is another XML tag, called CopyData. Sometimes, you need to store some information that users may need to enter. For an example, if you want to store a country list in a table (of course, you can get the country list from the Windows Registry) that you haven't sent to customers earlier. Still, you can use the INSERT command and do it. But, a better way is to send the template data in a MSAccess table and copy those data with the aid of a VB application.

VB Application

For this VB application, I have used Microsoft XML, v 3.0 and the Microsoft Activex Data Objects (ADO) 2.7 library.

Listing 2

Private Sub cmdContinue_Click()
Dim xmldoc As New MSXML2.DOMDocument30
Dim XMLNode1 As IXMLDOMNode
Dim XMLNode2 As IXMLDOMNode
Dim XMLNode3 As IXMLDOMNode
Dim XMLNode4 As IXMLDOMNode
If xmldoc.Load(App.Path & "\Script.XML") Then
lblVersion = "Version: " &
             xmldoc.childNodes(0).Attributes(0).nodeValue
For Each XMLNode1 In xmldoc.childNodes
For Each XMLNode2 In XMLNode1.childNodes
For Each XMLNode3 In XMLNode2.childNodes
For Each XMLNode4 In XMLNode3.childNodes
Select Case UCase(XMLNode2.baseName)
Case "DBCHANGE"
call_DbChange UCase(XMLNode3.Attributes.Item(0).nodeValue),
              XMLNode4.Text
Case "COPYDATA"
call_CopyData UCase(XMLNode3.Attributes.Item(0).nodeValue),
              XMLNode4.Text
End Select
Next
Next
Next
Next
End If

End Sub

This function will load the XML and read its content. From XMLNode2.baseName, it is identified whether it has to change the database or it has to copy the data. Depending on that, it calls the two functions.

Listing 3

Private Sub call_DbChange(strDbName As String, strCommand As String)
Dim rstConn As New ADODB.Connection
Select Case strDbName
Case "CONFIG"
rstConn.ConnectionString = TARGET_CONFIG_CONNCETION_STRING
Case "TRANSACTION"
rstConn.ConnectionString = TARGET_TRANS_CONNCETION_STRING
End Select
rstConn.Open
rstConn.Execute strCommand
rstConn.Close
End Sub

Listing 3 shows how it changes the database, which is self explanatory. TARGET_CONFIG_CONNCETION_STRING and TARGET_TRANS_CONNCETION_STRING are connection strings that you can build, depending on your database.

Listing 4

Private Sub call_CopyData(strDbName As String, strTable As String)

Dim rstSourceRst As New ADODB.Recordset
Dim rstTargetRst As New ADODB.Recordset
Dim i As Integer
Dim f As Field

Select Case strDbName
Case "CONFIG"
rstSourceRst.Open strTable, SOURCE_TRANS_CONNCETION_STRING,
                            adOpenForwardOnly, adLockReadOnly
rstTargetRst.Open strTable, TARGET_TRANS_CONNCETION_STRING,
                            adOpenDynamic, adLockOptimistic
Case "TRANSACTION"
rstSourceRst.Open strTable, SOURCE_TRANS_CONNCETION_STRING,
                            adOpenForwardOnly, adLockReadOnly
rstTargetRst.Open strTable, TARGET_TRANS_CONNCETION_STRING,
                            adOpenDynamic, adLockOptimistic
End Select

Do While rstSourceRst.EOF = False
rstTargetRst.AddNew
For i = 0 To rstSourceRst.Fields.Count - 1
rstTargetRst.Fields(i) = rstSourceRst.Fields(i)
Next
rstTargetRst.Update
rstSourceRst.MoveNext
Loop

rstSourceRst.Close
rstTargetRst.Close
End Sub

Listing 4 shows how to copy the template data to the customer's table.

Improvements

This can be improved to keep the updated version in the customer's computer—say, in the Windows Registry—so that when you are running the next upgrade, you can check the existing version. It would be better if you can keep the date there, as well.

Conclusion

My idea introduces a simple way to provide database upgrades using the power of XML. You may have greater ideas than this. Feel free to share your experience and creative ideas with us. Let's make database upgrading a normal work process from what looks like a mess!