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

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

Thread Previous | Thread Next
From:
Robert Denton
Date:
April 26, 2007 11:18
Subject:
Re: Is PERL --> MS SQL really that difficult?
Message ID:
5B9C2A90-F7D6-44C1-8DE9-B9243B7F5E3F@headsprout.com
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



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