By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,179 Members | 970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,179 IT Pros & Developers. It's quick & easy.

problem with DBI statements in while loop

P: 2
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
Expand|Select|Wrap|Line Numbers
  1.  my $sql = "SELECT District_ID,
  2.              ClientName,
  3.              Status,
  4.              Region,
  5.              ClientCode,
  6.              OriginalName,
  7.              JPA_ID FROM tblImportedDistricts";
  8.  
  9.  my $sth = $dbh->prepare($sql)
  10.       or die "Can't prepare statement: $DBI::errstr";
  11.  $sth->execute();
  12.  
  13.  warn "starting loops..";
  14.  while ( @region = $sth->fetchrow_array)
  15.   {
  16.     warn $region[1];
  17.     $dist_id = &guid;
  18.     $schema->resultset('customerDistrict')->create(
  19.       {
  20.         version_id                =>$version_guid,
  21.         region_id                 =>$dist_id,
  22.         district_id               =>"$region[0]",
  23.         clientname                =>"$region[1]",
  24.         jpa_id                    =>"$region[6]"
  25.       });
  26.  
  27.  my $sql_building = "SELECT building_ID, Name FROM tblImportedbuildings WHERE districts_ID =  ".$region[0];
  28.  my $sth_building = $dbh->prepare($sql_building)
  29.       or die "Can't prepare statement: $DBI::errstr";
  30.  $sth_building->execute();
  31.  
  32.   while ( @customer_building = $sth_building->fetchrow_array)
  33.   {
  34.  
  35.     warn $customer_building[1];
  36.     $building_id = &guid;
  37.  
  38.     $schema->resultset('customerbuilding')->create(
  39.      {
  40.       customer_building_id            =>$building_id,
  41.       building_id                   =>"$customer_building[0]",
  42.       name                      =>"$customer_building[1]",
  43.       region_id                 =>$dist_id
  44.     });
  45.  
  46.  my $sql_loc = "SELECT room_ID, Description FROM tblrooms WHERE building_ID = ".$customer_building[0];
  47.  my $sth_loc = $dbh->prepare($sql_loc)
  48.     or die "Can't prepare statement: $DBI::errstr";
  49.  $sth_loc->execute();
  50.  
  51.   while ( @customer_room = $sth_loc->fetchrow_array)
  52.    {
  53.      warn $customer_room[1];
  54.  
  55.     $loc_id = &guid;
  56.     $schema->resultset('customerroom')->create(
  57.    {
  58.     customer_room_id         =>$loc_id,
  59.     room_id                        =>"$customer_room[0]",
  60.     customer_building_id     =>$building_id,
  61.     name                           =>"$customer_room[1]"
  62.    });
  63.  
  64.    }
  65.     warn "done with this one";
  66.   }
  67.  
  68.     warn "starting next";
  69.  }
  70.  
  71.  
  72.  
  73. print "Done with: $datasource\n";
  74.  
  75. $dbh->disconnect || die;
  76. }
  77.  
Nov 10 '08 #1
Share this Question
Share on Google+
2 Replies


KevinADC
Expert 2.5K+
P: 4,059
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.
Nov 10 '08 #2

P: 2
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.
Nov 10 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.