sybperl-l Archive

From: "Doty, Kathy" <doty at bnl dot gov>
Subject: PLEASE HELP: DBPROCESS DEAD
Date: Sep 9 2002 7:18PM

I have what may be considered a very simple question regarding using
sybperl to display information retrieved from my database onto
my web page. However, this is causing extreme trouble for me.
And also, the sybase administrators and the web administrators
are very upset to say the least.

The simple perl code I am using is such:

#!/usr/bin/perl
use CGI qw(:cgi-lib);
ReadParse();

use Sybase::DBlib;

push(@INC,"../lib/");
require("my_lib.pl");

$ENV{SYBASE} = &get_sybenv;
$user = &get_user;
$password= &get_pwd;
$server = &get_sybserver;
$dbname = &get_dbnamenew;
#*******************************************************************************

# get last name of user from previous web form
#

$last_name=$in{last_name};

if ($last_name eq '') {
 $error=1;
} else {
 $error=0;
}
if ($error eq '1') {
 print &PrintHeader;
 print "\n";
 print "\n";
 print "  \n";
 print "\n";
 print "\n";
 print "USER INFORMATION:
\n";
 print "\n";
 print "\n";
 print "You didn\'t enter your last name.  Try again.
\n";
 exit;
} else {
 print &PrintHeader;
 print "\n";
 print "\n";
 print "  \n";
 print "\n";
 print "\n";
 print "
USER INFORMATION:
\n";
 $lclname = lc $last_name;
 $dbh = new Sybase::DBlib $user, $password, $server;
 $dbh-> dbcmd("use $dbname");
 $dbh->dbsqlexec;
 $countret=0;
 $countm=0;
 @countmatch = $dbh->nsql("SELECT count(*),count(*) FROM
remotedb..people WHERE
lower(name_last) LIKE \'$lclname\' order by name_last,name_first",
"ARRAY");
 foreach $countmatch (@countmatch) {
  $countm=$countmatch->[0];
 }
 if ($countm > 0) {
  print "Matches found: $countm
\n";
  print "Please select your name from the following:
\n";
  print "\n";
  print "\n";
  print "\n";
  print "\n";
  print "\n";
  print "\n";
  print "\n";
  print "\n";
  @ret = $dbh->nsql("SELECT
name_first,name_last,address_1,address_2,address_3,address_4,city,state,zipcode,country,affiliation,phone,email,person_id

FROM remotedb..people WHERE lower(name_last) LIKE \'$lclname\' order by
name_last,name_first", "ARRAY");
  foreach $ret (@ret) {
   $countret = $countret + 1;
   $first_name=$ret->[0];
   $last_name=$ret->[1];
   $organization=$ret->[10];
   $telephone=$ret->[11];
   $email_address=$ret->[12];
   $person_id=$ret->[13];
    print "\n";

   print "\n";
   print "\n";
   print "\n";
   print "\n";
   $person_id=$ret->[13];
  }
  print "\n";
 } else {

  print "

There is no
information about you in the
database.
\n";
 }
 dbexit;
 print "
 Name
 Affiliation
 Address
 Telephone
 Email
 $ret->[1], $ret->[0] 
select
 $ret->[10] 
 $ret->[2] \n";
   if ($ret->[3] ne "") {
    print "
 $ret->[3] \n";
   }
   if ($ret->[4] ne "") {
    print "
 $ret->[4] \n";
   }
   if ($ret->[5] ne "") {
    print "
 $ret->[5] \n";
   }
   print "
 $ret->[6], $ret->[7]  $ret->[8] \n";
   print "
 $ret->[11] 
 $ret->[12] 
\n";
 print "
\n";

}
print "\n";

The question, or perhaps better described as MY problem is:

The table I am reading is in a remote database.(I have a proxy table set
up locally)
The table I am accessing has approx 10000 rows (not too big)
Normally this query works just fine and is very quick.
Occasionally, though, this database has gone off line while the
above query is in progress.
This is causing lots and lots of trouble for me.
The webserver error log overflows with the DBPROCESS DEAD... type
message

I also end up with processes in my sybase server that I cannot kill
AWAITING COMMAND  remote I/O (or something to that effect - Ive managed
to
crash the server at the moment and I cannot do an sp_who to see
the exact phrase of the status for the web user)

Can you advise me on how to handle this situation?
Could someone show me the perl code that I need to insert to do this?
(My perl skills are not that good)

Thanks very much in advance for any help you can provide.
I will be most grateful.

Kathy