problem with DBI statements in while loop

[troled1]

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

[KevinADC]

A segmentation error is very hard to debug. That generally means the program or operating system is reading/writing to system memory incorrectly. Remove as much code from your perl program as possible and run it. If it runs, add in a little more code and repeat the process untill you get to the part of the perl program that causes the error. You can try and run the program with the debugger on -d:

#!/usr/bin/perl -d

and see if you can figure it out.

[troled1]

Thanks, i have tried stripping it down but it appears that the multiple select statements are killing it. I guess from what i have read the odbc driver i am using for this does not like it.