Skip to Content.
Sympa Menu

freetds - RE: [freetds] intermittent empty results

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Thompson, Bill D (London)" <bill_d_thompson AT ml.com>
  • To: "'FreeTDS Development Group'" <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] intermittent empty results
  • Date: Mon, 28 Jun 2004 14:36:06 +0100

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/>

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





Archive powered by MHonArc 2.6.24.

Top of Page