Need to read from a web application into SQL Server 2005 - how to choose between MySql and xml?

Question

Need to read from a web application into SQL Server 2005 - how to choose between MySql and xml?

Louise Boulton

SSC Veteran

Points: 231
More actions
February 8, 2010 at 12:52 pm

#210708

Hi,
A freelance web developer is writing an online job application for my company's website. I need to read submitted forms into a SQL Server 2005 db and I need to do this quickly as possible because there is a tight deadline. I can see that submitted forms can be saved as xml files which SQL Server could then read, or that SQL Server could read directly from the MySQL db which the web developer is creating. I need to preserve characters such as e with an accent, o with an umlaut etc. If anyone has any advice on pros and cons of either approach I would be grateful.
Cheers,
Louise

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

dmoldovan SSCertifiable Points: 6334 More actions · Answer 1

I'm not sure that I understand your environment (for example I see that there are a MySQL database and a SQL Server database involved (?)) and your requirements. Can you be more specific?

Louise Boulton SSC Veteran Points: 231 More actions · Answer 2

OK I'll try again. We have a job application form on our website, the back end of which is a MySQL database. After an applicant has completed the form, I want to be able to copy his/her entries (combination of free text and values from drop-down lists and checkboxes) into a SQL Server 2005 db. It seems to me we can either:

1. save an applicant's entries into an xml file and then import from that file into our SQL Server 2005 db; or

2. use SQL Server 2005 to read from the MySQL db directly.

What are the pros and cons of each? Which is likely to be fastest in development time? A consideration is that special characters e.g. e with an accent need to be preserved as we get applications from people with these characters in their names.

Thanks for any advice.

Louise

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

Instead of two steps in Asp.net you could just read the data in a dataset and insert into MySQL and SQL Server.

Kind regards,
Gift Peddie

nigel. SSChampion Points: 11636 More actions · Answer 4

A linked server maybe? See here

Nigel
How to post questions

P Jones SSChampion Points: 12361 More actions · Answer 5

Just remember the security issues of having personal data stored - can you secure the mySQL database suitably?

We decided against developing an external recruitment online application for this very reason.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 6

P Jones (2/10/2010)
Just remember the security issues of having personal data stored - can you secure the mySQL database suitably?
We decided against developing an external recruitment online application for this very reason.

In Asp.net the membership database which comes in all RDBMS data can be encrypted as needed.

Kind regards,
Gift Peddie

Louise Boulton SSC Veteran Points: 231 More actions · Answer 7

Hi,

Yes, that is a consideration. We're discussing with our webhost and designer. Also I'm hoping that if I connect to the MySQL db from a server in our corporate lan, I can remove an applicant's data from our webserver once I know it's been copied successfully. But correct me if that assumption is wrong 🙂

Thanks for the advice re connecting directly rather than using xml. I found this excellent guide on how to accomplish this. But I think our firewall is blocking port 3306. Do you happen to know what services need to run on 3306 to allow a MySQL connection to work? If I can specify services (as well as obviously source of requests) I may have more success with our firewall person than if I can't. This is obviously also part of the security consideration.

Cheers,

Louise