Slashdot Mirror
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."
Wouldn't it be nice if the protocol to RDBMS'es were standarized. Every DB server would use a standard protocol to talk to clients, sort of LDAP, which is a protocol and vendors implement that protocol..
Summary: to get data from a db to MySQL use PHP to read the db and print out a MySQL script that loads all the data.
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.
Why wouldn't you just add a parameter to your script that could do the inserts directly to the MySQL database right from PHP, instead of going to a file first? A simple type switch would be good. Then you could say if($type == "file") write data to file, else if($type == "db") write directly to the MySQL DB... It's a simple solution, and it could be easily configured for clients as a turnkey solution to getting their data.
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.
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.
I mean, seriously. MySQL just isn't that good with stored procedures. What's the point of going from more capable to less capable database?
Heute die Welt, morgen das Sonnensystem!
I just have to say Wooooopppppiiiieeeeee
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)
/^AudTmst$/, @names);
= TEST_ATRB", $user, $password);
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.
It would be quite nice to have a C-based converter, that could be used to replace databases within an hour... for apps that support both the BEFORE and AFTER database types.. without using ODBC.
What I have in mind is mysql, pgsql, oracle 9i, msaccess, db2, firebird, minisql.
Wonder if its possible.
"Give orange me give eat orange me eat orange give me eat orange give me you." -Nim Chimpsky
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'
You could give me a hand with this if you want. (a C++ library for reading access databases).
The projects been dormant for a while, (work makes my head hurt too much for real development)
thank God the internet isn't a human right.
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 ;-).
Java would have been a much better language if you wanted the project to be reusable. JDBC means that we have the same code for every type of DB's. So you could have a 'read all from DB' set of code and a 'write all to DB' set of code, then simply plug the two DB's into an XML config file, and voila, you have exactly what is needed for any type of DB with JDBC drivers (which is everything except the extremely rare and extremely obscure).
That is something worth writing an article about. Not just one very specific case.
Good quote, too many chars. Seriously, the slashdot 120 char limit sucks!
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.
/.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.
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.)
If this is something you do alot, get SQL Server DTS. It does this beautifully, as well as many other tasks.
Have you read my journal today?
check out hypersonic. a small footprint java RDBMS with a jdbc driver which stores its data on the filesystem. most people never get past checking the database out BUT....
it comes with an incredibly useful TRANSFER TOOL which can transfer tables and/or data from one JDBC source to another. i have only tried it with a limited amount of databases, including mySQL, Oracle, and AdabasD. the newer version has some problems with Oracle, but with a little "hand tuning" in the source code you should be able to get it to work.
my "favorite" uses for it are:
- making a quick copy of an oracle schema to my laptop, so i can continue developing my database driven webapp at home (use an object persistence layer like castor to make sure that your code is not database specific)
- prepared databases with test cases. just check out of CVS and you are ready to go...
You should check it out, it's open source.
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.
MSSQL has built in easy to use wizards to do this easily with Enterprise Manager.
This is news? In this century?
http://access2sql.sourceforge.net/
ODBC Import...
I wrote a small app for that once. It has basicly two comboboxes containing all ODBC DSN:s found on the system. Then you choose from which DSN to which DSN and click copy - regardless of database vendors (as long as they have ODBC drivers, that is). Just to show off, it gives the user a list of all available tables, so that he/she may copy only a subset.
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)!
Why does the kernel go through stable and then unstable forks? Can't it always be a stable build, like with Windows?
but I consider PostgreSQL and Oracle to be real databases (and obviously more advanced)
Heute die Welt, morgen das Sonnensystem!
...you read the article's title as "Transferring Data Teen Databases"
Thanks, I'll try that. I really should brush up on my ANSI SQL. Now lets just hope that MS Access ODBC, not my choice, supports some SQL-92!
Having been doing this for some time with my clients, there is an excellent tool : OpenAdaptor ( http://www.openadaptor.org) that I have been using. Of course, you could try the Oracle's hetrogeneous services. Again the coversion depends on many times what is exactly required 1) One time conversion 2) Conversion in batch : hourly, daily, monthly etc 3) Real time conversion : convert only when required, ex when somebody requests past 3 months data 4)Accessability all the time : jdbc, odbc, etc
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.