Re: osql and isql -- how to test ODBC config?

On Wed, 19 Sep 2012 16:39:22 +0000
Tony Esposito <[email protected]> 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.  

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, 

--jkl