Need help with DBI problem!!

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

I am trying to connect to an Access database with DBI & DBD:ODBC and the following code.

use CGI;
use DBI;

$q = new CGI;
$DSN = "my_database.mdb";

my $dbh = DBI->connect('DBI:ODBC:$DSN') or die "Couldn't connect to da
+tabase";
[download]

This brings up an error saying

DBI->connect($DSN) failed: Microsoft ODBC Driver Manager Data source name not found and no default driver specified (SQL-IM002)(DBD: db_login/SQLConnect err=-1)

I cannot seem to get any other clues as to what is happening. As to the location of the database, it is in the same directory as the script. Please can you help.

Comment on Need help with DBI problem!! Download Code

Replies are listed 'Best First'.
Re: Need help with DBI problem!! by mwp (Hermit) on Apr 16, 2001 at 14:11 UTC
You'd do well to follow ColtsFoot's advice. If you haven't set up a datasource, putting the database file (.mdb) in the same directory as the script isn't enough. ("database" NE "datasource") You have to go to "Control Panel > ODBC Data Sources" and configure a driver. If you have no idea what I'm talking about, you should probably read the documentation for DBI::ODBC. Also remember to use double quotes when you want to interpolate a variable in a string: `1 'DBI:ODBC:$DSN' == DBI:ODBC:$DSN 2 "DBI:ODBC:$DSN" == DBI:ODBC:my_database.mdb` [download] In your example, you're sending #1 to the database driver, which it isn't going to like, because it has no idea what `$DSN` means. You want to use double quotes, and send it the name of the datasource, not the name of the variable. Follow?	[reply] [d/l] [select]
Re: Need help with DBI problem!! by ColtsFoot (Chaplain) on Apr 16, 2001 at 14:08 UTC
grax, Rather than specifying the name of the database file $DSN = "my_database.mdb" when declaring the $DSN variable you should use the name that you gave the data source under "Data Sources" from the Control Panel	[reply]
Re: Need help with DBI problem!! by wardk (Deacon) on Apr 16, 2001 at 17:47 UTC
grax, After you ensure that you have the proper ODBC driver installed, I would strongly suggest NOT using DSN's and instead code the entire connect string in your program. This way you can move the script to any machine that has the drivers without having to rely on/recreate on that DSN entry. The entire string would resemble: `# replace DRIVERNAME,SERVERNAME,DATABASENAME,USERID,PASSWD with real +values... my $CONNECT = "Driver={DRIVERTYPE};Server=SERVERNAME;Database=DATABASE +NAME;UID=USERID;PWD=PASSWD";` [download] have fun!	[reply] [d/l]