Transferring Data 'Tween Databases 51
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."
Standarization. (Score:2, Insightful)
Re:Standarization. (Score:5, Funny)
Or does ODBC not ring any bells?
Re:Standarization. (Score:2, Informative)
Re:Standarization. (Score:2)
Yeah, that would be good. You could standardise around SQL for commands and then an XML syntax for data.
Re:Standarization. (Score:2)
Re:Standarization. (Score:1, Informative)
Is this really worthy of a /. article? (Score:4, Insightful)
It is nice to highlight that you can read lots of different databases using odbc in PHP, but still.
This basic concept is obvious to anyone with familiarity with MySQL. I mean, come on, "pick a language that can read the database in question and use it to dump the data into a format that can be read my MySQL".
This program could have been written in Visual Basic or C# or anything that can read the database you want to convert.
A more interesting PHP program that could have taken *any* two arbitrary odbc databases (MySQL can be accessed through odbc) and dumped table definitions and data from one db to the other.
Re:Is this really worthy of a /. article? (Score:2, Interesting)
No new techniques and code that almost any person with a day of PHP experience would have already written. The only reason I checked it out was that I wanted to see if they figured out how to grab column names or tables names via ODBC. I'd love to be able to grab meta-data about the databases themselves. If this isn't part of the SQL ANSI standard...
Re:Is this really worthy of a /. article? (Score:2)
Try select * from information_schema.columns for a taste.
Re:Is this really worthy of a /. article? (OT) (Score:1)
No. (Score:1)
If this is all it takes to get a dev-article to /., I'll just dump my everyday work here and become the uber-geek ;-).
Re:Is this really worthy of a /. article? (Score:1)
It's a lame braindead extremely ordinary task every near-decent php/db programmer had to do at some point, when he finally realized mysql sucks and he's gotta move everything into postgresql
Clearly, lame publicity for the company in cause, which now turned into bad publicity.
Re:Is this really worthy of a /. article? (Score:1)
In fact, since the autonumbers are generated - duh - automatically, copying between two access databases can be distinctly non-trival (indeed, if you have relations defined AND autonumber fields determining a way to put the d
Why not use PHP as glue? (Score:1)
Why not use a real target database? (Score:1)
Re:Why not use a real target database? (Score:2)
Re:Why not use a real target database? (Score:1)
And yes, my day job involves MySQL and I quite like it, but I'm not deluded enough to think it's the be all and end all of databases.
No.. (Score:1)
Re:Why not use PHP as glue? (Score:2)
Been there.. (Score:2, Informative)
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? (Score:5, Informative)
How we used the DBI to do this (Score:4, Informative)
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(!
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
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.
Bigger project (Score:2)
What I have in mind is mysql, pgsql, oracle 9i, msaccess, db2, firebird, minisql.
Wonder if its possible.
msaccess (Score:2, Interesting)
The projects been dormant for a while, (work makes my head hurt too much for real development)
As simple minded as the mods letting it pass (Score:5, Informative)
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'
Java (Score:3, Interesting)
That is something worth writing an article about. Not just one very specific case.
Re:Java (Score:3, Funny)
Gosh, with the Java/XML combo...I am surprised that there still is such a thang as a database. Dang, if you designed the blasted thang in UML with Java/XML there wouldn't even be a question of reusability...cause you would be in computer nirvana.
PS: I don't code...just read the trade journals and play foosball.
Re:Java (Score:2)
Testing and Whatnot (Score:4, Informative)
As obvious as the technique used above is to some
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 (Score:3, Informative)
Re:Moo (Score:2)
Not to mention that its pretty much free. The SQL Server Tools install is free if you have SQL Server installed anywhere. There's no licensing anyways, and the DTS stuff is redistributable whether you have one or not...
Octopus (Score:4, Informative)
You should check it out, it's open source.
Re:Octopus (Score:1)
On another note, one thing about writing xfer routines is that SQL lacks a hybrid statement that says something like, "if this record is already there, then updated it, else insert a new one". Instead you have to check for each record. It n
More Evidence that MySQL is a toy (Score:5, Informative)
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 typeor (faster)Both of which would blow away the proposed method speed-wise.
Re:More Evidence that MySQL is a toy (Score:2)
Re:More Evidence that MySQL is a toy (Score:2)
Well, mysqlimport or LOAD DATA INFILE seems like it would be a MUCH better MySQL solution than building an INSERT for each row. Why the author of the original article did not use this method is rather perplexing.
However, I don't see how the CREATE TABLE AS SELECT or INSERT AS SELECT could possibly work here unless MySQL has a way to link a table from an external source.
access2sql (Score:1, Informative)
Delphi! (Score:2, Insightful)
Coding it was a piece of cake - the Borland Database Enginge has it upsides every once in a while (but I never thought I'd admit that)!
quick transfers (Score:1)
I've used it before on importing an MS Access db to MySQL with 3000 rows and 50+ columns. Worked like a charm.
It can also import any ODBC connection. I've never had any problems with it.