Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight

SQL Server

by Losing (Acolyte)
on Nov 09, 2006 at 15:23 UTC ( [id://583120] : perlquestion . print w/replies, xml ) Need Help??

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

Ive been asked to configure some SQL Server databases with Perl. Ive been researching my options, and I cannot really find the best way to do this. There seem to be some different alternatives:
  • Win32:SqlServer
  • Win32:ODBC
  • DBI
Which is the best? I can't seem to find SQL Server support for any of these. I need to configure SQL Server 2000 and SQL Server 2005 databases. Thanks

Replies are listed 'Best First'.
Re: SQL Server
by jonadab (Parson) on Nov 09, 2006 at 15:46 UTC

    You're obviously going to want DBI, and an appropriate DBD module or whatever so it can talk to the database in question. When someone asks you to set things up so that Perl can talk to a database, DBI is what they want.

    I too need to get this set up (with SQL Server 2000), and I guess the real question is, what DBD module is most appropriate for this scenario? Should we be using DBD::ODBC somehow, or is DBIx::MSSQLReporter the way to go? Something else?

    Sanity? Oh, yeah, I've got all kinds of sanity. In fact, I've developed whole new kinds of sanity. Why, I've got so much sanity it's driving me crazy.
      DBI it is, thank you very much. I will now look into whether or not there exists an appropriate DBD. Thanks
        DBD::ODBC works very well for MS-SQL Server, at least on Windows. (See other replies for access from Unix).

        Allegedly, DBD::Sybase would work well to bypass ODBC, as MS-SQL Server is based on the (old) Sybase database. But I think I remember having heard to have its actual working for MS-SL Server is getting flakier.

        p.s. I found this parapgraph in the DBD::Sybase docs: Using_DBD::Sybase_with_MS-SQL

Re: SQL Server
by imp (Priest) on Nov 09, 2006 at 16:40 UTC
Re: SQL Server
by Herkum (Parson) on Nov 09, 2006 at 15:46 UTC
    Use DBI. You will need to specific a driver to connect to the SQL Server. I would guess you can use an ODBC connection, but stick with DBI.
Re: SQL Server
by derby (Abbot) on Nov 09, 2006 at 16:07 UTC

    You don't say what the clients are. For *nix, a combination of DBI, DBD::Sybase and FreeTDS would work. For windows, DBI, DBD::ODBC and SQLServer client libs should work.

Re: SQL Server
by Melly (Hermit) on Nov 09, 2006 at 16:14 UTC

    My SQL Server code tends to look something like this:

    use strict; use DBI; my $database = 'northwind'; my $login = 'sqluser'; my $password = 'sqlpw'; my $host = 'OURSQLSERVER'; my $data_source = "dbi:ODBC:driver={SQL Server};Server=$host;database= +$database;uid=$login;pwd=$password;"; }

    <update>IIRC you need to install DBD::ODBC - and I'm assuming you're running your perl on a Win OS...</update>

    Tom Melly,
Re: SQL Server
by markh (Scribe) on Nov 09, 2006 at 16:07 UTC
    You will definitely want to use DBI (as others have said). As for which driver, I will say I've had good luck with using DBD::Sybase in talking to SQL Server 2000 here. You need to install the FreeTDS drivers as well. See for more details about how this works.
Re: SQL Server
by jfroebe (Parson) on Nov 09, 2006 at 20:21 UTC
      Jason is spot-on regarding Linchi Shea's book. I stumbled on this book at Borders a year ago and bought it. It's one of my most worn and beloved perl books. Linchi's script examples and downloadable Perl modules are superb for SQL Server and Win32 management.
Re: SQL Server
by weiqk (Initiate) on Nov 09, 2006 at 16:58 UTC
    Win32:ODBC, I think