Is it possible to create a text formatted list from SQL Server which can then be processed by a PHP Cron job?

views:

answers:

+2 Q:

Is it possible to create a text formatted list from SQL Server which can then be processed by a PHP Cron job?

Requirement : Should update MySQL database with that of MS Sql Server updates which reside on Linux and Windows hosting on the same web host 1and1. Accessing each other database from either server is not possible on shared hosting, thanks to 1and1's geniuses..

The solution I chose : I want to output all the active listings in a text formatted Asp.Net page which is processed using a PHP cron job at regular intervals.

Why this weird solution : I am paid next to nothing for implementing this feature (to bridge the gap between MySQL and MS SQL Server).

What do you guys think? How should I proceed? Its been a while since I touched .Net programming. Code samples to achieve this solution is highly appreciated. Thanks folks!

If you set the ContentType of the Response in your ASP.NET page to a CSV(I prefer pipes to commas, but whatever) or just text (as you said) file before doing your Response.Writes, you should be able to use MySQL's LOAD INFILE command to not have to do a whole lot, depending on permissions.

Sounds like you already have a hack/kludge planned that should work, even if it makes you cringe to talk about it. :)

TomR 2010-03-18 18:35:39

Could you elaborate your idea? Are you referring to exporting a pipe delimited file and then processing that file or processing the file directly from the ASP.Net page? I am confused.

ThinkCode 2010-03-18 19:00:19

As I understood your plan, on a schedule, you'd have PHP HTTP-GET an ASP.NET page. I was sort of confirming and expanding on that. In ASP.NET, before you do any Response.Writes (or output of any kind) change your COntentType to text. Then in your PHP file, use MySql's LOAD INFILE command rather than iterating over the lines of the text file - unless you need to modify them as you go. Alternately, yes you *could* just have the ASP.NET page do the work and take the PHP step out of the equation. Let your cron job do the HTTP-GET at the shell, fire and forget. Did that help or no?

TomR 2010-03-21 15:33:45

+1 A:

I think that the answer is connected with the amount of data you are talking about.

If the amount is not huge (I think under 1 million of record at time), if the structure of the databases is the same and if you don't have problems of performances all you need to do is

1- query the SQL Server and extract everything from a table

2- empty the same table on MYSQL

3- insert all the data inside the MYSQL table.

If you have to take care of one of the previous "if", the solution can be different according with the kind of constraint you have.

Giovanni Di Milia 2010-03-18 20:42:47

Thank you so much!This is exactly what I am planning to implement. First I thought I would write incremental updates to an Out.txt file every hour based on a Flag (0/1) rather than emptying MySQL every time. The data will not go anywhere over 500 records!I am stuck with syntax for ASP.Net at this time. I am married to PHP and ASP.Net is no where close to being an acquaintance!

ThinkCode 2010-03-19 14:31:36

ansaurus

tags:

views:

answers:

Is it possible to create a text formatted list from SQL Server which can then be processed by a PHP Cron job?

related questions