FreeTDS Development Group

[Frediano Ziglio <freddy77 AT gmail.com>]

2012/9/20 James K. Lowden <jklowden AT freetds.org>:
> On Wed, 19 Sep 2012 16:39:22 +0000
> Tony Esposito <Tony.Esposito AT region10.org> wrote:
>
>>       The ANSI standard error code 100 stands for 'no rows found',
>
> Let's not get ahead of ourselves.  ANSI isn't involved.  ODBC is
> Microsoft's.  It is the basis for the X/Open SQL CLI, which is
> "governed" by a vendor consortium. Most people call that ODBC, too,
> probably because that what it pretty much is, and maybe also because
> it's easier to say and remember.
>
> SQL_NO_DATA is defined by the standard as a symbolic constant.  Its
> value -- the numeric value it compiles to -- isn't mentioned in the
> standard.  It's up to each driver manager to supply values for such
> symbols.  Many, including unixODBC, choose 100, probably because
> Microsoft did.
>
> I think by recompiling bsqlodbc with ODBCVER=0x300, it will produce a
> "no rows returned" message.  You could try "export
> CFLAGS=-DODBCVER=0x300", then reconfigure, build, and install.  That
> should at least get you a valid message.
>
>> Given that this may be an 'isql' error and that 'tsql' works, would
>> the ODBC layer actually be working
> ...
>>       TDSDUMP file from an execution of bsqlodbc attached
>
> Sadly, no: the dump makes clear the server returned no data, just as
> the driver reported.  The dump shows normal behavior, see below.
>
>> Could it be that FreeTDS 0.91 was never fully tested against Sybase
>> Anywhere 10 since version 10 is so old?
>
> Oh, yes.  If someone else reading this message has used that
> combination, I hope he'll speak up.  You could be the first.
>
> You may well be wondering now why tsql (and bsqldb) returns data, and
> bsqlodbc and isql do not.  I'm wondering too, but I'll give you the
> low-down on the differences to help you sort it out.  Bear in mind I've
> never used Sybase Anywhere except experimentally.  I just know a little
> something about the protocol and the driver.
>
> When the client connects to the server, it sends a login packet.  That
> packet includes a field with flags, and one of those flags is a
> bit that indicates to the server the client is an ODBC driver.  That is
> the sole difference in the login packet: one bit.
>
> The connection has server-side properties, things affected by the T-SQL
> SET statement.  The server may set some of those properties in response
> to seeing the ODBC-driver-in-use bit.  The driver may also emit SET
> statements prior to issuing the first query.  In addition, TDS 5.0 has
> a capability-negotiation stage during connection setup, wherein the
> client and server each represent what they want and need.
>
> FreeTDS has a very simple view of all this: the server does as it
> likes, and the client requires very little.  As you can see from the
> dump, no SET statements are issued, and the Capability exchange is
> brief.
>
> Then we come to the SQL.  DB-Library and CT-Library (and tsql) simply
> accept the SQL from the application and squirt it out the wire.  ODBC
> can't be so simple: it constructs a stored procedure
>
> 0010 74 72 32 38 36 30 30 30-34 00 63 72 65 61 74 65 |tr286000 4.create|
> 0020 20 70 72 6f 63 20 69 6f-74 72 32 38 36 30 30 30 | proc io tr286000|
> 0030 20 61 73 20 73 65 6c 65-63 74 20 2a 20 66 72 6f | as sele ct * fro|
> 0040 6d 20 73 72 5f 73 74 75-5f 64 65 6d 6f 0a       |m sr_stu _demo.|
>
> What comes back looks right
>
> net.c:609:Received packet
> 0000 04 01 00 21 00 00 00 00-e7 0d 00 20 00 0a 69 6f |...!.... ... ..io|
> 0010 74 72 32 38 36 30 30 30-fd 00 00 01 00 00 00 00 |tr286000 ........|
> 0020 00                     -                        |.|
>
> token.c:555:processing result tokens.  marker is  e7(TDS5_DYNAMIC)
> token.c:555:processing result tokens.  marker is  fd(DONE)
>
> That's an E7 packet, and you can see we're behind in our documentation:
>
> http://www.freetds.org/tds.html#t231
>
> but we can parse it using Sybase's document, "TDS 5.0 Functional
> Specification Version 3.8", available on their website.
>
> e7      TDS_DYNAMIC
> 0d00    13 bytes in message
> 20      TDS_DYN_ACK, an acknowledgement
> 00      Status, TDS_DYNAMIC_UNUSED, none
> 0a      IdLen, 10 more bytes
> 69 6f 74 72 32 38 36 30 30 30   "iotr286000"
>
> So far, so good.  Now the driver executes the query.
>
> odbc.c:3496:SQLExecute(0x15084270)
> prepare_query.c:203:parsing 0 parameters
> odbc.c:3211:_SQLExecute(0x15084270)
> odbc.c:3216:_SQLExecute() starting with state 0
> odbc.c:3334:End prepare, execute
> ...
> net.c:741:Sending packet
> 0000 0f 01 00 1a 00 00 00 00-e7 0f 00 02 00 0a 69 6f |........ ......io|
> 0010 74 72 32 38 36 30 30 30-00 00                   |tr286000 ..|
>
> which is right, "e7 0f 00 02 00 " mean E7 packet, 15 bytes long,
> type TDS_DYN_EXEC, no status.  Comes back the answer:
>
> net.c:609:Received packet
> 0000 04 01 00 21 00 00 00 00-e7 0d 00 20 00 0a 69 6f |...!.... ... ..io|
> 0010 74 72 32 38 36 30 30 30-fd 00 00 01 00 00 00 00 |tr286000 ........|
> 0020 00                     -                        |.|
>
> In "e7 0d 00 20 00 ", that 20 is TDS_DYN_ACK, acknowledging the
> request.  Then
>
> token.c:555:processing result tokens.  marker is  e7(TDS5_DYNAMIC)
> token.c:555:processing result tokens.  marker is  fd(DONE)
>
> No rows returned.
>
> You might want to try this yourself.
>
>         create procedure iotr286000
>         as
>         select * from sr_stu_demo
>
>         execute iotr286000
>
> That will probably work; you're not using TDS_DYNAMIC packets.
>

