Here's how we connect to a MS SQL Server at work:

    my $db = 'DBI'->connect("DBI:ODBC:Driver={SQL Server};Server=$serv
+er;Database=$database",
                             $user,
                             $secret,
                             @options);
[download]

Have you installed DBD::ODBC?

OK - now updated (sorry - I wasn't sure if I may have been muddying the waters by including the error messages as it seemed a fairly clear-cut case of using the wrong syntax.)

So, have you installed DBD::DriverName? Or maybe you need to adapt your DSN string to use dbi:ODBC if you are on Windows and want to use DBD::ODBC?

$DSN= 'dbi:DriverName:database=$db;host=$host;port=$port;'
[download]

Have you verified that $DSN contains what you think it should contain?

print "Connecting to [$DSN]\n";
[download]

This might help you to find out whether the value of $DSN is what you think it is.

The syntax for a DBI connection string is 'dbi:DRIVERNAME:stuff' where DRIVERNAME is the name allocated to the DBD (database driver you want to use) in DBI. Whatever, you put for DRIVERNAME will cause DBI to attempt to load DBD::DRIVERNAME and hence when you used 'dbi:DriverName' you got "you may need to install the DBD::DriverName error. For DBD::ODBC, the DRIVERNAME should be 'ODBC'. So, your connection string should start 'dbi:ODBC'.

After the 'dbi:ODBC' you add a further ':' and then usually follow it with a series of name=value pairs separated by semi colons ';'. So now your connection string looks like 'dbi:ODBC:attr1=value;attr2=value;'. DBD::ODBC (actually in this case ODBC more precisely) defines the attribute names you can use so basically, after 'dbi:ODBC:' you add whatever attributes ODBC and your ODBC driver define to connect to your ODBC data source. The most common and most simple thing to do is as you did and create a DSN (data source name) in the ODBC administrator and then you'd use 'dbi:ODBC:DSN=name_of_my_dsn' as the connection string (DBD::ODBC also supports for this case only 'dbi:ODBC:name_of_my_dsn' as back in time ODBC did not support attributes).

If you don't want to create a system or user ODBC DSN you can use so called DSN-less connection strings. In this case you have to tell the ODBC driver manager which ODBC driver to use )in much the same way you tell DBI which DBD to load) and then you tell the ODBC driver which database to connect to e.g., 'dbi:ODBC:DRIVER={MS SQL Server Driver};Server=mydatabaseserver;Database=mydatabase;' like the example already provided in this thread. In this case you should consult your ODBC driver documentation for driver specific attributes and the ODBC documentation for what ODBC defined connection attributes there are (e.g., DSN, DRIVER, FILEDSN). NOTE: a) I made up "MS SQL Server Driver", you need to use the string listed in the ODBC administrator under the drivers tab b) {} are used in ODBC connection strings to delimit values which may contain spaces or other punctuation.

Lastly, you need to be aware that since you are on Windows you might have a 64bit machine. As the ODBC driver is really a DLL (code wrapped up in a way that allows it to be dynamically loaded into a program) you cannot mix 32 bit apps with 64bit DLLs and vice versa. If your perl is 64 bit then you need to create your DSNs with the 64 bit ODBC driver manager setup dialogues and if your perl is 32 bit you need to use the 32 bit ODBC driver manager. If you hit this issue do a search for where to find the 2 different ODBC driver managers.

Hope this helps although I imagine by now you've read some documentation or searched online which would probably have been quicker all round than posting here for a "quick" answer.

With the help of Windows' ODBC Data Source Administrator, I have configured a system DSN (named $myDSN)

Since you've already created the DSN via Windows ODBC Data Source Administrator, you just need to use that $myDSN var in your connection string. The ODBC driver will use that name to lookup the connection details (i.e., the specific driver, server name, port, and db name)

my $myDSN = 'data_source_name';
my $dbh = DBI->connect("DBI:ODBC:$myDSN", 'user', 'password',
                       { RaiseError => 1}) or die DBI->errstr;
[download]

I would figure it out myself, but I'm in a hurry...

Dude. Really? So everyone else should drop everything and hurry to help you? I would say that if you are in too much of a hurry to figure it out for yourself, you have a deeper problem than can be solved here.

However, you did provide some halfway decent problem data, so ...

Use Data::Dumper to check your data as you build and debug your program. (For a scalar, you can simply print() it.) Your $DSN is probably not what you think it is. Been about 20 years since I used ODBC, but it looks like you are passing your username and password with quotes around them.

Sorry - didn't mean it that way, just meant that as I'm a newbie with DBI it may take me a long while (that I don't have) to get it cracked, and that if an uber-user could crack it in a few seconds, that would be miles more efficient...

No worries. Nice of you to reply back.

It's common to have these sorts of problems when trying to connect to a DB with the DBI. The problems usually have to do with escaping and interpolation in perl. Try these links given in a recent thread. Because you can connect to your DB using ODBC manager or whatever, with the same credentials you think you are giving in your $DSN, it's tricky to understand what the problem could be. Often the DBI error message is long and/or incomplete or confusing. As I say, this is a common DBI beginner's mistake.

What you can learn here are ways to become a better programmer, not just immediate solutions to what you're stuck on (although you'll often get those if you ask nicely).

So that's why my first answer (and that of a another respondent) was to advise you to start debugging your program as you write it. Print out $DSN and you will see if it has any quoting/interpolation problems. Get into the habit of examining all your data as you develop your program.

One way to do this is declare a global variable that turns debugging on or off, and then you can have conditional debugging statements in your code that are only printed when the switch is on. (Using && to test true for the first half of the statement before executing the second half...)

#!/usr/bin/env perl -w
use strict;
use 5.010;
use DBI;
my $DEBUG = 1;

&db_connect( 'foo', 'bar', 'baz' );

sub db_connect {
  my ($srvr, $usr, $pwd) = @_;
  
  my $DSN = "dbi:ODBC:Server=$srvr; UID=$usr;PWD=$pwd";
  $DEBUG && say $DSN;

  my $dbh=DBI->connect($DSN, { RaiseError => 1 });
}
__END__
[download]

I write most code with this built in, whether the debug flag is set at the top of a script, or in a config file, or in an environment variable, or whatever. Change one value somewhere and the whole thing turns on or off. You can use it to enable/disable logging, send/not send email while testing, whatever. And you can easily have more than one level of debugging and "escalate" certain things that you really want to examine when all else is working. Once it's working okay, set $DEBUG to 0 and it shuts up. Next time there's a problem, or you want to add or change something, it's all there ready to turn back on.

Good luck!

Remember: Ne dederis in spiritu molere illegitimi!

$DSN= 'dbi:DriverName:database=$db;host=$host;port=$port;'
[download]

$sth = $dbh->do('SELECT whatever ...');
[download]

my $sql = 'SELECT whatever ...';
print "Statement: [$sql\n]";
$sth = $dbh->do($sql);
[download]

use Data::Dumper;
...
my $sql = 'SELECT whatever ...';
print Dumper $sql;
my $sth = $dbh->prepare($sql) or die "prepare failed: ', $DBI::errstr;
...
[download]

That is bad advice!

Please do not create SQL statements with fixed strings and do (remember bobby?)

I agree with the wrong quotes being used (of course). Shell quotes are not perl quotes on Windows at least.

If you want to see the statement used when something fails, use the appropriate means:

my $dbh = DBI->connect ("dbi:Pg:", $user, $pass, {
    RaiseError         => 1,
    PrintError         => 1,
    ShowErrorStatement => 1,
    }) or die $DBI::errstr;

my $sth = $dbh->prepare ("select foo from bar where boom = ?");
$sth->execute (1);
[download]


Enjoy, Have FUN! H.Merijn

I did not intend to advise creating SQL with fixed strings if you have variable parts. This was just an example (and didn't contain any variables in the SQL).

And if you have things like q(SELECT ConfigValue FROM ConfigTable WHERE ConfigKey = 'ScreenWidth'), where you have neither placeholder nor variable interpolation, then there's other problems than simply the fixed string :-)

Plus in the "I prefer" part I used prepare anyway…

Update: Of course, $dbh->do("SELECT ... is very bad practice per se, because SELECT COUNT(*) ... usually is more efficient ;-) (see do in DBI) … used it for illustration purposes only.