punkish has asked for the wisdom of the Perl Monks concerning the following question:

1. I have server A running Oracle (most likely on Solaris, but irrelevant for now), and a database I know all the details of.

2. Another server B is running a proprietary database. I don't know its details, but I have the XML Schema to transform A to B.

3. At times I have to take a certain subset of the database from A and update B with it. At other times I have to do just the reverse. This process has to be done both periodically (say, at 2 in the morning) as well as on demand (more on that later).

4. So, here is what I think I have to do –

5. If everything is 4 is copacetic, setup a cron job to do the above at a set time daily.

6. Now the “on demand” part. There are clients running off of Server A. At times they make changes to the data on A. When they do that, steps in 4 should be run on demand, typically by pressing a button on the UI. Here’s the rub – the clients are running in Windows .NET environment. Actually, a few more details about this part – clients are actually running a web-based as well as a desktop application exclusively on Windows boxes. This web/desktop application is hitting a server application that is running in the .NET environment, and querying the Oracle database for its data.

The above was being done by a colleague completely in .NET environment. For certain reasons he was called away on another task, and I was brought in. I know nothing about .NET nor have the desire to learn. But I eagerly suggested our beloved and capable language. Although things are not 100% concrete, methinks folks in-charge have agreed to let me use Perl. I have even talked to the client’s IT person and he is cool with it (much to my amazement – it is incredulous how little so many IT folks usually know about Perl and are willing to accept it in their environment). Bottomline – I am pretty certain I can do all of the above in Perl, and probably do it more elegantly than in .NET.

I have to do all the development on a Windows box. So, I installed DBD::Oracle and can do 4.1. above, no problem. I have also fooled around with various XML modules including DBIx::XML_RDB, but nothing serious yet.

Update: The actual box running the final application may or may not be a Windows box. For now, let's assume that whatever I create has to run on any box -- Windows, Solaris, Linux, whatever (although, most likely Windows with ActiveState).

I am seeking general as well as specific advice from you sages. Answers to the following would help –

I have been diligently reading Kip Hampton’s columns, and tinkering with various XML modules. Needless to say, as I dive more into the above task, I will come back for more specific advice.

Many thanks.

;) Uninformed opinion of a novice – XML is a royal pain. ;-)

Replies are listed 'Best First'.
Re: syncing disparate DBs via XML
by 3dbc (Monk) on Dec 19, 2003 at 15:18 UTC
    Greetings punkish,

    Part 6: the “on demand” part... the evolution of technology has unfolded into a plethora of technologies and programming languages to solve the problems we face. However, all too often these technologies must be bridged in order to achieve a higher purpose previously unattainable. That is were PERL comes in for me, an ambitious developing JAPH. I must caution your use of user initialization of the on demand part: I quote you "At times they make changes to the data on A. When they do that, steps in 4 should be run on demand, typically by pressing a button on the UI“ This may be a perfectly effective way of implementing this transference of data between technologies but, my experience is to never trust the end user. I would recommend automating the process as much as possible. You could read from an etime (edited time) of the record you could programmatically keep track of all the etimes in you master db you will know when to write to the slave db. Task Schedule your program to scan etimes every 10min, correlate and reallocate modified data. This would ensure that no data will ever be lost and the other db always has the data it needs. As far as the XML modules I am sure there are a plethora of them just make sure you scrub the web for additional repositories that don't come standard with the ActiveState Perl build. In these alternate repositories like Jenda's or Dave Roth's repositories you will find many win32 and possibly XML modules not available with the standard activestate's repository.

    Hope this Helps!

    Happy Hacking, 3dbc
[reply]

Back to Seekers of Perl Wisdom