Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

jdavidb (1361)

jdavidb
  (email not shown publicly)
http://voiceofjohn.blogspot.com/

J. David Blackstone has a Bachelor of Science in Computer Science and Engineering and nine years of experience at a wireless telecommunications company, where he learned Perl and never looked back. J. David has an advantage in that he works really hard, he has a passion for writing good software, and he knows many of the world's best Perl programmers.

Journal of jdavidb (1361)

Thursday February 05, 2004
09:01 AM

DBD for MS SQL Server

[ #17225 ]

Anyone know off hand what the "preferred" method for accessing SQL Server from DBI is? Should I be going the ODBC route? Or is SQL Server an assimilated product with another former name for which a DBD exists?

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • DBD::ODBC (Score:3, Informative)

    by gtod (2306) on 2004.02.05 9:58 (#28078)

    We use DBD::ODBC and have been very happy. We connect like this (took me a while to work out so hope this helps...)

    my $dsn = join "", (
    "dbi:ODBC:",
    "Driver={SQL Server};",
    "Server=gort;",
    "UID=gpd;",
    "PWD=gpd;",
    "Database=gort_db",
    );

    my $user = 'gpd';
    my $passwd = 'gpd';

    my $db_options = {
    PrintError => 1,
    RaiseError => 1,
    AutoCommit => 0, #Use transactions
    };

    my $dbh =
    DBI->connect($dsn, $user, $passwd, $db_options)
    or exit_msg("Can't connect: $DBI::errstr");

    Sorry about the formatting. The 'code' tag doesn't do what I'd like...

    • Thanks for the help, and even more for the sample code! You'll probably find pudge's wonderful ecode tag will help you out.

      --
      J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
    • You realize, of course, that with RaiseError set, your code will never hit that call to exit_msg(), die'ing before it gets there if there are any problems with the connect.
      • You realize, of course, that with RaiseError set, your code will never hit that call to exit_msg()

        That certainly hasn't been my experience - perhaps things have changed with recent versions of DBI. I understood that RaiseError was a property of the database handle object which wouldn't exist until the connect method had returned successfully. Errors during the connect itself are handled by returning undef and storing the error message in $DBI::errstr.

        • I think you have misunderstood. My understanding is that if you have RaiseError set in the options you pass to the connect method, connect will die if unsuccessful. I just ran a short experiment to check, and it is true for Oracle, at least when you attempt to log in to an instance with an incorrect password. YMMV with other drivers or other specific errors, I suppose.

          I now connect always with RaiseError => 1, PrintError => 0 (to avoid duplicates; DBI docs say to do this, btw), and AutoCommit =

          --
          J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
        • perhaps things have changed with recent versions of DBI.

          It probably was different at one time. Found this in the Change log:

          Changes in DBI 0.91,    10th December 1997

            NOTE: This fix may break some existing scripts:
            DBI->connect("dbi:...",$user,$pass) was not setting AutoCommit and PrintError!
            DBI->connect(..., { ... }) no longer sets AutoCommit or PrintError twice.
            DBI->connect(..., { RaiseError=>1 }) now croaks if connect fails.

          • Thanks very much for that piece of detective work. The 'or die' on my connect calls is obviously a piece of baggage I've been carrying far too long.

            • You must undergo the cleansing ritual of purging cargo cult code!!! :)

              Could be worse. I still deal with programs from people who wrote open FILE, "$filename" || die "Cant open file", leaving the apostrophe out of can't because they never understood the difference between single and double quotes, and the folklore persisted to nearly every programmer here except me that you couldn't use contractions in the die statement...

              --
              J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
  • ... should work. MS-SQL was orignally SYBASE and both MS-SQL and SYBASE "talk" via TDS. Of course there are some gotchas (just google dbd sybase ms-sql).
    --
    -derby
    • Thanks. I thought I had heard something like that, but couldn't remember that Sybase was the DBMS in question, so I couldn't find anything with google.

      --
      J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
  • It took me awhile to figure out, but whenever I need to connect to several different databases of the same type through DBD::ODBC, I create a file dsn for the first one, then copy and paste the contents of the file (from c:\Program Files\Common Files\ODBC) into the program to use as a template. E.g., for SQL server (on my local machine) it was:

    my $dsn=<<EOT;
    DRIVER=SQL Server
    DATABASE=$dbname
    APP=Microsoft Open Database Connectivity
    SERVER=(local)
    Description=paxl
    EOT

    $dsn =~ tr/\n/;/;
    my $dbh = DBI->con

    • I should also mention that you can create a system dsn and just use that name directly in the "dbi:ODBC:$dsn" string, but that gets to be a hassle when there are alot of databases, or if you need to port the code to another machine(s) where the dsn's are not set up.
  • Then here's another vote for DBD::ODBC.

    I started off using DBD::Sybase, but it has some problems (with exceptions IIRC) when talking to MS SQL Server.

    For the ODBC layer I use unixODBC and of course freeTDS.
    • So either way I'm going to need to install freeTDS. Looks like I need to start there.

      This is from Solaris, if it makes any difference. (All Unices are the same to me, though; and usually to the code.)

      --
      J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers