Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

table creation from DBI handle info

by water (Chaplain)
on Mar 31, 2006 at 00:57 UTC ( #540335=perlquestion: print w/ replies, xml ) Need Help??
water has asked for the wisdom of the Perl Monks concerning the following question:

Hi --

This sounds like a problem others must have solved, so I'm looking for any pointers to modules or scripts.

I have a DBI handle to a funky datasource via ODBC. The funky datasource is quickbooks thru a purchased QB odbc driver.

I want to generate a CREATE TABLE statement for a mysql database using info from the DBI handle -- the column names, the types, the primary key(s), etc.

The idea is to mirror a QB table into a mysql database, sorta like this:

(1) DROP mysql table # because QB version may have changed (2) CREATE mysql table, using info from DBI odbc handle (3) SELECT * from QB table, creating a text file (4) LOAD DATA INFILE from textfile to populate mysql table
Any pointers toward existing code #2, before rolling my own?

Thanks!

water water everywhere

PS If you're asking 'why', it is because client needs to integrate QB billing data with telephony data from other systems, and those other systems pour log data into a mysql db.

Comment on table creation from DBI handle info
Download Code
Re: table creation from DBI handle info
by jZed (Prior) on Mar 31, 2006 at 01:45 UTC
    SQL::Translator will both find the ODBC table info and help to translate it into a CREATE statement suitable for MySQL. I think ODBC will let you make the dump to CSV directly, but if not, DBD::CSV will let you do that. You'll need at least two database handles in the script, one for ODBC and one for MySQL and a third for CSV if ODBC won't make the CSV table directly. You can also skip the CSV and just loop through the ODBC select doing a MySQL insert for each row. If you do use DBD::CSV, a single statement should createt the CSV file, something along the lines of "CREATE TABLE csv_table AS SELECT * FROM mysql_table".
      Hurrah. Thanks. I love this place.
Re: table creation from DBI handle info
by eric256 (Parson) on Mar 31, 2006 at 01:46 UTC

    Hey,I was looking for similar solutions for syncing data between an Oracle and a MySQL database. In the end I just decided that deleting all the rows and then copying over would have to do. I keep an eye on the tables and keep the columns in sync by hand. I don't know if that is helpfull or not, just my experience in similar matters. ;)


    ___________
    Eric Hodges

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://540335]
Approved by monkfan
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (6)
As of 2014-07-24 07:37 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (158 votes), past polls