I hace3 a script that needs to write to a mysql database after pulling records from an ACCESS database. The select there are three loops, one outer loop gets the region, the next gets each building in that region and the next gets each room in that building, then moves on to the next region and starts over. The problem is that it writes the first region, the first building and all the rooms in that building. when it goes to the next building it crashes and says segmentation fault. I am very new with using the DBI like this and am stuck. The problem i think is to write to the database it must pull a variable from the previous loop iteration, any ideas would help
-
my $sql = "SELECT District_ID,
-
ClientName,
-
Status,
-
Region,
-
ClientCode,
-
OriginalName,
-
JPA_ID FROM tblImportedDistricts";
-
-
my $sth = $dbh->prepare($sql)
-
or die "Can't prepare statement: $DBI::errstr";
-
$sth->execute();
-
-
warn "starting loops..";
-
while ( @region = $sth->fetchrow_array)
-
{
-
warn $region[1];
-
$dist_id = &guid;
-
$schema->resultset('customerDistrict')->create(
-
{
-
version_id =>$version_guid,
-
region_id =>$dist_id,
-
district_id =>"$region[0]",
-
clientname =>"$region[1]",
-
jpa_id =>"$region[6]"
-
});
-
-
my $sql_building = "SELECT building_ID, Name FROM tblImportedbuildings WHERE districts_ID = ".$region[0];
-
my $sth_building = $dbh->prepare($sql_building)
-
or die "Can't prepare statement: $DBI::errstr";
-
$sth_building->execute();
-
-
while ( @customer_building = $sth_building->fetchrow_array)
-
{
-
-
warn $customer_building[1];
-
$building_id = &guid;
-
-
$schema->resultset('customerbuilding')->create(
-
{
-
customer_building_id =>$building_id,
-
building_id =>"$customer_building[0]",
-
name =>"$customer_building[1]",
-
region_id =>$dist_id
-
});
-
-
my $sql_loc = "SELECT room_ID, Description FROM tblrooms WHERE building_ID = ".$customer_building[0];
-
my $sth_loc = $dbh->prepare($sql_loc)
-
or die "Can't prepare statement: $DBI::errstr";
-
$sth_loc->execute();
-
-
while ( @customer_room = $sth_loc->fetchrow_array)
-
{
-
warn $customer_room[1];
-
-
$loc_id = &guid;
-
$schema->resultset('customerroom')->create(
-
{
-
customer_room_id =>$loc_id,
-
room_id =>"$customer_room[0]",
-
customer_building_id =>$building_id,
-
name =>"$customer_room[1]"
-
});
-
-
}
-
warn "done with this one";
-
}
-
-
warn "starting next";
-
}
-
-
-
-
print "Done with: $datasource\n";
-
-
$dbh->disconnect || die;
-
}
-
