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);

[download]

Replies are listed 'Best First'.
Re: Database copy table with DBI
by choroba (Cardinal) 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,

    [download]
[reply]
[d/l]
[select]
      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
[reply]
Re: Database copy table with DBI
by Corion (Patriarch) 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);

    [download]
[reply]
[d/l]
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()) {
    poj
[reply]
[d/l]
[select]

      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 \@.

      Cheers,
      R.

      Pereant, qui ante nos nostra dixerunt!
[reply]
      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
[reply]

        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);
}

        [download]
        poj
[reply]
[d/l]

Back to Seekers of Perl Wisdom