Perl DBI SQL SERVER CONNECTIVITY ISSUES

Question

Very new to Perl. I am having issues trying to get DBI to communicate to a SQL server 2008 DB.

I get the following error when I try and connect to SQL Servereveb when I try to use ODBC or directly.

I am new to Perl, can someone please assist...thanks

install_driver(MSSQL) failed: Can't locate object method "set_sql" via package "Class::DBI::MSSQL" at C:/Perl/lib/DBD/MSSQL.pm line 79. Compilation failed in require at (eval 19)C:/Perl/site/lib/DBI.pm:744 line 3.

use strict; 
use warnings;
use diagnostics;
use Class::DBI::Loader;   

use DBI;

use File::Glob ':glob';


my $DBUserName                     = "*******";
my $DBPassword                     = "*******";
my $DBName                         = "dbi:MSSQL:uat-dbserver1";

my $dbh                         = "";
my $sqlStatement                 = "";
my $sqlCmd                         = "";

my @EasySetTableNames            = ();



$dbh = DBI->connect( $DBName, $DBUserName, $DBPassword,
    { PrintError => 0, AutoCommit => 0})
        || die "Database connection creation failed: $DBI::errstr\n";

$sqlStatement = "SELECT * from tableA ";
$sqlCmd = $dbh->prepare($sqlStatement);
$sqlCmd->execute();
@EasySetTableNames = @{$dbh->selectcol_arrayref($sqlStatement)};
print "hi";

and via ODBC

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

use DBI;

# Replace datasource_name with the name of your data source.
# Replace database_username and database_password
# with the SQL Server database username and password.
my $data_source = "dbi:MSSQL:test";
my $user = "test";
my $password = "test";

# 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";

# This query generates a result set with one record in it.
my $sql = "SELECT 1 AS test_col";

# Prepare the statement.
my $sth = $dbh->prepare($sql)
    or die "Can't prepare statement: $DBI::errstr";

# Execute the statement.
$sth->execute();

# Print the column name.
print "$sth->{NAME}->[0]\n";

# Fetch and display the result set value.
while ( my @row = $sth->fetchrow_array ) {
   print "@row\n";
}

# Disconnect the database from the database handle.
$dbh->disconnect;

Any help you can provide would be so appreciated.

Re "Does DBI support SQL SERVER directly?" It's up to the individual DBD. Most don't know about ODBC. DBD::ODBC is the obvious exception. — ikegami
Documentation for Class::DBI::MSSQL indicates that is uses DBD::ODBC under the hood. — tjd
Possible alternatives to DBD::ODBC might include DBD::Sybase ( which I would not recommend) or DBD::ADO. — tjd

tweaking tweaking · Accepted Answer · 2013-11-25T19:49:33

I usually use the ODBC driver from within the dbi and this is how I would usually hit sql server (2008 r2)

    #!/export/appl/pkgs/perl/5.8.4-fiq/bin/perl
    #!/usr/bin/perl
    #!/bin/sh

    use strict;
    use DBI;
    use Time::localtime;
    use Data::Dumper;

    my $dsn = 'DBI:ODBC:Driver={SQL Server}';
    my $host = 'xxx\yyy';
    my $database = 'testing';
    my $user = 'user';
    my $auth = 'password';


    my $dbh = DBI->connect("$dsn;Server=$host;Database=$database", $user, $auth) or die "Database connection not made: $DBI::errstr";

    my $sql = "EXECUTE database.schema.sproc";
    my $stmt = $dbh->prepare($sql);
    $stmt->execute();   
    $stmt->finish();
    $dbh->disconnect;

Perl DBI SQL SERVER CONNECTIVITY ISSUES

2 Answers