FreeTDS Development Group

["David Barnwell" <david.barnwell AT well.ox.ac.uk>]

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