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:Microsoft Windows (WinXP)
Assigned to: Jim Winstead CPU Architecture:Any

[9 Apr 2005 11:11] [ name withheld ]
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.
[26 Apr 2005 3:52] Stuart Hudson
I have stumbled across the same problem via JDBC (Bug #10166) and agree (asBinary 'should' return a BLOB)
[26 Apr 2005 3:52] Stuart Hudson
I have stumbled across the same problem via JDBC (Bug #10166) and agree (asBinary 'should' return a BLOB)
[2 Feb 2006 2:30] Peter Yuill
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.
[25 Apr 2006 17:22] P Eger
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!
[12 Mar 2007 8:56] Bugs System
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
[3 May 2007 22:07] Jim Winstead
This bug was apparently already fixed. I've added a regression test to the test suite.