How do I connect with Perl to SQL Server?

Question

I have a user id, password, database name and datasource details. I want to connect with Perl to a MSSQL server. I just used the following snippet, but I am getting an error.

#!/usr/bin/perl -w
use strict;

use DBI;

my $data_source = q/dbi:ODBC:192.168.3.137/;
my $user = q/bharani/;
my $password = q/123456/;

# Connect to the data source and get a handle for that connection.
my $dbh = DBI->connect($data_source, $user, $password)
    or die "Can't connect to $data_source: $DBI::errstr";

My error is:

DBI connect('192.168.3.137','bharani',...) failed: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (SQL-IM002) at my sqlconnect.pl line 14
Can't connect to dbi:ODBC:192.168.3.137: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (SQL-IM002) at mysqlconnect.pl line 14.

The SQL server runs on another system, I am just trying to connect with above details. Please tell me, should I crease DSN in my system, or is anything missing in my program?

Crochet

Answer 1

Everything following 'dbi:ODBC:' in your connection string is passed to the ODBC driver. For MSSQL, try this connection string:

DBI->connect("dbi:ODBC:Driver={SQL Server};Server=192.168.3.137;UID=$user;PWD=$password")

You can find some more alternatives on connectionstrings.com

Grief

i just created the DSN in my local , and it worked find, thx for advise, in this site am not find perl+mssql snippet, ya just find related to .NET AND db – Crochet

Firstly that should be 'dbi:ODBC' not 'DBI:ODBC'. Secondly it is not exactly correct to say everything after 'dbi:ODBC' is passed to the driver and certainly not the database. If you use 'dbi:ODBC:mydsn' and don't include a DRIVER= or DSN= then DBD::ODBC first calls the older SQLConnect API with mydsn, username, password (for historical reasons). If you put DRIVER= or DSN= in the connect string after 'dbi:ODBC' DBD::ODBC ignores SQLConnect and passes the string after 'dbi:ODBC' to SQLDriverConnect after parhaps adding UID/PWD attributes. – Pepsin

@Pepsin Updated the answer per your suggestions. Hope it is more correct now. – Grief

Answer 2

I am on Ubuntu (20.04) and followed the instructions to install SLQ Server in a docker container: https://learn.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-ver15&pivots=cs1-bash

I created the TestDB and the table Inventory as per the tutorial.

I installed the ODBC driver following https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15

The driver library file was /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.5.so.2.1

With that, the following script successfully connects to the DB and returns the content of the table:

 #!/usr/bin/perl
use strict;
use warnings;

use DBI;
my $user = 'SA';
my $password = '<YourNewStrong@Passw0rd>';

my $dbh = DBI->connect("dbi:ODBC:Driver={/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.5.so.2.1};Server=localhost;Database=TestDB;UID=$user;PWD=$password");

my $sth = $dbh->prepare("SELECT * FROM Inventory");
$sth->execute();

while ( my @row = $sth->fetchrow_array ) {
      print "@row\n";
}

Bereft