Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

Database copy table with DBI

by cbtshare (Monk)
on Nov 14, 2016 at 07:18 UTC ( #1175853=perlquestion: print w/replies, xml ) Need Help??

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

Hello all, I can connect successfully to two databases, I want to perform a query on one and then the results I want to insert into another database, my code is below, I am having a bit of issues with the insert portion.
#Connect to production database and results copy into test #database a +nd update the data my $table_results= $dbh2->prepare("SELECT * from $feed_table WHERE ent +ry_time >= $time"); $table_results->execute(); while(my @results = $table_results->fetchrow_array()) { if(@results) { ##prints out results that should be copied to test database foreach(@results) { print "$_\n"; } } else { print "There is no data for this selec +ted time\n"; } } ##Connect to the test feed database instance and insert data my $destination_host="destination"; my $destination_dsn =DBI:mysql:database=$dbrow;host=$destination_host" +; my $destination_user="root"; my $destination_pass="ol^16"; my $dbh3 = DBI->connect( $from_feed_dsn, $from_feed_user, $fro +m_feed_pass, { RaiseError => 1 }) or die ( "Couldn't connect to datab +ase: " . DBI->errstr ); my $sth_insert = $dbh3->prepare("insert into $feed_table (id_code,entr +y_time,parent_id_code,author_name,author_code,author_url,author_image +_url,entry_url,entry_types, status_code,entry_text,entry_data,last_update,pull_time,queue_code) VA +LUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) "); while (my $insert = $table_results->fetchrow_array()) { $sth_insert->execute(@$insert);

Replies are listed 'Best First'.
Re: Database copy table with DBI
by choroba (Archbishop) on Nov 14, 2016 at 07:41 UTC
    What kind of issues do you have? The obvious one is the missing double quote before DBI:mysql:database=$dbrow;host=$destination_host" . Try settitng PrintError to 1 on connect to get more information.

    ($q=q:Sq=~/;[c](.)(.)/;chr(-||-|5+lengthSq)`"S|oS2"`map{chr |+ord }map{substrSq`S_+|`|}3E|-|`7**2-3:)=~y+S|`+$1,++print+eval$q,q,a,
      Thank you, well the connection parts all work and I get the data, I was just wondering if the logic of it all is sound, and if printing inserting the array reference makes sense here
Re: Database copy table with DBI
by Corion (Pope) on Nov 15, 2016 at 11:25 UTC

    After you've got your primary logic and error tracking down, you might want to investigate the ->execute_for_fetch method of DBI, which allows you a tight fetch-insert loop:

    my $table_results= $dbh2->prepare("SELECT * from $feed_table WHERE ent +ry_time >= $time"); my $sth_insert = $dbh3->prepare("insert into $feed_table (id_code,entr +y_time,parent_id_code,author_name,author_code,author_url,author_image ++_url,entry_url,entry_types, status_code,entry_text,entry_data,last_update,pull_time,queue_code) VA +LUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) "); $sth_insert->execute_for_fetch(sub { $table_results->fetchrow_arrayref }, my \@tuple_status);
Re: Database copy table with DBI
by poj (Abbot) on Nov 14, 2016 at 08:13 UTC

    Try changing this line to use method fetchrow_arrayref

    while (my $insert = $table_results->fetchrow_arrayref()) {

      One quick question. As the data needs to be flattened to an array to insert, is there (in the general case) any benefit in using fetchrow_arrayref? I am doing a similar task to the OP, copying entire DB tables. I have used fetchrow_array, then insert the array using a prepared insert. I would benchmark the two, but in my case the fetch is from an well known online service desk tool providing instant gratification ;) and the variance in performance between runs is large enough to mask any difference between fetching @ or \@.


      Pereant, qui ante nos nostra dixerunt!
      Thank you, I will try this.using $table_results->fetchrow_arrayref() would seem to make sense, since I will then insert @$insert which is an array ref itself as the data. But if I wanted to use $table_results->fetchrow_array(), what then would I use within $sth_insert->execute( ); ? Thank you

        Why not print and insert in the same loop ?

        while( my @results = $table_results->fetchrow_array()){ print join ",",@results; print "\n"; $sth_insert->execute(@results); }

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1175853]
Approved by GrandFather
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (3)
As of 2021-05-18 21:29 GMT
Find Nodes?
    Voting Booth?
    Perl 7 will be out ...

    Results (185 votes). Check out past polls.