2

Working on setting up a new MySQL database for a website, and I'm trying to figure out how to transfer over long strings.
I have quite a few MSSQL tables that have Varchars ranging from 500 to 2000.
Obviously in MySQL these have to be stored in a Text field, which is a BLOB.
If I query a table on the MySQL with a Text field, it seems to return the data just fine. But if I Try to do an Insert into the table with the Text field, it returns an error saying [OLE/DB provider returned message: Query-based insertion or updating of BLOB values is not supported.]

Is there a way to make this work, or will I have to do it by flatfile transfer?

AndyD273
  • 209
  • 1
  • 10
  • It might be a duplicate, but I don't think so, and the answer is not useful. The differences are that my MSSQL field is already a Varchar, and actually for testing I'm putting in with an insert statement `insert into MYSQL...Test(Name,Details)values('Test','Test!!')` Which is not longer than MySQL Varchar(max) length. Also, I can't change the MySQL datatype to Varchar everytime I want to do an insert because it would crop the existing data. – AndyD273 Sep 01 '11 at 17:04
  • 1
    Yeah - I don't like the answer on there either - seems like the same root cause, though. All the research I've done says that this is an issue with the MySQL ODBC driver. One workaround I saw proposed in a few places ([here](http://bugs.mysql.com/bug.php?id=12446)) was to enable the "Don't Optimize Column Widths" option in the ODBC settings - maybe give that a shot? – Shane Madden Sep 01 '11 at 17:21
  • ODBC 3.51.28 doesn't have an option for Don't Optimize Column Widths. I think you are right about it being an issue with ODBC, but it looks like the other version (5.1.8) might solve the problem. – AndyD273 Sep 01 '11 at 19:02

2 Answers2

4

So, after looking around a lot, I noticed that there are two versions of the ODBC Connector, 3.51.28, and 5.1.8 ( http://dev.mysql.com/downloads/connector/odbc/ ).

Not sure why they are maintaining two versions, but whatever. Installed it, reset it up in DNS, and was able to insert 1000+ character string in.

Now hopefully there aren't any other bugs with this new one.

On a side note for future generations, when I set it up in the DNS I didn't check any of the boxes in Details options. I don't know if there are any that need to be checked, but I guess I'll figure that out when something doesn't work.

AndyD273
  • 209
  • 1
  • 10
  • Nice, good find! – Shane Madden Sep 01 '11 at 19:05
  • Weird bug. When a MEDIUMTEXT field is the last column, it refuses to set the ID column to anything other than 0 when an insert is started over the linked server. If I change the field order so that any non TEXT field is last (Int, Varchar, whatever) it inserts the ID correctly. – AndyD273 Sep 02 '11 at 19:35
4

This problem occured few days ago in my MySQL linked server. ODBC Driver version 5.1.

MySQL connection is made using system DSN, not connecion string in linked server properties. The solution: in DSN connection options switch to tab "Cursors/Results" and uncheck "force use of forward-only cursors" box.

Solution based on this: http://support.microsoft.com/default.aspx?scid=kb;en-us;175245

BartekR
  • 141
  • 1
  • 4