[freetds] intermittent empty results

Thompson, Bill D (London) bill_d_thompson at ml.com
Mon Jun 28 09:36:06 EDT 2004


David,

> my $dbh = DBI->connect($dsn, $USER, $PASSWORD) or die 'connect';
> my $sth = $dbh->prepare($sql);
> $sth->execute;

At the moment I'm a *little* sceptical (without any hard and fast evidence
yet) of the "prepare" functinality within ct-library.
I'm not familar with using DBI::Sybase myself. 
Is it possible to submit the sql without using "prepare" ?

Bill


> -----Original Message-----
> From:	David Barnwell [SMTP:david.barnwell at well.ox.ac.uk]
> Sent:	25 June 2004 20:08
> To:	freetds at lists.ibiblio.org
> Subject:	[freetds] intermittent empty results
> 
> Hi,
> 
> We have been using freetds successfully for four months on a website that
> displays medical research data. The database server runs SQL Server 2000,
> and the webserver (and database client) is Linux.
> 
> However, the website has an intermittent problem: occasionally, it
> incorrectly returns an empty data set. It usually returns the correct data
> if the user clicks again.
> 
> After a lot of investigation, I have managed to reproduce this problem
> consistently with a simple perl script that prints one column from a table
> using the following SQL:
> 
>     SELECT assay
>     FROM xxxxxx.xxxxx.assay
>     where [assay description] is not null
>     order by 1
> 
> The script seems to be upset by certain server activity. If I drop and
> recreate my table on the server, then the first time I run the script on
> the
> client I just get one line containing "0". Thereafter, it's OK.
> 
> The problem occurs whether I call the script from CGI or from the command
> line. It occurs with TDSVER = 4.2, 7.0 and 8.0. It still occurs if I
> reduce
> the select to a single line (using SELECT top 1).
> 
> This is what the TDS log shows. In the error case, the server always
> returns
> an extra 5 bytes (hex 79 00 00 00 00) at the start of the data stream. The
> log then shows:
> 
>     processing result tokens.  marker is  79(RETURNSTATUS)
>     ct_results() process_result_tokens returned 1 (type 4043)
>     generating return status row. type = 56(int), varint_size 0
> 
> Shortly after that, a ct_cancel is sent and we get repeated calls of
> "tds_process_default_tokens" until the incoming data is exhausted, rather
> than repeated "processing row tokens" that we see in the in the normal
> case.
> 
> In the error case, the log ends with:
> 
>   tds_process_end: more_results = 0
>     was_cancelled = 1
>     error = 0
>     done_count_valid = 0
>   tds_process_end() state set to TDS_IDLE
>   ct_close()
>   ct_con_drop()
>   ct_cmd_drop()
> 
> Any ideas or help would be gratefully received!
> 
> Many thanks,
> David Barnwell
> 
> Database Developer/Systems Administrator
> Wellcome Trust Centre for Human Genetics
> University of Oxford, Roosevelt Drive, Oxford OX3 7BN, UK
> 
> --------------------------------------------------------------------------
> -
> 
> (1) Server software:
> Windows 2000 5.00.2195 (Service Pack 4)
> Microsoft SQL Server  2000 - 8.00.818 (Intel X86)
> 
> (2) Client software:
> Red Hat Enterprise Linux 3
> perl 5.8.0
> Apache 2.0.46
> Apache-DBI-0.94
> DBD-Sybase-1.02
> freetds-0.62.1 (loaded from freetds-0.62.1-1.i586.rpm)
> 
> (3) The server SQL that triggers the problem:
> drop table assay
> select * into assay from copy_assay
> grant all on assay to xxxxxx
> 
> (4) The perl script that shows the problem:
> #!/usr/bin/perl -w
> use strict;
> use DBI;
> 
> $ENV{'TDSVER'} = '4.2';
> $ENV{'TDSDUMP'} = "/tmp/dbtest.log";
> $ENV{'TDSDUMPCONFIG'} = "/tmp/dbtest.config";
> 
> my $HOST     = 'xxxxxx.xxx.xxx';
> my $USER     = 'xxxxxx';
> my $PASSWORD = 'xxxxxxxxxxx';
> 
> my $dsn = "DBI:Sybase:server=$HOST" ;
> 
> my $sql = "
>   SELECT top 1 assay
>   FROM xxxxxx.xxxxx.assay
>   where [assay description] is not null
>   order by 1
> ";
> 
> my $dbh = DBI->connect($dsn, $USER, $PASSWORD) or die 'connect';
> my $sth = $dbh->prepare($sql);
> $sth->execute;
> 
> while (my @row = $sth->fetchrow_array) {
>   print join "  ", @row;
>   print "\n";
> }
> 
> $sth->finish;
> $dbh->disconnect;
> 
> --------------------------------------------------------------------------
> -
> 
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds

==============================================================================

If you are not an intended recipient of this e-mail, please notify
the sender, delete it and do not read, act upon, print, disclose,
copy, retain or redistribute it.

Click here for important additional terms relating to this e-mail.
     <http://www.ml.com/email_terms/>

==============================================================================




More information about the FreeTDS mailing list