Thanks for this feedback, Thomas. I'll update Step 5 to mention reasons for tweaking the types in the generated schema xml file.
Also, over on ddlutils-user Tom Dudziak mentioned that he checked in a change that addresses needing to set the database name in the schema file and I'll update Step 5 for that as well: http://mail-archives.apache.org/mod_mbox/db-ddlutils-user/200603.mbox/[EMAIL PROTECTED] -jean Thomas J. Taylor wrote: > I was just testing out the DDLUtils program. It will be quite useful if it > can handle things like this, and hopefully preserve UTF-8 characters. > > In the spefic case of our databases, I would much rather have the process > fail then to truncate the data. > > Other observations about the migration: > 1. The MySQL LONG VARCHAR was converted to Derby LONG VARCHAR(32700) - > resulting in Data Trunctation Exception > 2. Derby LONG VARCHAR cannot be used in SQL comparisons > - so in our case, we use Derby VARCHAR(32700) for MySQL LONG VARCHAR, when > we need to compare values, and CLOB if we need 'long text' > > After changing the appropriate table descriptions in 'db-schema.xml' (the > XML file that contains the DDL) (change some LONGVARCHARs to CLOB, others to > VARCHAR(32672)), everything went through correctly and the data appears > correct - although I haven't confirmed specifically that UTF-8 characters > transferred coreectly. > > So, in the case of MySQL to Derby Migration, it is necessary to change the > 'db-schema.xml' for some LONGVARCHAR entries. > - If SQL comparisons are needed, then LONGVARCHAR should be changed to > VARCHAR(32672). > - If long text needs to be preserved, then LONGVARCHAR should be changed to > CLOB. > > ** With Derby's restriction on SQL comparison queries limited to VARCHAR > fields, there doesn't appear to be a way to migrate LONGVARCHAR (very long > text >32700) in MySQL to an equivalent Derby data type. > > Thomas > > -----Original Message----- > From: Thomas Dudziak [mailto:[EMAIL PROTECTED] > Sent: Sunday, March 12, 2006 6:32 PM > To: Derby Discussion > Subject: Re: Using DdlUtils to Migrate Databases to Derby (MySQL-to-Derby) > > On 3/13/06, Thomas J. Taylor <[EMAIL PROTECTED]> wrote: > > >>I tried running DDLUtils to convert one of our existing MySQL >>databases to Derby. It got to the point of importing data from the >>data.xml file but failed due to an 'Data Truncation' Exception >>converting from LONG VARCHAR to VARCHAR(32700). > > > Seems you try to insert more than 32700 bytes of data. Don't know about > Derby, but in MySQL you can switch the JDBC driver to warn instead of > throwing an exception by adding "jdbcCompliantTruncation=false" to the > connection URL. > > cheers, > Tom > >
