Perl DBI Connection

[bknox]

I am using Win32::ODBC to connect to a database located on the C: drive of my computer. I have set up a file dsn through the administrator tools->ODBC tool in the control panel to point to the database with the microsoft access driver. Everytime I use the code

$DSN = "po.dsn";

#Connect to PO DB
$db=new Win32::ODBC($DSN) or die "couldn't open Purchase Order DB: $DSN because ", Win32::ODBC::Error(), "</br>";

The die statment prints out:

couldn't open Purchase Order DB: po.dsn because 911[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified</br> at C:\Inetpub\Scripts\templete.pl line 15.


I have saved the dsn under C:\Program Files\Common Files\ODBC\Data Sources.

Can anyone tell what I am doing wrong?

 

[Rieekan]

The DSN you create must be a System DSN. That's about all I can see that could cause the issue, although it's been years since I've used Win32::ODBC to connect to databases.

Have you considered installing DBI and the various DBD modules you'd need in lieu of using the Windows specific connections? This would allow your scripts to become more easily ported to other Operating Systems.

- Rieekan

 

[dmazzini]

Hi bknox

I've been using this module and it is working fine for me...

Anyway, I am sending you an example how I am connecting to access database...

If the DSN is not present, I am adding it via code

Here we go.

Cheers.

# ##############################################################################################
# Script: connection.pl
# Connecting to Database via ODBC.
# It adds the ODBC Datasource "RNWDB" if it was not created.
##############################################################################################

use Win32::ODBC;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft DAO';

$cont =0;
$ODBC =0;

system("cls");

# ##############################################################################################
# Checking ODBC DataSources
# Checking ODBC Connection to RNW Database.
# ##############################################################################################

my (%dsn,$key) = Win32::ODBC:ataSources();

foreach $key (sort keys %dsn) {
print "ODBC:$key\n";
$ODBC =1 if ($key eq "RNWDB");
}

# ##############################################################################################
# Creating ODBC DataSources "RNWDB" if it doesn't exist
# ##############################################################################################

if ($ODBC ==0){
print "The ODBC Connection to RNWDB was not established. Creating new ODBC Connection.....\n\n";
$DSN = 'RNWDB';
$Driver = 'Microsoft Access Driver (*.mdb)';
$Desc = 'RNW Database';
$Dir = 'M:\\DF TN Application\\Files';
$File = 'DoNotUse.mdb';
$Fullname = "$Dir\\$File";

Win32::ODBC::ConfigDSN(ODBC_ADD_DSN, $Driver,
"DSN=$DSN", "Description=$Desc", "DBQ=$Fullname",
"DEFAULTDIR=$Dir", "UID=", "PWD=");
}

# ##############################################################################################
# Connecting to "RNWDB".
# ##############################################################################################

if (!($db = new Win32::ODBC('RNWDB'))){
print "Error connecting to $DSN\n";
print "Error: " . $db->Error() . "\n";
$db->Close();
exit;
}

 

[MikeLacey]

Nice one dmazzini, have you thought of turning that into a FAQ entry?

Mike

"Deliver me from the bane of civilised life; teddy bear envy."

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[bknox]

I have created a System DSN for the file I wish to open. However, whenever I move to another script to handle other processing tasks, it gives me the following error:

couldn't open Purchase Order DB: po because -1032[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data.</br> at C:/Perl/lib/functions.lib line 7.

I have closed the connection to the database from script1 by using the $db->close() method. However when I go to connect to the DB again it gives me this error. The code contained within the functions.lib is the following:

sub connecttodsn
{

$DSN = "po";

#Connect to PO DB
$_[0]=new Win32::ODBC($DSN) or die "couldn't open Purchase Order DB: $DSN because ", Win32::ODBC::Error(), "</br>";
}
1;

Help please:

Ben

 

[dmazzini]

I have tested two connection to 1 access database at the same time with the example that I posted before. It works fine.

Try to adapt the script that I posted before. Another idea is to change the variable $_[0] for other like $db or something else...may be..

Cheers!

 

[bknox]

I can establish two connections with my script as well, it is just when I call another script from my script. I.E. I have a perl script that access a DB to fill in drop down lists on a web page. All the HTML is outputed by Perl. When I click the submit button I move to another script, it is this script's connection that gives me the aforementioned error.

Ben