DBI->connect problem

JB54 has asked for the wisdom of the Perl Monks concerning the following question:

I'm not sure if this is a Perl problem, an Oracle problem, a website problem, or a combination of all three. I apologize in advance if it is not a Perl issue.

I'm new to Perl, and have inherited a set of cgi files. The database server was changed and now what used to work no longer works. This is a troubleshooting help enquiry.

I don't expect a solution, but would appreciate if someone could point me in the right (another?) direction. I have read the FAQs and will try to keep this brief and well-formatted.

The tnsnames.ora file has been edited to relect the new server name.

I have not used the -w switch. I know. Burn me, but I have no idea how to retrieve the warnings in code to write them to the log file (below). If you can give me a snippet, in context, I will happily try that and reply.

I know this is immaterial, but I am in a corporate environment and do not have direct access to the servers. If I did, I would be willing to try things on my own, but the situation does not permit that. I have been in several teleconferences and Web-Ex's, but we are not making progress.

#!/appl/perl-5.6.1/bin/perl

no warnings 'recursion';

use DBI;
use CGI::Carp qw(fatalsToBrowser);
use CGI;
use FileHandle;

$ichs_cfg{shlib_path} = "/usr/oracle_cl8.1.6/lib:/usr/lib:/lib:";
$ichs_cfg{oracle_home} = "/usr/oracle_cl8.1.6";
$ichs_cfg{two_task} = "prch";
$ichs_cfg{tns_admin} = "/home/oracle/tnsnames";

#Next we set some environment variables.
#I don't fully understand the need for some of these either, but it wo
+rks.
$ENV{"LD_RUN_PATH"} ="/usr/oracle_cl8.1.6/lib";
$ENV{"SHLIB_PATH"} = $ichs_cfg{shlib_path};
$ENV{"TWO_TASK"} = $ichs_cfg{two_task};
$ENV{"ORACLE_HOME"} = $ichs_cfg{oracle_home};
$ENV{"TNS_ADMIN"} = $ichs_cfg{tns_admin};
eval "use Oraperl";

$mycgi = CGI->new();
[download]

#Modified by JB54 to include logname parameter (random number) for eac
+h transaction. File locks were becoming a latency issue.
$WWW_tempemp = $mycgi->param("tempemp");
$WWW_logname = $mycgi->param("logname");

$ichs_logs = "/data01/websites/ams/ichs/doc/logs/$WWW_logname.log";

$fh = new FileHandle $ichs_logs, "w";

#defined $fh
print {$fh} "Select PL running.   \n";
print {$fh} "$WWW_tempemp  \n";
print {$fh} "$WWW_logname  \n";

my $curDriver;

my $db = DBI->connect("dbi:Oracle:prch", "User", "PW");

#Added print {fh} and $DBI::errstr\n to die for debugging
($db) || die(print {$fh} "Database did not connect.\n $DBI::errstr\n")
+;
[download]

I have just added commented material above tonight and have not tested it. Is the syntax for the die correct? I really did RTFM, but it was ambiguous to me. The print {$fh} works for the Select PL running and the next two lines. The rest of the code should (and did before the database server change) write the sql select results to a .log file.

print {$fh} "Database connected...To select users\n";
[download]

$sql = "select * from sysadm.logon_p01 where clock = upper('$WWW_tempe
+mp')";

print {$fh} "$sql....\n";

$sth = $db->prepare($sql) || die "Can't prepare: $DBI::errstr";

$rc = $sth->execute || die "Can't execute: $DBI::errstr";
#Retrieve data from select statement

#Get single row of data returned from oracle.

($clock,$name,$firstname,$middleinitial,$deptid,$phone,$entrydate,
 $removedate,$location,$pageprogress,$course_start_date,$course_comp_d
+ate,
 $final_quiz_complete,$final_quiz_score,$quiz_attempts,$survey_1,$surv
+ey_2,
 $survey_3,$survey_4,$survey5,$survey_6,$survey_7,$survey_8,$survey_9,
+$survey_10,
 $eval_1,$eval_2,$existing_user) = $sth->fetchrow_array;

 print {$fh} "$clock\n$name\n$firstname\n$middleinitial\n$deptid\n$pho
+ne\n$entrydate\n";
 print {$fh} "$removedate\n$location\n$pageprogress\n$course_start_dat
+e\n$course_comp_date\n";
 print {$fh} "$final_quiz_complete\n$final_quiz_score\n$quiz_attempts\
+n$survey_1\n$survey_2\n";
 print {$fh} "$survey_3\n$survey_4\n$survey5\n$survey_6\n$survey_7\n$s
+urvey_8\n$survey_9\n$survey_10\n";
 print {$fh} "$eval_1\n$eval_2\n$existing_user\n";

#Check for problems during fetch
warn $DBI::errstr if $DBI::err;

#Disconnect
$rc  = $db->disconnect || warn $db->errstr;

print {$fh} "disconnecting....\n";
undef $fh;
chmod(0666,$ichs_logs);

#print "Content-Type: text/plain\n\n";
#print "";

exit $status
[download]

Comment on DBI->connect problem Select or Download Code

Replies are listed 'Best First'.
Re: DBI->connect problem by everybody (Scribe) on Sep 26, 2008 at 02:00 UTC
I will try to keep this brief Perhaps you should be a wee bit less brief and let us know what the actual trouble is that needs shooting. ;) What makes you think it isn't working? Is the app dying, are you getting the wrong data, no data at all, no web page, a mangled web page, ...? It's difficult to find the cure without knowing the symptoms.	[reply]
Re: DBI->connect problem by Mr. Muskrat (Canon) on Sep 26, 2008 at 14:45 UTC
everybody is right. You didn't do a good job of describing the problem. "now what used to work no longer works" just isn't going to cut it. We need to know what isn't working. Does the CGI app run at all? Does it run but not produce the correct results? How exactly is not working? :) If you add `use warnings;` right before `no warnings 'recursion';` then any warnings would be added to the web server log (if it's running Apache then more than likely that file is /var/log/httpd/error_log or /var/log/httpd/ssl_error_log). You said that "The database server was changed" and "The tnsnames.ora file has been edited to relect the new server name." Are you saying that you are now using a database on a different server or that the hostname of the same database server has changed? If it's the latter then I hope who ever did it is familiar with the necessary steps to change the hostname of an Oracle database server. Are you really using a mix of DBI and oraperl? I suspect that it did not get stripped out when the script was moved to DBI because I don't see any calls to "ora_" functions. I see some more potential problems but I don't have time to write them up. Sorry.	[reply] [d/l] [select]
Re: DBI->connect problem by runrig (Abbot) on Sep 26, 2008 at 16:25 UTC
You are not checking the status when trying to open your log file. I'd not use the FileHandle module (but that's just my preference), so I'd do: `open(my $fh, ">", $ichs_logs) or die "Can't open $ichs_logs: $!";` [download] On the DBI connect, I'd just use RaiseError: `my $db = DBI->connect("dbi:Oracle:prch", "User", "PW", { RaiseError => 1, });` [download] Then you don't have to check every DBI method and add "or die $dbh->errstr". If you want to warn instead of die on some methods, you can wrap them in an eval block and use $@ as the error message. Oh, and I second Mr. Muskrat's question about using Oraperl. With DBI, there should be no need for it anymore.	[reply] [d/l] [select]