Re: ct-options

Hi James,

I've used transactions successfully with FreeTDS 0.63, DBD::Sybase-1.05_01
and SQL Server 2000. In tests, I can cause an error and get a successful
rollback. SQL Server transactions work fine with DBD::Sybase-1.04 and
DBD::Sybase-1.05_01, but not with DBD::Sybase-1.05.

See http://www.well.ox.ac.uk/~barnwell/tests_2005-04-15/ for some test
programs and output.

I've also been looking at the test suite in DBD::Sybase-1.05_01. I agree
that the unit tests should work perfectly. On my first attempt, 131/209
tests failed, which was discouraging!

However, most of the errors are from placeholders, which don't appear to be
working. I've added non-placeholder versions of the tests (except for
xblk.t, which I have skipped as it seems heavily dependent on placeholders).

Also, some tests fail because they use Sybase syntax (e.g. " != NULL" rather
than "is not NULL"). I've added code to check the server string and choose
Sybase or Microsoft SQL.

This reduces the failures to 3/147 tests, or 1/136 if you don't use blobs.
This seems more manageable!

Revised tests are at http://www.well.ox.ac.uk/~barnwell/dbd_sybase_tests/

I hope this helps,
David Barnwell

Database Developer/Systems Administrator
Wellcome Trust Centre for Human Genetics
University of Oxford, Roosevelt Drive, Oxford OX3 7BN, UK



----- Original Message ----- 
From: "Lowden, James K" <[email protected]>
To: "FreeTDS Development Group" <[email protected]>
Sent: Thursday, April 14, 2005 8:15 PM
Subject: RE: [freetds] ct-options


> From: Thompson, Bill D (London)
> Sent: Thursday, April 14, 2005 6:12 AM
>
> James,
>
> The ct_options() implementation in Sybase is much more messy
> than I imagined.
>
> It turns out that there is a TDS 5.0 message which corresponds
> to the ct_options() call.

I had forgotten about that token.

> It would also appear to be the case that these settings are
> NOT kept in line with actual active server settings.

Blech.

> So, when you GET a setting using ct_options straight after
> connecting, before SETting any, they appear to be undefined.
> I tried this with the DATEFIRST setting. This should always
> have a value (between 1 and 7).  However ct_options returned
> zero as the value for this option, if I got it straight after
> connecting.  From this I deduce that you can only do a
> ct_options GET after a ct_options SET, if you hope to get a
> valid value.

Blech. Blech.  Agrees with the docs, though.  Not the most elegant part
of the library.

> I'll carry on investigating, but I'm tempted to just implement
> the option that we require for DBD::Sybase, which I would
> implement issuing SET CHAINED/IMPLICIT_TRANSACTIONS commands.

Well, if you want it to work with Microsoft servers, the TDS 5.0 packet
type is obviously no use.  (It would be nice to support it for Sybase
servers, because it might allow some settings to be changed even in the
midst of reading a result set.)

The goal, I think, should be to make the DBD::Sybase (and eventually
DBD::ODBC) unit tests work perfectly.  First up is transactions, even
though Michael pointed out a workaround.

As things stand, the only reason DBD::Sybase works at all is that
autocommit mode is always on: every BEGIN TRAN is quietly ignored, and
every COMMIT elicits an error message from the server.  Obviously, a
ROLLBACK wouldn't work out so "favorably".  Even without looking, I'm
sure some tests are failing for that reason.  Even if they're not,
there're bound to be programs "out there" either failing or waiting to
fail....

For reference, here's a current result (FreeTDS CVS HEAD,
DBD-Sybase-1.05):

Failed 8/12 test scripts, 33.33% okay. 129/208 subtests failed, 37.98%
okay.
Failed Test    Stat Wstat Total Fail  Failed  List of Failed
------------------------------------------------------------------------
-------
t/autocommit.t    0   139     5    2  40.00%  4-5
t/exec.t         12  3072    17   12  70.59%  5-6 8-17
t/fail.t        255 65280    12    9  75.00%  4 6-7 10-12
t/main.t        255 65280    29   15  51.72%  21 23-29
t/multi_sth.t   255 65280    43   56 130.23%  12 14 16-43
t/place.t       255 65280    13   11  84.62%  6-7 9-13
t/xblk.t          0   139    62  112 180.65%  6-62
t/xblob.t         7  1792    11   13 118.18%  4 6-11

I guess FreeTDS is some kind of hand grenade.  You know, "Close only
counts in horseshoes and hand grenades."

--jkl