MySQL Bugs: #9781: returned SQL

Bug #9781	returned SQL_Type on WKB query
Submitted:	9 Apr 2005 13:11	Modified:	4 May 2007 0:07
Reporter:	[ name withheld ]
Status:	Closed
Category:	Connector/ODBC	Severity:	S3 (Non-critical)
Version:	3.51	OS:	Microsoft Windows (WinXP)
Assigned to:	Jim Winstead	Target Version:

Description:
Hi, 

I'm not quite sure if I found a bug and if it's a already known problem I apologize in
advance, but I couldn't find any infos in the change history or in the bug list.

I use VC++.net and ODBCDataReader to read query results. My server is MySQL 4.1.11 and
the connector is MyODBC 3.51.10.

I want to query WKB from a spatial column and I expect to receive a BLOB, which means a
LONGVARBINARY in .net, if I'm right.

So if I do "SELECT g FROM table;" (where g is the geometry column) I receive a BLOB with
the internal geometry format. That works fine.

But when I do "SELECT AsBinary(g) FROM table;" in order to receive the WKB, I receive a
VARCHAR, although the documentation says WKB is delivered as a BLOB.

The problem is that .net maps VARCHAR to String type and I have the problem to
differentiate between "normal" VARCHAR fields, which contain simple text, and a received
WKB from a geometry column. Workaround is possible I think, but if I would get a BLOB for
WKB this workaround wouldn't be necessary.

How to repeat:
Execute a query like "SELECT AsBinary(g) FROM tabel;" where g is the geometry column via
ODBC. Check the returned SQL_TYPE by the ODBC function SQLDescribeCol().

Suggested fix:
So the "AsBinary()" -query should return a LONGVARBINARY instead of VARCHAR.
When I understand the documentation of 4.1.11 correctly the WKB should be returned as a
BLOB anyway.

I have stumbled across the same problem via JDBC (Bug #10166) and agree (asBinary 'should'
return a BLOB)

I have stumbled across the same problem via JDBC (Bug #10166) and agree (asBinary 'should'
return a BLOB)

I think there is some confusion about this bug report. The problem is not with
Connector/ODBC or Connector/J (equally affected), but rather in the DBMS itself. The
asWKB() function returns MYSQL_TYPE_VAR_STRING instead of BLOB. All modern Connector
versions attempt to convert the 'string' value on the client platform, corrupting the
WKB. This is an extremely serious bug for GIS users. While the bug remains WKB is
completely unusable with modern Connectors. Older versions of Connectors (eg J 3.0) did
not attempt to convert binary data, so anyone needing to use WKB must stick with
Connectors that are well out date.

Hello, has anybody found a workaround or fix for this? It makes the WKB format, and thusly
precise GIS data impossible (as the WKT format is lossy). Help!

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21699

This bug was apparently already fixed. I've added a regression test to the test suite.