CODEWITHSUNDEEP
sql-serverperlodbcdbi
Pooja
Pooja

Reputation: 31

Accessing Microsoft SQL Server from Windows in PERL

I am using SQL Server driver. But this is the following error I get:

DBI connect('Driver={SQL Server}:$database:$host','cartertest',...) failed:
[Microsoft][ODBC Driver Manager] Invalid connection string attribute (SQL-01S00)
at PERL_SQL_Connect.pl line 15
Can't call method "disconnect" on an undefined value at PERL_SQL_Connect.pl line 16

This is my code:

use DBI;
use DBD::ODBC;
#my $dsn = "dbi:SQL Server:$database:$host";
my $dsn = 'DBI:ODBC:Driver={SQL Server}:$database:$host';

my $host = 'amber';    ##This is the server name
my $database = 'assignmentdb';     ##This is the database name
my $user = 'something';         ## Database User Name
my $auth = "something";

#my $dsn = "dbi:SQL Server:$database:$host";

$dbiconnect = DBI->connect($dsn,$user,$auth);   #line 15
$dbiconnect->disconnect();                      #line 16

What mistake(s) am I making?

Upvotes: 3

Views: 5732

Answers (2)

bohica
bohica

Reputation: 5992

Firstly add use strict and use warnings as you are referencing $database and $host before they are declared and assigned anything. However, as you used ' instead of " in this case strict would not have picked up the issue you have on line 4. Then move the other declarations before the declaration of $dsn. Remove 'use DBD::ODBC' as it does nothing in this case. Lastly read up about ODBC connection strings (see the DBD::ODBC FAQ for references). You cannot put a bare database name and host in the connection string. The string required is 'dbi:ODBC:attr1=value1;attr2=value2' where the attributes are partly defined by ODBC and partly by the ODBC Driver. bvr's example above shows that.

Upvotes: 1

bvr
bvr

Reputation: 9697

You can try this:

use DBI;

my $host     = 'amber';
my $database = 'assignmentdb';
my $user     = 'something';
my $auth     = 'something';

my $dsn = "dbi:ODBC:Driver={SQL Server};Server=$host;Database=$database";
my $dbh = DBI->connect($dsn, $user, $auth, { RaiseError => 1 });

# .... work with the handle

$dbh->disconnect;

Note that '' string does not interpolate your variables, so in your example $dsn string contains verbatim $database or $host, not their contents.

Upvotes: 5

Related Questions