Creating a DSN-less connection to a MySQL database

[Al Reynolds]

Hello,

I hope someone will be able to help me with this.

I am trying to set up a connection to my MySQL
database using perl. I already have the database
set up and have used it with PHP without any problems.

When I try and execute the following code, I get an error:
(obviously I have substituted the names/passwords)

#!/usr/bin/perl
use DBI;
my $dbh =
DBI->connect('DBI:mysql:databasename:localhost','usern ame','password');
$dbh->disconnect or warn "Disconnection error: $DBI::errstr\n";
exit;

I contacted the service provider and they replied:
"Using this code, are you trying to create a dsn-less
(adodb) connection, as opposed to dsn (odbc), because
only the former is supported on our hosting packages?"

I think I understand from what I have read so far that I
am currently trying to create a dsn connection, but I might
well be completely wrong about that! Does what the ISP
said make sense?

Can I use DBI to create a dsn-less (ADODB) connection,
or do I need to use something completely different?

IN ASP, I would need to use CreateObject('ADODB.Connection')
Is there something similar I can use in Perl?

The perl version is 5.00503
DB_File, CGI, DBI and DBD-Mysql modules are installed.

Server software is:
Apache/1.3.20 Sun Cobalt (Unix) Chili!Soft-ASP/3.6.2
mod_ssl/2.8.4 OpenSSL/0.9.6b PHP/4.1.2
mod_auth_pam_external/0.1 FrontPage/4.0.4.3 mod_perl/1.25

Thanks in advance,
Al Reynolds

[James Willmore]

On Tue, 9 Sep 2003 13:37:09 +0100
"Al Reynolds" <al@bat400.com> wrote:

> I hope someone will be able to help me with this.
>
> I am trying to set up a connection to my MySQL
> database using perl. I already have the database
> set up and have used it with PHP without any problems.
>
> When I try and execute the following code, I get an error:
> (obviously I have substituted the names/passwords)
>
> #!/usr/bin/perl
> use DBI;
> my $dbh =
> DBI->connect('DBI:mysql:databasename:localhost','usern ame','passwor
> d');$dbh->disconnect or warn "Disconnection error: $DBI::errstr\n";
> exit;

Try to first, read the documentation for DBD::mysql and second, try to
connect using ...

DBI->connect('DBI:mysql:database=dbname;host=localhost ',
'username',
'password')
or die "Connection error: ".$DBI::errstr."\n";

If you had read the documentation, you would have discovered that your
connection statement is wrong.

HTH

--
Jim

Copyright notice: all code written by the author in this post is
released under the GPL. [url]http://www.gnu.org/licenses/gpl.txt[/url]
for more information.

a fortune quote ...
Never offend people with style when you can offend them with
substance. -- Sam Brown, "The Washington Post", January 26,
1977

[Al Reynolds]

"James Willmore" <jwillmore@cyberia.com> wrote in message
news:20030909123830.2ce6ab2f.jwillmore@cyberia.com ...

> On Tue, 9 Sep 2003 13:37:09 +0100
> "Al Reynolds" <al@bat400.com> wrote:

> > I hope someone will be able to help me with this.
> >
> > I am trying to set up a connection to my MySQL
> > database using perl. I already have the database
> > set up and have used it with PHP without any problems.
> >
> > When I try and execute the following code, I get an error:
> > (obviously I have substituted the names/passwords)
> >
> > #!/usr/bin/perl
> > use DBI;
> > my $dbh =
> > DBI->connect('DBI:mysql:databasename:localhost','usern ame','passwor
> > d');$dbh->disconnect or warn "Disconnection error: $DBI::errstr\n";
> > exit;

>
> Try to first, read the documentation for DBD::mysql and second, try to
> connect using ...
>
> DBI->connect('DBI:mysql:database=dbname;host=localhost ',
> 'username',
> 'password')
> or die "Connection error: ".$DBI::errstr."\n";
>
> If you had read the documentation, you would have discovered that your
> connection statement is wrong.

I read enough to decide that the using DBI would
generate a DSN connection*, which wasn't what I
wanted, which is why I gave up on the DBI route
to pursue the ADODB route. The code I used is
referred to in several perl-related resources and
tutorials on different web sites; I doubt that they
all made the same mistake. It isn't that uncommon
for a call to be possible in a variety of forms.

Anyway, thank you for the code snippet. If I am
trying to make a DBI connection again I will use it.

FWIW, I solved the problem using the older
mysql.pm module, which is less portable when
there is a possibility that the database type could
change later. It works fine just now though.

Cheers,
Al


* This may be wrong of course - I am still not quite clear
on the matter. There is surprisingly little info online about
the difference between DSN and DSN-less database
connections and how to set them up using perl.

[James Willmore]

"Al Reynolds" <al@bat400.com> wrote in message news:<bjn7n4$et28p$1@athena.ex.ac.uk>...

> "James Willmore" <jwillmore@cyberia.com> wrote in message
> news:20030909123830.2ce6ab2f.jwillmore@cyberia.com ...

> > On Tue, 9 Sep 2003 13:37:09 +0100
> > "Al Reynolds" <al@bat400.com> wrote:

> > > I hope someone will be able to help me with this.
> > >
> > > I am trying to set up a connection to my MySQL
> > > database using perl. I already have the database
> > > set up and have used it with PHP without any problems.
> > >
> > > When I try and execute the following code, I get an error:
> > > (obviously I have substituted the names/passwords)
> > >
> > > #!/usr/bin/perl
> > > use DBI;
> > > my $dbh =
> > > DBI->connect('DBI:mysql:databasename:localhost','usern ame','passwor
> > > d');$dbh->disconnect or warn "Disconnection error: $DBI::errstr\n";
> > > exit;

> >
> > Try to first, read the documentation for DBD::mysql and second, try to
> > connect using ...
> >
> > DBI->connect('DBI:mysql:database=dbname;host=localhost ',
> > 'username',
> > 'password')
> > or die "Connection error: ".$DBI::errstr."\n";
> >
> > If you had read the documentation, you would have discovered that your
> > connection statement is wrong.

>
> I read enough to decide that the using DBI would
> generate a DSN connection*, which wasn't what I
> wanted, which is why I gave up on the DBI route
> to pursue the ADODB route. The code I used is
> referred to in several perl-related resources and
> tutorials on different web sites; I doubt that they
> all made the same mistake. It isn't that uncommon
> for a call to be possible in a variety of forms.
>
> Anyway, thank you for the code snippet. If I am
> trying to make a DBI connection again I will use it.
>
> FWIW, I solved the problem using the older
> mysql.pm module, which is less portable when
> there is a possibility that the database type could
> change later. It works fine just now though.
>
> Cheers,
> Al
>
>
> * This may be wrong of course - I am still not quite clear
> on the matter. There is surprisingly little info online about
> the difference between DSN and DSN-less database
> connections and how to set them up using perl.

I had to review the ADODB manual to find out what you meant by
"DSN-less" database. In general, anything ADODB, DBI can do. There
is the DBD::ODBC module that you can use to connect to data sources
such as Access. You can also use DBD::Proxy to create a client/server
type connection.

Basically - this is a term that ADODB authors came up with. You still
need to declare a data source using a "DSN-less" connection. So, the
term is mis-leading.

HTH

Jim

[Al Reynolds]

"James Willmore" <jwillmore@cyberia.com> wrote in message
news:e0160815.0309101335.369977cb@posting.google.c om...

> "Al Reynolds" <al@bat400.com> wrote in message

news:<bjn7n4$et28p$1@athena.ex.ac.uk>...

> > "James Willmore" <jwillmore@cyberia.com> wrote in message
> > news:20030909123830.2ce6ab2f.jwillmore@cyberia.com ...

> > > On Tue, 9 Sep 2003 13:37:09 +0100
> > > "Al Reynolds" <al@bat400.com> wrote:
> > > > I hope someone will be able to help me with this.
> > > >
> > > > I am trying to set up a connection to my MySQL
> > > > database using perl. I already have the database
> > > > set up and have used it with PHP without any problems.
> > > >
> > > > When I try and execute the following code, I get an error:
> > > > (obviously I have substituted the names/passwords)
> > > >
> > > > #!/usr/bin/perl
> > > > use DBI;
> > > > my $dbh =
> > > > DBI->connect('DBI:mysql:databasename:localhost','usern ame','passwor
> > > > d');$dbh->disconnect or warn "Disconnection error: $DBI::errstr\n";
> > > > exit;
> > >
> > > Try to first, read the documentation for DBD::mysql and second, try to
> > > connect using ...
> > >
> > > DBI->connect('DBI:mysql:database=dbname;host=localhost ',
> > > 'username',
> > > 'password')
> > > or die "Connection error: ".$DBI::errstr."\n";
> > >
> > > If you had read the documentation, you would have discovered that your
> > > connection statement is wrong.

> >
> > I read enough to decide that the using DBI would
> > generate a DSN connection*, which wasn't what I
> > wanted, which is why I gave up on the DBI route
> > to pursue the ADODB route. The code I used is
> > referred to in several perl-related resources and
> > tutorials on different web sites; I doubt that they
> > all made the same mistake. It isn't that uncommon
> > for a call to be possible in a variety of forms.
> >
> > Anyway, thank you for the code snippet. If I am
> > trying to make a DBI connection again I will use it.
> >
> > FWIW, I solved the problem using the older
> > mysql.pm module, which is less portable when
> > there is a possibility that the database type could
> > change later. It works fine just now though.
> >
> > Cheers,
> > Al
> >
> >
> > * This may be wrong of course - I am still not quite clear
> > on the matter. There is surprisingly little info online about
> > the difference between DSN and DSN-less database
> > connections and how to set them up using perl.

>
> I had to review the ADODB manual to find out what you meant by
> "DSN-less" database. In general, anything ADODB, DBI can do. There
> is the DBD::ODBC module that you can use to connect to data sources
> such as Access. You can also use DBD::Proxy to create a client/server
> type connection.
>
> Basically - this is a term that ADODB authors came up with. You still
> need to declare a data source using a "DSN-less" connection. So, the
> term is mis-leading.
>
> HTH

Cheers - it does.
Al