MySQL Forums
Forum List  »  Connector/ODBC

Using Enum fields in MS SQL Server Studio Linked Server
Posted by: Jason Zech
Date: October 05, 2020 11:37AM

Hi all. I've been using the connector for a few years now to push and pull data between a MS SQL Server database and a couple different MySQL databases. I set up a Linked Server, then using OPENQUERY I create the views I need and I write my selects, updates, and inserts against those views. Works like a dream.

However, I'm trying to integrate with a new MySQL database built by a vendor which uses the ENUM datatype, which is causing me trouble.

When I try the OPENQUERY I get a weird error:

OLE DB provider 'MSDASQL' for linked server 'MYSQL_DATABASE' returned data that does not match expected data length for column '[MSDASQL].EnumDataField'. The (maximum) expected data length is 10, while the returned data length is 8.

I can fix this by converting the ENUM field to a CHAR in the query and it works ok.

But now I need to insert or update that ENUM field, and I cannot figure out how to do it. If I convert the datatype on the view, I can't use that view to insert or update.

Is there a way for me to work with ENUM fields in the connector? Especially a way for me to do INSERT or UPDATE?

Thank you!
jz

Options: ReplyQuote


Subject
Written By
Posted
Using Enum fields in MS SQL Server Studio Linked Server
October 05, 2020 11:37AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.