x404.co.uk
http://www.x404.co.uk/forum/

Transfer of data between two databases
http://www.x404.co.uk/forum/viewtopic.php?f=4&t=12392		 Page 1 of 1
Author:  JJW009 [ Fri Feb 04, 2011 9:10 pm ]
Post subject:  Transfer of data between two databases
Not sure which board to put this on, but my end is Windows so... I can think of various ways to do this job, but I'm wondering if there's a "standard" method to achieve it? The scenario is this:

A client has a PHP / mySQL web server, and new data is entered into the database. The data is very simple, just two fields being a 4 digit pin number and a phone number.

Our Windows server has a Microsoft SQL database, and a corresponding table needs to be updated with this same information. The flow of data is strictly one way. They will not allow us to query their database directly, even over a secure VPN. Any programming will be done in VB.net

So far, the simplest method we can think of is to use FTP to upload / download a CSV or XML file which is then polled on a regular basis. However, this polling is horribly inefficient and will inevitably lead to our server always being out of date.

I think this is a case of my knowing a little about a lot of subjects, and doing things the long way around because I don't know any better. This must be the kind of thing people do all the time, so I'm guessing there's a much tidier method. I'm thinking I should just "listen" on a port, and get them to send the data when it's created - but how to do that from php?

Suggestions? Thanks :)
Author:  tombolt [ Fri Feb 04, 2011 9:37 pm ]
Post subject:  Transfer of data between two databases
Like you, I'm not fully versed, but I'd be thinking along the lines of email. How that would be achieved I have no idea. Easy enough to send the email, but how it would be picked up, I don't know. Some sort of script at the receiver's end.

Daft really, as that would be less secure, but I've often found that IT bods don't think about the overall picture, just horrified that someone could have any sort of access to their precious hardware. It's the data that's more important.
Author:  tombolt [ Fri Feb 04, 2011 11:33 pm ]
Post subject:  Transfer of data between two databases
In fact, my php knowledge isn't that great either, so to break it down, I think you need to push the data from their server to yours at the point of entry. How you would achieve that, I don't know. I said email as I know how to do that, but it's more than likely to be able to do it in other ways. Someone who knows their php would be able to help. If what I suggest is possible, I'm sure google will be able to help.
Author:  Fogmeister [ Sat Feb 05, 2011 1:17 am ]
Post subject:  Transfer of data between two databases
The standard of data transfer is normally XML.

The only other way round it would be for them to provide some sort of web service that you can POST data to and it then writes to their db. However, even doing that you will more than likely be posting XML.

How often does this data change? If not very often then you could have a trigger on the relevant fields/tables that triggers when they are changed. This will then fire off the new data to the web service provided by the other side in order to update their db. Not sure how triggers work in msql though.

Hth.
Author:  Fogmeister [ Sat Feb 05, 2011 1:22 am ]
Post subject:  Transfer of data between two databases
Ok, just realised I got the data transfer the wrong way round.

I don't know enough to do this in php but check out wsa and wsdl with php on google. Wsa is a web service adapter that has access to your database and can be accessed by the other company. This then allows them to run the script with the given params (the updated info) and update your db.

Can't be anymore specific as I only know how to do this with our setup at work and it doesn't use php vb.net or SQL. Sorry.
Author:  JJW009 [ Sat Feb 05, 2011 12:14 pm ]
Post subject:  Re: Transfer of data between two databases
Thanks for the pointers Foggy, I'll be Googling a while on this I think :lol:
Author:  big_D [ Sat Feb 05, 2011 2:47 pm ]
Post subject:  Re: Transfer of data between two databases
Can't you use the mysql export / backup function? You ssh into the server, run the export and pipe it to a file. That formats the data in SQL statements to load it back into another database - it can also put headers in to drop the existing tables and completely rebuild them and their indexes, before inserting the date, you can leave that out / delete it from the file before running it on the SQL Server machine.

