configuration file in windows? (used with Perl script)

[johny]

Hi, all:

I tried two scripts, which are to use a configuration file to free users from inputing username, password, etc manually.

Here is the file "my.cnf":

# My configuration file
[client]
user=root
password=rochester

Here is the file "mysql_connect2.pl":

#!d:/perl/bin/perl

# Use what needs to be used.
use DBI;
use strict;

# Connect to the database.
my $user;
my $password;
my $dbh = DBI->connect('DBI:mysql:mylibrary;mysql_read_default_file=c:\my.cnf', $user, $password, {RaiseError => 1});

# Report on the success of the connection attempt.
if ($dbh) {
print "Successfully connected to the database! \n";
}

# Disconnect.
$dbh->disconnect;

(I have created a database named "mylibrary" in mysql. )

When I run this script, it has no response and hang there.
(I put the my.cnf file into the root directory of C drive. )

Yet, if I change the DBI->connect line into:

my $dbh = DBI->connect('DBI:mysql:mylibrary;mysql_read_default_file=./my.cnf', $user, $password, {RaiseError => 1});

It can run as well as expected. (I have copied my.cnf into the same folder as this script.)

Sounds strange, right?

I guess it is due to to colon in the string "c:\my.cnf".
But how to solve it? I tried to escape it, e.g. using "c\:\my.cnf", but to no avail.

I run the script again in Win2k with MySQL 3.23.56 installed.

This time, it will pop up an error like this:

***********************************************************

D:\DTemp>mysql_connect2.pl
DBI connect('mylibrary;mysql_read_default_file=c:\my.cnf','',...) failed: Unknown My
SQL Server Host '\my.cnf' (11001) at D:\DTemp\mysql_connect2.pl line 12

***********************************************************

From the error message above, it can be seen that the error is produced by its inability to interprete the colon in the string "c:\my.cnf". The string after this colon is looked as host name of MySQL server.

Because the canonical usage of connect is as follows:

$dbh=DBI->connect('DBI:mysql:database:host', 'user', 'password', {RaiseError => 1})

And in the case of localhost, the host name can be omitted.


How to solve this problem???

thanks,

[johny]

Here is the reply from Paul DuBois, the writer of the famous RED BOOK:

***********************************************************

The problem on Windows is that pathnames contain colons, which is also
the separator character for parts of the data source string. To get
around this, you can chdir to the correct drive, then use the filename
without a drive letter. Here's some example code from Doorstop II
that demonstrates this, assuming you want to use C:\my.cnf as your option
file:

# This code shows how to specify use a Windows pathname as the value
# of the mysql_read_default_file option in a data source name (DSN)
# string. Normally, pathnames that begin with a drive letter and colon
# will fail, because DBI uses colon as the DSN component separator.
# By cd-ing to the drive root directory before connecting, the filename
# can be specified without the drive letter and colon. The code below
# also saves the current directory first and changes back to it after
# connecting, to leave the current directory undisturbed by the
# connect operation.

use strict;
use DBI;

# save current directory pathname
use Cwd;
my $orig_dir = cwd ();
# change to root dir of drive where file is located
chdir ("C:/") or die "cannot chdir: $!\n";
# connect using parameters in C:\my.cnf
my $dsn = "DBI:mysql:sampdb:localhost;mysql_read_default_file=/my.cnf";
my $dbh = DBI->connect ($dsn, undef, undef,
{ RaiseError => 1, PrintError => 0 });
# change back to original directory
chdir ($orig_dir) or die "cannot chdir: $!\n";

$dbh->disconnect ();

exit (0);

***********************************************************

Hope it would be useful for anyone who experienced or will experience the same problem as mine.

:)