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

Environment: VB, XML, databases

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!



Comments

  • Work is much but easy

    Posted by Legacy on 01/31/2004 12:00am

    Originally posted by: Jino Thomas

    It is simple yet elegant. But it takestoo much coding. It will be better if you had a smaller version with abilityto manipulate databases

    Reply
  • Lot of work..

    Posted by Legacy on 01/30/2004 12:00am

    Originally posted by: Terry Sansom

    Cool!
    For SQL server this process seems error prome.
    I'd set up file watcher to watch for SQL Scripts..
    then call ISQL with the script. Conveting a SQL script to XML is work and error prone...
    or use a DTS Package - this is a SQL server solution only..

    Any thoughts?

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • With JRebel, developers get to see their code changes immediately, fine-tune their code with incremental changes, debug, explore and deploy their code with ease (both locally and remotely), and ultimately spend more time coding instead of waiting for the dreaded application redeploy to finish. Every time a developer tests a code change it takes minutes to build and deploy the application. JRebel keeps the app server running at all times, so testing is instantaneous and interactive.

  • Confused by all the agile advice? Relax! With the Agile for Dummies eBook by your side you'll learn the fundamentals of agile and how to increase the productivity of your software teams while enabling them to produce higher-quality solutions that better fulfill customer needs much faster.

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds