FreeTDS Development Group

["James K. Lowden" <jklowden AT schemamania.org>]

On Sat, 14 Dec 2002 17:41:20 -0700, "Andy Clark"
<andy AT abednegosystems.com> wrote:
> I have recently inherited a hybrid Linux/SQL Server web
> site and I am in the process of trying to set up a test system, but I
> having trouble getting the FreeTDS/DBD-Sybase connections to work the
> way they are used in the production site.  
[...]
> # test a connection using the config file
> print "<p><b>Attempting to connect using the config file and retrieve a
> table list...</b></p>\n";
> my $database = "/mydir/config.ini";
>     open(OPTIONS,"$database");
>     my @options = <OPTIONS>;
>     close(OPTIONS);
>     my $pwd2 = pop(@options);
>     my $usr2 = pop(@options);
>     my $pwd = pop(@options);
>     my $usr = pop(@options);
>     my $ip = pop(@options);
> chomp($ip);
> chomp($usr);
> chomp($pwd);
> chomp($usr2);
> chomp($pwd2);
> $ENV{DSQUERY}=$ip;   #this line seems to be the key
> $dbh = DBI->connect('dbi:Sybase:database=mydb', $usr, $pwd);
> $sth = $dbh->prepare("select * from information_schema.tables");
> if($sth->execute) {
>     while(@dat = $sth->fetchrow) {
>         print "@dat<br>\n";
>         }
>     }

Andy, 

Welcome to the project.  I always find it gratifying when the
documentation helps people get going.  

BTW, I can't help offering to simplify your perl:

chomp my ($pwd2, $usr2, $pwd, $usr, $ip) = <OPTIONS>;
close(OPTIONS);

> The main factor seems to be whether DSQUERY is assigned the
> value for the data server IP or the DSN defined in the Sybase interfaces
> file - when I assigned DSQUERY to the DSN name ("MSSQL" in this case) it
> works, but if I assign it to the server IP like the production site
> pages do, it fails to connect. 

DSQUERY is a pretty old-fashioned way to indicate the server.  The usual
way nowadays is to include the servername in the "DBI->connect" 
statement.  

In any case -- whether you indicate the servername in the "DBI->connect"
statement or allow FreeTDS to infer it from $DSQUERY, the result is the
same: FreeTDS uses it as a key to find the server's hostname and (finally)
its ip address.  

By setting DSQUERY to the server's address, you guarantee FreeTDS won't
find it (by name) in the interfaces file.  FreeTDS will notice, however,
that the name looks like an address, and in desperation it will try to
connect to that address.  But, because it didn't find the server in the
interfaces file, it can't know anything else about the server, namely its
port and TDS protocol version, and so it will use its built-in defaults. 
For that to be successful, the planets have to be aligned.  

You can set the TDSDUMPCONFIG variable to see how FreeTDS searches for
your server.  I think you'll find that by setting DSQUERY to an address
instead of a name, FreeTDS fails to look up the name, and makes a
last-ditch effort to connect to the address using default parameters. 
Your production environment might be "just so" and thus work, whereas the
test environment might be slightly different, and fail.  

I recommend:

1.  Eliminate the use of DSQUERY.
2.  Add the servername to the DBI->connect string.
3.  Describe the server in $SYBASE/interfaces.
4.  (optional) Convert to freetds.conf.

Regards, 

--jkl