471,066 Members | 953 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,066 software developers and data experts.

problem with DBI statements in while loop

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
2 1711
KevinADC
4,059 Expert 2GB
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
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.

Similar topics

6 posts views Thread by Iain Bishop | last post: by
6 posts views Thread by Ravi | last post: by
7 posts views Thread by Munzilla | last post: by
5 posts views Thread by jbruno4000 | last post: by
4 posts views Thread by James E Koehler | last post: by
4 posts views Thread by phantom | last post: by
6 posts views Thread by JayCallas | last post: by
6 posts views Thread by weidongtom | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.