[Pdx-pm] Perl DBI DSN string specification

[T. William Schmidt]

Hello All, especially DBI DBD::ODBC and Win32::ODBC experts,

I am writing an application for a client that accesses data in Microsoft 
SQLServers.  Currently I have parallel developments underway using both 
DBD::ODBC and Win32::ODBC on ActiveState Perl, v5.8.3 build 809.  The 
target platforms for the client application are W2K and XP.  The 
application accesses multiple SQLServers concurrently and will be installed 
on many desktop PCs.  This makes ease of installation and admin very important.

I can easily create DSN definitions using the ODBC Data Source 
Administrator in the Control Panel.  My applet based definitions all work 
so the problem is not being able to access those servers.  I prefer instead 
to use the DSN string specification.  Using the applet is very easy for 
someone installing from the keyboard but a pain in the petootie for remote 
installs on Windows.  Windows of course, hides the DSN attributes in 
multiple keys in the registry.  Problem is, there is a dearth of 
documentation on specifying the DSN string.  Most writers on DBI discuss 
the Control Panel applet, because it is so easy and the string method is 
relatively obscure.

Cases in point:

Here is one way to make a connection to an SQLServer using the external DSN 
definition:

use DBI;
my $DSN = 'clesql01';           # logical name given to a data source in applet
my $dbh = DBI->connect("dbi:ODBC:$DSN", 'username', 'userpw',
         { RaiseError => 1, AutoCommit => 1 })
                 or die "$DBI::errstr\n";

or,

use Win32::ODBC;
my $db = new Win32::ODBC("DSN=clesql01;UID=username;PWD=userpw") or die 
Win32::ODBC::Error();

Both of these work just fine but what I prefer to write is something like:

use DBI;
my $DSN = 'driver={SQL 
Server};Server=node.domain.com;database=my_database;uid=username;
         pwd=userpw;';
my $dbh = DBI->connect("dbi:ODBC:$DSN", 'username', 'userpw',
         { RaiseError => 1, AutoCommit => 1 })
                 or die "$DBI::errstr\n";

or, in Win32::;

use Win32::ODBC;
my $DSN = 'driver={SQL 
Server};Server=node.domain.com;database=my_database;uid=username;
         pwd=userpw;';
my $db = new Win32::ODBC("$DSN") or die Win32::ODBC::Error();

I found the following example in the ActiveState documentation, and is the 
basis for those code fragments above.  The problem is, it does not work.  I 
am assuming the example is correct but insufficient, and is missing 
mandatory attributes that the MS ODBC driver requires; for example the 
server port number and the network protocol.  I have been hacking different 
key=value pairs trying to guess what the driver requires, but so far all 
have failed.  The error messages from the driver are not much help.  They 
state the obvious, client failure to connect.

Does anyone know exactly how to specify a DSN string for SQLServer?  Thanks 
in advance for at least reading this post, and any help will be much 
appreciated.

======= From ActiveState Perl documentation ==========
Example (using MSSQL Server):
       my $DSN = 'driver={SQL Server};Server=server_name;
       database=database_name;uid=user;pwd=password;';
       my $dbh  = DBI->connect("dbi:ODBC:$DSN") or die "$DBI::errstr\n";



Regards,
Will Schmidt

WilliamSchmidt.com, LLC
11201 NW 77th Street
Terrebonne, OR  97760
541 504-0290
will at williamschmidt.com
http://www.williamschmidt.com/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.pm.org/pipermail/pdx-pm-list/attachments/20040516/4537c7bd/attachment.htm