Changing Databases Using XML

,

Introduction and Scope

As a database developer , you may have being fed

up with the never ending customer changes. Actually this is not only relevant to

database developers but for all of the other developers. But for database developers

has to

meet the customer requirements they may need to change the databases. But if

these changes are done by manually you must list out all the changes to be done

and do it perfectly. It is not sound methodical isn't it? So we have to find out

an elegant way of doing it.

What are the changes?

What are the changes that you may need to do for the

databases? They can be creating a new table, dropping an existing the table,

altering an existing table, deleting existing data of a table, inserting some data.

This all the changes need not to be done to only for one database. Their can be

more than one database depending on the customer's needs. For example I have a

done a project which 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 three times. If we use a simple VB application every time we need to change

application so that it does the necessary changes to the relevant database.

Problem will become more worse if you are having several customers, and they are

asking several changes. Then you will have to keep more than one EXE for this

simple and straight forward task. We, our self  have made our life uneasy.

Haven't we?

If we can write an exe so that it reads some other source

which users can change and do the changes it will be handy. We just need to

change the source file rather than the VB application. Text file would do the job

for us. But XML file be the most suitable one. This gives you another advantage

over the VB application. That is XML file size will be just over 2-3 KB which you can

send it through an e-mail.

XML File

Let's first create the XML file.

Figure 1

Figure 1 is the listing of the XML file. we may call it

SCRIPT.XML.

There are are several things in the XML file which I have not

discussed earlier. First of them is the Version. This is for the reference.

Version would be enough to identify the changes that has done to the

database. As I discussed earlier commands are separated for the databases.

Here I have used two databases which are Config and Transaction. As you can see

there is a another XML tag called CopyData. Sometimes you need to store some

information which may to be entered by users. For an example if you want to

store country list in a table (of course you can get the country list from the

windows registry) which you haven't send it to customers earlier. Still you can

use INSERT command and do it. But better way is sending 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

Microsoft Activex Data Objects (ADO) 2.7 library.

Figure 2

Above function will load the XML and read it's content. From the

XMLNode2.baseName it is identify whether it has to change the database or it has

to copy the data.  Depending on that it calls the two functions.

                                                           

Figure 3

Figure 3 shows how it changes the database which is self

introductionery. TARGET_CONFIG_CONNCETION_STRING, TARGET_TRANS_CONNCETION_STRING

are connection strings which you can build depending on your database.

Figure 4

Figure 4 shows how to copy the template data to the customers

table.

Improvements

This can be improved to keep the updated version in the

customers computer say in windows registry so that when you are running the next

upgrade you can check the existing version. Better if you can keep the

date as well there.

Conclusion

My idea is to introduces a simple way to the database

upgrades with using the power of XML. You may have great ideas than this. Fell

free to share your experience and creative ideas with us and Let's make database

upgrade an normal work from what it looks like a mess!

Rate

Share

Share

Rate