Transferring Data 'Tween Databases

Sysbotz writes "A common request our company gets is how to get data from Access, Paradox, or some other database format and transfer it to a MySQL database. Well we have written a article on how to do this. W accomplish this task by writing a PHP script to read a database file through ODBC and then to construct a SQL file of the data that can then be read into MySQL. I think some slashdotters would like this."

Comments

Been there..

[JohnFluxx]

I found a script on freshmeat that could read an access file and produce the appropriate sql commands.

This was a few years back, and it had some problems with special characters - like spaces iirc - since access is more leniant.

I kept meaning to write a script to turn the forms it created into glade xml.

Perl DBI?

[merlyn]

The Perl DBI can talk to all of those listed databases and more. It'd be trivial to fetch everything from one database and store it in another, without worrying about local quoting conventions, as long as you use the DBI placeholders.

Re:Standarization.

[noselasd]

Er.. didn't I say protocol ? Yes I did. ODBC is an API. Big diffrence. ODBC loads (transparently) drivers that each know how to actually do the conversation with the database server. This is not the same as a protocol.

How we used the DBI to do this

[fizbin]

In fact just recently I wrote a one-off script here that did essentially what this PHP script does - it takes data out of a local sybase db and reformats it as a bunch of SQL statements. (We don't have direct access to the database into which this needs to be loaded, so there needs to be an intermediate form anyway)

I suppose this _might_ be worth a post on perlmonks, as an example of using the DBI, (and of working around the fact that DBD::Sybase doesn't really implement column_info) but not much more than that.

This code generates an SQL load file for each table that has a column named "DataSrcId" where that column has the value "35". It also substitutes the value 'guy' for any column named 'AudUsrId' and does not include any column named 'AudTmst' in the load output. As I said, it's a one-off hack.

#!perl
use DBI;
use DBD::Sybase;

my($dbh);

sub dumpstatement {
my ($tablename, $statement) = @_;
my $sth;
$sth = $dbh->prepare($statement);
$sth->execute();
while ( my(@row) = $sth->fetchrow_array ) {
my @names = @{$sth->{NAME}};
@row = map { $names[$_] eq 'AudUsrId'?'guy':$row[$_] } (0..$#row);
@row = map { $names[$_] eq 'AudTmst'?qw():$row[$_] } (0..$#row);
@names = grep(! /^AudTmst$/, @names);
print "INSERT $tablename (", join(',',@names), ")\n";
print "VALUES (", join(",",
map {$dbh->quote($row[$_],$sth->{TYPE}->[$_])}
(0..$#row)
), ")\n";
}
print "\n";
}

my($user, $password) = qw[sa confusion];
$dbh = DBI->connect("dbi:Sybase:server=njdscope;database= TEST_ATRB", $user, $password);

my($sth) = $dbh->table_info('%','%','%', '%');

my(@tables);
my($hashr);
while ($hashr = $sth->fetchrow_hashref("NAME_uc")) {
my($ctable) = $hashr->{TABLE_NAME};
push @tables, $ctable;
}
$sth = undef;

foreach my $table (@tables) {
$dbh->{PrintError} = 0;
my $teststatement = $dbh->prepare("SELECT max(DataSrcId) FROM $table WHERE DataSrcId = 35");
$teststatement->execute;
if ($teststatement->err) {next;}

$dbh->{PrintError} = 1;
print "-- for $table \n";
dumpstatement($table, "SELECT * FROM $table WHERE DataSrcId = 35");
}

By the way - slashdot inserts an extra ";" in this code, even though it is NOT there in what I copy/paste in. Go figure.

As simple minded as the mods letting it pass

[Sembiance]

This is totally worthless.
Do mods just let anything with the words 'PHP' and/or 'MySQL' make it on the website?

The article is less than 2 screen pages long, it's not much more than a code dump, and it's totally hardcoded for a specific and individual database table.

It also only covers Windows installations of PHP and and person who knows that they need to move from one database to another, and what PHP is, is smart enough to do what this author wrote.

I don't diss the author on this, it looks as if he is just new to computers and doesn't know any better.
But geez, if this is the crap that we allow on slashdot now, I'm just gonna start submitting articles on 'How cool Google is'

Testing and Whatnot

[Inexile2002]

Despite some of the criticisms above it's nice to see stuff like this. As part of my job I have to occasionally go into companies and review database conversions after the fact to confirm that they did everything correctly.

As obvious as the technique used above is to some /.ers, DB conversions are not always obvious to the people who actually do them IRL. I've seen some of the most horrific improvisations involving a third database as a data warehouse or worse, the process done manually with SQL dumping data into Notepad which is then copy/pasted into new SQL.

The one thing though - testing. Post conversion testing is essential unless you were doing all this for shits and giggles. If you can't show someone through rigorous testing that your conversion worked, no responsible person out there should rely on the new DB. (Assuming they were relying on the old one.)

Moo

[Chacham]

If this is something you do alot, get SQL Server DTS. It does this beautifully, as well as many other tasks.

Octopus

[grugruto]

The Enhydra Octopus project seems to be the right tool to do this and you can specify data transformations in an XML file.

You should check it out, it's open source.

More Evidence that MySQL is a toy

[bwt]

Here's another example from real world use that shows that MySQL is a toy compared to a real database like Oracle. You shouldn't have to write that much code to freaking load data. For industrial strength uses the method given will be horribly slow because it doesn't use bind variables. This results in each INSERT statement being different and having to be parsed separately by the RDBMS. SLOW!

Frankly, even the overhead of having to construct the INSERT sql string is waste. You also don't want to maintain the indexes in the target table for each row update. MySQL doesn't have transactions, so you don't have to worry about commit-frequency, but if your load stops in the middle somewhere, I'm not sure what you do.

Oracle provides a loader utility called sql*loader that eliminates the overhead of the per-row maintenance. It has a mode called "Direct Load" which can bypass trigger processing and directly write binary datablock output. This is the fastest way to load data. Of course bypassing triggers is of no interest to MySQL users because MySQL doesn't have triggers, but if it did you'd have another thing to worry about with loading data into MySQL.

As an alternate to sql*loader, you could use external tables or Oracle Generic Connectivity to create an oracle table whose data was supplied by a flat file or ODBC connection. Then you would type

INSERT INTO target_table
VALUES ( [[field_list]] )
(SELECT [[field_list]] from external_table)

or (faster)

CREATE TABLE target_table AS (SELECT * from external_table)

Both of which would blow away the proposed method speed-wise.

access2sql

[Anonymous Coward]

http://access2sql.sourceforge.net/

Re:Standarization.

[Anonymous Coward]

That's what the Open Database Connectivity Router does ... http://odbcRouter.com/