db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jean T. Anderson" <...@bristowhill.com>
Subject Re: Using DdlUtils to Migrate Databases to Derby (MySQL-to-Derby)
Date Mon, 13 Mar 2006 01:10:22 GMT
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:


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
> ** 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:tomdzk@gmail.com] 
> 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 <thomas.taylor@itqa.miami.edu> 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

View raw message