MySQL Bugs: #37345: --ODBC--

Bug #37345	--ODBC--
Submitted:	11 Jun 2008 14:01	Modified:	29 May 2013 11:06
Reporter:	Ed Dempsey	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	Connector/ODBC 5.1	OS:	Windows (Server 2003)
Assigned to:		CPU Architecture:	Any
Tags:	ODBC, TEXT fields

Description:
Web applicaton (ISAPI/Delphi) using ADO internally to connect - using SystemDSN and ODBC connection string.  Production app that works well with ODBC 3.51 connector, as well as all MS products.  Partner installed MySQL 5 with ODBC Connector 5.1, broke several pieces of application.  Research shows that TEXT fields (memo) have difficulty writing/reading text.  Installed several debugging options on this server to try to isolate.  Results are that if a field is defined as a VarChar(8000) or smaller, then operation is fine.  If field is defined as a varChar(8192) or higher, or as TEXT, MEDIUMTEXT, etc., then data written shows up in the field as random binary.  Only difference is field definition.  Same data, same read/write methods.  Attempted to write data via MySQL Query tool, data appears to be written fine, but unable to read with ODBC connector.  

Customer is installing db/connector to work with ODBC 3.51, so not critical - but will need resolution to recommend MySQL for future installations.

How to repeat:
Use ODBC connector 5.1 on a table with a field defined as TEXT, write/read any text data via the ODBC Connector.  Using SystemDSN.

Hi Ed and thanks for your report.

You have hit one of the biggest fans of Delphi around :-) But, your report lacks both accuracy and substance...

Accuracy:
- MySQL server version
- Delphi version
- c/ODBC 3.51 and 5.1 version
- MDAC version

Substance:
- ODBC trace. See manual for details. I can not tell what Delphi (in fact BDE) does in background without this.
- Delphi test case.

Now, I can assure you Access, for example, is able to write TEXT via both c/ODBC. This is just to illustrate several points:
o) One should not use dead standards. Both BDE and ODBC have been euthanised.
o) Types you're having problems with are missing from MS products, ie. MSSQL can not have TEXT field as big as MySQL's.
o) Prior point raises a question about how Delphi maps those types.
o) ADO implementation in Delphi has been constant source of frustration for me, over the past 10+ years, that I actually wrote low-level library to access MySQL server. Refusing to talk of ADO as standard due to concern for my health.
o) BDE implementation of ODBC standard is faulty, search BugsDB for examples. I'll post just one that causes us pain; BDE presents itself as ODBC v3 compliant client and then calls deprecated ODBC functions.
o) Fact that c/ODBC 3.51 works and 5.1 does not is of no consequence here. We strive to make v 5.1 as close to ODBC standards as possible while 3.51 was not so strict (to put it mildly).

So, your "How to repeat" should read:
Use ODBC connector 5.1 on a table with a field defined as TEXT, write/read any text data via the ODBC Connector *from Delphi*. Using SystemDSN and old c/ODBC driver without option to limit field size to what MS SW can understand.

Looking on the bright side, there is a will to make things more compatible with Delphi here. So please provide me with, at least, info requested under "Accuracy" section and I'll see what we can do.

Hi Tonci,  Thanks for the feedback.  First, not using BDE or Delphi database tools.  On the Delphi side using TADOConnection/TADOQuery.  All read/writes are done via TADOQuery.

This is an 3 year old application installed on hundreds of sites.  Sites currently have mix of MS SQL Server (all versions/native ADO), MySQL using ODBC Connector 3.51, and MS Access (native ADO).  Some test sites using Oracle (Oracle ADO)  Can connect to Excel using native ADO, but not practical.  All using the same engine with different connection strings.  So the above is what currently works - no bugs with the above stuff.

The broken piece is using ODBCConnector 5.1.  MySQL DB Version 5.0.  Delphi 6 dll, using TADOConnection/TADOuery - runtime created components.  ADO Connection string is using SystemDSN created with ODBCConnector5.1.  Error reading/writing only fields defined as TEXT or VarChar with a width greater than 8191. (Only using TEXT fields with MySQL.  MS Engines use MEMO.  Apllication makes several adjustments in constants/field types/etc for different engines)  All other fields and Varchar width <= 8191 work fine.  Only using ODBC because I can't find ADO drivers for MySQL.  Not using BDE.  No special Delphi related DB client software is installed in client sites.  Only file required is single dll.  Not using any complex ADO Delphi calls.  Simple TADOQuery read/write.

Oh - forgot to add - if you want to install our stuff so you can put some debug tools against it - I can set you up with an eval license.  Send me an email address I can send details to

Sorry Ed, was on vacation. Now, I can not take your offer as you'll need support contract for that. Can you attach small but complete test case showing the problem?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Ed, this still needs small but complete test case.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.