From: GlenM <glenmill...@gmail.com>
> # Read them into an array
> my @files_in_dir = grep { /xml/ } readdir(DIR);
You want all files with lowercase "xml" anywhere the the name?
Really? I think you want
my @files_in_dir = grep { /\.xml$/i } readdir(DIR);
instead. That is files with extension .xml.
> closedir DIR;
>
> # connect to database and create parser object
> my $dbh = DBI->connect ("DBI:mysql:can_us_ratecenters",
> "xxxx", "xxxxxx",
> { RaiseError => 1, PrintError => 0});
>
> # clear the database - new DIDs coming in
> $dbh->do ('TRUNCATE TABLE rc_city_town');
>
> #Now the fun begins - read each file and put it in the database
> foreach my $f (@files_in_dir) {
> open IN, "<$f";
>
> my $xp = XML::XPath->new (filename => "./$f");
> my $nodelist = $xp->find ("//row");
> foreach my $row ($nodelist->get_nodelist ())
> {
> $dbh->do (
> "INSERT IGNORE INTO rc_city_town (state_prov, city_town,
> did_number) VALUES (?,?,?)",
> undef,
> $row->find ("state")->string_value (),
> $row->find ("ratecenter")->string_value (),
> $row->find ("number")->string_value (),
> );
Do split this into two calls
my $sth = $dbh->prepare("INSERT IGNORE INTO rc_city_town
(state_prov, city_town, did_number) VALUES (?,?,?)");
just below the connection to the database and
$sth->execute(
$row->find ("state")->string_value (),
$row->find ("ratecenter")->string_value (),
$row->find ("number")->string_value (),
);
in the foreach loop.
Then you most probably want to turn the autocommit off
my $dbh = DBI->connect ("DBI:mysql:can_us_ratecenters",
"xxxx", "xxxxxx",
{ RaiseError => 1, PrintError => 0,
AutoCommit = 0});
and then call
$dbh->commit or die $dbh->errstr;
once every (say) 1000 rows and at the end of each file.
Jenda
===== je...@krynicky.cz === http://Jenda.Krynicky.cz =====
When it comes to wine, women and song, wizards are allowed
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
