develooper Front page | perl.dbi.users | Postings from April 2007

Re: Is PERL --> MS SQL really that difficult?

Thread Previous | Thread Next
From:
Martin Evans
Date:
April 26, 2007 01:51
Subject:
Re: Is PERL --> MS SQL really that difficult?
Message ID:
463067E5.1010309@easysoft.com
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

Thread Previous | Thread Next


nntp.perl.org: Perl Programming lists via nntp and http.
Comments to Ask Bjørn Hansen at ask@perl.org | Group listing | About