Quite strange. The server accept the execute statement and reply
correctly with no error. The strange thing is that it didn't even
return the row format!
Now, why? Does the "create proc" confuse this version and server
create a procedure instead of executing the code inside? I always saw
this syntax but perhaps there is a capability bit that say to not use
this syntax. Or perhaps there is another option that say to not return
any rows and use cursor? I don't this so cause dynamic does not mix
that much with cursors in TDS 5.0.

> Recommendations:
>
> 1.  Make absolutely sure you're connecting to the same database
> on the same server with both tsql and bsqlodbc.  Use bsqlodbc to update
> a table, and verify your update is there with tsql.  Try "select
> @@servername, db_name()" on both servers.  This whole thing
> smells like misdirection.
>
> 2.  Inspect the properties of each connection.  Make them the same to
> see if that matters.
>
> 3.  Verify it works with Sybase's own driver, with the driver installed
> on a machine other than the server.
>
> If Sybase's driver works on the same database that FreeTDS doesn't, and
> you really, really want this to work, use tcpdump (or windump on
> Windows) to capture the tcp/ip traffic for both connections -- the
> working one with Sybase's driver, and the non-working one with
> FreeTDS.  Please be sure to use an account whose password you don't
> mind publishing for all time on the Internet. Post those dumps where I
> can look at them, and ... wait.  I will try to help you find the
> difference and construct a patch to fix it.
>
> I know full well the work involved.  That's why any for-profit venture
> would tell you to upgrade to the latest version (which will probably
> work).  But this is free software, where time isn't money, where anyone
> who wants to can join the party.
>
> Regards,
>

Frediano