[Pdx-pm] Perl DBI DSN string specification
T. William Schmidt
will at williamschmidt.com
Sun May 16 13:55:58 CDT 2004
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
More information about the Pdx-pm-list
mailing list