Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

What is the best Perl library to use to connect and access MSSQL2008 Database from winXP PC?

by blackadder (Hermit)
on Oct 12, 2009 at 11:50 UTC ( #800680=perlquestion: print w/ replies, xml ) Need Help??
blackadder has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks, greetings

I need to connect to a MSSQL2008 database from my winXP laptop. I have googled and searched on PM for an answer, but I got a bit confused!

I wasn't sure wether to go for:

DBI, or DBD::ODBC (am I correct in assuming those two packages will only work from Linux/Unix box?)

Win32::ODBC (some say its no longer supported by Mssql2008)

Win32::ADO or Win32::OLE (which is better!? but I thought one issues ADO command using Win32::OLE)

Or Win32::Sql (Does it work with MSSQL2008? And I could NOT install win32::sql package as it was not found in any of my repositories in PPM - which contains all PPM respositories that i know of!)

Can someone please enlighten me??THANKS!



Blackadder

Comment on What is the best Perl library to use to connect and access MSSQL2008 Database from winXP PC?
Re: What is the best Perl library to use to connect and access MSSQL2008 Database from winXP PC?
by mje (Deacon) on Oct 12, 2009 at 12:04 UTC

    I would say DBI and DBD::ODBC but I am the current maintainer of DBD::ODBC. You can use those modules on Windows and so long as you can get hold of a SQL Server ODBC driver or native client driver (both available from MS) you should have no problems.

      I am the current maintainer of DBD::ODBC

      WOW, thank heavens for that :-)

      Ok, I wonder if you can help me please (this may seem very simple but I am having kittens already)

      I have this script
      #! c:/perl/bin/perl.exe # # # # # use strict; use DBI; use DBD::ODBC; use DBD::ADO; use Win32::OLE; use Win32::ODBC; #use Win32::SqlServer; my $server = "ABCB00062\\Dev"; my $dbusername = "uk_ggg\\xyz12345"; my $dbpassword = '7seven7'; my $database = 'ukdevxxxx'; my $Error_Message = "\nThere Was A Problem Connecting To The Database\ +n"; my $dbh; #Database Handler #$dbh = DBI->connect("DBI:ODBC:driver={SQL Server};Server=$server;Data +base=$database;UID=$dbusername;PWD=$dbpassword") || die "$Error_Mess +age $DBI::errstr"; $dbh = DBI->connect("DBI:ODBC:dsnname;Server=$server;Database=$databas +e;UID=$dbusername;PWD=$dbpassword")
      But when I run it i get the following error
      U:\Doccuments\Scripts\Start>db2.pl DBI connect('dsnname;Server=ABCB00062\Dev;Database=ukdevxxxx;UID=uk_gg +g\\xyz12345; PWD=7seven7','',...) failed: [Microsoft][ODBC Driver Manager] Data sou +rce name not found and no default driver specified (SQL-IM002) at U:\Doccu +ments\Scri pts\Start\db2.pl line 32
      I thought I had all the requirements in place! Would it be possible for you please to advise me on what I need to do? Thanks in advance

      Blackadder

        Try "DRIVER=" instead of "driver=" - this attribute is case sensitive in ODBC.

Re: What is the best Perl library to use to connect and access MSSQL2008 Database from winXP PC?
by uwevoelker (Pilgrim) on Oct 12, 2009 at 12:23 UTC
    DBD::ODBC works for me very stable and reliable.

    The problems start with Linux clients. But the commercial Easysoft SQL Server ODBC driver works quite well.
