I have been converting a perl cgi script from mySQL use to MS SQL. In doing so, I have had problems just connecting to MS SQL, so I tested code out at the command prompt. Server is Win 2003 x64 and using Active Perl. I have configured the ODBC manager in both 32 and 64 bit incarnations. The confusion is with the ODBC driver name. The code below is what I use to initiate a connection.
#!c:/perl/bin/perl
# DBI ODBC Test
BEGIN
{
push @INC, '/gfs/www/cgi-bin/pers/karlk';
}
# push @INC, '..';
use Time::localtime;
use DBI;
use strict;
my $db_name;
my $db_type = 'MSSQL';
my $db_access_path;
my $dbh;
my $db_err = '';
my $db_errstr = '';
my @dns;
my $d;
my %sql_srvr_type = ('mySQL' => 'mysql',
'MSSQL' => 'ODBC:DRIVER={MS SQL 2005}'); # SQL S
+erver
my %sql_srvr_name = ('mySQL' => 'localhost', 'MSSQL' =>'localhost'); #
+ \\SQLEXPRESS
my %sql_srvr_port = ('mySQL' => '3306', 'MSSQL' => '1433');
my %sql_user = ('mySQL' => 'xxx', 'MSSQL' => 'yyy');
my %sql_pwd = ('mySQL' => 'xxx', 'MSSQL' => 'yyy');
my $db_name = 'online_labs_qa';
# dbi:DriverName:database=database_name;host=hostname;port=port
$db_access_path = "DBI:$sql_srvr_type{$db_type};server=$sql_srvr_name{
+$db_type};database=$db_name;UID=$sql_user{$db_type};PWD=$sql_pwd{$db_
+type}";
print "DBI Connect: $db_access_path\n";
my @dsns = DBI->data_sources('ODBC');
foreach my $d (@dsns)
{
print "Driver = $d\n";
}
# connect
print "Get DBI handle\n";
$dbh = DBI->connect($db_access_path) || die "Could not connect to data
+base: $DBI::errstr";
if (!defined($dbh))
{
# connect error
$db_err = $dbh->err;
$db_errstr = $dbh->errstr;
print "Connect Error: $db_err, $db_errstr\n";
}
else
{
print "Handle = $dbh\n";
}
It prints out the connection string, what the available ODBC drivers are, and the handle if it connects. The confusion is with the ODBC driver name. If I use "{SQL Server}", it connects properly but that is not the ODBC System DSN I setup. That was "{MS SQL 2005}", and that shows up as one of the 2 ODBC drivers. When I use "{MS SQL 2005}", I get the following output.
DBI Connect: DBI:ODBC:DRIVER={MS SQL 2005};server=localhost;database=
+online_labs_qa;UID=xxx;PWD=yyy
Driver = DBI:ODBC:MySQL
Driver = DBI:ODBC:MS SQL 2005
Get DBI handle
DBI connect('DRIVER={MS SQL 2005};server=localhost;database=online_lab
+s_qa;UID=xxx;PWD=yyy','',...) failed: [Microsoft][ODBC Driver Manager
+] Data source name not found and no default driver specified (SQL-IM0
+02)(DBD: db_login/SQLConnect err=-1) at odbc_test.pl line 49
Could not connect to database: [Microsoft][ODBC Driver Manager] Data s
+ource name not found and no default driver specified (SQL-IM002)(DBD:
+ db_login/SQLConnecterr=-1) at odbc_test.pl line 49.
So what's up? Why does one work and the other not? I have another issue in getting this to work via IIS6 in CGI, but that's another post. Any insight is greatly appreciated!
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.