EEddieEmm has asked for the wisdom of the Perl Monks concerning the following question:
Dear Perl Monks
I am running a Perl script on a Linux server and accessing a MsSQL database on another server. Most everything is fine except for one query. When I run a SELECT, parse through the returned data and then attempt a table update on the same table, the database locks. When I go to the database itself and try to look at the table, I get the "Waiting to execute query" message. When I kill the script back on the Linux server, everything immediately starts to work again with no issues.
The script and query throw no errors. There is no indication that something is wrong. Has anyone else seen anything like this? If you have, what was your solution?
I have a code snippet below that shows what I am trying to do. The problem happens when I try to run the second query.
~Ed
#!/usr/bin/perl -w use DBI; #### Make a database connection eval { $MY_CONNECTION = DBI->connect("$SERVER", "$UID", "$PWD", {PrintErr +or => 0}) or die "Cannot connect: $DBI::errstr\n"; }; if ($@) { print "ERROR: Database issue. The error was: $@"; exit(2); } #### Get the data my $GET_DB_DATA = $MY_CONNECTION->prepare("SELECT Field_1, Field_2, Fi +eld_3 " . "FROM " . $TableName . " " . + "WHERE Field_4 = 'foo' "); eval { $GET_DB_DATA->execute() or die "Database issue. $DBI::errstr\n"; }; if ($@) { print "ERROR: Cannot get table data. The error was: $@"; exit(2); } else { while ( my @RETURNED_DATA_ARRAY = $GET_DB_DATA->fetchrow ) { if ( $RETURNED_DATA_ARRAY[0] ne $RETURNED_DATA_ARRAY[1] ) { #### Update the table data my $UPDATE_TABLE = $MY_CONNECTION->prepare("UPDATE " . $Ta +bleName . " " . "SET Field_2 = +'" . $RETURNED_DATA_ARRAY[0] . "' " . "WHERE Field_3 += '" . $RETURNED_DATA_ARRAY[2] . "'"); eval { $UPDATE_TABLE->execute() or die "Database issue. $DBI: +:errstr\n"; }; if ($@) { print "ERROR: Cannot update table data. The error was +: $@"; exit(2); } } }
|
---|
Replies are listed 'Best First'. | |
---|---|
Re: Perl Query Locks A MsSQL Database
by moritz (Cardinal) on Jan 23, 2013 at 18:25 UTC | |
by EEddieEmm (Initiate) on Jan 23, 2013 at 18:43 UTC | |
by Corion (Patriarch) on Jan 23, 2013 at 18:47 UTC | |
by EEddieEmm (Initiate) on Jan 23, 2013 at 20:38 UTC | |
Re: Perl Query Locks A MsSQL Database
by wink (Scribe) on Jan 23, 2013 at 19:08 UTC | |
by vincent_veyron (Sexton) on Jan 25, 2013 at 18:08 UTC | |
Re: Perl Query Locks A MsSQL Database
by RichardK (Parson) on Jan 23, 2013 at 19:29 UTC | |
Re: Perl Query Locks A MsSQL Database
by tobyink (Canon) on Jan 23, 2013 at 20:31 UTC | |
Re: Perl Query Locks A MsSQL Database
by mje (Curate) on Jan 24, 2013 at 09:41 UTC | |
Re: Perl Query Locks A MsSQL Database
by nikosv (Deacon) on Jan 23, 2013 at 20:24 UTC | |
Re: Perl Query Locks A MsSQL Database
by EEddieEmm (Initiate) on Jan 23, 2013 at 21:04 UTC | |
by tobyink (Canon) on Jan 23, 2013 at 21:29 UTC | |
by choroba (Cardinal) on Jan 24, 2013 at 09:44 UTC |