in reply to Re: Re: Re: Win32::ODBC vs. Win/MSSQL 2000
in thread Win32::ODBC vs. Win/MSSQL 2000

Well, snot. So all this talk about DBI finally convinced me to give it a shot, and I've ended up right back at the same place I started. Here's what I've got right now:
use strict;
use DBI;
my $db = DBI->connect('DBI:ODBC:myDSN')
    or die "Could not connect to database: " . DBI->errstr;

[download]

and I get squat. Actually, I get the same, "Login failed for user '(null)'" error as before. Now, if I do:
use strict;
use DBI;
my $db = DBI->connect('DBI:ODBC:myDSN','user','passwd')
    or die "Could not connect to database: " . DBI->errstr;

[download]

It works just great. I can fetch data, the sun comes out, etc. So am I missing the blindingly obvious? Is it just not possible to save the Username and Password in the DSN?
I'm going to continue to scour the web for help, but so far every example I've seen hard codes the username and password.

Thanks again,
Ardenstone

Replies are listed 'Best First'.
Re: Re: Re: Re: Re: Win32::ODBC vs. Win/MSSQL 2000
by growlf (Pilgrim) on Nov 21, 2001 at 14:27 UTC

    Sorry I took so long to reply. I was both out of touch for a few days and I wanted to be sure of my answer so as not to lead you astray.

    To that end, I have tested several theories about this scenario - and here is what I have found:

    When I used a MyODBC driver (for - MySQL on the windows platform) it worked just fine - no hitches at all when I did not spell out the user id and pass in the connect.

    However, the same code on a *nix box using the *nix ODBC (a BSD box in particular) it worked too!

    This got me to thinking - what about a non-MySQL/MyODBC connection.. like with MS-SQL specifically ..and guess what. It failed without the user/pass.

    My hunch was this: the part that had been working for me was that I had been using the MyODBC driver not the standard ODBC for Access or MS-SQL. Unfortunately, there is little to no choice on ODBC drivers for MS-SQL on the Windows platform as far as I know.

    I tried several combinations of things to get it to work, but to no avail. I even tried using named-pipes and all the other protocols with MS-SQL as well - nothing worked.

    It appears that the Microsoft ODBC driver does not pass on the DSN information as well as the MyODBC version does. What is the point in specifying the user and pass in the DSN if it wont USE it?  At this point I got frustrated and finally gave in and put a call in to the help team at MS (I still get free calls there thank goodness). 

    According to them, the login info on the MS-SQL ODBC driver is ONLY used for initial configuration to gather the setup information and then is discarded before the ODBC connection is saved. Whereas, the ORACLE ODBC driver works more like the MySQL version as well. Oddly enough, their own spec for ODBC drivers seems to dictate that it CAN (should) save the authentication information.

    I hope this helps - I know that I sure learned something new.

    *G*
[reply]

In Section Seekers of Perl Wisdom