Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

table creation from DBI handle info

by water (Deacon)
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?


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

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (4)
As of 2020-09-21 12:45 GMT
Find Nodes?
    Voting Booth?
    If at first I donít succeed, I Ö

    Results (125 votes). Check out past polls.