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

Hi folks.
I'm having strange trouble. I have a script that accesses a database via a DSN and ODBC drivers. It works fine as frogs hair if I run it from the console, but not when it's called via CGI. This may be a funky IIS configuration problem, but alas I'm the only Perl user here, so there will be no help from our web monkeys.
We're running Win2k Advanced Server, IIS 5, and the latest perl from Activestate.
Here's a snippet of where my woes come from:
use strict;
use DBI;
use DBD::ODBC;
use CGI_Lite;

my $DSN = 'BLAH';
my $dbh;
open LOG, ">>log.log";
print LOG "\nTesting..\n";

$dbh=DBI->connect("dbi:ODBC:$DSN",'USER','PASSWORD',{ 
        RaiseError => 1, 
        AutoCommit => 1,
        PrintError => 0 } );
if(!$dbh){
    print LOG "Error: $DBI::errstr";
    exit;
}

print LOG "Good";
exit;

[download]
When I run this chunk of code from the console, I get a log file with:

Testing..
Good

However, if I run this from the web, I get a log file with:

Testing..

No clue here, folks. Can't for the life of me figure out why I'm not even getting an error message, which would be preferable to no message in lieu of that coveted "Good".
Any insight would be greatly apprecaited.

Thanks,
-=rev=-

Replies are listed 'Best First'.
Re: IIS-CGI-DBI Errorless Errors
by JupiterCrash (Monk) on Aug 08, 2002 at 04:34 UTC
    This sounds like the ODBC DSN that you are using might be available to the user running the script from the console, but not to the user who runs IIS. (which is, by default, a user called IUSR_MACHINENAME.)

    If the DSN *IS* a user DSN, try making it a System DSN instead and see if you have the same problem. You might also want to enable ODBC tracing from the ODBC-Admin control panel.
[reply]
Re: IIS-CGI-DBI Errorless Errors
by grinder (Bishop) on Aug 07, 2002 at 21:55 UTC
    Are you sure the IIS environment has the DSN correctly defined? That would be my first hypothesis to prove or disprove.

    If you're not seeing the "good", then either the connect() is dumping core or else you're going into the if block and hitting the exit. Maybe because there's no \n pm the print statement it's not getting flushed? (But I don't really believe that myself).

    Try putting print LOG "1\n" before and after the connect() call to see if you're getting through that successfully.

    You might also want to coniser a use CGI::Carp qw/fatalsToBrowser/. Is LOG being opened successfully? You're not testing to see whether it works or not, and since you are appending to the file (without datestamps I might add) you could be looking at stale information.

    Good luck :)


    print@_{sort keys %_},$/if%_=split//,'= & *a?b:e\f/h^h!j+n,o@o;r$s-t%t#u'
[reply]
[d/l]
[select]
      The machine (IIS Environment) should have the DSN defined correctly, as the script works as long as it's not called by the 'web user'. Also, the same DSN is accessable via ASP (though I personally haven't done that). Pardon the slackness and uncleanliness of the snippet of code - I knocked it down to the bare essentials really, and I know that the LOG part works because I do print 'Testing..' to the log prior to the connect call, and immediately attempt to print Good or Bad following that call. The log file ends up containing JUST the "Testing.." line, meaning that the code dies specifically on the connect call. I'm going to try error handling as suggested by one of the other posts here tomorrow, and hopefully that will solve things. Thanks for the input =)
      -=rev=-
[reply]
Re: IIS-CGI-DBI Errorless Errors
by runrig (Abbot) on Aug 07, 2002 at 22:10 UTC
    You have RaiseError set, which in the Unix world, would mean any error from DBI will go to stderr and with Apache they would end up in the apache error log (but in any case, if there were a problem with the DBI->connect(), your example would die before it printed any DBI error to your logfile). I don't know where error output ends up on IIS, but I would try something like this to make them go where you want them to:
    eval {
  my $dbh=DBI->connect("dbi:ODBC:$DSN",'USER','PASSWORD',{ 
             RaiseError => 1, 
             AutoCommit => 1,
             PrintError => 0 } );

 # More database stuff create statements, execute
};
if ($@) {
 print LOG $@;
 exit;
}
print LOG "Good\n";

    [download]
[reply]
[d/l]
      Hrm.. good point. I know little about the database parameters used there.. I looked 'em up once, and have copied them in all of the databases I've used w/out problems. Nothing mission critical, I should point out =) I'll try passing the connect through an eval like you suggest tomorrw when I get to work. Hopefully that will shed some light.
      -=rev=-
[reply]
      Thank you, kind monk. You've brought me to the world of Viewable Errors. From here, I expect to secure passage to the great Rock Candy Mountain of Successful CGI Programming.
      -=rev=-
[reply]
Still Stuck - IIS-CGI-DBI Errorless Errors
by Reverend Phil (Pilgrim) on Aug 08, 2002 at 14:51 UTC
    I appologize for probably getting quite off topic now. I've received my coveted error string.
    DBI->connect(BLAH) failed: [IBM][Client Access ODBC Driver (32-bit)][D
+B2/400 SQL]Communication link failure. COMM RC=0x5 (SQL-08S01)(DBD: d
+b_login/SQLConnect err=-1) at E:\Perl\scripts\x.pl line 11

    [download]
    Line 11 is obviously the connect line ;) Either way, I'm lost. The best advice I've gotten so far is that the default web user is not given access to make connections to databases which do not reside on the same machine as the web server. I know that via .asp this connection is possible on this machine, so this may be the wrong assumption. However, I don't even know how to configure this to try it out. This is internal, btw, so security is not really an issue - I've been told that giving access to external databases is a no-no kind of thing. *shrug*
    Any help/ideas would again be appreciated.

    Danke,
    -=rev=-
[reply]
[d/l]

Back to Seekers of Perl Wisdom