I recently wrote this script that pulls information from text files and adds
it to a MS-SQL database. When the script is finished running, the SQL server
is stuck using a large amount of memory. Each consecutive time I run the
script the memory usage for the SQL server goes up. I believe that I am
closing/finishing/committing correctly, but thought I would put it here for
review. Please excuse my lack of Perl skills, I usually close the book as
soon as I think I can figure it out for myself! This is one of 3 text files
that add/modify record in the database, I have posted the smallest example
here. The largest table has more than 160 fields, but is coded the same way.
chdir("D:\\CMLS\\download\\dailytemp") or error_report("could not change to
temp directory before inserting data: $!\n");
# open SQL database
my $dbh = DBI->connect( 'dbi:ODBC:CMLS', 'user', 'pass',
{RaiseError => 0,
AutoCommit => 0}
) or error_report("Database connection not made (update offices):
$DBI::errstr");
# open txt file of new data
open(OFFICES, "offices.txt") or error_report("could not open offices.txt
file to read data");
my($rcnt)=0;
my($sth) = $dbh->prepare
('SELECT officenumber FROM offices WHERE officenumber= ?');
my($sth1) = $dbh->prepare
('UPDATE offices SET OfficeName= ? WHERE OfficeNumber= ?') or
error_report("Could not prepare update statement for offices:
$DBI::errstr");
my($sth2) = $dbh->prepare
('INSERT INTO offices (OfficeNumber, OfficeName) VALUES (?,?)') or
error_report("Could not prepare insert statement for offices:
$DBI::errstr");
while(<OFFICES>){
chomp($_);
if($rcnt>0){ # skip first line
my($officenumber,$officename) = split(/\|/, $_);
$sth->execute($officenumber);
my @row = $sth->fetchrow_array;
$sth->finish;
if(@row>0){
print "record exists - updating data for office $officenumber\n";
$sth1->execute($officename, $officenumber) or
error_report("Error updating office record: $DBI::errstr");
}else{
print "record does not exist - adding office $officenumber\n";
$sth2->execute($officenumber, $officename) or
error_report("Error inserting new office record:
$DBI::errstr");
}
}
$rcnt++;
}
$sth1->finish;
$sth2->finish;
$rcnt=$rcnt-1;
print "$rcnt records affected\n";
$dbh->commit or $dbh->rollback and error_report("Database changes to offices
table not commited: $$DBI::errstr");
$dbh->disconnect;
_______________________________
|
| Scott Phelps
| NT Systems Administrator
| WebKorner Internet Services
| [EMAIL PROTECTED]
| www.webkorner.com
| MCDTP (Microsoft Certified
| duct tape professional)
|_______________________________
