Win has asked for the wisdom of the Perl Monks concerning the following question:

Do any monks have any offerings of Perl code that will connect up to two databases at the same time (different databases on different machines), so the data can be transferred from one to the other without outputting to a text file first? Such a move would also remove the need to close, open, close and then open databases.

Replies are listed 'Best First'.
Re: DBI connection to multiple databases
by ptkdb (Monk) on Nov 11, 2003 at 15:37 UTC
    Just because most DBI examples don't show two simultaneous connections, doesn't mean it can't be done.

    Just off the top of my head (not tested, debugged etc)

    
use DBI ;

$dbh1 = DBI::connect(...) ;

$dbh2 = DBI::connect(...) ;

$sth1 = $dbh1->prepare(...) ;

$sth1->execute(...) ;

@row = $sth->fetchrow_array(...) ;

$dbh2->do(..., @row) ;

    [download]
[reply]
[d/l]
Re: DBI connection to multiple databases
by Art_XIV (Hermit) on Nov 11, 2003 at 16:54 UTC

    It sounds like a matter of having two connections, one for each database, BUT - If you trying transfer data from one database to another without doing any sort of data massaging, you may be creating alot of extra work for yourself by trying to script the transfers.

    I hate to discourage you from using perl for this, but it's probably not the most easy or efficient way of doing so.

    If you are trying to transfer between two databases with the same engine (that is MySQL->MySQL or Access->Access, check your engines docs. Most of them have nice utilites that will allow you to do transfers w/o scripting.

    If you are trying to transfer between heterogenous dbs, then still check your docs. Many dbs have utilities that allow for imports.

    If you DO have to do some massaging, though, Perl/DBI might be a pretty good pick.

    Hanlon's Razor - "Never attribute to malice that which can be adequately explained by stupidity"
[reply]
Re: DBI connection to multiple databases
by dragonchild (Archbishop) on Nov 11, 2003 at 18:18 UTC
    I have production code that connects to two different databases and does stuff. Just try it. Remember - you have two database handles, one for each database. (You can, theoretically, connect to the same database twice, as different users, even!)

    ------
    We are the carpenters and bricklayers of the Information Age.

    The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6

    ... strings and arrays will suffice. As they are easily available as native data types in any sane language, ... - blokhead, speaking on evolutionary algorithms

    Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

[reply]
Re: DBI connection to multiple databases
by talexb (Chancellor) on Nov 11, 2003 at 15:32 UTC

    Are you doing a one time transfer (so you can use pg_dump and pg_restore, for example) or are you going to continuously update one from the other (so you'd do a fetchrow_hashref coupled with an insert or update)?

    --t. alex
    Life is short: get busy!
[reply]
      One time transfer. I am setting it up so that there is a table creation and then data transfer from a separate database, to populate this newly created table.
[reply]

        If it's a one-time thing, just do a dump from one database and a restore in the other database. No need to get DBI or Perl, for that matter, involved.

        Good luck.

        --t. alex
        Life is short: get busy!
[reply]
Re: DBI connection to multiple databases
by princepawn (Parson) on Nov 11, 2003 at 17:02 UTC

    regarding duplicating a table, I have not seen a more straightforward solution than DBIx::Table::Dup, written by me and based on Ivan Kohler's DBIx::DBSchema Carter's compass: I know I'm on the right track when by deleting something, I'm adding functionality

[reply]

Back to Seekers of Perl Wisdom