Skip to Content.
Sympa Menu

freetds - Re: [freetds] 1st select fails, 2nd works

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "David Barnwell" <david.barnwell AT well.ox.ac.uk>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] 1st select fails, 2nd works
  • Date: Thu, 15 Jul 2004 18:55:28 -0000

Hi Carl,

I had exactly the same problem using freetds-0.62.4 and SQL Server 2000.
Selects failed intermittently, but were OK when the users tried again. After
some investigation, I discovered that the problem occurs whenever the table
statistics are updated by the server. This happens from time to time after a
certain number of rows have been altered. Unfortunately, SQL Server returns
an additional status to say that this has happened, which confuses freetds.

If you drop and recreate your table, this will always happen on the first
select and not on the second. If you look at line 417 of your first TDS log
(freetds-17284-log.txt), you will see that SQL Server has returned an extra
5 bytes (hex 79 00 00 00 00) at the start of the data stream. This is the
additional status. It doesn't occur in the second log.

I believe that this problem has been fixed in freetds 0.63 (see recent
postings). However, if you are still using 0.62 there is a simple workround:
you can detect the return status in syb_result_type and retry the SQL if
necessary.

I use the following Perl code:
use DBI;
use DBD::Sybase;
...
my $dbh = DBI->connect($dsn, $user, $passwd);
my $sth = $dbh->prepare($sql);
$sth->execute;
$rows = $sth->fetchall_arrayref;
my $result = $sth->{syb_result_type};
$sth->finish;

This sets $result to:
4040 CS_ROW_RESULT data incomplete, only 1 row
4043 CS_STATUS_RESULT successful exec of a stored procedure
4046 (no name) successful SQL select

If $result == CS_ROW_RESULT, I retry the prepare, execute, fetchall_arrayref
and finish. So far, this has worked fine.

David Barnwell
Wellcome Trust Centre for Human Genetics
University of Oxford

----- Original Message -----
From: "Carl Edwards" <edwards AT vitesse.com>
To: <freetds AT lists.ibiblio.org>
Sent: Wednesday, July 14, 2004 9:58 PM
Subject: [freetds] 1st select fails, 2nd works


Hello,

I'm running 0.62.4 with DBI 1.42 against a MSSQL server (8.0.x).

Can anyone explain why running the same SELECT statement returns
different results the second time?

The first run of the perl script shows only 1 element is returned
but when the same SQL is rerun I get the expected 8 columns?

I've attached a zip file containing the freetds-pid-log.txt
and dbi-pid-log.txt for the two identical runs.

Any ideas about what might be happening would be appreciated,
or how I could restructure my DBI calls to minimize problems.

Thank You,
-Carl Edwards


Here is a snippet of the diff of the two freetds logs:

121 ! st_next_result() -> ct_results(4043) == 1 121 ! st_next_result() ->
ct_results(4040) == 1
122 ! ct_res_info() returns 1 columns 122 ! ct_res_info()
returns 8 columns

123 ! ct_describe(0): type = 2, maxlen = 4 123 ! ct_describe(0):
type = 1, maxlen = 50
124 ! describe() -> col 0, type 2, realtype 2 124 ! describe() -> col
0, type 1, realtype 1

130 ! 1 <- FETCH= [ 'COL(1)' ] ... 144 ! 1 <- FETCH= [
'FileName' 'UploadedDate' ...

132 ! dbih_setup_fbav for 1 fields => 0x45fcc0 146 ! dbih_setup_fbav for
8 fields => 0x4b6080



----------------------------------------------------------------------------
----


_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds





Archive powered by MHonArc 2.6.24.

Top of Page