Look at "mysqldump" in the MySQL documentation (and mysqlimport for loading it back into MySQL). We used to use that for copying from our live shop to test and development platforms.
Author:  JJW009 [ Sun Feb 06, 2011 2:30 pm ]
Post subject:  Re: Transfer of data between two databases
The thing is, it's an actively changing database. It could update 100 times a day, so I'm trying to find a method that updates immediately rather than periodically.
Author:  Fogmeister [ Mon Feb 07, 2011 9:48 am ]
Post subject:  Re: Transfer of data between two databases
Just thought I'd provide some links as it's a bit of a minefield when I search in Google...

http://www.ferdychristant.com/blog/articles/DOMM-6J2QFF
http://www.phpclasses.org/package/3509- ... -code.html
http://phpwebservices.blogspot.com/2008 ... h-php.html
http://www.herongyang.com/Web-Services/ ... rWsdl.html

Some of these will help.

You should be able to find hundreds of equivalent docs but these all look like they would help.
Author:  didgeman [ Mon Feb 14, 2011 11:13 am ]
Post subject:  Re: Transfer of data between two databases
How is the first db updated? Can your db be updated at the same time? ie if it is a php update, the script could also send an xml stream to your system?

You could also have a script running on your site which POSTs the UPDATE to your sql server- ie take the passed variables in the url and use them.

You would need to secure the web based script in some way to ensure it cant be accessed to dodgy variable but that should be fairly strait forward ..

Didge
Author:  JJW009 [ Mon Feb 14, 2011 1:08 pm ]
Post subject:  Re: Transfer of data between two databases
didgeman wrote:
How is the first db updated? Can your db be updated at the same time? ie if it is a php update, the script could also send an xml stream to your system?

You could also have a script running on your site which POSTs the UPDATE to your sql server- ie take the passed variables in the url and use them.

You would need to secure the web based script in some way to ensure it cant be accessed to dodgy variable but that should be fairly strait forward ..

Didge

Thanks Didge.

In my musings, I had thought of this. I had a cunning plan which sounded like it should work great. However, although it would be almost trivial to get them to send the data to us as it's entered into their web portal, I'm told that their web portal can not be modified in any way because it serves multiple clients. I said "but you could just . . . " and wasn't allowed to finish the sentence :lol:

So annoyingly, it looks like the database is going to have to be queried daily at their end because it's the only access they're allowed. We'll just have to deal with the data a day behind, and right now it looks like FTP has been decided on so it's out of my hands. I'll lock down the FTP to a single IP address and obviously check the data properly before using it.
Author:  Fogmeister [ Mon Feb 14, 2011 1:38 pm ]
Post subject:  Re: Transfer of data between two databases
JJW009 wrote:
In my musings, I had thought of this. I had a cunning plan which sounded like it should work great. However, although it would be almost trivial to get them to send the data to us as it's entered into their web portal, I'm told that their web portal can not be modified in any way because it serves multiple clients. I said "but you could just . . . " and wasn't allowed to finish the sentence :lol:

So annoyingly, it looks like the database is going to have to be queried daily at their end because it's the only access they're allowed. We'll just have to deal with the data a day behind, and right now it looks like FTP has been decided on so it's out of my hands. I'll lock down the FTP to a single IP address and obviously check the data properly before using it.

Surely they could just add a program to the database (i.e. nothing to do with their web portal).

Every time the chosen records/fields are updated it sends them your way.

This is standard stuff that most of the companies we deal with do.

Oh well, you tried.
Author:  didgeman [ Mon Feb 14, 2011 2:21 pm ]
Post subject:  Re: Transfer of data between two databases
ah well. sounds like your hands are tied.

The only other suggestion i can think of is to scrape their portal every 30 minutes and grab changes that way. (Presuming the info is available that way!) Would add to the traffic a bit though.

Didge.
Page 1 of 1 All times are UTC
Powered by phpBB® Forum Software © phpBB Group
https://www.phpbb.com/