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

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

Thread Previous | Thread Next
Robert Denton
April 26, 2007 11:18
Re: Is PERL --> MS SQL really that difficult?
Message ID:
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/
> Description     = TEST Data
> Trace           = No
> Server          =
> Database        = TEST
> ;Port            = 4444
> TDS_Version     = 8.0
> ----------------
> /etc/freetds.conf
>         host = # 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


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=<>;  
>> database=<userFeedback>;uid=<testUser>; pwd=<testPass>;, no  
>> database driver specified and DBI_DSN env var not set at ./ 
>> 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/
>> 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=<>;  
>> 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 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:
> 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:
>> 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 and in  
>> Programming the Perl DBI seem to never include MSSQL examples,  
>> unfortunately.
>> Robert
> Good luck.
> Martin
> -- 
> Martin J. Evans
> Easysoft Limited

Thread Previous | Thread Next Perl Programming lists via nntp and http.
Comments to Ask Bjørn Hansen at | Group listing | About