Actually it's not a MySQL problem per se -- I've seen similar things before from the ODBC driver.
I'd first look at the schema for the MySQL db generated by DTS to see what column type the memo field was turned into -- if your memo fields generally only hold 255 characters or less, by all means turn them into varchar columns! If not, your life gets a little more complicated. If they really are long text fields, you should be using one of the TEXT types in MySQL for that column -- TEXT, MEDIUMTEXT, or LONGTEXT depending on the size. You *could* use the transform functions in DTS to generate a script to do the insert/update of the text data, but since this is a CF list, I'm guessing you know CF, so it might be easier (assuming this is a one-off conversion) to write a quick CF script that reads in the data and then inserts the memo data into MySQL using CFMX and it's JDBC MySQL driver which is more robust IMHO. While you're at it, take a look at how the boolean data fields converted -- if you're not careful, those fields can get mapped onto int or tinyint which are unsigned by default in MySQL -- so all the true values (-1) get truncated to 0 so everything becomes false in the column. Converting can be a little tricky since the data types are defined differently in Access and MySQL. Regards, John Paul Ashenfelter CTO/Transitionpoint [EMAIL PROTECTED] ----- Original Message ----- From: "Robertson-Ravo, Neil (RX)" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, March 27, 2003 9:35 AM Subject: RE: OT-DTS > This is a mySQL problem methinks and it would appear to be self-explanatory. > > -----Original Message----- > From: Greg Luce [mailto:[EMAIL PROTECTED] > Sent: 27 March 2003 14:29 > To: CF-Talk > Subject: OT-DTS > > > Anyone ever used DTS to migrate data from MS Access to MySQL? I've > installed the MyODBC driver and created an ODBC connection then I'm > using DTS to export the data from the specified access db to the mysql > db. It creates all tables, but when inserting Memo type columns (I > think) it gives this error: > > "Query-based insertion or updating of BLOB values is not supported." > > Do I need to customize the query and convert the data to another > datatype? I'm new to MySQL. > > Thanks, > > Greg > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
