?
Solved

first script failing to connect

Posted on 2006-04-18
9
Medium Priority
?
300 Views
Last Modified: 2008-02-01
Created a test db in access and placed it on the root of a network drive, which is partitioned into 3 drives one for users which is what I'm using, drive letter 'H'

#!/usr/bin/perl
# load module
use DBI;

# connect
my $dbh = DBI->connect("DBI:ODBC:dbname=Test; host=H", "Username", "password",  {'RaiseError' => 1});

# execute INSERT query
my $rows = $dbh->do("INSERT INTO pets (species, name) VALUES ('parrot', 'Polly')"); print "$rows row(s) affected\n";

# execute SELECT query
my $sth = $dbh->prepare("SELECT species, name FROM pets"); $sth->execute();

# iterate through resultset
# print values
while(my $ref = $sth->fetchrow_hashref()) { print "$ref->{'name'} is a $ref->{'species'}\n"; }

# clean up
 $dbh->disconnect();

this is the result when run

C:\Perl\eg>test1.pl
DBI connect('dbname=Test;host=H','username',...) failed: [Microsoft][ODBC Driver
 Manager] Data source name not found and no default driver specified (SQL-IM002)
(DBD: db_login/SQLConnect err=-1) at C:\Perl\eg\test1.pl line 6
0
Comment
Question by:deNZity
9 Comments
 
LVL 28

Expert Comment

by:FishMonger
ID: 16483867
The host needs to be the hostname or IP address of the server, not the drive letter.  
0
 

Author Comment

by:deNZity
ID: 16483978
Thanks for the reply, I put in the IP address and got the same result.

D
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 16484194

my $dbh = DBI->connect("DBI:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=h:\test.mdb","","");

where h:\test.mdb is the path to your access database.

Or define a System DSN with ODBC Data Source Admin and use the data source name you defined.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 25

Accepted Solution

by:
clockwatcher earned 2000 total points
ID: 16484205
Sorry forgot to escape the '\'  

   my $dbh = DBI->connect("DBI:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=h:\\test.mdb","","");
0
 
LVL 51

Expert Comment

by:ahoffmann
ID: 16490874
you have not installed the proper DBI driver module
0
 

Author Comment

by:deNZity
ID: 16491783
Thanks for the replies,
clockwatcher followed your suggestion and got


C:\Perl\eg>perl test1.pl
DBD::ODBC::db do failed: [Microsoft][ODBC Microsoft Access Driver] Record(s) can
not be added; no insert permission on 'pets'. (SQL-42000)(DBD: Execute immediate
 failed err=-1) at test1.pl line 9.
DBD::ODBC::db do failed: [Microsoft][ODBC Microsoft Access Driver] Record(s) can
not be added; no insert permission on 'pets'. (SQL-42000)(DBD: Execute immediate
 failed err=-1) at test1.pl line 9.

Looks like the connection is ok I just have to sort the permissions, which is a bit strange as I'm connecting as the same user as
when I created the db.
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 16493472
What kind of security do you have set up on your database?  Unless you've added user security to your access database (which would make it easy to explain -- but is doubtful since few people use that feature of access and you'd know you had if you had), the primary reason I can think of that would cause that error is that the account running the script (not whatever user you're using in the ODBC parameter setup) but the actual account the script is running under doesn't have write access to the directory housing the access database.  Access has to create/modify a locking file (.ldb) in the directory that holds the database.  If it can't, the database will be read only.
0
 

Author Comment

by:deNZity
ID: 16502240
Clockwatcher points to you. I can see I'm going to have to find some basic perl
tutorials dealing with database connections and data extraction, any resources
you could point me towards would be appreciated.

D
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 16502939
Shoot.  I'm not gonna be much help on this one.  I don't have any real good links to share.  I always fall back to the documentation whenever I use DBI:  perldoc DBI
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Six Sigma Control Plans
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question