FreeTDS Development Group

["Andy Clark" <andy AT abednegosystems.com>]

James,

Thanks for your quick response.  I appreciate your suggestions, and
given what I can see on my end, I have no doubt they would all work.
Unfortunately, the system needs to be a test copy of the production site
and I need to recreate the production functionality exactly as it exists
in the main system.  I would happily implement the measures you
recommend if I could, but modifying page code is not an option at this
point.  Having said that, here is the way I need to make this thing work
(see dysfunctional snippet from previous posting below for sample code):
        - DBI->connect('dbi:Sybase:database=mydb', $usr, $pwd);
        - $DSQUERY specifies the data server IP
        - user, password, IP all specified in text "config" file to be
parsed and assigned to page vars

Can you point me in the direction of the "planets" that need to align?
Based on the documentation, I would guess the planets are represented by
ENV variables?

Based on that assumption, I spent some time working through the issue
using the information you provided and I can offer the following as
additional information:
1.  If DSQUERY = "MSSQL" (the server DSN as defined in the interfaces
file) everything works.  Unfortunately, this is not the way the
production site is written, so it is of little value beyond the
troubleshooting data point.
2.  The existing production pages (i.e., unaltered) throw a DBI connect
error.  The freetds log output (thanks for that one, by the way) shows
that the server IP is correct (as specified in the interfaces file), but
the port is 4000 (Sybase default?) instead of the 1433 specified in the
interfaces file.
3.  Setting $TDSPORT = "1433" in the page code results in a
non-responsive page that hangs for 2 minutes or more.  Occasionally the
ultimate result is the death of Apache requiring a "start".  No log
entries show up in the TDSDUMP log.
4.  Removing the TDSPORT line from the page returns the behavior to the
original state, as described in #2 above
5.  Setting $SYBASE = "/usr/local/freetds" in the page code raises the
same DBI connect error, but the TDSDUMP log shows that both the IP and
the port are incorrect.  The IP varies with about 4 different addresses
appearing at random (load balancing at a default freetds test server?)
6.  Again, removing the $SYBASE line from the page returns the behavior
to the original state.

Would reinstalling FreeTDS and/or DBD-Sybase with some specific ENV vars
set hardwire the values to override the defaults?

I didn't think it was possible, but I may be even more confused at this
point than when I started.  Any ideas?

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