MySQL Bugs: #40932: LONGTEXT fields are converted to text(255) in Access 97

Bug #40932	LONGTEXT fields are converted to text(255) in Access 97
Submitted:	21 Nov 2008 16:49	Modified:	15 Oct 2009 14:36
Reporter:	Stefano Sapienti	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1.5	OS:	Windows (XP)
Assigned to:	Lawrenty Novitsky	CPU Architecture:	Any

Description:
When linking, in Access 97, a table containing a LONGTEXT or TEXT field, the fields is shown as text (255) in table structure and, if modified it gives an error if data length is more than 255 charachfield type in Visual
Foxpro 7.0 and naturally data are truncated to 255 characters. 
This happens only with myODBC 5.x both on mySQL Server 4.x or 5.x, it works with myODBC 3.51 but I can't use it cause on mySQL server 5.x it gives some problems that are fixed in myODBC 5.x.

How to repeat:
- create a table with LONGTEXT or TEXT field
- create a MyODBC 5.x DSN
- open access 97 and link the above table using the above DSN
- look at the table structure, the LONGTEXT field is shown as a Text field of length 255 and it accepts only 255 chars.
- open access 2000 and link the above table using the above DSN
- look at the table structure, the LONGTEXT field is shown as a Memo field and it accepts more than 255 chars.

Hi Stefano and thanks for your report.

I believe your problem is result of Bug#26950 which is fixed as rev.813 in c/ODBC 5.1.6. Alas, as Access'97 is EOL-ed ~6-7 years ago and I can not test to confirm the suspicion. So please try latest build from http://downloads.mysql.com/snapshots/mysql-connector-odbc-5.1/mysql-connector-odbc-noinsta... and inform me of result. It is enough to replace myodbc3*.dll in System32 folder. Otherwise I'd be forced to close as duplicate.

Stefano, you already opened support issue for this, don't need bug report.

Hi, thanks for the quick answer.
I downloaded the package and placed all the myodbc5* files in \program files\mysql\connector5.1
In odbc manager the driver was correctly changed to 5.01.06 but the issue checking failed, the problem is still there.
Can I do something more?

P.S.: I don't understand what you mean with "you already opened support issue for this, don't need bug report"

Stefano, I will need ODBC traces for 3.51 and 5.1 latest versions to see where the difference is. Please attach them.

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".

This is actively worked on.

TEXT column is properly linked to MEMO field (MyODBC 5.1 used)

Attachment: linked_tables_screen2.jpg (image/jpeg, text), 152.14 KiB.

Sorry for the delay with working on this bug (we had to obtain a licensed copy of MS Access 97, which took longer than we expected). Finally, MS Access 97 has been installed, though not without problems related to newer instances of MS Access (2000 and XP) installed on the same machine. Therefore, I set up Access 97 on a clean (just installed) Windows 2000 in order to eliminate any possibility of influence from other software. I also installed MySQL Commnunity Server 5.0.67 and MyODBC 5.1.5.

However, as you can see on the screenshot, the text MySQL type is linked correctly to MEMO type in Access 97:

http://bugs.mysql.com/file.php?id=11145

I did not use any MyODBC options in the initial test, but even when trying different combinations of them I could not repeat the problem: TEXT or LONGTEXT column was displayed as MEMO and field contents (~10K of text) were successfully and fully retrieved from MySQL table in Access edit control.

CHARSET option in DSN should resolve the issue for MS Access 97.
Charset has to be set exactly to the same character set used in TEXT column (or in the table if it is inherited).

The proper GUI update is coming.

patch for windows GUI with CHARSET and STMT options

Attachment: charset_stmt_gui.diff (application/octet-stream, text), 7.31 KiB.

Additional patch - resetting no_catalog before connection.

Attachment: bug40932_additional.diff (text/x-diff), 1.36 KiB.

Posting here additional patch for review. If user checks "Disable catalog support", retrieving data for both dropdown lists(database and charset) fails (check that checkbox and try to open any of those lists). Patch resets no_catalog before connection.

Combined Bogdan's and my patch.

Attachment: bug40932_full.diff (text/x-diff), 8.02 KiB.

Patch pushed as rev #839

An entry was added to the 5.1.6 changelog:

In Access 97, when linking a table containing a LONGTEXT or TEXT field to a MySQL Connector/ODBC DSN, the fields were shown as TEXT(255) in the table structure. Data was therefore truncated to 255 characters.

See http://bugs.mysql.com/bug.php?id=61024.