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

MS SQL Server

by Ovid (Cardinal)
on Aug 29, 2000 at 04:07 UTC ( #30058=perlquestion: print w/replies, xml ) Need Help??

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

I have searched for the answer to this to no avail :(

Does anyone know of a DBD driver specific for Microsoft's SQL Server that I can use with DBI? I am specifically not looking for DBDx::MSSQLReporter, which is standalone. I am also not looking to use DBD::ODBC because my new boss has specifically asked if there was one that can connect directly to the server (to be fair, I'm not familiar enough with ODBC to be sure if this is really an issue or not).

Any information on this subject would be greatly appreciated. I'm also wondering if there is a performance hit with using ODBC as opposed to connecting to the database directly.

Cheers,
Ovid

Replies are listed 'Best First'.
Re: MS SQL Server
by athomason (Curate) on Aug 29, 2000 at 08:26 UTC
    According to the DBI book (the O'Reilly cheetah), the Sybase DBD driver offers some (limited) support for MS-SQL 7 servers with a patch (see related info here). You might also want to check if DBD::FreeTDS is mature enough for your uses yet.

    You said you want to use DBI, but if nothing else works, there's a set of non-CPANed modules called MSSQL::DBlib and MSSQL::Sqllib available here. These don't use DBI and don't claim support for MS-SQL 7, so I'd shy away from those unless really necessary.

Re: MS SQL Server
by gregorovius (Friar) on Aug 29, 2000 at 12:22 UTC
    I threw together the following little benchmark in order to get some numbers on DBD::ODBC peroformance, which is just what I'm working with. I'm running both server and client on a PIII 500 PC with 128 MB of RAM, IDE hard drive, NT Server 4.0 and MSSQL 7.0. No optimization or tunning whatsoever. I would say this numbers are close to the minimum you could expect from a similar system. Performance aside I'd reccommend DBD::ODBC for maximum portability. I would also because I didn't have any problems installing it and it has behaved exactly as expected during development.
    #!/usr/bin/perl -w use strict; use DBI; my $dbh; my $time = time; for(1..999) { $dbh = DBI->connect('DBI:ODBC:sqlkranon', 'sa', '') || die $dbh->err +str; $dbh->disconnect; } $dbh = DBI->connect('DBI:ODBC:sqlkranon', 'sa', '') || die $dbh->errst +r; print "1000 connects in ", time - $time, " seconds\n"; $time = time; my $rows; for(1..10000) { my $sth = $dbh->prepare("select CveAsegSeguro,CveSeguro from C_Asegu +radorasSeguros"); $sth->execute(); while (my @row = $sth->fetchrow_array ) { $rows++; } } $dbh->disconnect; print "10000 selects fetching $rows rows in ", time - $time, " seconds +\n"; __END__ Results: 1000 connects in 15 seconds 10000 selects fetching 30000 rows in 17 seconds
    Good luck!

    Brother Greg
Re: MS SQL Server
by toadi (Chaplain) on Aug 29, 2000 at 12:15 UTC
    Well,
    I wrote a complete database-driven website with ODBC.

    my $DSN = 'driver={SQL Server};Server=212.233.1.70; database=db;uid=us +r;pwd=pwusr;'; my $dbh = DBI->connect("dbi:ODBC:$DSN") or die "$DBI::errstr\n";

    is the connection string. Only problem I had was that special SQL syntax that was just a bit different then mysql or oracle.

    Noticed no speed difference but the database is not yet really heavely taxed...

    I would say go for it!!!!
    This is the best alternative I found!

    --
    My opinions may have changed,
    but not the fact that I am right

Re: MS SQL Server
by lhoward (Vicar) on Aug 29, 2000 at 17:32 UTC
    You didn't say if your client perl program was going to be running on NT or UNIX. If it is going to be running on NT, you should have no problems, DBD::ODBC works just fine and is as good performance-wise because MS Sql is as-fast w/ ODBC as with any other driver:
    use DBI; my $dbh = DBI->connect("dbi:ODBC:MQIS","USER","PASSWORD");
    where MQIS is your ODBC DSN.

    If your client is gonna run on UNIX (or any OS for which MS does not provide client-side ODBC libraries) you have 2 choices:

    • DBD::Proxy - run a proxy DBD on an NT box and have your perl programs use DBD::Proxy to connect to the proxy.
    • 3rd party ODBC driver - there are some 3rd party ODBC drivers for MS Sql server (some commercial, some not) that you could use. I've never used these (don't even know where to get them), but I have heard that they exist :)
      Openlink Software is a company that has a very good generic type
      of ODBC driver, that will connect to just about any type of database,
      whether it is Oracle, MySQL, Informix, MS SQL Server, Solid, etc.

      TStanley
      There can be only one!
        An update to this post..The Openlink drivers work with pretty much any database at the moment, except MS SQL Server. It seems their code has a bug in it, of which they are aware, and it will be fixed in their next release, which is due out within (HOPEFULLY) the next month. The drivers for Oracle work quite well, and the only problem that has been noted, is that you need to use a SQLDRIVERCONNECT to connect to the database, instead of SQLCONNECT. This has been reported to Openlink, and will be addressed in the next release.

        TStanley


        UPDATE (13 NOV 00): The new drivers are out and unfortunately, the same bug is still there.
Re: MS SQL Server
by JanneVee (Friar) on Aug 29, 2000 at 12:02 UTC
    I'm also wondering if there is a performance hit with using ODBC as opposed to connecting to the database directly.

    It is very likely for a small performance hit of using the ODBC. Due to the overhead function calls of DBI -> DBD::ODBC -> ODBC -> MSSQL. But I don't see the reason for a large performance hit.

    Actually I don't see any reason not to use ODBC. Even if it is Microsoft technology but MSSQL and ODBC agree with each other, why shouldn't they... both are Microsoft.

    JanneVee

      I think there IS a real performance problem when you use ODBC instead of a native driver. ODBC is slow. I've never seen a fast ODBC connection.
      Unfortunately I've never seen a native SQL Server driver for DBI. I once had to write some Perl to SQL Server, and we used ADO.pm (I believe Matt Sergeant wrote that one). It worked very well (and faster than DBI::ODBC), but that wasn't what you would want, since it wasn't a DBD-driver.
      There IS a DBI::ADO driver available though, but I've never used it. It's at least faster than ODBC.

      Jouke
        I always have considered ODBC as a low level API to databases. The slowness that I experienced was by the "blocking" API calls. i.e. poorly written code....

        By using ADO you get some performance enhancements, even if the ADO connection goes through OLE DB and that goes through ODBC.

        One performance enhancement I can think of is Persistant connections. That is a thing you've wont get with a native MSSQL driver!

        Janne

Re: MS SQL Server
by wardk (Deacon) on Aug 29, 2000 at 21:06 UTC

    Although your boss may not desire you use Win32::ODBC for performance reasons, I think that you'll find the performance adequate.

    I recently used Perl/ODBC heavily in an NT/Netscape Enterprise/MSSQL 7 environment and was quite pleased with the performance. I also had a direct connect to Oracle on Sun. To the end user, the differences were not noticable.

    If you go with ODBC I would strongly suggest avoiding using DSN's on the boxes, create your connect strings in your programs, then you can move them around without worrying.

    If you find a good direct-connect solution, please provide the information! While ODBC was sufficient, it still requires MDAC and all that being installed in addition to the perl libs.

    good luck!

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://30058]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (4)
As of 2021-12-05 22:49 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    R or B?



    Results (31 votes). Check out past polls.

    Notices?