DBI connection to a remote host

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

Fellow monks,
I am entirely new to DBI, and wanted to start at the beginning and simply connect to the mysql server, but from the start I have been having trouble. (Note: that my test machine and the mysql server are two separate boxes). I am using activestate perl 5.8 on windows 2000. I am trying to connect to a mysql server (also on windows 2000). Can anybody give me any clues as to what I'm doing wrong. Here's the code:

#! /usr/bin/perl -w

use strict;
use DBI;

my $database="testdb";
my $host="server";
my $port="3306";
my $dsn = "DBI:ODBC:MSSQL:database=$database; host=$host; port=$port";
my $user = 'tester'; 
my $auth = 'password';
my $dbh = DBI->connect($dsn,$user,$auth,{ RaiseError => 1, AutoCommit 
+=> 1});

die "Can't connect: " . DBI->errstr() unless $dbh;

print "Success: connected\n";
$dbh->disconnect();
[download]

Here's the error message I am receiving:

DBI connect('MSSQL:database=testdb; host=server; port=3306','tester',.
+..) failed
: [Microsoft][ODBC Driver Manager] Data source name not found and no d
+efault dri
ver specified (SQL-IM002)(DBD: db_login/SQLConnect err=-1) at dbitest.
+pl line 12
[download]

Thanks in advance,
Jonathan

Comment on DBI connection to a remote host Select or Download Code

Replies are listed 'Best First'.
Re: DBI connection to a remote host by gnork (Scribe) on Apr 29, 2004 at 09:02 UTC
Hi! I think `my $dsn = "DBI:ODBC:MSSQL:database=$database; host=$host; port=$port";` should look like `# ODBC not used, change DSN if needed my $dsn = "DBI:mysql:database=$database; host=$host; port=$port";` [download] In any case, use the correct driver module, afaics you are using MSSQL Drivers to access a mysql server. Did you install the driver module DBD::mysql or the ODBC drivers for mysql? Rgds, Gnork cat /dev/world \| perl -e "(/(^.*? \?) 42\!/) && (print $1))" errors->(c)	[reply] [d/l] [select]
Re: Re: DBI connection to a remote host by Anonymous Monk on Apr 29, 2004 at 09:51 UTC
Hi Gnork, Thanks for replying so promptly. To answer your question, I did have the DBD::mysql module installed already. While DBD::OBDC was already installed on my system (I think it probably comes included in the standard activestate Perl release). I tried your suggestion, but instead I get this error message: `DBI connect('database=testdb; host=server; port=3306','tester',...) fa +il ed: Access denied for user: 'tester@localhost' (Using password: YES) a +t dbitest. pl line 15` [download] Again, any further pointers or advice would be appreciated. Thanks in advance, Jonathan	[reply] [d/l]
Re: Re: Re: DBI connection to a remote host by gellyfish (Monsignor) on Apr 29, 2004 at 11:07 UTC
This is because the access rights to the database do not allow this connection - you will have to alter this at the MySQL server: you can find out more here /J\	[reply]
Re: Re: Re: Re: DBI connection to a remote host by hmerrill (Friar) on Apr 29, 2004 at 12:23 UTC
Re: DBI connection to a remote host by gnork (Scribe) on Apr 29, 2004 at 10:26 UTC
Check the user table in mysql. Is there a user 'tester' and if yes, which host is allowed to connect? Additionally, pay attention to the footer line. You can run it, its no evil code, simply a message. ;o) Rgds Gnork perl -e 'print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10);'	[reply]