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 Name="Transacation">
<Command>alter table TrendViewCache add
         ControllerTrend Bit</Command>
- <CopyData>
- <Database Name="Config">

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
If xmldoc.Load(App.Path & "\Script.XML") Then
lblVersion = "Version: " &
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)
call_DbChange UCase(XMLNode3.Attributes.Item(0).nodeValue),
call_CopyData UCase(XMLNode3.Attributes.Item(0).nodeValue),
End Select
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
End Select
rstConn.Execute strCommand
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
                            adOpenForwardOnly, adLockReadOnly
                            adOpenDynamic, adLockOptimistic
                            adOpenForwardOnly, adLockReadOnly
                            adOpenDynamic, adLockOptimistic
End Select

Do While rstSourceRst.EOF = False
For i = 0 To rstSourceRst.Fields.Count - 1
rstTargetRst.Fields(i) = rstSourceRst.Fields(i)

End Sub

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


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.


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!


  • 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

  • Lot of work..

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

    Originally posted by: Terry Sansom

    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?

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

Top White Papers and Webcasts

  • Complex hybrid environments can make it difficult to track interdependencies, increasing the risk of disrupting critical business services. In this white paper by EMA, you'll learn how application discovery and dependency mapping can help you: Meet granular targets for availability, cost, and time-to-revenue for cloud services. Accelerate mean time to repair (MTTR) while communicating better with stakeholders. Manage even the most complex hybrid environments more efficiently and effectively Understand the …

  • Rocket Mobile® for IBM i is an enterprise mobile application development and deployment platform for customers who rely on the IBM i system. Rocket Mobile for IBM i enables customers to leave proven applications in-place and rapidly repurpose them into new managed and secure mobile applications. Fast, easy creation of mobile, web, and hybrid mobile applications that deploy to any iOS, Android, or Windows mobile phone or tablet Built-in integration for seamless repurposing of existing IBM i applications …

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date