Re: What is the best Perl library to use to connect and access MSSQL2008 Database from winXP PC?
by Bloodnok (Vicar) on Oct 12, 2009 at 12:28 UTC
    i can't (and won't) argue with mjes suggestion since I have not used the ODBC DBI.

    I would, however, say that when last I was tasked with talking to M$ Server, Win32::ODBC worked well ... once I'd ironed out the, not entirely unexpected, Windoze peculiarities & foibles with the aid of Dave Roths' excellent website & ODBC homepage - his books were also of great help.

    A user level that continues to overstate my experience :-))

      I've worked with both Win32::ODBC and DBD::ODBC, and I think Win32::ODBC is a bad choice for new projects, even small ones. It restricts you to the Win32 platform, whereas DBI allowes you to move to any platform supported by Perl. The API requires manual error checks, whereas DBI can throw exceptions as needed. And finally, it gives you heaps of wrappers, abstracting away the native interface AND its speed. DBI allowes you to switch to a native driver whenever you like, often giving you a nice speed boost over ODBC drivers. The only DB interface with more bloat would be DBD::ADO wrapping ADO wrapping OLE wrapping ODBC wrapping the native API.

      Also, I don't see one bit of documentation regarding Unicode in Win32::ODBC, so I think Unicode is not supported at all in Win32::ODBC. The major DBI drivers do have Unicode support, at least DBD::Pg, DBD::Oracle, DBD::mysql, and of course DBD::ODBC.

      Having worked with SQL Server, Oracle and PostgreSQL, I would always recommend PostgreSQL.

      • MS SQL Server uses a very limited communication protocol that either limits you to one active connection or requires drastic changes to your code. It also has the very nasty habbit to deadlock when working with several tables and triggers at once. And there is no fast native driver available. You can club DBD::Sybase and FreeTDS to talk to MS SQL, but it only works "by accident".
      • Oracle is a nice database engine, but the bloat that is also installed makes me sick. It bundles everything including a coffee machine and the kitchen sink, and it gets worse with every new version. And you pay for the bloat. There is a native driver, but DBD::Oracle cannot be bundled with the Oracle client code due to license restrictions, so you need a compiler or someone who does not care violating the license conditions.
      • PostgreSQL is free as in speech and free as in beer, clean, fast, and of course, it has a native driver. (In fact, there are two: One typical XS-based DBD::Pg, and a pure-perl DBD::PgPP that does not need any PostgreSQL code and does not need a compiler.) To control PostgreSQL, you typically use the command line utility or the separate PgAdminIII. Since v8.0, Pg runs natively on Windows, so using Windows is no longer an excuse not to use Pg.
      • MySQL is just too far away from a reasonable database. It has lots of optimizations for some special cases, several even violate what one can expect from a RDBMS. I've read the docs, and was scared away by the heaps of special cases, shortcuts, implementation details that affect the overall behaviour, and premature optimizations. You get what you paid for. At least, there is a native driver.

      Alexander

      --
      Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
Re: What is the best Perl library to use to connect and access MSSQL2008 Database from winXP PC?
by roboticus (Canon) on Oct 12, 2009 at 12:44 UTC
    blackadder:

    I also use DBI with DBD::ODBC on Windows to talk with MS SQL Server 2008 from WinXP. It works just fine. I've heard complaints of "slow performance" from ODBC and SQL Server, but have seen no evidence of it in my applications for the last 10 years.

    The others likely work fine too, but as I also do work on Linux, etc., I stick with the cross-platform tools...

    ...roboticus
      Ok it seams to work - only a little bit thu - but I get this error!!
      U:\Doccuments\Scripts\Start>db2.pl DBI connect('dsnname;Server=ABCB00062\Dev;Database=ukdevxxxx;UID=uk_gg +g\\xyz12345; PWD=7seven7','',...) failed: [Microsoft][ODBC Driver Manager] Data sou +rce name not found and no default driver specified (SQL-IM002) at U:\Doccu +ments\Scri pts\Start\db2.pl line 32
      How do I over come this driver error? What do I need to do?

      Thanks
      Blackadder

        Do not post your personal database connection details!



        Update your post.
Re: What is the best Perl library to use to connect and access MSSQL2008 Database from winXP PC?
by bart (Canon) on Oct 12, 2009 at 19:28 UTC

    I mainly have one piece of advice, and it's the same for about 10 years now:

    ignore everything that is not running under DBI

    Now that means you still have to choose a DBD driver. These are the options I know of, and that I consider viable:

    • DBD::ODBC: Works very well, especially from Windows to Windows, but you may have to install some extra software to make it make connections from a Linux client.

    • DBD::Sybase: Allegedly MS SQL Server is based on the Sybase SQL server engine, and this driver ought to be useable for MS SQL Server too. However, this has always been a bit iffy and I sincerely doubt that the situation can be improving. I really don't expect it to: after all the two products are gradually growing apart.

    • DBD::ADO: I never got this to work, but allegedly some people are quite happy using it.

    Personally, I'd (still) go for DBD::ODBC.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://800680]
Approved by wfsp
Front-paged by wfsp
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (8)
As of 2014-08-23 18:25 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (176 votes), past polls