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?


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.

Replies are listed 'Best First'.
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