in reply to
Re: What is the best Perl library to use to connect and access MSSQL2008 Database from winXP PC?
in thread What is the best Perl library to use to connect and access MSSQL2008 Database from winXP PC?
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.
Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)