db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas J. Taylor" <thomas.tay...@itqa.miami.edu>
Subject RE: Using DdlUtils to Migrate Databases to Derby (MySQL-to-Derby)
Date Mon, 13 Mar 2006 00:30:12 GMT
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: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



Mime
View raw message