Irishboy24 has asked for the wisdom of the Perl Monks concerning the following question:

Hi monks, I am trying to connect to a SQL Server 2008 local db on my computer. i've written a small perl script using the DBI module. I am running Activate State Perl v5.8.8. Here is the code. Note that my server name is "MADDY" and no pw to connect. Note that i also installed the DBI module recently using ppm. 

use DBI;

 $dsn = "MADDY";
 $user = "Maddy17";
 $pw = "";
 $dbh = DBI->connect('$dsn,$user,$pw');
 $sth = $dbh->prepare ("select * from sysobjects where name        = '
+pub_titles' and type = 'P' and uid = USER_ID()");
 $sth->execute();
while ( @row = $sth->fetchrow_aray ) {
    print "@row\n";
    }
 $dbh->disconnect();

[download]

The error it throws at command line is as follows: "Can't connect to data source '$dsn,$user,$pw' because I can't work out what driver to use (it doesnt seem to contain a 'dbi:driver:' prefix and the DBI_DRIVER env var is not set) at data.pl line9"

Replies are listed 'Best First'.
Re: DBI Module Question
by toolic (Bishop) on Sep 29, 2009 at 14:25 UTC
    $dbh = DBI->connect('$dsn,$user,$pw');
    Get rid of the single quotes because they do not interpolate variable values. You are passing a single literal string to connect, not 3 separate values. Try this: 
     $dbh = DBI->connect($dsn,$user,$pw);

    [download]
[reply]
[d/l]
[select]
Re: DBI Module Question
by marto (Cardinal) on Sep 29, 2009 at 14:35 UTC
[reply]
[d/l]
Re: DBI Module Question
by Fletch (Bishop) on Sep 29, 2009 at 14:26 UTC

    You seem to be having problems both with reading the error message and with basic Perl syntax.

    • Single quotes don't interpolate; you would want to use "$foo" instead.
    • However even changing the single quotes wouldn't fix your problem since connect wants three distinct arguments, not one.
    • The format of your DSN doesn't match what DBI expects as the error message says; you need to read the documentation for the DBD driver for your particular database and find out what format it should take.

    The cake is a lie.
    The cake is a lie.
    The cake is a lie.

[reply]
[d/l]
[select]
Re: DBI Module Question
by Tux (Canon) on Sep 29, 2009 at 14:36 UTC

    And besides the useful comment already passed, a DSN is not in the form of a hostname, but in the form of a DSN, as the name already suggest, and these differ per database type. I have no idea how that would look for SQL Server 2008 as I don't have that

     $dbh = DBI->connect ("dbi:Pg:dbname=test", $usr, $pass, { AutoCommit 
+=> 1 });
 $dbh = DBI->connect ("dbi:CSV:", undef, undef, { f_dir => ".", PrintE
+rror => 1 });
 $dbh = DBI->connect ("dbi:Oracle:host=MADDY", $usr, $pass, { FetchHas
+hKeyName => "NAME_lc" });
 $dbh = DBI->connect ("dbi:mysql:database=gigo", $usr, $pass, { RaiseE
+rror => 1 });
 $dbh = DBI->connect ("dbi:SQLite:dbname=db/db", "", "", { ChopBlanks 
+=> 1 });

    [download]

    Enjoy, Have FUN! H.Merijn
[reply]
[d/l]
      Is it just me or would the DBI connect syntax make a lot more sense if instead of concatenating a whole bunch of stuff together, it would just accept named arguments? 
      my $dbh = DBI->connect(
  driver   => 'Pg',
  dbname   => 'foo',
  host     => 'foo',
  user     => 'bar',
  password => 'something'
);

      [download]
[reply]
[d/l]

        For me it would not, but I might be biased. A simple reason might be that in my case the DSN is read from $ENV{DBI_DSN}, as I work in an environment where I use the same content on many different database types, and my scripts should not care about the way to connect to the source.

        So the first three of your parameters I would never use. Then there is the freedom (shrug) of the DBD authors to implement the DSN in a way that best matches the underlying database, which is why you will see variations in 'database=', 'db=', 'dbname=' etc. It would be a disaster to build all that in DBI, which is just the API to all the DBD modules.

        User and password make sense to most databases, but certainly not to all.

        Personally, I think that $dbh = DBI->connect (dsn, usr, pw, { attr }); is a very good compromise that is easy to remember.


        Enjoy, Have FUN! H.Merijn
[reply]
[d/l]
Re: DBI Module Question
by runrig (Abbot) on Sep 29, 2009 at 15:00 UTC
    Keep in mind there's a difference between the dsn you set up under control panel...data sources(ODBC), and the dsn that dbi expects. If you set up a dsn under control panel named 'MADDY', then you can probably connect with:
    my $dsn = "MADDY";
my $user = "Maddy17";
my $pw = "";
my $dbh = DBI->connect("dbi:ODBC:$dsn", $user, $pw, {
  RaiseError => 1,
});

    [download]
    Also, use RaiseError as in the above connect statement.
[reply]
[d/l]
Re: DBI Module Question
by Irishboy24 (Sexton) on Sep 29, 2009 at 15:43 UTC

    Hey Guys, Thank you all for taking time to solve my issue. All of you have been really helpful. i tried connecting to the db using a diff driver and got the expected result. even though i am puzzled as to why DBI doesnt work. i am happy that Win32::ODBC works. here is the code. 

    use Win32::ODBC;

 $connection = new Win32::ODBC("SQL SERVER") or die "Could not open co
+nnection to DSN because of [$!]";

$SQL = "select * from pubs.dbo.authors where au_lname = 'green'";

$connection->Sql($SQL);

while($connection->fetchrow())
{
 @datarow = $connection->Data();
print "@datarow\n";

}
$connection->Close();

    [download]
[reply]
[d/l]
      Just keep in mind that Win32::ODBC is less used, so you will be less likely to find help with it (as quickly anyway) if needed. Unless you really need some feature of Win32::ODBC (and that is less likely with the latest versions of DBI and DBD::ODBC), I'd stick with DBI.
[reply]

Back to Seekers of Perl Wisdom