up vote 1 down vote favorite
share [g+] share [fb]

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?

link|improve this question
50% accept rate
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 1 '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) was to enable the "Don't Optimize Column Widths" option in the ODBC settings - maybe give that a shot? – Shane Madden Sep 1 '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 1 '11 at 19:02
feedback

1 Answer

up vote 2 down vote

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.

link|improve this answer
Nice, good find! – Shane Madden Sep 1 '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 2 '11 at 19:35
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.