Hello All,

I'm a complete Unix newbie, and I have been asked to provide a solution for the following opportunity:

Currently there is an Oracle database residing on a Unix box that I need to connect to on a regular basis from another Unix box. Upon connecting I need to hit multiple tables and query data which will then need to be written to a MySQL database.

I've thought of the following logic:

Have an Oracle stored Procedure run nightly on the one Server to grab the data I need and throw the records into a CSV file.

Then ftp from the other server nightly and grab that file.

Then use an API to grab each record from the file and write it to the MySQL database.

Does this sound like an appropriate solution?

I would really appreciate alternate solutions as well, as I have to present a list of suitable and functional ideas.

Thank you very much in advance for all the help,
Andrew

If the servers are on same network. All
you really need is the API part.

If the servers are on remote locations and
a dial-up connection is needed, and you
must use ftp. Then your solution of putting
the data on a CSV file sounds good.
You will then have to parse the CSV file and
load it into MySQL. There is a good script
in the Perl Cook book on parsing CSV files.

Thanks Photon,

They are on seperate servers, thanks for the confirmation of my idea!

Anyone else have any comments or suggestions?

Andrew

Have you thought about using XML. XML is the ideal format.
There are plenty of API’s available to parse the format and it
is more reliable.

For instance, in a CSV file

a, comment, with, commas, is, a, problem

or “ “ is this empty?

Also there is an issue with the disordering of data.

I could probably go on and on, but will spare you.

XML is worth learning, because it solves all these problems and more.

Just a comment on the comma delimited issue, i am no PERL or XLM person, but if you delimit with | instead of commas, your , and " issues go away and very rarely do people store | (pipes) in their data fields.

i just finished migrating 200GB of company wide data and ran across 3 homegrown tables (out of over 4000) that actaully used | in a record, and oddly enough it was a table describing delimiters!!

hope this helps.

I do not want to beat a dead horse, but bars
and commas are passé. Unless you want to
deal with excel and some other application
that gives you no choice to do so, but Hey,
eventually even excel is going to go with XML.

The point is; there is no dynamic enough parser
that can catch every error, unless you have an
agreed standard. Agreed?

Hi All,

thank you for taking the time to respond to my post.

As it stands, it appears I am going to go with a driect data grab from the Oracle DB using a PHP script then a driect write to the the MySQL DB using PHP as well.

Thanks again,
Andrew