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:
Category:Connector / ODBC Severity:S2 (Serious)
Version:5.1.5 OS:Microsoft Windows (XP)
Assigned to: Lawrenty Novitsky CPU Architecture:Any

[21 Nov 2008 16:49] Stefano Sapienti
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.
[21 Nov 2008 16:52] Tonci Grgin
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.
[21 Nov 2008 16:54] Tonci Grgin
Stefano, you already opened support issue for this, don't need bug report.
[21 Nov 2008 18:08] Stefano Sapienti
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"
[24 Nov 2008 13:52] Tonci Grgin
Stefano, I will need ODBC traces for 3.51 and 5.1 latest versions to see where the difference is. Please attach them.
[25 Dec 2008 0:00] Bugs System
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".
[25 Dec 2008 18:40] Tonci Grgin
This is actively worked on.
[27 Jan 2009 7:07] Bogdan Degtyariov
TEXT column is properly linked to MEMO field (MyODBC 5.1 used)

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

[27 Jan 2009 7:08] Bogdan Degtyariov
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:


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.
[4 Mar 2009 8:49] Bogdan Degtyariov
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.
[20 Mar 2009 10:13] Bogdan Degtyariov
patch for windows GUI with CHARSET and STMT options

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

[24 Sep 2009 12:22] Lawrenty Novitsky
Additional patch - resetting no_catalog before connection.

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

[24 Sep 2009 12:27] Lawrenty Novitsky
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.
[24 Sep 2009 12:44] Lawrenty Novitsky
Combined Bogdan's and my patch.

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

[7 Oct 2009 20:48] Lawrenty Novitsky
Patch pushed as rev #839
[15 Oct 2009 14:36] Tony Bedford
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.
[3 May 2011 13:17] MySQL Verification Team
See http://bugs.mysql.com/bug.php?id=61024.