Updated below: I've got over 1 million records to go through as I normalize tables, which means I need to use DBI's fetchall_arrayref $max_size to keep memory usage in check unless there is a better way.
In my loop, I'm getting this error: Can't coerce array into hash at line 44. and can't figure out why since the same code worked minus the while statement for $max_size.
# Assuming:
#
# Table1
# +-----+-----------+
# | id | Name |
# +-----+-----------+
# | 1 | Bill |
# +-----+-----------+
# | 2 | Bob |
# +-----+-----------+
# | 3 | Mike |
# +-----+-----------+
#
# Table2 before
# +-----+-----------+
# | id | Name |
# +-----+-----------+
# | ? | Mike |
# +-----+-----------+
# | ? | Bob |
# +-----+-----------+
# | ? | Bill |
# +-----+-----------+
#
# Table2 after
# +-----+-----------+
# | id | Name |
# +-----+-----------+
# | ? | 3 |
# +-----+-----------+
# | ? | 2 |
# +-----+-----------+
# | ? | 1 |
# +-----+-----------+
#!/usr/local/bin/perl -w
# print "Content-type: text/html\n\n";
use lib "/home/champ1/public_html/cgi-bin";
use warnings;
use CGI::Carp qw(fatalsToBrowser);
use strict;
use CGI qw/:cgi/;
use Data::Dumper;
use Common;
use Validate;
use HTML::Template;
use HTML::FillInForm;
$|=1;
#------------------------- declare --------------------------
my $query = new CGI;
my ($sth, $stmt, $template, $hdrflg, $fvalues); #$hdrflg =
+ is set when a header is first written
my ($dbh, $keylock, $basehref) = dbconnect(); #f
$stmt = "SELECT id, name FROM table1";
$sth = execute_it($dbh,$stmt);
my $table1 = $sth->fetchall_arrayref({});
my $max_rows = 2;
my ($i, $j);
$stmt = "SELECT id, name FROM table2";
$sth = $dbh->prepare($stmt);
$sth->execute();
while (my $table2 = $sth->fetchall_arrayref(undef, $max_rows))
{
for $i ( 0 .. $#$table2 ) {
for $j ( 0 .. $#$table1 ) {
###line 44### if ( $table2->[$i]{'name'} eq $table1->[$j]{'name'} ) {
$stmt = ("UPDATE table2 SET name = ? WHERE id = '$
+table2->[$i]{'id'}'");
$sth = $dbh->prepare($stmt);
$sth->execute ( $table1->[$j]{'id'});
}
}
}
};
close_db();
exit();
I used the super search and found one node, which wasn't very helpful.
http://www.perlmonks.com/index.pl?node_id=320547
Update:
Thanks to gmax here is the code I ended up using.
It's currently running through the rows. :)
$stmt = "SELECT id, name FROM table1";
$sth = execute_it($dbh,$stmt);
my $table1 = $sth->fetchall_arrayref({});
my ($i, $j, $sth2);
$stmt = "SELECT id, name FROM table2";
$sth = $dbh->prepare($stmt);
$sth->execute();
print "Content-type: text/plain \n\n";
my $max_rows = 500;
my $rows = []; # cache for batches of rows
while ( my $row = ( shift( @$rows ) || shift( @{$rows=$sth->fe
+tchall_arrayref({},$max_rows) || []} ) ) )
{ for ( $row ) {
for $j ( 0 .. $#$table1 ) {
if ( $row->{'name'} eq $table1->[$j]{'name'} ) {
$stmt = qq/UPDATE table2 SET name = ? WHERE id = "
+$row->{id}"/;
print "Here.$j".Dumper ($stmt)."\n";
$sth2 = $dbh->prepare($stmt);
$sth2->execute ( $table1->[$j]{'id'});
}
}
}
};
close_db();
exit();
READMORE tags added by Arunbear
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.