DBI Failing to connect from cgi script

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

I have a script that connects over ODBC to a MS Access database.
When this is run from the command prompt it produces the expected output

#!d:/perl/bin/perl.exe -w
#
use strict;
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use DBI;

#DBI->trace(2, 'c:/temp/timetable.log');
my $dbh = DBI->connect('dbi:ODBC:TIMETABLE', '', '', {RaiseError=>1})o
+r die "Failed to connect to database: ",DBI->errstr();

my $page = new CGI;
print $page->header();
.
.
.
[download]

When I run this cgi script from a web browser I get the following error message

Software error:

DBI->connect(CMIS) failed: [Microsoft][ODBC Microsoft Access Driver] N
+ot a valid file name. (SQL-S1000)(DBD: db_login/SQLConnect err=-1) at
+ d:/apps/apache/cgi-bin/timetable.pl line 9
[download]

Can anyone shed some light on this please

Comment on DBI Failing to connect from cgi script Select or Download Code

Replies are listed 'Best First'.
Re: DBI Failing to connect from cgi script by Chmrr (Vicar) on Mar 27, 2002 at 12:58 UTC
I have very little knowledge of my own, but I did wield Google in a brief search for answers. This seems to be a problem which is specific to Microsoft ODBC, and not to Perl at all. Very few of the results had any solutions to offer -- the only one which seemed like it was possibly on the right track was this one. It also stands to reason that this is perhaps a permission problem of one sort or another, based on the "works in one environment, but not another" sympom. perl -pe '"I lo`+$^X$\"$]!$/"=~m%(.)%s;$_=$1;y^`+*^e v^#$&V"+@( NO CARRIER'	[reply]
Re: DBI Failing to connect from cgi script by rdfield (Priest) on Mar 27, 2002 at 12:43 UTC
Is TIMETABLE set up as a data source in your control panel? Does it work? Can you connect via any other method eg via Excel or Word? rdfield	[reply]
Re: DBI Failing to connect from cgi script by peppiv (Curate) on Mar 27, 2002 at 13:10 UTC
I may be way off track because I've never used ODBC with DBI, but in all my DBI connections I've always had to name the login something. I know I've read it's OK to login as '', '', but my ISP taught me otherwise. Have you tried logging in as root? `my $dbh = DBI->connect('DBI:mysql:your_database_here','root','');` [download] peppiv	[reply] [d/l]
Re: DBI Failing to connect from cgi script by ColtsFoot (Chaplain) on Mar 27, 2002 at 13:42 UTC
I followed the advice of peppiv and low and behold the connect work but the script then fell over on the $sth->execute() line below `my $query = qq(select distinct roomid, name from rooms order by roomid +); my $sth = $dbh->prepare($query) or die qq(Failed to prepare query: $db +h->errstr) ; my $ret = $sth->execute() or die qq(Failed to execute query: $dbh->err +str); my @row = $sth->fetchrow_array() or die qq(Failed to fetch data: $dbh- +>errstr);` [download] with the following error `DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver +] Not a valid file name. (SQL-S1000)(DBD: st_execute/SQLExecute err=- +1) at d:/apps/apache/cgi-bin/timetable.pl line 72.` [download] I then removed the username from the connect statement and the connect worked but the execute still failed What is going on?	[reply] [d/l] [select]
Re: DBI Failing to connect from cgi script by peppiv (Curate) on Mar 27, 2002 at 13:56 UTC
I'm running on FREEBSD with MySQL database. Our platforms may be way different, but here's a chunk of code that works for me. My sth is written a little differently. `my $sth = $dbh->prepare('SELECT * FROM applicants WHERE date_col += ? ORDER BY date_col, job_position'); $sth->execute($date) or die $sth->errstr; while (my @result = $sth->fetchrow_array()) { print qq(@result\n); }` [download] peppiv	[reply] [d/l]