[freetds] intermittent empty results

Bort, Paul pbort at tmwsystems.com
Mon Jun 28 10:37:36 EDT 2004


Bill, 

For what it's worth, I can report that DBD::Sybase (version 0.91) works with
the prepare method as long as there are no placeholders used. IIRC,
placeholder support was added in a later version. 

Hope this helps.
Paul

> -----Original Message-----
> From: Thompson, Bill D (London) [mailto:bill_d_thompson at ml.com]
> Sent: Monday, June 28, 2004 9:36 AM
> To: 'FreeTDS Development Group'
> Subject: RE: [freetds] intermittent empty results
> 
> 
> 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/>
> 
> ==============================================================
> ================
> 
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
> 



More information about the FreeTDS mailing list