Is it just me, or is using perl on a linux server to extract
information from an MS SQL server insanely difficult? Has no one
else struggled with this? Here is my set up:
FC4 -> PERL w/DBI -> DBD::ODBC -> unixODBC -> FreeTDS
The irony is that all I need to do is a single simple select from a
single table, and this is something I would run once a day. Yet the
process of getting perl to talk to an MSSQL server seems
disproportionately convoluted.
I am hoping some reader of this list will help me identify where my
attempts are failing. The error that I get when I try to run the
script is this:
Can't connect to data source driver={SQL
Server};Server=<192.168.0.149>;
database=<userFeedback>;uid=<testUser>; pwd=<testPass>;, no database
driver specified and DBI_DSN env var not set at ./feedback.pl line 11
However, my understanding is that the DBI_DSN env var (which I have
not explicitly set, not am I sure what to set it to if necessary) is
only called if the $data_source in the connect() statement is empty.
My connect() statement is as follows:
$dbh = DBI->connect( $data_source, $username, $password );
And my $data_source var is set to this:
my $data_source = 'driver={SQL Server};Server=<192.168.0.149>;
database=<userFeedback>
;uid=<testUser>; pwd=<testPass>;';
I have seen other syntax for the connect statement for other
databases such as MySQL and Oracle but never for MSSQL. I have
searched dbi.perl.org as well for example syntax but all the examples
I can find there are also for non-MS databases. If anyone knows of
an alternative syntax that they know for certain works for MSSQL that
would be terrific.
As for the other part of the error, "no database driver specified", I
am not sure what else I need to do to make perl aware of the FreeTDS
driver. I have 'use DBI;' in the top of the script which is supposed
to be sufficient. In otherword, I do not believe that I need to tell
it to use DBD::ODBC. Further, I specified the install dir for
unixODBC when I installed DBD::ODBC through cpan. Finally, I
registered FreeTDS with unixODBC using template files and odbcinst
commands.
What am I missing here? It seems to me that there is some piece of
the puzzle that I am overlooking that will bring all of this
together. If no one knows the answer I would love to at least see
some pointers to guides/tutorials/examples etc of how this set up is
supposed to work. The examples at dbi.perl.org and in Programming
the Perl DBI seem to never include MSSQL examples, unfortunately.
Robert