Copying a Database Table Using DBI's execute

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

Hello,

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);
[download]

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;
[download]

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.

Comment on Copying a Database Table Using DBI's execute_array Select or Download Code

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!	[reply]
Re^2: Copying a Database Table Using DBI's execute_array by plendid (Sexton) on May 03, 2013 at 17:24 UTC
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.	[reply] [d/l]
Re^3: Copying a Database Table Using DBI's execute_array by karlgoethebier (Abbot) on May 03, 2013 at 17:45 UTC
Ť...Sorry to be vague...ť OK, but can't you: Dump the mySQL table to CSV using mysqldump 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ť	[reply]
Re^3: Copying a Database Table Using DBI's execute_array by ww (Archbishop) on May 03, 2013 at 19:33 UTC
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!	[reply]
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.	[reply]
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.	[reply]
Re^2: Copying a Database Table Using DBI's execute_array by plendid (Sexton) on May 06, 2013 at 18:35 UTC
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.	[reply]