campblem has asked for the wisdom of the Perl Monks concerning the following question:

Hello esteemed monks, and thanks ahead of time. We are writing a secure web site in Java on an Ubuntu installation on a linux box that communicates with a MS SQL server (2008 R2) on a Microsoft box. The web site works fine. I now need to pull data from the web site and format it in HL7, and I want to use my beloved Perl. Problem is, I am having a lot of trouble getting Perl to see the DBD installation. (DBI is fine.)
I have tried downloading and installing DBD::ODBC as well as FreeTDS (because I am not thrilled about spending tons of money at Easysoft for their stuff, because I have always downloaded the drivers from CPAN for free).
I am hurting here. I run a bash shell under Debian (Ubuntu) and have set my LD_LIBRARY_PATH to library location: /usr/lib/odbc. I have manually updated the odbc.ini file there with the usual info (host server, database, etc.). Regardless of what I do, a DBI->connect tells me I have not specified the a driver. Here is my current perl code:
I have tried a full path to the driver: 

 #my($driver) = "/usr/lib/libiodbc.so";

[download]
...hard-coding the driver name 
 my($driver) = "ODBC";

[download]
...trying "Proxy" just for grins, as in: 
 my($driver) = "Proxy"

[download]

(DBI->available_drivers tells me it can see both ODBC and Proxy)
The rest of my script reads as: 
 my($host)   = "<my IP address>";
 my($db)     = "myDb\@$host";
 my($port)   = 1433;
 my($dsn)    = "dbi:$driver:database=$db;port=$port" 

my($dbh)  = DBI->connect("dbi:ODBC:$db;$port", "me", "mypassword") || 
+die(DBI->errstr);

[download]

..and I get, regardless: 
[dbi:ODBC:database=myDB@oag7.oag.local:1433]
DBI connect('myDB@host;1433','me','myPassword') failed: [iODBC][Driver
+ Manager]Data source name not found and no default driver specified. 
+Driver could not be loaded (SQL-IM002) at ./sqlTest.pl line 30
[iODBC][Driver Manager]Data source name not found and no default drive
+r specified. Driver could not be loaded (SQL-IM002) at ./sqlTest.pl l
+ine 30.
";

[download]

Suggestions? Thanks!

Replies are listed 'Best First'.
Re: Perl, DBD and SQL Server 2008 (R2)
by roboticus (Chancellor) on Feb 02, 2011 at 23:55 UTC

    From the messages, it sounds like you just need to configure your data sources. I've not used ODBC on a Unix box, so I don't know exactly how it's done. But this link ought to help you out: https://help.ubuntu.com/community/ODBC, and you can google for others.

    Typically, in ODBC, you'll have a connect string something like: "dbi:ODBC:FOO", where FOO is an entry in a configuration table somewhere that describes a database connection. (The link I provided shows a sample for Ubuntu.) If you don't want to set one up, you can also use a connect string where you provide the connection information, something like "dbi:ODBC:driver={FreeTDS};server=localhost;database=Fred", where the driver chunk tells ODBC which particular database driver to use, and the rest of the string is a set of parameters for that driver telling it how to locate the database. So I'd look up the FreeTDS connection string information and either configure a DSN or create the connection string from scratch.

    Update: Here's a link to the FreeTDS ODBC parameters: http://www.freetds.org/userguide/odbcconnattr.htm.

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

[reply]
Re: Perl, DBD and SQL Server 2008 (R2)
by mje (Curate) on Feb 03, 2011 at 09:47 UTC

    Just to add one little bit to the excellent information roboticus provided as it sometimes confuses people. When you use "dbi:ODBC:mydsn" DBD::ODBC actually calls the SQLConnect ODBC API first with the DSN mydsn and only if that fails does it call the SQLDriverConnect ODBC API. The SQLConnect API is old, still called for backwards compatibility and does not support connection attributes of the form "a=b;c=d". If you want to skip the call to SQLConnect and go immediately to SQLDriverConnect then use "dbi:ODBC:DSN=mydsn" or "dbi:ODBC:DRIVER={xxx}".

[reply]
      Thanks so much everyone. We have installed FreeTDS. I can execute, at the command line: 
      tsql -S ODBC -U myDatabaseName

      [download]
      I get prompted for a username and password, and can log in and execute commands against the database. So I know I can connect.
      What I can't find in the documentation is WHERE DBI looks for its available_drivers. For example, I should expect that FreeTDS would show up in my DBI->avialable_drivers() call...but it doesn't.
      We keep updating the configuration in /etc/odbc.ini; now it looks like we need to update /usr/local/etc/freeTDS.conf.
      We will keep looking.
      Many thanks for your kind help.
      Emily
[reply]
[d/l]

        DBI's available_drivers only lists available DBDs e.g., DBD::ODBC, DBD::Pg (the DBDs installed on your machine not ODBC drivers). If you are using DBD::ODBC the DBI method data_sources returns valid ODBC data sources (which I think you are looking for) and this info comes from the ODBC Driver manager and the SQLDataSources ODBC call. This is usually a list of data sources in one or more odbc.ini files. The odbcinst.ini file (if using unixODBC) lists available ODBC drivers on your machine - see the links I gave before - they explain all this.

[reply]

Back to Seekers of Perl Wisdom