Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister

Copying a Database Table Using DBI's execute_array

by plendid (Sexton)
on May 03, 2013 at 14:57 UTC ( #1031908=perlquestion: print w/replies, xml ) Need Help??
plendid has asked for the wisdom of the Perl Monks concerning the following question:


My goal is to copy data in one MySQL table ($bard) to one Oracle table ($pics). I believe that all but one of my lines of code are near enough okay to be okay.

$mysql_db = DBI->connect("DBI:mysql:database=$d;host=$h", $u, $) $oracle_db = DBI->connect("dbi:Oracle:host=$h;service_name=$d", $u, $p +) $mysql_db->{RaiseError} = 1; $oracle_db->{RaiseError} = 1; # get data from MySQL $sql = ' select bmprefix, bmid, englishtitle, foreigntitle from mysql_books '; $sql_handle = $mysql_db->prepare($sql); $sql_handle->execute(); $mysql_data = $sql_handle->fetchall_arrayref(); # revise Oracle table $del_sql = " delete from bard_books where bkmed = '?' and bkseq = ? "; $ins_sql = " insert into oracle_books (bkmed, bkseq, english_title, foreign_title) values ('?', ?, '?', '?') "; $del_handle = $oracle_db->prepare($del_sql); $ins_handle = $oracle_db->prepare($ins_sql);

I have a hard time with references and how to use them. It's hard to get my head around these. So this next and last statement is probably really wrong. How do i want to correct this?

$tuples = $del_handle->execute_array(\%attr, $mysql_data) or die $del_handle->errstr;

Several existing threads on perlmonks address my issue. They point to cpan modules outside DBI that are cool but a tiny bit unstable, such as DBIx::Copy.

Please accept thanks for whatever assistance or advice you're able to share.

Replies are listed 'Best First'.
Re: Copying a Database Table Using DBI's execute_array
by ww (Archbishop) on May 03, 2013 at 15:18 UTC
    Not to endorse or deny your conclusion, but how did you arrive at "...this next and last statement is probably really wrong." Is there some substantive reason -- like errors or warnings or your computer puking out blue flames -- that leads you to your belief?

    IOW, details ... all the details, help us to help you.

    If you didn't program your executable by toggling in binary, it wasn't really programming!

      It is not a tested statement because i had and have no confidence that it will run correctly. I believe that the $mysql_data = $sql_handle->fetchall_arrayref(); statement creates a reference to an array of arrays. I do not feel confident nor knowledgeable enough to determine how to employ this structure in the execute_array statement.

      Sorry to be vague. I'm so clueless here with this one statement and what execute_array expects that i find it difficult to write my request either more cogently or more coherently.

        «...Sorry to be vague...»

        OK, but can't you:

        1. Dump the mySQL table to CSV using mysqldump
        2. Bulk insert the data into Oracle

        If you need to manipulate your data, you can do something in the bulk insert script or you can use a trigger and a stored procedure...

        Regards, Karl

        «The Crux of the Biscuit is the Apostrophe»

        Still, you could create a small db -- a few columns per record and only a handful of records -- and use that db as a testbed for statements about which you're unsure.

        Among other things, that approach is apt to be quicker than waiting for a workable answer here (and you still won't know if it's a workable answer unless you test it, or the responder provides demo material.

        And, (bonus!) you get the additional experience -- right or wrong -- that will help you on your path to expertise.

        If you didn't program your executable by toggling in binary, it wasn't really programming!

Re: Copying a Database Table Using DBI's execute_array
by mje (Curate) on May 08, 2013 at 13:55 UTC

    Read all of execute_for_fetch as it shows you how to effectively copy data from one statement handle to another.

Re: Copying a Database Table Using DBI's execute_array
by Anonymous Monk on May 03, 2013 at 17:46 UTC
    Your solution yanks everything into memory. Not a good idea.

      Yes i agree. I am trying to avoid that. I will try it on my own and see if i can get some meaningful errors.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1031908]
Approved by ww
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (5)
As of 2018-02-25 00:50 GMT
Find Nodes?
    Voting Booth?
    When it is dark outside I am happiest to see ...

    Results (312 votes). Check out past polls.