MySQL Bugs: #9781: returned SQL

Bug #9781	returned SQL_Type on WKB query
Submitted:	9 Apr 2005 11:11	Modified:	3 May 2007 22:07
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51	OS:	Windows (WinXP)
Assigned to:	Jim Winstead	CPU Architecture:	Any

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.