CODEWITHSUNDEEP
sql-serveractiveperl
steven hong
steven hong

Reputation: 21

how to connect SQL Server with perl

I know there is a similar question: Connect to SQL Server 2005 from Perl and do a SELECT , but I tried the accepted answer and am unable to get it to work.

Assuming I have a db named test, and would love to do a select from mytable (select id, name from mytable)

Code is from the link above with updated dsn:

use strict;
use warnings;
use DBI;

# Insert your DSN's name here.
my $dsn = 'database=test'

# Change username and password to something more meaningful
my $dbh = DBI->connect("DBI::ODBC::$dsn", 'username', 'password')

# Prepare your sql statement (perldoc DBI for much more info).
my $sth = $dbh->prepare('select id, name from mytable');

# Execute the statement.
if ($sth->execute)
{
    # This will keep returning until you run out of rows.
    while (my $row = $sth->fetchrow_hashref)
    {
        print "ID = $row->{id}, Name = $row->{name}\n";
    }
}

# Done. Close the connection.
$dbh->disconnect;

This is what I got when running the script: Can't connect to data source 'ODBC::database=test' because I can't work out what driver to use (it doesn't seem to contain a 'dbi:driver:' prefix and the DBI_DR IVER env var is not set) at script.pl line 9

Looks like the problem is in the dsn but I have no idea how to fix it (I am on sql 2005, active perl 5.10 and windows xp).

Edit: I used the following code to verified whether ODBC is installed. use DBI;

print join (", ", DBI->installed_versions);

Output: It looks like ODBC is indeed in the list.

ADO, CSV, DBM, ExampleP, File, Gofer, ODBC, SQLite, Sponge, mysql

What am I missing?

Upvotes: 2

Views: 3667

Answers (3)

Yis
Yis

Reputation: 1

Assume SQL server is located on local server, connection below can be right:

my $DSN = "driver={SQL Server};Server=127.0.0.1;Database=test;UID=sa;PWD=123456";
my $dbh = DBI->connect("dbi:ODBC:$DSN");

Upvotes: 0

Izkata
Izkata

Reputation: 9323

I got the same error with SQLite just now, and it looks like you did the same thing wrong as me. Note the number of colons in the first argument - this is the wrong format:

my $db = DBI->connect('DBI::SQLite::dbname=testing.db', '', '', {RaiseError => 1, AutoCommit => 1});

There should actually only be two colons, not two pairs of colons in the first argument:

my $db = DBI->connect('DBI:SQLite:dbname=testing.db', '', '', {RaiseError => 1, AutoCommit => 1});

Question answered despite its age because it's still top of the results in Google for this particular error message

Upvotes: 2

mopoke
mopoke

Reputation: 10685

Try setting your DSN to something like:

my $dbh = DBI->connect("dbi:ODBC:test", 'username', 'password')

If that doesn't work, ensure you have DBD::ODBC installed by running:

perl -MDBI -e 'DBI->installed_versions;'

Upvotes: 0

Related Questions