Okay, as it tunrs out maybe it is not all that difficult
afterall... With the help from someone form the FreeTDS developers
team I got it to work. All I had to do was the following:
~~~~~~
Since I just got this set up for myself...
odbc.ini:
[ODBC Data Sources]
TEST = Microsoft SQL Server 2000
[TEST]
Driver = /usr/lib/libtdsodbc.so
Description = TEST Data
Trace = No
Server = 192.168.100.250
Database = TEST
;Port = 4444
TDS_Version = 8.0
----------------
/etc/freetds.conf
[SERVER]
host = 192.168.100.250 # server.test.local
port = 1433
tds version = 8.0
-----------------
#!/usr/local/bin/perl
#
use DBI;
my $dbh = DBI->connect("dbi:ODBC:TEST", 'DOMAIN\user', 'domainpwd',
{PrintError => 1, RaiseError => 1, LongTruncOk=>1});
die "Unable for connect to server $DBI::errstr"
unless $dbh;
~~~~~
My biggest error, other than borrowing unproven connect() syntax from
who-knows-what-google-return, was in not declaring "dbi:ODBC:" in
from of the data source and also in not defining my odbc.ini file.
Many thanks to everyone who contributed to the resolution of this issue
Robert
On Apr 26, 2007, at 4:50 AM, Martin Evans wrote:
Robert Denton wrote:
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?
It is not that difficult but you've taken a free route which is
slightly harder than a few commercial ones. You haven't got the
money for a commercial solution but if you had tried OOB or our sql
server driver they would have checked your Perl was OK, checked DBI
and DBD::ODBC was installed (and if not given recommendations),
created a unixODBC/OOB data source which connected to your actual
SQL Server, demonstrated data coming back through isql and through
Perl and shown you the DBI connection string to use.
Anyway, to your problem:
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
The error is telling you that you failed to specify a database
driver i.e. you have not prefixed your connection string with "dbi"
and then a driver name "ODBC" e.g.
dbi:ODBC:your_odbc_connection_string_goes_here
(see the connect method in the DBI documentation - perldoc DBI)
Fix this first. Then you've got further issues with your ODBC
connection string as the "driver" attribute should be "DRIVER" (as
the ODBC spec says). If you use "driver=" you'll get a unixODBC
error like this:
[unixODBC][Driver Manager]Data source name not found, and no
default driver specified (SQL-IM002)
I'm also assuming you don't really have <> in your connection
string - if you do remove them. So you should end up with:
my $dsn = q/dbi:ODBC:DRIVER={SQL
Server};Server=ip_address;attr=value/;
DBI->connect($dsn, $database_username, $database_password);
where "attr=value" represents all your other attributes.
However, you should note that the driver string in {} needs to be the
exact string name for your driver as it is in the odbcinst.ini file or
unixODBC won't know what driver you mean i.e. your odbcinst.ini file
might look like:
[SQL Server]
Driver = /usr/local/lib/libxxx.so
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>;';
see above.
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.
You'll find a potentially useful tutorial ( Perl DBI/DBD::ODBC
Tutorial Part 3 - Connecting Perl on UNIX or Linux to Microsoft SQL
Server) at:
http://www.easysoft.com/developer/languages/perl/
sql_server_unix_tutorial.html
It uses our driver for examples but most will apply equally to any
ODBC driver.
You may also find the Linux/UNIX ODBC article below useful:
http://www.easysoft.com/developer/interfaces/odbc/linux.html
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
Good luck.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
